I have a report with several groups and totals. At the lowest grouping, I am adding several lines for summary information and I'm adding expressions to populate those lines. One expression I've added is: =Sum(Fields!Gift_Card_Pay.Value) + Sum(Fields!Cash_Pay.Value) + Sum(Fields!Credit_Card_Pay.Value) + Sum(Fields!Check_Pay.Value) which gives me a summary of 4 different totals of column data. Now I want to add another summary expression which, in English, will be the total of all credit card payment WHERE a different column has a value "VISA" so I need something like: =Sum(Fields!Credit_Card_Pay.Value) where (Fields!CrCardType.Value="VISA") CrCardType being another column which indicates either VISA , Discoverer , some other card or is null for non-credit card payments. So how can I sum a column using a different column as a filter?

Hi Escanaba, Please try the expression below. =SUM(IIF(Fields!CrCardType.Value="VISA", Fields!Credit_Card_Pay.Value,0))

Unfortunately, SSRS won't let you aggregate a statement that uses an Iif statement, nor will it let you aggregate an existing aggregate. There is no way to do this using the native functionality of SSRS except through custom code, which has it's own inherent problems. But I do have a work around. If your data set is a SQL query you are golden. Add a column to your dataset using the following sql CASE WHEN CrCardType='VISA' THEN Credit_Card_Pay ELSE 0 END as Credit_Card_Pay_Visa Now you can add the following expression to your table: =Sum(Fields!Credit_Card_Pay_Visa.Value) If your data set is a cube you will need to create a custom calculation in your dataset filtering the measure on the Visa attribute. And then add the expression to your report as above. I have blogged about more complex examples of how to aggregate an aggregate when you can't hardcode something like Visa into your dataset. You can use math to essentially zero out the unwanted values, if you find the right combination. http://dataqueen.unlimitedviz.com/2011/05/how-to-build-a-weekly-report-with-most-recent-day-and-week-to-date-values-filtered-correctly-for-last-year/ I hope this helps.Martina White

Tried but it didn't work. It's not evaluating correctly since it sets the expression to 0 (or whatever I put the default value to). There are many rows with payments. Some are VISA, others Discoverer, etc.. and others null. Would the above look at each row separately? That's what I need. Maybe some sort of calculation? Trying to avoid using a subreport but maybe will have to.