0

Good day,

I'm trying to pass multiple SSRS/SSDT parameter values, to a parameter used in my OPENQUERY statement.

What I've done thus far:

i. The specific "Sample Point" parameter's properties, has been checked, to "Allow multiple values", in SSRS/SSDT.

a. Multiple "Sample Point" values can be selected successfully: https://i.stack.imgur.com/lAigV.png b. List of "Sample Point" values, as it appears after selection: https://i.stack.imgur.com/fvgyy.png

ii. The "Sample Point" parameter's, "Parameter Value" expression, in the Dataset, has been set to:

=Split(Join(Parameters!SamplePoint.Value,", "),", ")

iii. The code below, utlises an OPENQUERY statement, to extract a "Property List", based on the "Sample Point(s)", selected:

BEGIN
    DECLARE @cSQL nvarchar(max)
    DECLARE @cEquipment nvarchar(50) = LTRIM('SOLVENTS WEST')
    DECLARE @pStartDate datetime = '2022-11-01 00:00:00'
    DECLARE @pEndDate datetime = '2022-11-05 00:00:00'

    DECLARE @cSamplePoint nvarchar(max)

    DECLARE @cStartDate nvarchar(30) = SUBSTRING(CONVERT(varchar(20), @pStartDate, 120), 1, 10)
    DECLARE @cEndDate nvarchar(30) = SUBSTRING(CONVERT(varchar(20), @pEndDate, 120), 1, 10)

    SET @cSamplePoint = REPLACE(REPLACE(@SamplePoint, ', ', ','), ',', ''',''')

    SELECT @cSQL = 'SELECT
                ''N.A.'' AS PropertyName
            UNION
            SELECT DISTINCT
                PropertyName
            FROM
                OPENQUERY(**[LINKSERVPRD.ORACLE**],
                ''SELECT
                    r.prprty_name AS PropertyName
                FROM
                    totalplant.ip_a e,
                    totalplant.ip_b s,
                    totalplant.ip_b t,
                    totalplant.ip_c r
                WHERE
                    e.eq_name = ''''' + @cEquipment + '''''
                AND s.smpl_pnt_name IN ( ''''' + @cSamplePoint + ''''')
                AND s.smpl_dt_tm BETWEEN TO_DATE(''''' + @cStartDate + ''''', ''''YYYY-MM-DD HH24:MI:SS'''') AND TO_DATE(''''' + @cEndDate + ''''', ''''YYYY-MM-DD HH24:MI:SS'''')'')
            ORDER BY
                PropertyName'


    EXEC (@cSQL)

END

iv. Selecting a single "Sample Point", populates the "Property List" successfully: https://i.stack.imgur.com/v8sOU.png

v. Selecting multiple "Sample Points", generates an error message. I fail to understand, since my REPLACE statement is, as far as I know, correct: https://i.stack.imgur.com/6ArZ2.png

vi. Any assistance will be greatly appreciated.

Johann
  • 1
  • 3
  • The first thing I would do is make a copy of your report for testing, remove the associated tablix and have the dataset query just return your `@SamplePoint` and once that looks OK, add the `@cSamplePoint` and `@cSQL` variables so you can show them in your test report. At least you might be able to see what is actually being passed to your query . – Alan Schofield Nov 17 '22 at 09:57
  • Actually, I just noticed that you are using `SPLIT`. You probably just need to remove this if you intend to pass the parameter as a simple comma separated list of values. So change it to `=Join(Parameters!SamplePoint.Value,", ")` . This will just take the parameter collection and join them into a single string with `, ` between. – Alan Schofield Nov 17 '22 at 10:01
  • I think it would be easier to use the QUERY's Expression builder in SSRS and create the SQL with your parameters as a string. https://stackoverflow.com/questions/60193413/how-to-correctly-turn-an-imported-sql-query-into-an-expression-string-in-ssrs – Hannover Fist Nov 18 '22 at 19:07

0 Answers0