Saturday, March 21, 2009

RunningValue Function (Reporting Services) – Calculating Running Totals

In one of my previous posts I talked about Calculating Running Totals in T-SQL.  Now I will show you how you can calculate the running totals in Sql Server Reporting Services(SSRS).  Yes, it is very much possible with RunningValue function in SSRS and also very easy compare to doing it in SQL.  

The syntax for RunningValue function goes like this -

RunningValue(expression, function, scope)
  • expression : The expression on which to perform the aggregation, for example, [Quantity].
  • function : The name of the aggregate function to apply to the expression, for example- Sum.
  • scope :The name of a dataset, data region, group or Nothing.

See the table below and RunningTotal column which is Cumulative SUM of Quantity column. 

ShipId OrderId ShipDate Quantity RunningTotal
1 1 2009-02-01 12:06:16.820 10 10
2 1 2009-02-02 16:16:16.820 15 25
3 2 2009-02-01 13:26:16.820 20 45
4 2 2009-02-03 17:40:16.820 12 57
5 2 2009-02-04 13:05:16.820 15 72
6 3 2009-02-03 14:25:16.820 25 97
7 3 2009-02-04 15:50:16.820 50 147
8 3 2009-02-05 19:40:16.820 30 177
9 4 2009-02-06 15:30:16.820 20 197
10 4 2009-02-07 16:20:16.820 10 207
11 5 2009-02-05 17:05:16.820 35 242

For achieving that in SSRS, add extra column in your Report’s Layout Page and put following expression in it-

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

Yes, it is easy and simple, isn’t it?  But what about when you want the RunningTotals for group of records?  Like for above example you want the Running Totals for each orderId separately.  See the table below :

ShipId OrderId ShipDate Quantity RunningTotal
1 1 2009-02-01 12:06:16.820 10 10
2 1 2009-02-02 16:16:16.820 15 25
3 2 2009-02-01 13:26:16.820 20 20
4 2 2009-02-03 17:40:16.820 12 32
5 2 2009-02-04 13:05:16.820 15 47
6 3 2009-02-03 14:25:16.820 25 25
7 3 2009-02-04 15:50:16.820 50 75
8 3 2009-02-05 19:40:16.820 30 105
9 4 2009-02-06 15:30:16.820 20 20
10 4 2009-02-07 16:20:16.820 10 30
11 5 2009-02-05 17:05:16.820 35 35

Now for achieving that you need do followings :

  • First go to Report Layout page and add a new Group.   Give some name to the group.  If you don’t gave any name the default name would be something like “table1_Group1”.  Just note that.  And add OrderId to “Group On” expression.
  • Now change the expression at RunningTotal column as
    =RunningValue(Fields!Quantity.Value,SUM,"table1_Group1")

        Now if you have noticed I have replaced the scope Nothing to Group name “table1_Group1” .

Well after adding the extra group in the report you might see blank spaces after every  OrderId change. If you don’t want those blanks and want continuous report, then go to the property page of the new group added and set Visible to “false”.

Mangal Pardeshi

SQL MVP

16 comments:

  1. what abt the row wise total for each page, followed by grand total in the last page in SSRS ...... any idea

    ReplyDelete
  2. how to get the row wise total for each page, followed by grand total in the last page in SSRS

    ReplyDelete
  3. Fantastic article and a huge help in a series of reports I have been developing for a client. Thanks!

    ReplyDelete
  4. many thanks...really help me to solve the report that need page total.

    ReplyDelete
  5. Wonderfull! Very straight forward solution.
    You saved me a lot of time.
    Thanks

    ReplyDelete
  6. Wonderfull! Very straight forward solution.
    You saved me a lot of time.
    Thanks

    ReplyDelete
  7. I do the same
    "=RunningValue(Fields!Quantity.Value, Sum, Nothing)"

    for me the rows are empty. i want page wise total in ssrs. Kindly help

    thanks and regards
    suray kathir

    ReplyDelete
  8. What about if your group is sorted descending?

    ReplyDelete
  9. Keep up the great work, I read few articles on this site and I think your blog is very interesting..

    Hire SSRS developers

    ReplyDelete
  10. Keep up the great work, I read few articles on this site and I think your blog is very interesting..

    Hire SSRS developers

    ReplyDelete
  11. What if I have two tables in a I have a calculated value of an initial inventory and want to mix with the values ​​of transactions every day and have so far had real value of existence for each move.

    ReplyDelete
  12. What if I have two tables in a I have a calculated value of an initial inventory and want to mix with the values ​​of transactions every day and have so far had real value of existence for each move.

    ReplyDelete
  13. Thanks for sharing. furthermore, RunningValue function is very useful feature in SSRS report which is used to pull a running aggregate of all non-null numeric values specified by the expression, evaluated for the given scope. This function takes three input parameters. We cannot use RunningValue function as for the filter or sort expression.
    RunningValue Function in SSRS

    ReplyDelete