Add a subtotal and Grouping to an existing report in SSRS

In this blog we cover how to add a Grouping to an existing Report. For the original report creation steps please visit this blog. In our report we see a list of nomination id by SPOC , but the list is very huge so we need to group and summarize the data.

First we go to design mode and identify the grouping for the details, right click the group and select add total > Before (before means above the actual detail).

Notice a new row is added to the table above


The nominations show up as a sum of the numbers, ideally it should be a count so we now change the formula and move it one level up. Right click the cell for Sum of Nom_id

Select Expression and change the text Sum to Count in the below pop up

Once you press OK on the above pop up you will see the formula has changed to Count. Select the cell cut and paste it to the cell above.

Select the newly empty row by right clicking it as shown below and then choose Delete rows

If you click Preview on the tab on the top it would now look like below

 

Next we need to hide the visibility of the details and show them only when the name of the SPOC is clicked. Go back to design mode and right click to get the group properties of the details as shown below

In the below pop up , select visibility and change the default state to Hide and then check the box to toggle the visibility by clicking the report item SPOC (which is the Cell that contains the name of the SPOC).

Press OK and go to preview mode to see the new layout

 

Toggle the visibility by pressing the name the SPOC.