SSRS: Create a running total using RunningValue

In many reports, you need to have a column that shows the balance and it needs to show the running total for all of the data that shows up.

In the report below, its shows date, amount and a beginning balance. We need to show a running total in the Balance column.

For instance in the report below it shows a $112.05 balance and then we want to subtract $48.96 to show $63.09 to show the current balance, and then continue that down to the final line.

Here is what the report currently looks like in Visual Studio. It is just pulling a few fields and we have an expression for the Balance column.

Now open up the expression we currently have that calculates the Balance. Right click and choose Expression.

That displays the expression builder. Right now it is just pulling the beginning balance field from one of the datasets.

There is a function in the expression builder called: RunningValue. The syntax is as follows RunningValue(expression, function, scope)

To read more about RunningValue and to see other examples, please follow this link:

https://docs.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-runningvalue-function?view=sql-server-ver15

In our example we want to add the AMT column to the Beginning Balance value. The function is SUM and the scope is Nothing which “specifies the outermost context, usually the report dataset”

After closing the expression builder, I preview the report and it shows a running balance!

Using the RunningValue function saves a lot of development time because the T-SQL does not need to be modified into some complex mess of code to try and figure out how to calculate a running balance.

Happy report writing.