reporting-servicesssrs-2012ssrs-2019

SSRS Expression to Filter and then Count

I have a large query that each record has a unique WO Number and a division. I would like to use an expression in the text box to give me a number of unique work orders with a specific division.

Example:

Dataset name: CorrectiveWOsOPEN

WO_NUMBER  DIVISION
20-005802  Maintenance
20-005803  Maintenance
20-014771  Maintenance
20-027196  PCA
20-027197  Water
20-027198  Water
18-005079  Water
18-005541  Water

Desired Result for filter for 'Water" division and count the number of records:

4

I got the count expression but not know how to attach on the filter for "Water" in division column.

=Count(Fields!WO_NUMBER.Value, "CorrectiveWOsOPEN")

Any ideas?

Solution

  • Put an IIF inside a sum to count the ones with your desired filter.

    =SUM(IIF(Fields!DIVISION.Value = "Water", 1, 0), "CorrectiveWOsOPEN")

    This will check each record for a Division of Water and return 1 if it is and zero if not and then sums these up.

    I suppose you could also do the same with COUNT and NOTHING but I think the former method is more elegant (for lack of a better word).

    =COUNT(IIF(Fields!DIVISION.Value = "Water", Fields!WO_NUMBER.Value, NOTHING), "CorrectiveWOsOPEN")