Report Desk: Understanding SumValuesInGroup Aggregates
Elliott Version: 8.6 and Higher
Background
The Sum aggregate is useful for calculating the sum of a value in all report rows. For example, a detail line in a report might contain a column for Quantity On Hand. You might use the Sum aggregate in a Group Header, Group Footer, or Report Footer to add up the Quantity-On-Hand value for all the detail rows in a group or in the entire report.
Using the Normal Sum Aggregate
To use the Sum aggregate, select a column that represents a numeric value (in a report footer, for example) and press the Info icon in the grid:
That brings up the Update Aggregates dialog, where you can select the Sum aggregate:
Now, when you save the report and run it, you will get a value in that column in the Report Footer that sums the detail lines:
In this case, the report designer was attempting to sum the Sales-Amt for the two items in the Report Footer. But you can clearly see that the amount should be 114.00 instead of 142.00. What is going on here?
It turns out that there are a total of four detail lines (underlined in blue). The Sum aggregate actually added the 14.00 amount 3 times (once for each detail line) and the 100.00 amount 1 time (once for each detail line). Clearly, that is not a total that makes any sense.
Using SumValuesInGroup1 Aggregate in a Report Footer
What is happening in the above example is that the amount in the Group Header is being added to the sum each time a detail line is displayed instead of each time a Group Header is displayed. We have added some new aggregates to solve this problem.
In the footer line, click the Info icon to bring up the Updates Dialog box. Notice that you have an option in the drop down for an aggregate called SumValuesInGroup1 :
When you use this aggregate, it will only add to the sum each time Group Header 1 is displayed.
The report will have the correct totals:
The aggregate SumValuesInGroup1 only appears in the Update Aggregate dialog because this functionality is only necessary when processing Report Footers. There will be a separate SumValuesInGroup aggregate for each Report Group (SumValuesInGroup1, SumValuesInGroup2, etc.).
JEG
Programs: EL860RD, SYRPTUDR, UDRDesign