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 screenshot shows the data set that we use for this example.
The Sql query that we used above SSRS example is:
SELECT * FROM Employee
Calculate Running Total in 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.
Here, we have chosen the Occupation column as the group by option and clicked Ok. It means the employees will group by their occupation.
And the report review is.
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.
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")
And the final report is.
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.
=RunningValue(Fields!Sales.Value, sum, "EmpDataSet")
Now you can see the running total of a complete table from top to bottom.
