SSRS Expression still returns a count for all fields

Question

Would anyone be able to help me with my SSRS expression? It seems to be returning a count for all records even though I have certain requirements in my iif function.

Here is what I currently have:

=Count(Iif((Fields!METHOD.Value = "Something") AND (Fields!CATEGORIZATION.Value = "Incident") AND (Fields!SOURCE.Value = "Phone"), Fields!MONTH.Value = 1, 0))

The idea is to return a 1 and count it for each record that meets these requirements. If anyone needs me to elaborate any further, please let me know and I will edit/comment on your questions.

@IanPreston - Replacing Count with Sum results in an error: uses a numeric aggegate function on data that is not numeric. any other ideas? — scapegoat17
OK, I had to chance to have a proper look... Please see answer! — Ian Preston

Ian Preston Ian Preston · Accepted Answer · 2015-01-27T18:53:18

As per the earlier comment, this should be a Sum and not Count - the latter simply counts the non-null values, so will always give the same result no matter what the IIf returns.

You also don't need Fields!MONTH.Value = 1 - just return either 1 or 0 in the IIf is enough:

=Sum(
  Iif(
    (Fields!METHOD.Value = "Something")
      AND (Fields!CATEGORIZATION.Value = "Incident")
      AND (Fields!SOURCE.Value = "Phone")
    , 1
    , 0
  )
)

SSRS Expression still returns a count for all fields

1 Answers