Add Total and Subtotal to SSRS Report

In this article, we will show you how to Add Total and Subtotal to SSRS Report. It’s one of the standard requirements from the Client end while you are designing the Table or a Matrix report.

To explain the steps involved in adding Total and Subtotal to the SSRS Report, we are going to use the below-shown DataSet. Please refer to the Embedded Data Source and Dataset articles to understand the steps involved in creating the Embedded Data Source and Dataset that we used for this report. The Custom SQL query that we used in the Dataset is:

-- Add Total to SSRS Report
SELECT [FirstName] +  ' ' + [LastName] AS FullName
      ,[Education] ,[Occupation],[YearlyIncome],[Sales],[HireDate]
  FROM [Employee]

Add Total and Subtotal to SSRS Report

SSRS provides two approaches to add totals or subtotals to the reports. In this example, we will explain one approach, and later we will explain the other.

To add totals and subtotals, we need a table or matrix report in SSRS. We are going to use the below-shown report for this example. Let me show you the Report Preview. If you see the below screenshot, it displays the data in three levels: Occupation is at the Top Level, then Education at the Second Level, and Details (Full Name, Hire date, Income, Sales) at the third level.

Here our task is to add Totals to Both the Incomes and Sales Column at all levels. Please refer to Table Report, Format Table, and refer to Grouping in Table Report article for Grouping techniques.

Add Total and Subtotal to SSRS Report: Approach 1

First, we will add the Total at the Details level. To do so first, go to the Row Groups pane and right-click on the Details, which will open the context menu. From the context, Please select Add Total and then select the After option. It will add the new Row after the Details row, and add the Total at the Details level.

Let me change the Background color of the total column to Light Steel Blue.

Let me open the Preview tab to check the Totals at the level of detail. From the below screenshot, you can see that an extra column was added with subtotals in SSRS.

Next, we will add the Total at the Education Level (Second Level). To do so first, go to the Row Groups pane and right-click on the Education will open the context menu.

From the context, Please select Add Total and then select the After option. It adds the new Row after the Education level and adds the Total at the Education Level.

Let me change the Background color of the total column to Pale Turquoise. It differentiates the Totals at the detail level and Education Level.

Please click the Preview tab to check the Totals at the Education level. Lastly, we will add the Total at the Occupation (Top Level). To do so first, go to the Row Groups pane and right-click on the Occupation, which will open the context menu.

From the context, Please select Add Total and then select the After option. It adds a new Row at the End of the Table and adds the Grand Total at the Occupation level.

Let me change the Background color of the total column to Plum.

Let me open the Preview tab to check the Totals and Grand Totals at the level of detail. From the below image, see that an extra column was added at the End with Grand Total.

Add Total and Subtotal to SSRS Report: Approach 2

First, we will add the Total at the Details level. To do so, go to the Metric Column (Yearly Income) right-click on it, and select the Add Total option. It adds a new Row after the Details row and adds the Total at the Details level.

Apply the same technique to the Sales Amount Column. Next, Let me change the Background color of the total column to Pale Turquoise

Please open the Preview tab to check the Totals at the details level. It will show an extra column that was added with subtotals, the same as the third image in the first approach.

Next, we will add the Total at the Education level. For this, go to the Yearly Income Total Column (Details Total Row -> Yearly Income Column) right-click on it, and select the Add Total option.

Apply the same technique to the Sales Amount Column, and change the Background color of the Education Level total to plum. Please open the Preview tab to check the Totals at the Education level. Lastly, we will add the Total at the Occupation Level, or Grand Total.

To do so first, go to the Yearly Income Total Column (Education Level Total Row -> Yearly Income Column), and right-click on it to open the context menu. From the context, Please select the Add Total option as shown in the below screenshot.

Let me apply the same technique to the Sales Amount column, and change the Background Color

Let me open the Preview tab to check the Grand Totals at the details level. Now, you see that an extra column was added at the End with Grand Total in SSRS.

From the above screenshot, you can see that the second approach is also showing the Grand Total.