SSRS 2005 - code variables and grouping?
We need to do a few complex calculations for each group summary and it looks like we can keep track of some data as the rows process, but when the group footer code executes it seems to have the values prior to the group running. A simple example: Dim MyTotal as integer Function AddToTotal(inval as integer) as string MyTotal = MyTotal + inval Return "So farX: " & MyTotal End Function Function GetTotal() as String GetTotal = "SubTotal is " & MyTotal MyTotal = 0 ' reinitialize End Function We call AddToTotal by using an expression of =code.AddToTotal(3) which should add 3 to the running total for each row in the group and return a string of the running total. That works fine. We then call GetTotal by using an expression of =code.GetTotal(). I figured that it would give us the total after the group but instead gives us the total prior to the group executing. I don't know how variables are supposed to work in SSRS but it looks like the footer code executes before the detail code, but that can't be correct because SUM and such work correctly. I would greatly appreciate if anyone can tell me how to do this. An example output with my comments: So FarX: 3 - one detail record in group SubTotal is 0 - total is from before group So FarX: 3 - one detail record SubTotal is 3 - total is from previous group So FarX: 3 - eight detail records in this group So FarX: 6 So FarX: 9 So FarX: 12 So FarX: 15 So FarX: 18 So FarX: 21 So FarX: 24 SubTotal is 3 - total is from previous group So FarX: 3 - nine detail records So FarX: 6 So FarX: 9 So FarX: 12 So FarX: 15 So FarX: 18 So FarX: 21 So FarX: 24 So FarX: 27 SubTotal is 24 - total is from previous group
September 29th, 2012 1:45pm
Hi Dave, As Leo suggested, you can try the RunningValue function instead in the SubTotal textbox. The expression for the textbox may be like below: =RunningValue(Fields!SalesAmount,Sum,"SalesTerritoryCountry") Note: The "SalesTerritoryCountry" scope in the expression points to the innermost group that contains the details row. Hope this helps. Regards, Mike Yin TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here. Mike Yin TechNet Community Support
October 28th, 2012 11:42am
Thanks, we're looking into that. I still don't see why my original idea didn't work though. Running value doesn't allow us to do anything that requires decision making but maybe we can make it work. There does seem to be more support for variables in SSRS2008 but that isn't what we're running now.
October 28th, 2012 1:29pm
Hi, I think you cana use the RunningValue Function instead of the custom code. Regards, Leo
October 28th, 2012 1:58pm