Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculate Sum of an Expression field Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, September 23, 2013 3:21 PM
 SSC-Addicted Group: General Forum Members Last Login: Wednesday, November 30, 2016 3:27 PM Points: 406, Visits: 1,217
 Here is the situation :Transaction Type Cr Amount Db Amount Crvalue DBValueC 500 100 400 D 100 300 200CrValue 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.valueExperssion for DBvalue= switch (Field!Transactiontype.value )= “D”,Field!DbAmount.Value-Field!CrAmount.valueUp 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 Group: General Forum Members Last Login: Tuesday, October 18, 2016 4:42 PM Points: 22, Visits: 231
 Wrapping your expression in a "sum" doesn't work? e.g. Sum()
Post #1497594
 Posted Monday, September 23, 2013 5:34 PM
 Say Hey Kid Group: General Forum Members Last Login: Today @ 7:22 AM Points: 691, Visits: 3,774
 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 @t1select Trans_type, SUM(CR_Value), SUM(DB_VALUE)from @t1group 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
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 9:59 PM Points: 2,134, Visits: 12,262
 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
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 9:59 PM Points: 2,134, Visits: 12,262
 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

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.