How to set a parameter in SSRS subreport that is a static list

Question

I have a SSRS report that uses a drill thru subreport. The subreport is used 3 times in the main report and takes several parameters. In 2 of the subreports I set one of the parameters, SexAtBirth, using a static value, Female or Male. Here is a screen shot of the parameter:

For the 3rd subreport, I want to set the parameter SexAtBirth to Not Male or Female. In SQL it would be SexAtBirth NOT IN ('Female', 'Male')

The parameter SexAtBirth is set to take multiple values.
I don't know how to create an expression to NOT include a static list of values.

UPDATE I am trying to create a static list for the values and the error is SexAtBirth parameter missing a value I am using Report Builder 3.0 on SQL Server 2008 R2. This is what I have tried: in the dropdown list:

"Female", "Male"

Female, Male

My understanding is once you have a parameter with multiple values, it's treated like a list or array you would use in the IN() clause. Are there only male, female, null or both in SexAtBirth? What query is the sub report running? You might be able to use Case in a Where clause. — Kevin
This is my where clause: WHERE AgeRange = @AgeRange AND SexatBirth NOT IN (@SexAtBirth) I would like to create a string like this: Female, Male — Gloria Santin
When I run the query in Query Designer, for the SexAtBirth parameter I can input Female and it will return only male which is correct and input Male to return only female. But I want to create a list of 'Female, Male' and have it only return the unknowns. — Gloria Santin

Kevin Kevin · Accepted Answer · 2017-01-10T21:15:36

I have done something similar where I was using the active directory to filter a report (except for admins). In this example, @UserID was the domain user's name which comes from the browser session.

Where t.[Date] Between @DateFrom And @DateTo And
(u.adsGuid = @UserID OR @UserID In (@UserAdmins))

However, I was essentially using a different parameter in SSRS to supply input for another parameter. This is probably not necessary here. For readability and to move my head away from inverse logic, I would try something like the below:

WHERE AgeRange = @AgeRange AND 
    Coalesce(SexAtBirth, '') = Case @SexAtBirth 
        When 'Male' Then 'Female' 
        When 'Female' Then 'Male' 
        Else Coalesce(SexAtBirth, '') End

This should handle any NULLs and provide the inverse results. If @SexAtBirth is anything but male/female, give us the rest.

How to set a parameter in SSRS subreport that is a static list

1 Answers