SSRS: is there a way of passing the values of a parameter to a "Values" keyword in the SQL query?

Question

I would like to know the correct method for passing the values of a parameter to a "VALUES" keyword in the SQL in the underlying dataset. I'm using Microsoft Report Builder v3.0, querying an MS-SQL database.

At the moment, after a lot of googling and stack-overflowing, I have come up with the following nicely-working SQL in order to find patients with diagnosis codes starting with either "AB" or "XC":

SELECT
    x.PatientId

FROM
  (
    VALUES
      ('AB%'),
      ('XC%')
  ) AS v (pattern)

  CROSS APPLY

  (
    SELECT 
            p.PatientId, 
            p.LastName

    FROM
            dbo.Patient p
            inner join Course c on (c.PatientSer = p.PatientSer)
            inner join CourseDiagnosis cd on (cd.CourseSer=c.CourseSer)
            inner join Diagnosis diag on (diag.DiagnosisSer=cd.DiagnosisSer)


    WHERE       
            diag.DiagnosisCode like v.pattern
  ) AS x
;

However, what I want to do is make the patterns searched for, as generated by the "VALUES" keyword, to be generated when the user selects a drop-down box corresponding to a particular group of patterns. I have used a parameter for this named @Diagnoses, with the label "Grouping1" (there will be other groupings later - I intend to make the parameter multi-valued), and the value "'AB%', 'XC%'", but this doesn't work - the report runs, but returns nothing at all, so clearly I'm doing something wrong.

I have tried to avoid specifiying these diagnosis codes directly in the WHERE clause using the "OR" keyword, as everything I can find along these lines seems to involve using separately declared functions, and the pattern specification / cross-applying solution seemed the neatest.

Can someone help me out?

Thanks in Advance.

Answer 1

You can use a JOIN to combine your parameter values and use the Dataset Expression to build the query text.

="SELECT x.PatientId FROM (VALUES ('" & JOIN(Parameters!VALUES.Value, "'),('") & "') ) AS v (pattern) " & VBCRLF & 
 "CROSS APPLY " & VBCRLF & 
<rest of your query>

and the resulting part of the query is: