I have a countdistinct query that runs well in enterprise manager but not in reporting services:
select count (distinct VendorNum) from tblMaster where deficiencyID is null
(returns expected results)
When I convert the query to what RS seems to like:
CountDistinct(Fields!VendorNum.Value) where Fields!DeficiencyId.Value is null
I get a message indicating it is not valid. If I remove the 'where' clause, it work fine. But it the results are not as desired.
Any ideas on how to accomplish this in Reporting Services? We don't do it in the stored procedure that produces the resultset because the user has the opportunity to use grouping or not, and to pick from several grouping values.