SSRS IIF Syntax with Multiple Datasets
I have a report with 2 data sets and would like to perform a SUM operation in a textbox expression. The problem arises when I want to perform an IIF in the sum since I only want a particular category of values summed.
I would like to get a sum of all the "Good" ranking values from the dsRetrieveCustomerAssetScores dataset. Please note there is more than one data set in the report, so I need to specify the scope when using the aggregate function. Below is the code I've tried (along with other permutations).
=Sum(iif(Fields!ranking.Value,"Good",1,0), "dsRetrieveCustomerAssetScores")
Any ideas?
Answer
You may have more than one dataset in your report, but I don't think it's possible to have more than one dataset per tablix. (Subreports within the tablix may be bound to a different dataset, but anywhere within the subreport will only be accessing that other dataset.)
The scope specified within aggregation formulas is normally related to groups within the tablix, not the datasources.
So, the code:
=Sum(iif(Fields!ranking.Value,"Good",1,0))
- should work within your tablix, as long as that tablix is accessing the dsRetrieveCustomerAssetScores dataset.