Tutorial Gateway

  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
  • MySQL

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 to add Total and Subtotal to SSRS Report, We are going to use the below-shown DataSet. Please refer to Embedded Data Source and Dataset articles to understand the steps involved in creating Embedded Data Source and Dataset that we used for this report.

Add Total and Subtotal to SSRS Report 0

And the Custom SQL query that we used in the above Dataset is:

-- 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 Subtotal to SSRS Report 0

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 in SSRS. We are going to use the below-shown report for this example.

Please refer to Table Report, Format-Table Report, and refer to Grouping in Table Report article for Grouping techniques.

Add Total and SubTotal to SSRS Report 1

Let me show you the Report Preview. If you see 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 Incomes and Sales Column at all levels.

Add Total and SubTotal to SSRS Report 2

Add Total and Subtotal to SSRS Report: Approach 1

First, we will add the Total at the Details level. 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. It will add the new Row after the Details row, and add Total at Details level

Add Total and SubTotal to SSRS Report 4

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

Add Total and SubTotal to SSRS Report 5

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

Add Total and SubTotal to SSRS Report 6

Next, we will add the Total at the Education Level (Second Level). 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. It adds the new Row after the Education level, and add Total at Education level

Add Total and SubTotal to SSRS Report 7

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

Add Total and SubTotal to SSRS Report 8

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

Add Total and SubTotal to SSRS Report 9

Lastly, we will add the Total at the Occupation (Top Level). 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. It adds a new Row at the End of the Table, and add the Grand Total at Occupation level

Add Total and SubTotal to SSRS Report 10

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

Add Total and SubTotal to SSRS Report 11

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

Add Total and SubTotal to SSRS Report 12

Add Total and Subtotal to SSRS Report: Approach 2

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

Add Total and SubTotal to SSRS Report 13

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

Add Total and SubTotal to SSRS Report 14

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

Add Total and SubTotal to SSRS Report 15

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

Add Total and SubTotal to SSRS Report 16

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

Add Total and SubTotal to SSRS Report 17

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

Add Total and SubTotal to SSRS Report 18

Lastly, we will add the Total at the Occupation Level, or Grand Total. To do so first, goto the 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 the Add Total option as shown in the below screenshot.

Add Total and SubTotal to SSRS Report 19

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

Add Total and SubTotal to SSRS Report 20

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

Add Total and SubTotal to SSRS Report 21

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

Filed Under: SSRS

  • Learn SSRS in 28 Days
  • Create New SSRS Project
  • Create a New SSRS Report
  • SSRS Query Designer
  • SSRS Shared Data Source
  • SSRS Shared Dataset
  • SSRS Embedded Dataset
  • SSRS Embedded Data Source
  • SSRS Lookup – combine datasets
  • SSRS Report Items
  • SSRS Table Report
  • SSRS Table using report wizard
  • Format SSRS Table Report
  • SSRS Matrix Report
  • SSRS Matrix with report wizard
  • Display Image in SSRS Report
  • Add Rectangle to SSRS Report
  • Add TextBox to SSRS Report
  • Create a SSRS List Report
  • Grouping in SSRS Matrix Reports
  • Grouping in SSRS Table Reports
  • Create SSRS Report using Views
  • SSRS report with store procedure
  • Add Total and Subtotal to Report
  • Add Headers & Footers to Report
  • SSRS Global References
  • Export Report to PDF, XML, Excel, Word
  • Format SSRS Table Report
  • Format Textbox Fonts & Bg Color
  • Format Numbers in SSRS Report
  • Format SSRS Report Date, Time
  • Add Row Numbers to Report
  • Headers Visible While Scrolling
  • Repeat Headers on Each Page
  • SSRS Sorting
  • SSRS Interactive Sort
  • Filters at Dataset Level in SSRS
  • Filters at Tablix Level in SSRS
  • Page Breaks in SSRS Report
  • Custom Code in SSRS Report
  • SSRS Calculated Fields
  • SSRS Dashboard Reports
  • SSRS Document Map
  • SSRS Drill Through Reports
  • SSRS Drill Down Reports
  • SSRS Drill Down Matrix Report
  • SSRS Go To URL Action
  • SSRS Subreports
  • SSRS Report Parameters
  • SSRS Cascading Parameters
  • SSRS Multiple Parameters
  • SSRS Multi Value Parameter
  • SSRS Drop Down List Parameters
  • SSRS Charts
  • SSRS Area Chart
  • SSRS Bubble Chart
  • SSRS Column Chart
  • Format SSRS Column Chart
  • SSRS Data Bars
  • Data Bars in SSRS Matrix Reports
  • Format SSRS Data Bars
  • SSRS Funnel Chart
  • SSRS Linear Gauges
  • SSRS Radial Gauges
  • Format SSRS Gauges
  • SSRS Indicators
  • Format SSRS Indicators
  • SSRS Line Chart
  • SSRS Pie Chart
  • Format SSRS Pie Chart
  • SSRS Pyramid Chart
  • SSRS Range Chart
  • SSRS Radar Chart
  • SSRS Scatter Plot
  • SSRS Sparkline
  • SSRS Stacked Bar Chart
  • SSRS Configuration Manager
  • Change Report Manager Title
  • SSRS Deploying Reports in BIDS
  • Deploy using Report Manager
  • SSRS Report Manager Shared DataSource
  • SSRS Cached Reports
  • SSRS Linked Reports
  • SSRS Report Parts
  • SSRS Report Snapshot
  • SSRS Report History Snapshot
  • SSRS Report Subscription
  • SSRS Security
  • Upload File to Report Manager
  • Install SSRS Report builder
  • Report Builder – Create a Report
  • Report Builder – Create a Dataset
  • Report Builder- Create Bar chart
  • Report Builder – Create Pie Chart
  • Report Builder – Create report without wizard

Copyright © 2021ยท All Rights Reserved by Suresh.
About | Contact | Privacy Policy