CODEWITHSUNDEEP
reporting-servicesparametersmultivalue
MusicMan
MusicMan

Reputation: 37

SSRS multiple values optional

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

Answers (1)

Alan Schofield
Alan Schofield

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

Related Questions