## Supressing two groups

## Supressing two groups

(OP)

Hi I am new here and new (self taught) to crystal.

I am trying to supress group two footer if group one footer is supressed, I can't find an easy way or can't find a formula that works. I have googled for ages!!

Here is a summary of my report

Group 1 Header: Customer Name

Group 2 Footer: Cust ID - Debt - Credit limit - Month 1 Ship - Month 2 Ship - Month 3 Ship - Spare(+credit limit - debt - Month 1-3 Ship)

Group 1 Footer: Cust ID - Debt - Credit limit - Month 1 Ship - Month 2 Ship - Month 3 Ship - Spare(+credit limit - debt - Month 1-3 Ship)

Each customer name will have up to 3 ID's as we deal with customers in 3 currencys

So the values in Group 1 footer are the sum of the items in group 2 footers.

What i am trying to do is only show customers when "spare" in group 1 footer is less then 200, so i need G1H, G2F and G1F supressed.

I have tried various differant formulas in the section expert but i can't get the Group 2 footers to supress if its value in less than 200.

Please help!!

Thank you

Beth

I am trying to supress group two footer if group one footer is supressed, I can't find an easy way or can't find a formula that works. I have googled for ages!!

Here is a summary of my report

Group 1 Header: Customer Name

Group 2 Footer: Cust ID - Debt - Credit limit - Month 1 Ship - Month 2 Ship - Month 3 Ship - Spare(+credit limit - debt - Month 1-3 Ship)

Group 1 Footer: Cust ID - Debt - Credit limit - Month 1 Ship - Month 2 Ship - Month 3 Ship - Spare(+credit limit - debt - Month 1-3 Ship)

Each customer name will have up to 3 ID's as we deal with customers in 3 currencys

So the values in Group 1 footer are the sum of the items in group 2 footers.

What i am trying to do is only show customers when "spare" in group 1 footer is less then 200, so i need G1H, G2F and G1F supressed.

I have tried various differant formulas in the section expert but i can't get the Group 2 footers to supress if its value in less than 200.

Please help!!

Thank you

Beth

## RE: Supressing two groups

Sum({@spare},{table.customername})<200

If this doesn't work, please post your actual formula for {@Spare} and also show the content of any formulas nested within that formula.

-LB

## RE: Supressing two groups

If i try this it tells me that it cannot be summerised.

This is the formula for Group 2 "spare"

+Maximum ({SOFCM.CREDIT_LIMIT}, {SOFCM.CUSTOMER_ID})-Maximum ({@Debt}, {SOFCM.CUSTOMER_ID})-Sum ({@Month 0}, {SOFCM.CUSTOMER_ID})-Sum ({@Month +2}, {SOFCM.CUSTOMER_ID})-Sum ({@Month +1}, {SOFCM.CUSTOMER_ID})

Maximum is used for debt and credit limit as in the detail these values are repeated on each line of shipments, the "debt" formula is open invoices less unapplied cash.

This is the formula for Group 1 "Spare"

-{#Debt}-Sum ({@Month +1}, {SOFCM.CUSTOMER_NAME})-Sum ({@Month 0}, {SOFCM.CUSTOMER_NAME})-Sum ({@Month +2}, {SOFCM.CUSTOMER_NAME})+{#Cred lim}

Thank you for your help

## RE: Supressing two groups

-LB

## RE: Supressing two groups

Field to summerise: @Debt

Evaluate - on change of field - SOFCM.customer_ID

Reset - On change of fiels - SOFCM.customer_name

For credit limit is is exactly the same but summerise @crd lim.

Is there another way to get the total of the group 2 footers into the group one footer and therefore get ris of the running totals?

Thanks

Beth

## RE: Supressing two groups

You need to show the content of at least one of your month formulas AND the exact content of your {@debt} formula.

-LB

## RE: Supressing two groups

The debt formula is like this....both fields are from customer master.

-{SOFCM.LC_UNAPPLIED}+{SOFCM.LC_CURRENT_BAL}

Month formulas are like this....everything is from the shop order

if ({SOFOM.SO_STATUS} = "O" ) and ({SOFOD.SO_LINE_STATUS} = "O") then (if month({SOFOD.REV_SHIP_DATE}) = month(dateadd("m", 1, currentdate))

and year({SOFOD.REV_SHIP_DATE}) = year(dateadd("m", 1, currentdate))

then ({SOFOD.LC_AMOUNT})) else 0

## RE: Supressing two groups

If so, you can just substitute debt (or cr limit) for amount in the "then" clause. Then you can use a summary on that in formulas at whatever group level you want.

It is hard to visualize the data, so if this suggestion doesn't work, please show sample data at the detail level with expected results at the group levels so I can see what repeats where--clearly labeled.

-LB

## RE: Supressing two groups

I think your suggestion will still end up with some repetitions.

Here is a very simple version of what the details look like, it can have many more lines for some customers, also more ID's but this is the basic of how it works.

And this is how I would want that data to display

If the orange square was higher than -200 then I would want that whole customer supressed.

Thanks so much for your help

Beth

## RE: Supressing two groups

//{@debtper):

If {@month0}+{@month1}+{@month2}=0 then {@debt}

Repeat for credit limit. Then replace the running total with sum({@debt},grouplevel1or2). Repeat for credit limit.

-LB

## RE: Supressing two groups

This will not work as some customers have multiple lines with 0 in month 0 1 and 2.

Is there away to put a count on each line of detail? if so I could do as above but if row number =1.

Not sure if this is possible just thinking in my head!!

Thanks

Beth

## RE: Supressing two groups

This is a little kludgy, but it will work. Save your current report, as is, under a different name, e.g., add "-sub" at the end of the name. Next insert a new GH_1 section in the original report. Then in design mode, move the section so that your current GH1 becomes GH_1b.

Insert the subreport into GH_1b and link it to the main report on customername.

Next, go into the subreport and create a formula:

//{@sharedspare}:

Whileprintingrecords;

Shared numbervar shsp := {@spare-grp1};//the name of your current grp1 spare formula

Place this in the Group#1 footer.

Then create a reset formula to be placed in the subreport report header:

Whileprintingrecords;

Shared numbervar shsp := 0;

Now in the main report, use a suppression formulas in the section expert :

Whileprintingrecords;

Shared numbervar shsp;

Shsp < 200 //I'm not sure whether you mean less than 200 or less than -200

Do you know the steps to make the subreport and the GH_1a section disappear? You can't suppress the section or the subreport directly. Let me know if you need me to walk you through those steps.

-LB

## RE: Supressing two groups

I don't know how to do much of the stuff in your last reply, I will see what I can work out/google tomorrow.

Will let you know again if I get stuck.

Thank you very much

Beth

## RE: Supressing two groups

-LB

## RE: Supressing two groups

I worked the whole thing out with the help of google and its worked perfectly.

Thank you so so so much for all your help, I really appreciate it.

Beth