Hi,Is it possible to use the built-in ReportItems collection to sum up all the values in a particular column in a tablix? For example, I have a textbox called "Balance" that appears for every row in a tablix and I'm trying to sum them all up using=Sum(ReportsItems("Balance").Value)but when I do I get an error:The value expression for the textrun 'Balance1.Paragraphs[0].TextRuns[0] uses an aggregate function on a report item.By the way, using:=SUM(Balance)isn't an option because I am applying an additional business rule within the report which may or may not change the value in the 'Balance' field, hence why I want to access the value of the textbox after that business rule has been applied.Can anyone help me out?thanksJamiehttp://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Hi Jamie, If I understand correctly, you may or may not change the value in the ‘Balance’ field, and then sum the items’ value on the footer of the table (or group). Obvious, in the case, the “Sum(Fields!Balance.Value)” is invalid. And using ReportItems in aggregate function is not allowed. To solve the issue, one workaround is using Custom Code. We can add each item which is calculated to a variable in the custom code, and sum the variable in the custom code. In the footer, get the total value from the total variable. From is the sample code for your reference: Dim public totalBalance as Integer Public Function AddTotal(ByVal balance AS Integer ) AS Integer totalBalance = totalBalance + balance return balance End Function Public Function GetTotal() return totalBalance End Function To use the code, please use the AddTotal in the ‘Balance’ field such as: =AddTotal( change the Balance value based on the business role). This expression will return the value of the balance that has been changed based on the business role. And then in the footer, use this expression: = GetTotal(). That will return the total value of the changed balance. For more information, please see: Using Custom Code References in Expressions (Reporting Services): http://msdn.microsoft.com/en-us/library/ms155798.aspx If there is anything unclear, please feel free to ask. Thanks, Jin Chen

Jin, This post and your answer just saved a lot of work... I have been looking all over for this and now I also have my answer!!!

Jin, I am trying to make this work for a report that is not mine and I am helping someone with. The actual and logical fix is probably to change the dataset which we may do. However, being curious I have tried to implement this and I am close but not there yet not sure if this is the right solution. I have a report that has a table not tablix with 3 groups and I want the (total appearing on the report not the dataset) of the data that makes up group 3 to appear in the Group1 Header. Ideally I would use Sum(ReportItems.texbox51.Value). I implemented your solution and when the report reaches the final record in group 3 the total is correct, however, i am uncertain how to make that appear in the Group 1 header or call it. Example. Group 1 - XXXXX Budget 1 Group 2 - Engineering Group 3 Data - 022 $100 021 $300 Group 1 - XXXXX Budget 1 Group 2 - Manufacturing Group 3 Data - 033 $100 034 $300 What I would like is to show $800 next to Group1. I know normally I would just use Sum(fields.textbox51.value) however, in this instance due to the dataset it can not be done. I am just trying to design a workaround for my own knowledge of how this would be done if changing the dataset was not possible. I was trying to change the groupings but that would display everything from the dataset something we are trying to avoid. When I use your code above I show in the footer below 034 - $800 and below 033 it shows $500. Is there a way to call that value after the sum into the header for Group1 or an alternative method? Here is the code I am using: Dim public totaltextbox51 as Integer Public Function AddTotal(ByVal textbox51 AS Integer ) AS Integer totaltextbox51 = totaltextbox51 + textbox51 return textbox51 End Function Public Function GetTotal() return totaltextbox51 End Function

Hi Jinchun, I tried your solution and it seem to accumulate the total within the same group, but when I try to access the total value within the outer parent group, the value is 0. I have tried to set the variable as Shared, but no success. Note: I am using a table in VS2005, not a matrix. Can you help? Thanks, Eric

Hi All I recently had to solve this problem, and was able to do it using math. THe custom code approach didn't quite suit my requirement. I've posted it on my blog here: http://dataqueen.unlimitedviz.com/2011/05/ssrs-aggregate-last-ytd-or-last-child-value-in-an-ssas-query/

Brilliant Solution.... Thanks a lot

Thanks

I am using the following - both are returning 0. Can you see why? The AddTotalReplacement and AddTotalGSF both return as they should. Dim public totalReplacement as Integer Dim public totalGSF as Integer Public Function AddTotalReplacement(ByVal replacement AS Integer ) AS Integer totalReplacement = totalReplacement + replacement return replacement End Function Public Function AddTotalGSF(ByVal thisGSF AS integer) AS Integer totalGSF = totalGSF + thisGSF return thisGSF End Function Public Function GetTotalReplacement() return totalReplacement End Function Public Function GetTotalGSF() return totalGSF End Function Randy Sims ~Blog: http://www.sqlPunch.com

I don't really see why, but when I write code there are a few things different. No Dim, the Function would not have 'AS Integer' after the variable. Perhaps you're using a different version. Here is how I would have written it: Public totalReplacement as Integer Public totalGSF as Integer Public Function AddTotalReplacement(ByVal replacement AS Integer ) totalReplacement = totalReplacement + replacement End Function Public Function AddTotalGSF(ByVal thisGSF AS integer) totalGSF = totalGSF + thisGSF End Function Public Function GetTotalReplacement() return totalReplacement End Function Public Function GetTotalGSF() return totalGSF End Function Hope it helps. MartinaMartina White

Still returns 0s, and the Add.... functions stopped working. Put it back to the original and it started working again on the Add... parts. Still 0s on the bottom.Randy Sims ~Blog: http://www.sqlPunch.com

Are you grouping on anything? If so, that can interfere with your code. You would need to reset the total Replacement value after each Dept change, like this. Public CurrentDept as Object Public Function Reset_YTD_Dept (Dept as Object) If Not Dept=CurrentDept CurrentDept = Dept totalReplacement = 0 End If Return return totalReplacement End Function For troubleshooting, I would also try returning the totalReplacement and totalGSF at each call to the Add functions to see what is happening as it iterates through. Like this: Public Function AddTotalReplacement(ByVal replacement AS Integer ) AS Integer totalReplacement = totalReplacement + replacement return totalReplacement End Function Public Function AddTotalGSF(ByVal thisGSF AS integer) AS Integer totalGSF = totalGSF + thisGSF return totalGSF End Function Cheers, MartinaMartina White

Great troubleshooting tip, thanks! It's totaling as it goes, just as you would expect. This might be part of what you're talking about. The AddTotalX is occurring within group 2's header. The GetTotalX occurs in group 1's footer. But that's where I need to see it, and seems to be what the description about this piece of code describes it doing. Is it tripping up on that grouping? Is there some way to jump it?Randy Sims ~Blog: http://www.sqlPunch.com

I haven't done this in awhile, but here's what I think you need to do. If not, it should at least give you some idea where to start. I think this is what you have now: Group 1 Header Group 2 Header - AddTotalX Group 2 Footer Group 1 Footer - GetTotalX I think this is what you need: Group 1 Header Group 2 Header <Fields!Group2Total> Group 2 Footer Group 1 Footer - <AddTotalX from ReportItems!Group2Total> <GetTotalX> Here's how: Name the Total field in your Group 2 Header "Group2Total" Add a List box to the Group 1 Footer for the Group 1 Footer Total Add 2 textboxes to the Listbox. Textbox1 (very small but don't hide): =Code.AddTotalReplacement(ReportItems!Group2Total.Value) This totals up all the values Textbox2: =Code.GetTotalReplacement This displays the sumMartina White

Alright, did as you said. It gives me the error, "Report item expressions can only refer to other report items with the same grouping scope or a containing grouping scope." But the way you described it above, that is exactly the gist of it. Thanks for your help with this!Randy Sims ~Blog: http://www.sqlPunch.com

If it's adding up correctly in the AddTotalReplacement function, try placing the total in a textbox in grouping 2, and then refer to that ReportItem in Grouping 1.Martina White

Jin Chen, Your solution works, but now I'm trying to use =Code.GetTotal() in an other tablix to show only the totals and I get zero's. =(ReportItems!TextboxXX.Value) also doesn't give a result. Thanks, Johan

Martina, I am attempting to replicate this code in our report - and am having trouble 'traversing' the groups to get totals to calculate properly. Here is a representation of our NEEDS: Clinic Header PCP Site Header PCP Footer - CountDistinct(Member)/CountDistinct(Member,"PCP") PCP Site Footer - Average of PCP values Clinic Footer - Average of PCP Site values Here is what I have CONFIGURED: Clinic Header PCP Site Header PCP Footer - AddTotal (This is working) PCP Site Footer - GetTotalX (This is working) Clinic Footer - Average of PCP Site values (Can't get this to work) Each time I do this - it provides me just the value of the last PCP Site Footer GetTotalX, instead of the 'aggregate' of all PCPSite GetTotalX values. Any help would be much appreciated. Thanks Justin

Hi Justin, Haven't done this recently, but it sounds like the code might be resetting after the PCP Site Footer grouping, rather than retaining the values from each iteration of the group. Try including the "Average of PCP Site values" in the PCP Site Footer to see if it adding them up correctly. You should see the values slowly changing through each grouping. In fact I would just return the CountDistinct while troubleshooting, rather than taking the average of an average. Easier to track. If you're still stuck, please include your code. Cheers, Martina Martina White