Monday, March 19, 2012

Another SUM limitation problem

I am trying to simply do a rather simply you'd think.... SUM in my textbox on 3 COUNT fields in 3 tables...it's not possible, I get errors whatever way I try this. Look at the Grand Total, that's what I want....a sum of the 3 black fields that have COUNT in them. If this is not possible in SSRS 2005, then Microsoft missed something huge that totally degrades their entire platform, this sucks!

I'm so sick and tired in the past 2 months, very spent on battling SSRS 2005 limitations on Grand Totals like this on many occasions both in tables and freeform textboxes. If you can't do an aggregate on an aggregate then this interface is useless!

http://www.webfound.net/grand_total.jpg

You should be able to do this with a workaround and Code. On each count row, add a hidden cell that assigns a value to a private variable. In your text box, run the code to add up your values.

You should be able to do this with an array. But here is a quick and dirty, assuming you only have two count fields. For simplicity, they are called item1 and item2.

So the cell next to your first count would call code.SetItem1(ReportItems!item1.Value)

The next one would reference the SetItem2 function.

Your textbox then calls GrandTotal()

Code behind:

Dim private _item1 as Integer
Dim private _item2 as Integer

Public Function SetItem1(ByVal item1 As Object) As Integer

_item1 =item1
End Function


Public Function SetItem2(ByVal item2 As Object) As Integer

_item2 =item2
End Function

Public Function GrandTotal() as Object

Return _item1 + _item2

End Function

No comments:

Post a Comment