Search code examples
reporting-servicesssrs-2008mdxssrs-2008-r2reportbuilder3.0

SSRS: Filter 1 field based on 2 possible values in another field

I don't if I'm being a knucklehead, but I've searched considerably and tried several options: can't make it happen. Here is the issue.

I have the following Data Query: enter image description here

First of all, sorry it's French. Secondly, as you see, the fields are "File number", the "Year" and the "Number of distinct patients".

Wanted result: I want to filter for Case numbers who appear both at 2015 and 2016.

As you see, certain patients showed up several years in a row. However, I want to find out how many showed up in 2015 AND 2016 only (not 2014, etc.) (so a total of 2 visits for the 2 years combined). So solely filtering in my Data Query on 2015 and 2016, doesn't do the job, since it fails to exclude the patients that only showed up once. Furthermore, filtering on 2015 and 2016, and then Count(Fields!File_number.Value)=2 wouldn't work since it fails to exclude the patients that might have been here in 2014.

I have tried several Boolean expressions, but as soon as I include 2 years in my filter, it blanks my tablix out. (Understandibly cause I tell it that Fields!Year.Value must be equal to 2015 and 2016 simultaneously).

So I tried bypassing it by telling it that and did the following: Expression:

=IIF(Lookup(Fields!FileNumber.Value, Fields!FileNumber.Value, Fields!Year.Value, "Dataset")=2015 AND Lookup(Fields!FileNumber.Value, Fields!FileNumber.Value, Fields!Year.Value, "Dataset")=2016, True, False)

It works for 1 year only: as soon as I add 2015 either by and "AND" or by adding an additionnal filter, mytable goes blank.

Any suggestions? Thanks!

Solution

  • Yeah, this isn't an easy thing to solve, you aren't being a knucklehead :) You are correct about the double boolean expressions in the query and the two combined lookups won't work either. In the case of a lookup, LOOKUP returns the first match in a dataset, so every time, both of those looksups are going to return the first value.

    There is another function called LookupSet which does return ALL matches from a query though. It should be possible to create an ugly expression around this function to test specifically whether the string 2015 and 2016 both appear in the results with something like:

    =Join(LookupSet(Fields!FileNumber.Value, Fields!FileNumber.Value, Fields!Year.Value, "Dataset"),",")

    and then substring searching for both "2015" and "2016" (I'll let you fiddle with that part)