Running Totals Using Reporting Services

Sometimes one may need to see a running number that represents a quantity for a period of time. For example, let’s say you have a month to date report that breaks out the quantity of units sold on a daily basis. And you get a request from the Sales Manager stating that she wants a monthly report that shows sales quantity distribution by day, with an extra column that shows a running value for that month. At the beginning of the month, you will have 0 units sold right (unless you’re adding in year to date)? Let’s say that you have 100 units sold on the first day, 300 on the second and 245 on the third day. After that third day you should have 645 units sold right? This is what is called a running value and can easily be calculated in SQL Server Reporting Services. Here is an example expression that one may use to get the running value of a field called UnitsSold in a dataset called ds_GetSalesByDay:

Listing 1: RunningValue Expression

=RunningValue(Fields!UnitsSold.Value,Sum,"ds_GetSalesByDay")

Figure 1: Sample Report

“..and until that day comes, keep your ear to the grindstone” – Good Will Hunting

Brian K. McDonald, MCDBA, MCSD

Business Intelligence Consultant

Convert with DTS xChange   | Develop with BI xPress   | Process with TaskFactory | Document with BI Documenter