Add Total and Subtotal to SSRS Report

In this article we will show you, How to Add Total and Subtotal to SSRS Report. This is one of the common requirement from the Client end while you are designing the Table, or a Matrix report.

To explain the steps involved to add Total and Subtotal to SSRS Report, We are going to use below shown Data Set. Please refer Embedded Data Source and Dataset articles to understand the steps involved in creating Embedded Data Source, and Dataset that we used for this report.

and the Custom SQL query that we used in the above Data set is:

SQL Query

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

Data written by the above query is:

Add Total and Subtotal to SSRS Report

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

To add totals, and subtotal we need a table or matrix report. We are going to use below shown report for this example. Please SSRS Table Report, Format Table Report in SSRS articles to understand the steps involved in creating and formatting Table Report, and refer Grouping in SSRS Table Report article for Grouping techniques. If you observe the

Let me show you the Report Preview. If you observe the below screenshot, it is displaying the data in three levels: Occupation is at Top Level, then Education at Second Level, and Details (Full Name, Hire date, Income, Sales) at third level. Here our task is to add Totals to Both Income, and Sales Column at all levels.

Add Total and Subtotal to SSRS Report: Approach 1

First we will add the Total at the Details level. In order to do so first, goto Row Groups pane and right-click on the Details will open the context menu. From the context, Please select Add Total and then select the After option as shown in below screenshot. This will add the new Row after the Details row, and add Total at 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 details level. From the below screenshot you can see that, an extra column is added with subtotals.

Next we will add the Total at the Education Level (Second Level). In order to do so first, goto 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 as shown in below screenshot. This will add the new Row after the Education level, and add Total at Education level

Let me change the Background color of the total column to Pale Turquoise. This will differentiate the Totals at Details Level, and Education Level

Let me open the Preview tab to check the Totals at Education level.

Lastly we will add the Total at the Occupation (Top Level). In order to do so first, goto Row Groups pane and right-click on the Occupation will open the context menu. From the context, Please select Add Total and then select the After option as shown in below screenshot. This will add the new Row at the End of the Table, and add the Grand Total at Occupation level

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

Let me open the Preview tab to check the Grand Totals at details level. From the below screenshot you can see that, an extra column is 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. In order to do so first, goto Metric Column (Yearly Income) and right-click on it will open the context menu. From the context, Please select Add Total option as shown in below screenshot. This will add the new Row after the Details row, and add Total at 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

Let me open the Preview tab to check the Totals at details level. From the below screenshot you can see that, an extra column is added with subtotals.

Next we will add the Total at the Education level. In order to do so first, goto Yearly Income Total Column (Details Total Row -> Yearly Income Column) and right-click on it will open the context menu. From the context, Please select Add Total option as shown in below screenshot.

Apply the same technique to the Sales Amount Column, and change the Background color to plum

Let me open the Preview tab to check the Totals at Education level.

Lastly we will add the Total at the Occupation Level, or Grand Total. In order to do so first, goto Yearly Income Total Column (Education Level Total Row -> Yearly Income Column) and right-click on it will open the context menu. From the context, Please select Add Total option as shown in below screenshot.

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

Let me open the Preview tab to check the Grand Totals at details level. From the below screenshot you can see that, an extra column is added at the End with Grand Total.

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

Thank You for Visiting Our Blog