Adding column values together

Question

Adding column values together

Trybbe

Hall of Fame

Points: 3967
More actions
February 4, 2009 at 5:26 am

#383864

Hi
How do I add column values together in SSRS 2005.
I tried this but no luck please assist
=IIf (sum(Fields!Terminations.Value + Fields!TransferOut.Value + Fields!HeadcountEnd.Value)) = 0, 0

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

Try something like

=IIf (sum(Fields!Terminations.Value + Fields!TransferOut.Value + Fields!HeadcountEnd.Value) = 0, 0,sum(Fields!Terminations.Value + Fields!TransferOut.Value + Fields!HeadcountEnd.Value))

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Trybbe Hall of Fame Points: 3967 More actions · Answer 2

Hi Carolyn

Thanks for the help I will try it out. as I had already managed to drumm up something like this

=IiF((ABS(Fields!Terminations.Value) + ABS(Fields!TransferOut.Value) + ABS(Fields!HeadcountEnd.Value))=0,0,Round((ABS(Fields!Terminations.Value) + ABS(Fields!TransferOut.Value))/(ABS(Fields!Terminations.Value) + ABS(Fields!TransferOut.Value)+ ABS(Fields!HeadcountEnd.Value))*100,2))

and it's working so I'll try your method a little later as it seems shorter

Kind Regards

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 3

[font="Verdana"]be a little careful here. You could well get different behaivour depending on what you actually want.

For example, if you have three values, A, B, C, and they are set to 10, -6, -4, then:

ABS(A + B + C) will give you 0.

ABS(A) + ABS(B) + ABS(C) will give you 20.

So the question is: do you want the absolute value of the sum, or do you want the sum of the absolute values? The answers posted here include both.[/font]

Trybbe Hall of Fame Points: 3967 More actions · Answer 4

Bruce W Cassidy (2/4/2009)
[font="Verdana"]
ABS(A) + ABS(B) + ABS(C) will give you 20.[/font]

Thanks Bruce but this one has yielded the desired results except for the Grand total which gives me a incorrect Results.

=IiF(ABS(Sum(Fields!Terminations.Value) + ABS(Sum(Fields!TransferOut.Value)) + ABS(Sum(Fields!HeadcountEnd.Value)))=0,0,Round(ABS(Sum(Fields!Terminations.Value) + ABS(Sum(Fields!TransferOut.Value)))/(ABS(Sum(Fields!Terminations.Value)) + ABS(Sum(Fields!TransferOut.Value))+ ABS(Sum(Fields!HeadcountEnd.Value)))*100,2))

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 5

I think the best way would be having function in code window:

Public Function Total(FirstN as object, SecondN as object, ThirdN as object) As Object

Total = FirstN + SecondN + ThirdN

End Function

Cheers,
Hari
Tips & Tricks for SQL BI Developers