• Home
  • Blog
  • SSRS
  • Adding Totals and Subtotals to Reporting Services Reports - SSRS

Adding Totals and Subtotals to Reporting Services Reports - SSRS

One of the common client requirements when producing a Table or Matrix report is to include totals and subtotals. Totals and subtotals are simple to add, but they aren't always intuitive. As a result, we hope you find this brief article to be informative.

Adding totals and subtotals

In this Try it, the report from the previous section is modified to add totals to each grouping level, make the different grouping levels expandable, and colours are applied to the different levels.

The report will initially show a list of years along with the grand total for that year. The user will be able to click on a + symbol to expand the year into months, again showing a total for each month, as well as the grand total for the entire year. The user will be able to further expand a month to view the sales by each sales person in that month.

If you would like to become an SSRS (SQL Server Reporting Services) certified professional, then visit Mindmajix - A Global online training platform: "SSRS Training and Certification Course". This course will help you to achieve excellence in this domain.

The technique for expanding summary rows to show further details may be familiar if you’ve seen grouped data in Excel.

The data displayed in a multi-level report can become tricky to read because of the dynamic nature of the layout. For this reason, it can be helpful to apply formatting such as colours and font styles to different group levels to help the user tell them apart.

group properties

Related Page: SSRS – How To Create Dynamic Reports Using Parameters

Try it

1. In the MultipleLevel report there should be two empty cells in the TotalDue column, immediately below the header row. In each of these, use the field selector icon (hover over the cell to see it) to select TotalDue. These will automatically be converted to SUM functions.

2. Click each one of these new cells in turn and look for the vertical orange bar on the report. This shows which group each cell belongs to.

Frequently Asked SSRS Interview Questions & Answers

3. Preview the report to confirm that there are now cells showing the totals by year and by month, as well as the employee totals.

TotalDue column

4. Back in Design view, in the Row Groups panel at the bottom of the screen, rightclick on Detail and choose Group Properties.

5. Open the Visibility page and select the Hide option, and Display can be toggled by:
Month.

6. Click OK.

MindMajix Youtube Channel

7. In the Row Groups panel at the bottom of the screen, right-click on Month and choose Group Properties.

8. Again, open the Visibility page and select the hide option, but this time specify that the Display can be toggled by
Year.

9. Click OK.

10. Preview the report to confirm that the groups are now collapsed and have plus buttons to expand them. Also check that the totals are visible for each level of grouping.

each level of grouping

11. Back in Design view, select each row in turn, using the grey row selector boxes and set a different background colour using either the Properties panel on the right of the screen, or the toolbar button. This will make it easier to see the different groups on the report and also to match totals to grouping levels.

12. Preview the report.

Explore SSRS Sample Resumes! Download & Edit, Get Noticed by Top Employers!   Download Now!

List of Related Microsoft Certification Courses:

 SSIS Power BI
 SSAS SQL Server
 SCCM SQL Server DBA
 SharePoint BizTalk Server
 Team Foundation Server BizTalk Server Administrator

 

 

Course Schedule
NameDates
SSRS TrainingOct 08 to Oct 23View Details
SSRS TrainingOct 12 to Oct 27View Details
SSRS TrainingOct 15 to Oct 30View Details
SSRS TrainingOct 19 to Nov 03View Details
Last updated: 03 Apr 2023
About Author

Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.

read less