Providing group totals within nested groups.

Often a need arises with reports to group data.
Grouping data provides the user with the ability to see records grouped based on shared values for certain fields, “Product Name” for example.
As helpful as grouping may be, most people will need metrics to go along with those grouped records.
Providing total values based on aggregation helps the user understand at specific points in time how well goals are being achieved.
However, due to the complexity of grouping records and creating totals, this task may be confusing – something for which I will hopefully provide clarity in this article.

I’m going to start with a simple dataset from the AdventureWorks database that I’ve configured to display some data related to sales.

Next, I will drag unto the design surface a table and remove all but two columns – they will be re-added when I build groups.

From the dataset, I will drag the two fields on which I will not create a group and on which I will apply aggregation to display totals.

Now I’m ready to build my groups.
I will drag each of the these three fields from the dataset unto to the Row Groups field.
Notice the order in which I placed these fields and their display in the table – the order they appear in the Row Groups determine their order in the table.

As you can see, my report displays records grouped by Name, Product Cat Name, and Color.

For my first metric, I would like to display Total values for the Unit Price and Order by Qty fields.

As you can see for each group of records, I now have total values display above Unit Price and Order Qty.

I would like to see total values for the entire report.
Therefore, I will repeat the same process for adding total values for the Product Cat Name field.

Next, I’ll modify the Textbox properties to apply the proper currency format.

Next, I wish to see total values for each row and the total values.

First, I will add a new right column to display these values.
Then, I will add an expression to these fields that performs a Sum() on the Unit Price and Order Qty fields.

Example Report

For the Total fiels, I’ll apply a similar expression, except I’ll use the RunningValue() function and denote the proper scope on which it should reset.

Example Report

After applying the same expression for the report Total field (top row), I now have total values for all fields.