The SSRS Sum Function is an aggregate function that returns the Sum or Total of all numeric values from the given field. This article explains how to use the SSRS Sum Function to find the Total of a particular group or complete table with an example.
The syntax of the SSRS Sum Function is as shown below.
Sum(Fields!SalesAmount.Value) - Default scope
Sum(Fields!SalesAmount.Value, "Group Name") - Find the Sum for each Group.
Sum(Fields!SalesAmount.Value, "Group Name", Recursive)
To demonstrate the SSRS Sum Function, we use the Employee table below, which has 15 records grouped by Occupation. The image shows the records in the Table report.
SSRS Sum Function Example
Right-click the YearlyIncome or Sales field, choose the Insert a Row option, and select the Outside Group – Below option to add a new row. Next, Merge the first three columns and add Total as the text. I suggest you refer to the Table, Grouping, and Format Table articles in SSRS to understand the report.
There are multiple ways to write the SSRS Sum function. The first approach is simply choosing the numerical field. For instance, click the YearlyIncome. As Sum is the default aggregate function, the reporting services will select it.
Similarly, choose the Sales field as well.
Please click the preview tab to see the total yearly income of the Employee and their total Sales. If you observe the report, by default, it calculates the sum for each Occupation because it is the default scope.
SSRS Sum Function Scope Demonstration
Though there are many ways to demonstrate the scope of the Sum function, I chose the simple approach. Let me add a new column to the right side of the Sales. Next, right-click the textbox and choose the Expression.
It opens the following SSRS expression window, which is the place where we write the more complex Sum function.
For example, the expression below returns the same result as shown by the Total row: 133.01, 12924.06, etc. However, you have more control over the calculation because we manually changed the Sum function scope. For more functions >> Click Here!
=Sum(Fields!Sales.Value, "Occupation")
The expression below subtracts the individual employee sales from the total sales of each occupation group. Remember, “Occupation” is the group name.
=Sum(Fields!Sales.Value, "Occupation") - Fields!Sales.Value
If you check the report, Christy Mehta—133.01 (the Sum of Sales of Clerical Occupation)—24.99 (Christy Mehta) = 108.02.
To find the sum of the whole table or all the employees, you must replace the group name in the SSRS Sum function with the table name. Let me add an extra row outside the Group and add the below two expressions for the Yearly Income and Sales.
=Sum(Fields!YearlyIncome.Value, "Tablix1")
=Sum(Fields!Sales.Value, "Tablix1")
The report below shows the grand total of all the groups. Please replace Tablix1 with the Dataset name to get the Sum of Sales irrespective of the filters applied to the report.
