Reputation: 37
I'm having some issues with SSRS and multi select values. Basically, apart from the Date parameters (@From) and (@To), all other parameters, I want to be optional. Multiple values are allowed and pulled from a query but if the user doesn't want to specify a value I want it to pull all items.
So for example if the Ward parameter hasn't specified a value it includes all values.
Any ideas?
SELECT
rTranslog.[Month]
,CONVERT(datetime,rTranslog.LogDateTime, 103) AS [Date]
,rTranslog.IssueDate
,rTranslog.Site
,rTranslog.CaseNo
,rTranslog.NSVCode
,REPLACE(rProduct.LabelDescription, '!', ' ') AS [Drug]
rProduct.ReportGroup
,rTranslog.BNFCode
,rTranslog.Ward
,rConsultant.ConsultantName
,rSpecialty.Description AS [SpecialtyName]
,rTranslog.Kind
,rTranslog.LabelType
,rTranslog.Qty AS [Quantity]
,rTranslog.CostExTax/100 AS [CostGBPExVat]
,rTranslog.Cost/100 AS [CostGBPIncVAT]
,rTranslog.DispId
FROM
rTranslog
INNER JOIN rProduct
ON rTranslog.NSVCode = rProduct.NSVCode
LEFT JOIN rConsultant
ON rConsultant.Consultant = rTranslog.Consultant
LEFT JOIN rSpecialty
ON rTranslog.Specialty = rSpecialty.SpecialtyCode
WHERE rTranslog.IssueDate BETWEEN (@To) AND (@From)
AND rTranslog.Ward IN (@Ward) OR (@Ward) Is Null
AND rProduct.LabelDescription IN (@Drug) OR (@Drug) Is Null
AND rTranslog.CaseNo IN(@CaseNo) OR (@CaseNo) Is Null
AND rSpecialty.Description IN(@Specialty) OR (@Specialty) Is Null
AND rTranslog.Site IN (@Site)
ORDER BY [Date] ASC;
Upvotes: 1
Views: 716
Reputation: 21713
You are almost there, just a slight change should give you what you want.
WHERE rTranslog.IssueDate BETWEEN (@To) AND (@From)
AND (rTranslog.Ward IN (@Ward) OR @Ward Is Null)
AND (rProduct.LabelDescription IN (@Drug) OR @Drug Is Null)
AND (rTranslog.CaseNo IN(@CaseNo) OR @CaseNo Is Null)
AND (rSpecialty.Description IN(@Specialty) OR @Specialty Is Null)
AND rTranslog.Site IN (@Site)
Upvotes: 1