SSRS: Nested Groups with Totals

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.

1-ssrs-nested-grouping-totals

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.

2-ssrs-nested-grouping-totals

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.

3-ssrs-nested-grouping-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.

4-ssrs-nested-grouping-totals

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

5-ssrs-nested-grouping-totals

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

6-ssrs-nested-grouping-totals

7-ssrs-nested-grouping-totals

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

8-ssrs-nested-grouping-totals

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.

9-ssrs-nested-grouping-totals

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

10-ssrs-nested-grouping-totals

11-ssrs-nested-grouping-totals

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.

12-ssrs-nested-grouping-totals

Example Report

13-ssrs-nested-grouping-totalsFor 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.

14-ssrs-nested-grouping-totals

Example Report

15-ssrs-nested-grouping-totals

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

16-ssrs-nested-grouping-totals

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s