Adding Totals and Subtotals to Reporting Services Reports - SSRS

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.

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.

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.

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.

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: