SSRS CountDistinct on Dataset

Epicor ERP 10
1

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?

Thanks,
Jonathan

2

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
3

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: