Supressing two groups

[Beth_a_ny]

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

 

[lbass]

You should be able to use a section suppression formula like this for each of those sections:

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

 

[Beth_a_ny]

Hi LB

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

 

[lbass]

You have to eliminate the running totals. To see whether you might be able to replace these (you have two that I can see) you would need to show the specific setup of each.

-LB

 

[Beth_a_ny]

The running total for Debt is as follows,

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

 

[lbass]

Why are the Month formulas accurate despite repeated records? I'm wondering whether you could create conditional formulas for debt and credit limit that return only one value per ID so you can insert sums on the formulas at the Group 1 level, e.g., if there is only one value per time period.

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

-LB

 

[Beth_a_ny]

The detail of the report has one row for each line of a shop order so the months are correct and not repeated, each row then finds the credit limit and open balances from the customer master, it does this for each row so they are repeated over and over.

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

 

[lbass]

Is the debt the same for each month? Is the credit limit the same for each month?

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

 

[Beth_a_ny]

Hi LB,

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

 

[lbass]

Try this to get one value of debt and credit limit per group:

//{@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

 

[Beth_a_ny]

Hi LB,

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

 

[lbass]

Counts won't work in this situation because they would be evaluated too late for use in suppression of previous sections.

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

 

[Beth_a_ny]

Hi,

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

 

[lbass]

If you explain what you are having trouble with, I'll walk you through it.

-LB

 

[Beth_a_ny]

Hello LB,

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