Report Desk: Understanding SumValuesInGroup Aggregates

Report Desk: Understanding SumValuesInGroup Aggregates

Release Date: 10/18/2022
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




    • Related Articles

    • Report Desk: Report Footer

      Release Date: 4/28/23 Version: 8.6 The purpose of report footers in User Defined Reports is to provide the ability to report aggregate information (sum, average, min, max, etc.) for the report. For example, in a Salesman list, a report footer can ...

    • Report Desk: Documentation Roadmap

      Release Date: 4/28/23 Version: 8.6 Setup and Configuration The following articles explain initial setup and ongoing maintenance of Report Desk: Report Desk: Setup and Configuration : Initial installation and ongoing maintenance. Elliott V8.6 ...

    • Report Desk: Groups

      Release Date: 4/28/23 Version: 8.6 Groups Overview Data in a report can be sorted and organized with one or more groups subdividing the information. Groups can include one or more Group Header lines, where information about the group, particularly ...

    • Report Desk: Column Formatting

      Release Date: 4/28/23 Elliott Version 8.6 Changing Column Format When you are in the Report Desk designer and you drag a column one of the Line grids, a default column format will appear. You may change the format of the column to another type by ...

    • Report Desk: Telerik Functions

      Release Date: 4/28/23 Elliott V8.6 Telerik Functions in Report Desk In addition to the Aggregate functions that are automatically mapped to Telerik functions in Group Headers and Footers and Report Footers, you may also use built-in Telerik functions ...