Search code examples
reporting-servicesaggregateiif

Multiple datasets Count with IIF in SSRS

I am trying to write an expression in SSRS which counts only specific data using IIF. I found the following solution: =Sum(IIF(Fields!Program.Value = "FC", Fields!QuantityToShip.Value, 0))

The code above works but only when there is ONE dataset, while I have several. Here is the code I wrote:

=Count(IIF(Fields!Mgroup.Value,"DataSet1"=303,1,0))

I get the aggregation error:

Textbox refers directly to the field ‘Mgroup’ without specifying a dataset aggregate

I added a sum:

=Count(IIF(Sum(Fields!Mgroup.Value,"DataSet1")=303,1,0))

Still getting the same error.

Why is that? What can I put instead of Sum? All I need is to count how many groups named 303 I have.

Solution

  • The expression you used has some sintax errors. The Count function only aggregate from the scoped Dataset.

    Try this:

    =LookupSet(303,Fields!Mgroup.Value,Fields!Mgroup.Value,"DataSet1").Length

    Let me know if this helps you.