Let's say I have two groups.
Group 1 = Location
Group 2 = Product
I have a bunch of calculations on group 2's footers. One thing I want to be able to do is is get a cumulative sum of all "Product" quantities for each "Location", and display the actual cumulative value on Group 2's footer instead of Group 1's footer. I am using a running total due to the fact that I also have a separate record evaluation condition I must have.
For example, Location ABC has products Apples and Oranges. There are 10 Apples and 12 Oranges in location ABC. So the total cumulative value I'm looking for is 22. This number is easy to obtain obviously using running totals, or even a simple sum formula.
What I'm trying to accomplish is that I am actually trying to do a separate calculation on Group 2's footer using that cumulative value. The problem is, the running total will only give me the cumulative value related to Group's 2 cumulative value and not Group 1's cumulative value.
For example, if I want to accomplish a percentage calculation by dividing the number of Apples (10) against all product count (22), the formula would be:
Sum of Apples / Running Total. In all sense, it should be 10/22. The actual calculation Crystal is outputting is 10 / 10... which is 10 apples divided by total number of group 2. Unfortunately, despite the fact that I set the running total's reset on change of Group 1, this cannot be done in group 2's footer.
I hope I'm making sense here but... Any way I can get around this?
I thought about this and I think a better question to ask is...
How can I create a formula that sums a field based on multiple criteria?
It needs to match a certain group level (in this case, group 1) and follow along the evaluation formula I have in the running total:
if {?Report Type} = 'Calendar Year' then {FuelAnalysis.CAL_YEAR} = {?YearPrompt} else if {?Report Type} = 'Fiscal Year' then {FuelAnalysis.FISC_YEAR} = {?YearPrompt}
Only reason the running total isn't working is because if there are multiple group 2 items, the running total will not sum the remaining records until AFTER the subsequent groups are displayed.
IE:
Group 2, Item 1: Apple (has 10) -- The running total will show 10
Group 2, Item 2: Orange (has 12) -- The running total will show 22.
I need item 1 to show 22 instead of 10... 22 being the sum of all items belonging to group 1.
Add a comment