Calculate Sum of an Expression field

Question

Post reply

Calculate Sum of an Expression field

sharonsql2013

SSCertifiable

Points: 5908
More actions
September 23, 2013 at 3:21 pm

#282872

Here is the situation :
Transaction Type Cr Amount Db Amount Crvalue DBValue
C 500100 400
D100 300 200
CrValue and DbValue are Calculated Fields so I have written the following expression:
Expression for Cr value
= switch (Field!Transactiontype.value )= “C”,Field!CrAmount.Value-Field!DbAmount.value
Experssion for DBvalue
= switch (Field!Transactiontype.value )= “D”,Field!DbAmount.Value-Field!CrAmount.value
Up until here , I am able to see the above data fine.
But ,now I need to get the total sum the Expression CrValue and DBValue.
How do I calculate the Sum of an Expression field.
Kindly advise

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

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

metralgia Ten Centuries Points: 1005 More actions · Answer 1

Wrapping your expression in a "sum" doesn't work? e.g. Sum(<expression>)

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

I don't do anything in Reporting Services, so this could be totally off base, but the following works for me:

declare @t1 table

(

Trans_type char,

Credit int,

Debit int,

CR_Value as (case when Trans_Type = 'C' then credit-debit else 0 end),

DB_Value as (case when Trans_Type = 'D' then debit-credit else 0 end)

)

Insert @t1 (Trans_Type, Credit, Debit) values

('C', 500, 100),

('C', 100, 200),

('D', 100, 300),

('D', 500, 100)

select * from @t1

select Trans_type, SUM(CR_Value), SUM(DB_VALUE)

from @t1

group by Trans_type

Give this a shot in SSMS and hopefully the same principles can be applied for Reporting Services.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

pietlinden SSC Guru Points: 63557 More actions · Answer 3

you could add a calculated value to your dataset that did the calculation "row-wise" and then use that calculated value in your SUM / aggregate expression

pietlinden SSC Guru Points: 63557 More actions · Answer 4

you could add a calculated value to your dataset that did the calculation "row-wise" and then use that calculated value in your SUM / aggregate expression

Calculate Sum of an Expression field

Cookies on SQLServerCentral