SSRS Using an expression to specify values for parameters

Question

I am trying to have a parameter that either filters out interns or includes interns and every other job title using a column that holds 'Yes' or 'No' whether that job title is an intern.

The basics of my code are like this:

SELECT
Date
,BillHours
,OrgArea
,Jobtitle
,InternYesNo
FROM(
SELECT
Date
,BillHours
,OrgArea
,Jobtitle
,CASE WHEN LEFT(jobtitle, 6) = 'Intern' THEN 'Yes' ELSE 'No' END AS 'InternYesNo') MainQuery
WHERE InternYesNo IN(@includeinterns)

So I'm thinking I want to specify the available values in the @includeinterns parameter, but I think I would need to have one of the values be an expression so that it includes both 'Yes' and 'No' rows based on the InternYesNo column.

My question is how do I write an expression for specifying values so that one of the available values includes both 'Yes' and 'No' rows? Do I need an additional dataset to hold those values? Or is there better way to accomplish this?

score 0 · Answer 1 · answered Jul 05 '22 at 18:17

0

I think I have figured out a solution. I updated my code to this:

SELECT
Date
,BillHours
,OrgArea
,Jobtitle
,InternYesNo
FROM(
SELECT
Date
,BillHours
,OrgArea
,Jobtitle
,CASE WHEN LEFT(jobtitle, 6) = 'Intern' THEN 'Yes' ELSE 'No' END AS 'InternYesNo') MainQuery
WHERE (InternYesNo = @includeinterns OR @includeinterns = 'abc')

Then I specify available values with one labeled as 'No' and the value is 'No' and the other is labeled as 'Yes' with a value of 'abc'.

answered Jul 05 '22 at 18:17

scorbin

9
5

I would have just done `WHERE (InternYesNo = 'No' OR @includeinterms = 'Yes')` – Alan Schofield Jul 05 '22 at 21:25
That also works and might be a bit more elegant. Thanks! – scorbin Jul 06 '22 at 15:05

SSRS Using an expression to specify values for parameters

1 Answers1