Calculate Sum of an Expression field

 Author Message sharonsql2013 SSCrazy Group: General Forum Members Points: 2606 Visits: 1265 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 metralgia Mr or Mrs. 500 Group: General Forum Members Points: 529 Visits: 248 Wrapping your expression in a "sum" doesn't work? e.g. Sum( LinksUp Hall of Fame Group: General Forum Members Points: 3111 Visits: 4936 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/ pietlinden SSC-Dedicated Group: General Forum Members Points: 31620 Visits: 15129 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-Dedicated Group: General Forum Members Points: 31620 Visits: 15129 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