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.



  • Sena

    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

  • John

    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

  • http://blog.stevienova.com Steve Novoselac

    if you copied the statement from my blog directly, the quotes (“) get goofed up when you paste into SSRS. Just try writing it yourself, with quotes where they should be.

  • John

    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.

  • http://blog.stevienova.com Steve Novoselac

    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.

  • John

    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…..)

  • Ssalim

    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.

  • http://www.egsmartsys.com Eric Gonzalez

    Thank you, exactly what I was looking for.
    It worked great.
    Eric

  • lakshmi

    Wow! This is really good.
    Lakshmi

  • Ryan

    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.

  • Kailash

    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

  • Kartik Lata

    Thanks a lot… I have been searching for this since two days. Thanks again…

  • kims1119

    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.

  • kims1119

    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.

  • kims1119

    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.

  • noellevine

    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 rownum

    Then within Reporting Services

    cQuantity = SUM(IIF(<rownum field> = 1, 1,0) * <Quantity Field>)

  • noellevine

    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 rownum

    Then within Reporting Services

    cQuantity = SUM(IIF(<rownum field> = 1, 1,0) * <Quantity Field>)

  • noellevine

    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 rownum

    Then within Reporting Services

    cQuantity = SUM(IIF(<rownum field> = 1, 1,0) * <Quantity Field>)

  • kims1119

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

  • kims1119

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

  • kims1119

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

  • noellevine

    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.

  • noellevine

    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.

  • noellevine

    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.

  • kims1119

    Custom functions sound promising but I'm unsure of the syntax. Can you
    help? This is an example of what I did in SQL to prevent the
    duplicates. How would I translate that into a function? Every instance
    where the Operation = 10 can be summed, while everything else should be
    ignored. Thank you for your help.

    Case when (Operation = 10) then
    quantity_ordered/ConversionFactor
    else 0 end as LBSnodupes,

  • kims1119

    Custom functions sound promising but I'm unsure of the syntax. Can you
    help? This is an example of what I did in SQL to prevent the
    duplicates. How would I translate that into a function? Every instance
    where the Operation = 10 can be summed, while everything else should be
    ignored. Thank you for your help.

    Case when (Operation = 10) then
    quantity_ordered/ConversionFactor
    else 0 end as LBSnodupes,

  • kims1119

    Custom functions sound promising but I'm unsure of the syntax. Can you
    help? This is an example of what I did in SQL to prevent the
    duplicates. How would I translate that into a function? Every instance
    where the Operation = 10 can be summed, while everything else should be
    ignored. Thank you for your help.

    Case when (Operation = 10) then
    quantity_ordered/ConversionFactor
    else 0 end as LBSnodupes,

  • Jshop

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

    Thanks,

  • Jshop

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

    Thanks,

  • Tom

    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.

  • Brianne Wilson

    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!