none
Use ReportItems! to sum all textboxes in a tablix

    Question

  • 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?

    thanks
    Jamie
    http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet
    Saturday, October 10, 2009 8:26 PM

Answers

  • 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

    Monday, October 12, 2009 3:00 AM
    Moderator

All replies

  • 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

    Monday, October 12, 2009 3:00 AM
    Moderator
  • 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!!!

    Wednesday, May 26, 2010 7:09 PM
  • 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
     

     
      

    Thursday, July 01, 2010 9:37 PM
  • 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


    Wednesday, March 30, 2011 12:09 PM
  • 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/

     

     

    • Proposed as answer by Martina White Sunday, June 05, 2011 1:33 PM
    • Unproposed as answer by Martina White Thursday, October 11, 2012 5:48 PM
    Sunday, June 05, 2011 1:33 PM
  • Brilliant Solution....

    Thanks a  lot

     

    Tuesday, January 10, 2012 10:40 AM
  • Thanks
    Tuesday, February 14, 2012 1:45 PM
  • 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


    • Edited by Randy Sims Friday, May 11, 2012 4:11 PM adding more info
    Friday, May 11, 2012 4:09 PM
  • 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.

    Martina


    Martina White

    Friday, May 11, 2012 4:20 PM
  • 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

    Friday, May 11, 2012 4:26 PM
  • 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,

    Martina


    Martina White

    Friday, May 11, 2012 4:54 PM
  • 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

    Friday, May 11, 2012 5:11 PM
  • 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 sum


    Martina White

    Friday, May 11, 2012 5:36 PM
  • 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

    Friday, May 11, 2012 8:38 PM
  • 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

    Friday, May 11, 2012 9:30 PM
  • 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

    Tuesday, July 17, 2012 12:28 PM
  • 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

    Monday, October 01, 2012 5:28 PM
  • 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

    Monday, October 01, 2012 5:54 PM
  • Hi, Mr. Chen,

    I really don't know what is happens, because my getTotal does not return de value of totalBalance even I put in the footer or in the total of Matrix.

    Could you help me?

    Best Regards

    Thursday, July 17, 2014 9:57 PM
  • Hi Giulianno,

    I had the same problem. I fixed my issue by changing the code to take a Decimal rather than an Integer.

    I hope this helps you,

    Bobby

    Thursday, September 11, 2014 8:56 AM
  • Very helpful post. Thank you.
    Thursday, December 17, 2015 11:11 PM
  • Nicely Done!
    Thursday, June 30, 2016 7:18 PM
  • Hi,

    Need a help in Matrix report in SSRS. We have two row group and one column group and we using expression in data field. We want “Sub Total” and “Grand Total” of that expression.

    Below is the reports. Requester Name and Complexity Major is row group and Services is column group. We need sum of Sum/Count (193.5+781.48+147.64) in Pink Cell just besides the 143 for Java Services for all services.

    Requester

    Complexity

    Java Services

    ABC

     

    Sum of Hrs

    Count of Work

    Sum/Count

    Complex

    1935

    10

    193.5

    Medium

    4688.9

    6

    781.48

    Simple

    18749.95

    127

    147.64

    Total

    25373.85

    143

     

    DEF

     

    Sum of Hrs

    Count of Work

    Sum/Count

    Complex

    2515.5

    8

    314.44

    Medium

    598

    3

    199.33

    Simple

    17168

    99

    173.41

    Total

    20281.5

    110

     



    • Edited by techanurag Tuesday, August 09, 2016 11:16 AM
    Tuesday, August 09, 2016 11:15 AM