SSRS: RunningValue() to get Cumulative Totals

If you have a SSRS (SQL Server Reporting Services) report, and you want to aggregate values on a column, the function RunningValue() is what you would use.

The function takes 3 parameters, the field you want to “run the value” on, the function you want to run on it (Sum for example), and then the scope. The scope can be “Nothing” ex: RunningValue(Fields!Blah.Value,Sum,Nothing) but where the scope really comes into play is when you want to group by given fields in your row.

So if you want to group by say, salesperson, and run their values by day of their sales for a month to date total, you would have

 

Salesperson           Date           Daily Sales          Month To Date Sales
——————————————————————————-
Homer Simpson   
                              07/01/2007       50                            50
                              07/02/2007       43                            93
Bart Simpson
                              07/01/2007        5                              5
                              07/02/2007        8                              13

 

How you achieve this is: on the row with the salesperson, there is a group defined if you right click. Look at the group name, by default it is usually table1_detailsGroup or something similar (if you used the wizard).

Then, the column for the Month to Date Sales would have the expression

=RunningValue(Fields!DailySales.Value,Sum,”table1_detailsGroup”)

Where you can see the scope is now defined as the table group name as a string.

About these ads

36 thoughts on “SSRS: RunningValue() to get Cumulative Totals”

  1. Hi Steve,

    Thanks. I had searched for a while to get this right. Micorsoft documentation does not have a good example on RunnigValue.

    As for the beer, let me know when you come to Toronto. I will definitely buy you one :-) .

    Cheers,

    Sena

    Like

  2. I can’t seem to set a scope. I’ve tried “table1_detailsGroup”, “table1_detailsGroup1″, “table1_Month” (which is the name of the details group)….only get an error message

    Like

  3. I didn’t copy/paste. Here is my statement:
    =RunningValue(Fields!Add_Remove.Value, Sum,”table1_Month”)

    I’ve also tried =RunningValue(Fields!Add_Remove.Value, Sum,”table1_detailsGroup”)

    My table is named table1 and I have two groups: Month and GroupByRegion.

    Like

  4. not sure then, it works for me, and others. Actually just came back here a week ago and used this again and it worked for me then, that is when I noticed the copy/paste didn’t work.

    its tough to say without seeing the report/datasource, etc. but this does work.

    Like

  5. got it to work….had to input scope as “Month” instead of “table1_Month”

    Thanks…..runningvalue isn’t going to do what I want it to do anyway so I’m back to square 1……I have a whacky way I’m trying to aggregate my inventory data and I can’t seem to make it work like I want/need it to….thanks again!

    PS Liked your songs (I write/record songs too…..don’t find many hackers that do so…..)

    Like

  6. Slightly similar problem here… I have a column that is “quantity / sum(quantity)” in the detail section… I want the report to “sum” them up in the group footer. However, sum() cannot be nested. I tried using custom code and also runningvalue() — nothing works. Any help? Thanks.

    Like

  7. Also, instead of having the summary details in the footer, try putting the summary details directly on the report. This means the main table will render first, then the summary fields.

    You will need to define the scope though.

    Like

  8. Hi,

    I need to get a cumulative total on a reportitem. How is it possible to do that.The expression that I am using is =runningvalue(reportitems!textbox49,sum,nothing). The error says “Aggregate function can be used on report items in page headers or footers”. It is working fine for a field but not for a report item.

    Rgs,
    Kailash

    Like

    1. Kailash,I am facing exactly the same issue. Did you find any solution to this problem? Please post it if you have.Thanks,

      Like

  9. I have 3 groups, WorkCenter, Machine and ProdOrd. I do not have a detail row because at the production order level, we can have the same order multiple times because of routings and we only want to see the production detail at the order level. So for subtotals, I want to total on Work Center and Machine but I only want the subtotal at an order level otherwise I can double or triple the results. (ex. order quantity is 500 but if you count each row for each operation it could come out 1000 or 1500). So what I've done in Crystal reports and I'm trying to duplicate in Reporting services is make a running total that I can place in the Machine and WorkCenter groups that resets on the Production Order. However, when I do that, I get an error that the Scope Parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set. Please tell me there's a way to do this. We have SQL 2005.

    Like

    1. Crystal Reports has an accumulation option on the running total that Reporting Services does not have. My work around is to modify the SQL to flag the first row in a recurring group using the Row_Number() function. I then create a calculated field within Reporting Services which returns 0 if the row <> 1. So for example, The roughed out SQL is:Select *,– might not have the exact syntax here ROW_NUMBER() OVER (PARTITION BY WorkCenter, Machine, ProdORd Order by WorkCenter, Machine, ProdORd) as rownumThen within Reporting ServicescQuantity = SUM(IIF(<rownum field> = 1, 1,0) * <Quantity Field>)

      Like

      1. Thanks I was able to do something similar but it's sad to findsomething that Reporting Services doesn't do well. We have a LOT ofreports that use a similar logic done in Crystal Reports and we plan toswitch everything over to Reporting Services to use with SharePoint. Idon't look forward to editing all the working SQL views to do it.

        Like

      2. On the positive side SSRS does have an open architecture allowing you to create your own functions, so potentially create a function that handles this accumulation issue. Another example of the open architecture is the ability to programatically create reports making it even possible to automatically convert Crystal Reports to SSRS.

        Like

      3. Custom functions sound promising but I'm unsure of the syntax. Can youhelp? This is an example of what I did in SQL to prevent theduplicates. How would I translate that into a function? Every instancewhere the Operation = 10 can be summed, while everything else should beignored. Thank you for your help. Case when (Operation = 10) thenquantity_ordered/ConversionFactor else 0 end as LBSnodupes,

        Like

    2. For this problem add some code like such:

      Public innergroup = String.Empty
      Public outergroup = String.Empty
      Function SumEndValue(ByVal inner As String, ByVal outer As String, ByVal thingtosum As Decimal) As Decimal
          If outergroup = String.empty Or outergroup outer Then
              outergroup = outer
              innergroup = String.empty
          End If
          If innergroup = String.Empty Or innergroup inner Then
              innergroup = inner
              Return thingtosum
          End If
          Return 0
      End Function

      Then use your runningvalue like such: =RunningValue(Code.SumEndValue(Fields!InnerGroupField.value, Fields!OuterGroupField, thingtosum), SUM, “OuterGroup”)

      The runningvalue call goes in the header or footer for the outergroup. This will add the detail value once per inner group.

      Like

  10. I have 3 groups, WorkCenter, Machine and ProdOrd. I do not have a detail row because at the production order level, we can have the same order multiple times because of routings and we only want to see the production detail at the order level. So for subtotals, I want to total on Work Center and Machine but I only want the subtotal at an order level otherwise I can double or triple the results. (ex. order quantity is 500 but if you count each row for each operation it could come out 1000 or 1500). So what I've done in Crystal reports and I'm trying to duplicate in Reporting services is make a running total that I can place in the Machine and WorkCenter groups that resets on the Production Order. However, when I do that, I get an error that the Scope Parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set. Please tell me there's a way to do this. We have SQL 2005.

    Like

  11. I have 3 groups, WorkCenter, Machine and ProdOrd. I do not have a detail row because at the production order level, we can have the same order multiple times because of routings and we only want to see the production detail at the order level. So for subtotals, I want to total on Work Center and Machine but I only want the subtotal at an order level otherwise I can double or triple the results. (ex. order quantity is 500 but if you count each row for each operation it could come out 1000 or 1500). So what I've done in Crystal reports and I'm trying to duplicate in Reporting services is make a running total that I can place in the Machine and WorkCenter groups that resets on the Production Order. However, when I do that, I get an error that the Scope Parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set. Please tell me there's a way to do this. We have SQL 2005.

    Like

  12. Crystal Reports has an accumulation option on the running total that Reporting Services does not have. My work around is to modify the SQL to flag the first row in a recurring group using the Row_Number() function. I then create a calculated field within Reporting Services which returns 0 if the row <> 1. So for example, The roughed out SQL is:Select *,– might not have the exact syntax here ROW_NUMBER() OVER (PARTITION BY WorkCenter, Machine, ProdORd Order by WorkCenter, Machine, ProdORd) as rownumThen within Reporting ServicescQuantity = SUM(IIF(<rownum field> = 1, 1,0) * <Quantity Field>)

    Like

  13. Crystal Reports has an accumulation option on the running total that Reporting Services does not have. My work around is to modify the SQL to flag the first row in a recurring group using the Row_Number() function. I then create a calculated field within Reporting Services which returns 0 if the row <> 1. So for example, The roughed out SQL is:Select *,– might not have the exact syntax here ROW_NUMBER() OVER (PARTITION BY WorkCenter, Machine, ProdORd Order by WorkCenter, Machine, ProdORd) as rownumThen within Reporting ServicescQuantity = SUM(IIF(<rownum field> = 1, 1,0) * <Quantity Field>)

    Like

  14. Thanks I was able to do something similar but it's sad to findsomething that Reporting Services doesn't do well. We have a LOT ofreports that use a similar logic done in Crystal Reports and we plan toswitch everything over to Reporting Services to use with SharePoint. Idon't look forward to editing all the working SQL views to do it.

    Like

  15. Thanks I was able to do something similar but it's sad to findsomething that Reporting Services doesn't do well. We have a LOT ofreports that use a similar logic done in Crystal Reports and we plan toswitch everything over to Reporting Services to use with SharePoint. Idon't look forward to editing all the working SQL views to do it.

    Like

  16. On the positive side SSRS does have an open architecture allowing you to create your own functions, so potentially create a function that handles this accumulation issue. Another example of the open architecture is the ability to programatically create reports making it even possible to automatically convert Crystal Reports to SSRS.

    Like

  17. On the positive side SSRS does have an open architecture allowing you to create your own functions, so potentially create a function that handles this accumulation issue. Another example of the open architecture is the ability to programatically create reports making it even possible to automatically convert Crystal Reports to SSRS.

    Like

  18. Custom functions sound promising but I'm unsure of the syntax. Can youhelp? This is an example of what I did in SQL to prevent theduplicates. How would I translate that into a function? Every instancewhere the Operation = 10 can be summed, while everything else should beignored. Thank you for your help. Case when (Operation = 10) thenquantity_ordered/ConversionFactor else 0 end as LBSnodupes,

    Like

  19. Custom functions sound promising but I'm unsure of the syntax. Can youhelp? This is an example of what I did in SQL to prevent theduplicates. How would I translate that into a function? Every instancewhere the Operation = 10 can be summed, while everything else should beignored. Thank you for your help. Case when (Operation = 10) thenquantity_ordered/ConversionFactor else 0 end as LBSnodupes,

    Like

  20. Kailash,I am facing exactly the same issue. Did you find any solution to this problem? Please post it if you have.Thanks,

    Like

  21. Hi,

    I have an SSRS report that looks at Inventory balances by item and then has weekly groups of on order qtys. At each weekly group column I want to see what my inventory levels will be after shipping the open orders. CUrrently I have the following formula expression in the field:

    =Fields!Inventory.Value-RunningValue(Iif(Not IsNothing(Fields!LbsOrdered.Value)=TRUE , Fields!LbsOrdered.Value, NOTHING), SUM, “ProdCode”)

    It works perfectly if there is a value in the group field but if there’s not, then the field value is wrong…. when a value shows up in the next field it picks up just fine where the formula left off. Can you shed any light for me? Thanks a lot!

    Like

  22. try also to build the expression like:
    =RunningValue(Sum(Fields!NRO_CONTRATOS.Value)), Sum, “Department_Id”)

    and to avoid the NULL values in a tablix and to replace them with “0” :
    =RunningValue(iif(isnothing(Sum(Fields!NRO_CONTRATOS.Value)),0,Sum(Fields!NRO_CONTRATOS.Value)), Sum, “Department_Id”)

    build the scope always on the row group (eg. “Department _Id” in this case – first pic. attached) and try to build on the parent group. you’ll need to hide the row group in the tablix to avoid the aggregate of the other cells to obtain the totals.

    the result is in the second pic. attached and also a graphic showing this evolution.

    Like

  23. Thank you for this detailed post and comments! I was able to follow the logic here to achieve something that I’ve been trying to do for a long time! :)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s