1

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.

Campbell Reid
  • 139
  • 1
  • 6
  • 1
    The difficulty you will face is in the way multi-value parameters are gathered from user input. In SSRS the parameters!Value will be a comma delimited list. You will need to somehow transpose that into a list and this is typically done by splitting the values by the delimiter into a table of sorts but that goes against the strategy above. – Ross Bush Feb 05 '20 at 13:24
  • the LIKE operator should be in your where clause – JonWay Feb 05 '20 at 13:40
  • What is the benefit (if any) of doing a cross apply like that versus just putting those values in the WHERE clause with `(diag.DiagnosisCode LIKE 'AB%' OR diag.DiagnosisCode LIKE 'XC%')`? – Zorkolot Feb 05 '20 at 14:14

1 Answers1

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>

enter image description here

and the resulting part of the query is:

enter image description here

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • Thank you, sir - this worked a treat! (Or, looking at your user name, maybe that should that be "Danke schoen, mein herr, das war ein vergnuegen!"?). It's a bit inelegant that one has to use this workaround to pass multiple search strings into a query, but hey ho. – Campbell Reid Feb 06 '20 at 10:26