SSRS: RunningValue() to get Cumulative Totals

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

LikeLike

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

LikeLike

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.

LikeLike

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.

LikeLike

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.

LikeLike

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

LikeLike

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.

LikeLike

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

LikeLike

Wow! This is really good.
Lakshmi

LikeLike

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.

LikeLike

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

LikeLike

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

LikeLike

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

LikeLike

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.

LikeLike

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

LikeLike

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.

LikeLike

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.

LikeLike

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,

LikeLike

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.

LikeLike

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.

LikeLike

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.

LikeLike

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

LikeLike

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

LikeLike

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.

LikeLike

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.

LikeLike

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.

LikeLike

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.

LikeLike

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,

LikeLike

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,

LikeLike

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

LikeLike

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!

LikeLike

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.

LikeLike

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! 🙂

LikeLike