多个在SSR中作为数据集滤波器的子句

Question

我正在使用下面的SQL查询作为报告的单个数据集创建SSRS报告。如您所见，我在子句中有不同的位置，这些条款将根据运行报告时选择哪个参数下拉。如果我只需要将一个值传递给一个单个值，我只需使用下拉列表的选定值即可。但是，似乎我需要从下拉列表中传递一个虚拟值，然后基于选择，执行过滤器开关语句，并确定要使用哪个子句。我可以根据选择哪个下拉列表将SQL字符串传递给数据集吗？我还能完成哪些其他方法？

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

请提供任何帮助。谢谢。

Answer 1

使用括号与OR一起创建一组标准，因此对于您传递的变量的任何值，您可以使用一个查询，并且可以使用适当的WHERE标准，例如：

  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

在上述情况下，必须满足@variable = n标准之一，并且具有日期标准的底线，根据需要进行调整。