Creating a dynamic subtotal in your SSRS report

By | November 7, 2009

I had to create a report this week where the client wanted the values of this year next to the values of last year. To compare year total until the current month they want a subtotal field at the current month.

To achieve this i created a row between the Total and the Total of the month where i want to show the Total until that month.

tablix

As expression I use the RunningValue function:

=RunningValue(Fields!Reseller_Sales_Amount.Value, Sum, Nothing)

This creates the sum per month of the current month. Now all you have to do is make the row invisible on all months except the current. I used a second dataset to determine the last month we have data from.

This results in the following result where the subtotal field will automatically move up and down depending on the data.

result