Showing posts with label sum. Show all posts
Showing posts with label sum. Show all posts

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

Another Suggestion

i am new at this. but i need to do a calculation that has to sum a running
value or running value of a running value . here is my expression.
=sum((((Fields!PART_QTY_ON_HAND.Value
- runningvalue((Fields!REQUIREMENT.Value), sum, "tbl1grp3")
* Fields!Temp_Part_Cost.Value ) + .005) * Fields!ANNUAL_CARRYING_RATE.
Value / 1200))
anyone have another suggestion or at least tell me that it can not be done.
thanx, kerries
--
Message posted via http://www.sqlmonster.comKerrie,
I'm not sure what you are asking here. In principle the expression
looks fine although I think your parantheses are in the wrong place. If
you are after a running total of this expression, I just tried a
RunningValue on a ReportItems object, but it didn't like it.
I'd suggest it would be more efficient to try and do this in the SQL
query if you can.
Chris
Kerrie S via SQLMonster.com wrote:
> i am new at this. but i need to do a calculation that has to sum a
> running value or running value of a running value . here is my
> expression.
> =sum((((Fields!PART_QTY_ON_HAND.Value
> - runningvalue((Fields!REQUIREMENT.Value), sum, "tbl1grp3")
> * Fields!Temp_Part_Cost.Value ) + .005) *
> Fields!ANNUAL_CARRYING_RATE. Value / 1200))
> anyone have another suggestion or at least tell me that it can not be
> done. thanx, kerries