Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculate Sum of an Expression field Expand / Collapse
Author
Message
Posted Monday, September 23, 2013 3:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 215, Visits: 639
Here is the situation :
Transaction Type Cr Amount Db Amount Crvalue DBValue
C 500 100 400
D 100 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


Post #1497569
Posted Monday, September 23, 2013 5:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 17, Visits: 162
Wrapping your expression in a "sum" doesn't work? e.g. Sum(<expression>)
Post #1497594
Posted Monday, September 23, 2013 5:34 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:43 AM
Points: 344, Visits: 1,322
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/
Post #1497597
Posted Monday, September 23, 2013 8:16 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:17 PM
Points: 710, Visits: 4,529
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
Post #1497621
Posted Monday, September 23, 2013 8:20 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:17 PM
Points: 710, Visits: 4,529
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
Post #1497622
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse