September 23, 2013 at 3:21 pm
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
September 23, 2013 at 5:06 pm
Wrapping your expression in a "sum" doesn't work? e.g. Sum(<expression>)
September 23, 2013 at 5:34 pm
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/
September 23, 2013 at 8:16 pm
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
September 23, 2013 at 8:20 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply