SSRS CountDistinct on Dataset

Good morning everyone,

I’m trying to a CountDistinct on a dataset value.

The value of the field I’m trying to do count on is:

=IIF((First(Fields!CustID.Value, “Customer”) = “STELL”) AND (Fields!ProdCode.Value = “MC”) OR (Fields!ProdCode.Value= “MT”) OR (Fields!ProdCode.Value= “CH”) OR (Fields!ProdCode.Value= “HH”) OR (Fields!ProdCode.Value= “XTL”), “Demo”, “Work Ready”)

I thought I could do:

=CountDistinct((IIF(Fields!CustID.Value, “Customer”) = “STELL”, AND (Fields!ProdCode.Value = “MC”) OR (Fields!ProdCode.Value= “MT”) OR (Fields!ProdCode.Value= “CH”) OR (Fields!ProdCode.Value= “HH”) OR (Fields!ProdCode.Value= “XTL”,Nothing)))

but I get the error: Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.

Does anyone know a way I can do a Count on this field?


I don’t think a count distinct can count an expression. It needs to be a field. Make your expression a calculated field then do a count distinct of that.

1 Like

Hey Kent!!

No you can’t. I recreated the TWIP table in the report and added Customer and CustID then made a calculated field. It all worked in the end. :slight_smile: