Sum Help

[Lightbug3]

Hi all!

I am using Buisness Objects XI.

My report:
I have a crystal report that lists a members claims for a specific date range. In the details section, I have 3 formula fields -- ExcludeDrugCost, ExcludeGroupBillAmount, ExcludeCopay. In the Group footer I sum all three of these fields.

My problem:
In the Group header field I am suppose to show how much money is going to each section of their plan. To do this I need to check to see if the sum of the ExcludeDrugCost field is greater than 3000 for each claim. When it is, I need to know what the sum of the ExcludeCopay field is at that moment and put it in a field to use in my group header. I am not sure how to do this.

Example:
Date ExcludeDrugCost ExcludeGroupBill ExcludeCopay
1/1/2006 1000.00 950.00 50.00
2/1/2006 1500.00 1400.00 100.00
3/5/2006 1000.00 950.00 50.00
3/8/2006 500.00 430.00 70.00

For this member, the group header should show Copay: 200.00; because the ExcludeDrugCost equals 3000 on the third claim.

Date ExcludeDrugCost ExcludeGroupBill ExcludeCopay
1/1/2006 100.00 950.00 50.00
2/1/2006 150.00 1400.00 100.00
3/5/2006 1000.00 950.00 50.00
3/8/2006 500.00 430.00 70.00

For this member, the group header should show Copay: 270.00; because the ExcludeDrugCost never equals 3000.

Thanks.
Danielle

 

[Madawc]

Right-click on ExcludeDrugCost, choose Insert > Summary. It will put a total in the group footer, but you can move it to the group header and also test it in the group header.

This assumes you are adding everything in the group. If it is selective, a running total, it wouldn't be known until the group footer. You'd have to do some dodge like a subreport, very inefficient.

Questions about sums etc. are best asked in Crystal Reports 1 Formulas

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[mocgp]

You can set up a running total on the ExcludeDrugCost column(RT1), then another running total on the ExcludeCopay column (RT2). In RT2, in the Evaluate section, click the "Use a formula" button and enter the formula RT1<=3000, then reset on your group. This will sum your ExcludeCopay column up to the $3000 point and identify your target value. I'm not sure if this value can be moved to the header. My guess is that it can not.

 

[Lightbug3]

Thanks for your help! I ended up using the Running total... and I had to switch things around so it was in the footer.