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

Answer 1

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

Answer 2

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

Answer 3

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
 

 
  

Answer 4

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

Answer 5

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/

 

 

Answer 6

Brilliant Solution....

Thanks a  lot

 

Answer 7

Thanks

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

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

Answer 14

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

Answer 15

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

Answer 16

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

Answer 17

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

Answer 18

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

Answer 19

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

Answer 20

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

Answer 21

Very helpful post. Thank you.

Answer 22

Nicely Done!

Answer 23

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

Answer 24

Hi Jin,

I'm using your custom code and e.g. the =Code.GetTotal() works as an expression for a cell in the tablix - only for the current page however! I am trying to get the total for the report in its entirety, such as in the footer (?) and then export this (report) total to Excel. Thoughts?

Answer 25

I am trying to get the sum of the group Area and grand total of all areas. Is there a way that I can grab whatever is in the texbox and sum them up. The values are a if expression what doesn't for some reason sum up correctly of I try to put them in a total sum field.

ANY HELP WOULD BE WONDERFUL!!

--Roland Rodriguez

Answer 26

Kevin,

Did you ever figure out the excel export?  I am having same problem.  The amount renders in webpage but is zero when exported to Excel.  Thanks, Brian

CODE:

'MyVariables
Public totalLWSBeginLifetimeExpense as Integer

' varLWSBeginLifetimeExpense
Public Function getLWSBeginLifetimeExpense()
	return totalLWSBeginLifetimeExpense
End Function

Public Function addLWSBeginLifetimeExpense(ByVal thisLWSBeginLifetimeExpense AS Integer )
	totalLWSBeginLifetimeExpense = totalLWSBeginLifetimeExpense + thisLWSBeginLifetimeExpense
	return thisLWSBeginLifetimeExpense
End Function

GROUP VARIABLE:

= Code.addLWSBeginLifetimeExpense(CDbl(
IIF(Parameters!boolUseLwsBalance.Value = false, 0,
Lookup(Fields!ProjectId.Value, Fields!ProjectId.Value, Fields!LWSBegin_LifetimeExpense.Value, "dsCAPEXAmountsCustomDataUpload"))
))

FOOTER TEXTBOX EXPRESSION: 

Code.getLWSBeginLifetimeExpense()