Multiple filter conditions on same field in reporting services

Question

I have a report model in reporting services containing 3 tables:

• Persons
• Backgrounds
• PersonBackgrounds

The content of the tables are:

Persons (ID, NAME)

1, John Doe

2, Jane Doe

Backgrounds (ID, BACKGROUND)

1, Female

2, Male

3, Single

4, Married

PersonBackgrounds (PERSONID, BACKGROUNDID)

1, 2 1, 3

If i create a new dataset in SSRS and add a filter to include only Males the query returns the "John Doe" record as expected. But as soon as i add the second filter to return "single males" the record set is empty.

Is it possible to have multiple filters on the same column/field where both conditions needs to be meet. Or is there any other way this can be obtained?

Answer 1

I don't know how to do it with filters on the report, but with SQL you can do it:

Select  p.*
From    Persons p 
        INNER JOIN PersonBackgrounds pb1 ON p.ID = pb1.PersonID
        INNER JOIN PersonBackgrounds pb2 ON p.ID = pb2.PersonID
WHERE
    pb1.BackgroundID = 2    --Male
AND pb2.BackgroundID = 3    --Single

You need to join to the PersonBackgrounds table twice because you are using it like two different lookup tables.