This SSRS article shows how to calculate the running total for a grouped table report using a RunningValue function and group name with an example. To do so, right-click on the Datasets folder to create a new DataSet. The below code shows the data set that we use for this example.
The Sql query that we used in this SSRS example is:
SELECT * FROM Employee
Calculate the Running Total in the SSRS Grouped Report
We have designed an Employee sales table report and formatted the font and colors. Under the Row group section, click the down arrow beside the details and select the add group and parent group. For a regular report, please refer to Calculate the Running Total.
Here, we have chosen the Occupation column as the group by option and clicked OK. It means the employees will be grouped by their occupation and check the report preview. Please refer to add groups to the Table article.
Right-click the last column, i.e., Sales, and choose the insert column and then the Right option to create a new column on the right side of the sales. Please refer to the running sum example to add a column. Name the header column as Running Tot, right-click on the empty cell below it, and choose an expression.
The SSRS reporting service has a RunningValue function to calculate the running total of a grouped report that accepts a numeric column, an aggregate function, and the dataset name. Here, we have chosen the Sales Column, sum aggregate function to find the total, and Occupation group.
=RunningValue(Fields!Sales.Value,Sum,"Occupation")
If you go to the SSRS report preview now, you can see that the running total was calculated based on the occupation group. Then, for each group, the running total value was restarted by the reporting services.
To calculate the running total of a complete table, replace the Occupation group name with the DataSet name, i.e., EmpDataSet. To do so, edit the expression under Running Tot and replace the previous one with the below expression.
=RunningValue(Fields!Sales.Value, sum, "EmpDataSet")
Now you can see the running total of a complete table from top to bottom.
