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 Journeyman Group: General Forum Members Last Login: Friday, December 06, 2013 3:44 PM Points: 79, Visits: 251
 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: Thursday, December 05, 2013 10:51 AM Points: 16, Visits: 142
 Wrapping your expression in a "sum" doesn't work? e.g. Sum()
Post #1497594
 Posted Monday, September 23, 2013 5:34 PM
 SSC Veteran Group: General Forum Members Last Login: 2 days ago @ 10:12 PM Points: 240, Visits: 843
 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
 Old Hand Group: General Forum Members Last Login: Yesterday @ 11:38 PM Points: 353, Visits: 2,604
 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
 Old Hand Group: General Forum Members Last Login: Yesterday @ 11:38 PM Points: 353, Visits: 2,604
 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