CODEWITHSUNDEEP
reporting-servicesssrs-2012
SASUSMC
SASUSMC

Reputation: 681

SSRS Count Distinct with iif filter

So this is a pretty complex one. I am trying to put a filter on a count distinct in SSRS. Here is the situation.

I have 3 datasets in the report. All 3 datasets have the same fields but are built on different code.

I want to count the distinct hospitals in one of the datasets while applying a filter to one of the fields.

For example I want to Count distinct Hospital codes when the DatePeriod is between 3 months ago and today. Here is what I have.

=iif((Fields!DatePeriod.Value, "Rpt_WinBack") between DateAdd("m",-3,Today()) and DateAdd("m",0,Today()), (CountDistinct(Fields!HospCode.Value, "Rpt_WinBack") & " Hospitals"),nothing)

I currently get an error that states:

"Textbox4 refers directly to the field 'DatePeriod' without specifying a dataset aggregate. When the report contains multiple datasets, filed references outside of a data region must be contained within aggregate functions which specify a dataset scope."

It is possible that a hospital will show up on multiple dates.

Thanks, Scott

Upvotes: 0

Views: 6633

Answers (1)

BarneyL
BarneyL

Reputation: 1362

Try this instead:

=CountDistinct(iif((Fields!DatePeriod.Value, "Rpt_WinBack") between DateAdd("m",-3,Today()) and DateAdd("m",0,Today()), (Fields!HospCode.Value, "Rpt_WinBack"),nothing )) & " Hospitals"

Upvotes: 0

Related Questions