• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4295
  • 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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