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
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
Hi Brian. Thanks for the article on creating running totals. It's very helpful but I wonder if you could extend it to help me create conditional running total.
I want to create sub-totals on fees column where case no. code = "DDT", "SQR", "BTK"
So, above the fees grand-total would be sub-total for fees where case no code is "DDT"; then below this would be sub-total for fees where case no code is "SQR"; and below this would be sub-total for fees where case no code is "DDT".
I've serached Google for solution but can't seem to crack it.
Your help would be greatly appreciated!
You're welcome! As for assisting you, I don't see a problem with what you are trying to do. I'll write a blog about this tomorrow night (or at lunch if I take one). Just to make sure that I understand what it is that you are asking... You have groups of "DDT", "SQR", "BTK", etc.. You want the the numbers withing DDT and then again in SQR?