This SSRS article shows how to calculate the running average for a table report with and without grouping using a RunningValue function. 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
How to Calculate the Running Average in SSRS?
We have designed a simple table report of Employee sales and formatted the font and colors.
Under the Row group section, click the down arrow beside the details and select add group and parent group.
Let me choose the Occupation column as the group by option and click Ok.
Now the complete employee table will group by their occupation.
And the report review is.
Right-click on the sales column, choose the insert column and then select the right option to create a new column on the right side of the sales.
We named the header Running Avg. Next, right-click on the empty cell and choose an expression to calculate the running average.
Write the below expression. The SSRS reporting service has a RunningValue function to calculate the running average, which accepts a numeric column, an aggregate function, and the dataset name. Here, we have chosen avg aggregate method to find the average.
=RunningValue(Fields!Sales.Value, Avg, "DataSet1")
If you go to the report preview now, you can see the running average from top to bottom.
To calculate the running average for the SSRS group report, we must replace the data set name with the group name in the expression. So, replace DataSet1 with Occupation (group name).
=RunningValue(Fields!Sales.Value, Avg, "Occupation")
Now you can see the running average calculated based on the occupation group. So for each group, the running average value restarts by reporting services.
