Multiple where clauses as dataset filters in SSRS

Question

I'm creating an SSRS report using the SQL query below as a single Dataset for the report. As you can see I have different Where clauses, these would be used depending upon which Parameter dropdown is selected when running the report. If I only needed to pass a single value to a single where I could just use the selected value of the dropdown. However, it seems like I need to pass a dummy value from the dropdown and then based on that selection, do a filter Switch statement and determine which where clause to use. Can I pass a SQL string to my dataset based upon which dropdown is selected? What other ways can I accomplish this?

SELECT DISTINCT
    cip.CaseID
   ,cip.ProsNum AS FileNum
   ,cip.CaseInvPersLastName + ', ' + cip.CaseInvPersFirstName AS Attorney
   ,c.CaseStatusCode
   ,c.CaseStatusDesc
   ,dbo.ConvertTimeToClientTimeZone(ce.EventDT) AS OpenDate
   ,cip2.CaseInvPersLastName AS Respondent
   ,j.Dispo
   ,cd.Description AS DispoDesc
FROM jw50_Case c
INNER JOIN jw50_CaseInvPers cip
    ON cip.CaseID = c.CaseID AND cip.InvolveTypeCode = 'CP007' AND cip.CaseInvPersActive = 1
INNER JOIN jw50_CaseInvPers cip2
    ON cip2.CaseID = c.CaseID AND cip2.InvolveTypeCode = 'CP001' AND cip2.CaseInvPersActive = 1
INNER JOIN jw50_CaseEvent ce
    ON ce.EventID = (SELECT TOP 1 EventID FROM jw50_CaseEvent WHERE CaseID = cip.CaseID ORDER BY EventDT)
LEFT JOIN devJudgement j
    ON j.CaseID = cip.CaseID
LEFT JOIN devCountDispo cd
    ON cd.Code = j.Dispo

-- Sent to screening
--WHERE c.CaseStatusCode = 'CS007'

-- Closed
WHERE c.caseStatusCode = 'CS006' AND j.Dispo IN ('CD002','CD003') AND c.CaseTypeCode = 'TY001'

-- Closed - Info Only
--WHERE c.caseStatusCode = 'CS006' AND j.Dispo IN ('CD001') AND c.CaseTypeCode = 'TY001'

-- Reopened
--WHERE c.caseStatusCode = 'CS008' AND c.CaseTypeCode = 'TY001'

-- Under appeal
--WHERE c.caseStatusCode = 'CS009' AND c.CaseTypeCode = 'TY001'

    AND ce.EventDT >= @startDate AND ce.EventDT < @endDate + 1

Order BY cip.ProsNum

Please provide any assistance. Thanks.

Hart CO Hart CO · Accepted Answer · 2014-01-23T17:15:34

Use parenthesis to create groups of criteria along with OR, so that for any value of your passed variable you can use one query and the proper WHERE criteria will get used, something like:

  WHERE ((@variable = 1 AND c.CaseStatusCode = 'CS007')
         OR  (@variable = 2 AND c.caseStatusCode = 'CS006' AND j.Dispo IN ('CD002','CD003') AND c.CaseTypeCode = 'TY001')
         OR  (@variable = 3 AND  c.caseStatusCode = 'CS006' AND j.Dispo IN ('CD001') AND c.CaseTypeCode = 'TY001')
         OR  (@variable = 4 AND c.caseStatusCode = 'CS008' AND c.CaseTypeCode = 'TY001')
         OR  (@variable = 5 AND c.caseStatusCode = 'CS009' AND c.CaseTypeCode = 'TY001'))      
    AND ce.EventDT >= @startDate AND ce.EventDT < @endDate + 1

In the above situation, one of the @variable = n criteria must be met, AND the bottom line with the date criteria, adjust as needed.

Multiple where clauses as dataset filters in SSRS

1 Answers