• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4282
  • Last Modified:

Need equivalent of "count distinct where" in Reporting Services

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.

1 Solution
=IIF(Fields!DeficiencyId.Value is null, CountDistinct(Fields!VendorNum.Value), "NULL")
larrysunAuthor Commented:
Perfect!  Thanks.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now