SSRS multi-value parameter using a stored procedure

Question

I am working on a SSRS report that uses a stored procedure containing a few parameters. I am having problems with two of the parameters because I want to have the option of selecting more than one item.

Here's a condensed version of what I have:

CREATE PROCEDURE [dbo].[uspMyStoredProcedure] 
  (@ReportProductSalesGroupID AS VARCHAR(MAX) 
  ,@ReportProductFamilyID AS VARCHAR(MAX)
  ,@ReportStartDate AS DATETIME
  ,@ReportEndDate AS DATETIME)

--THE REST OF MY QUERY HERE WHICH PULLS ALL OF THE NEEDED COLUMNS

WHERE DateInvoicedID BETWEEN @ReportStartDate AND @ReportEndDate
AND ProductSalesGroupID IN (@ReportProductSalesGroupID)
AND ProductFamilyID IN (@ReportProductFamilyID)

When I try to just run the stored procedure I only return values if I enter only 1 value for @ReportProductSalesGroupID and 1 value @ReportProductFamilyID. If I try to enter two SalesGroupID and/or 2 ProductFamilyID it doesn't error, but I return nothing.

-- Returns data
EXEC uspMyStoredProcedure 'G23',     'NOF',     '7/1/2009', '7/31/2009'

-- Doesn't return data
EXEC uspMyStoredProcedure 'G23,G22', 'NOF,ALT', '7/1/2009', '7/31/2009'

In SSRS I get an error that says:

Incorrect syntax near ','

It appears that the , separator is being included in the string instead of a delimiter

Brookweed

You could try sending the data as a single xml parameter. For details see: msdn.microsoft.com/en-us/library/dd788497.aspx Note I have not tested this together with SSRS. – Proton

The SQL "IN" keyword doesn't work that way. You can't search IN this string: 'NOF, ALT' because it's a single parameter. – Howenstein

Alternatively this is a close duplicate of #338204 In particular David Robbins' solution looks nice. – Lightfingered

You could use: - Dynamic SQL - Table-Valued User-Defined Function See this link for details. – Mneme

Ok, I did that and it works if I run EXEC uspMyStoredProcedure 'G23,G22', 'NOF,ALT', '8/1/2009', '8/9/2009' But as soon as I try it in rs and select more than one value from the dropdown (generated from a dataset) I get incorrect syntax near ',' Not sure what I am missing – Brookweed

Possible duplicate of Passing multiple values for a single parameter in Reporting Services – Querist

Answer 1

You need three things:

1. In the SSRS dataset properties, pass the multi-value param to the stored procedure as a comma-delimited string

    =Join(Parameters!TerritoryMulti.Value, ",")
   

2. In SQL Server, you need a table-value function that can split a comma-delimited string back out into a mini table (eg see here). edit: Since SQL Server 2016 you can use the built-in function STRING_SPLIT for this

3. In the stored procedure, have a where clause something like this:

    WHERE sometable.TerritoryID in (select value from STRING_SPLIT(@TerritoryMulti,','))
   

   ... where STRING_SPLIT is splitting the comma-delimited string back out into a table structure. NB: If you're on something older than SQL Server 2016 you'll need a custom function here instead of STRING_SPLIT, e.g. see here.

(Full steps and code in my blog post 'SSRS multi-value parameters with less fail'):

Rotz

In SQLServer you can use: SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' '); – Print

@JoséLuisGonzálezVergara ah yes you are right, since SQL Server 2016 that fn is available – Rotz

Not really sure about step 1, did you create a new parameter with that expression? – Palembang

re @Ariox66: Depends which tool you are using to build the report but broadly, in the 'Dataset Properties' dialog there is a tab called 'Parameters' and in there you can (optionally) apply a formula to a parameter as it is passed to the Stored Procedure. Thats where you need to do the =Join(...) stuff. – Rotz

Answer 2

Let us assume that you have a multi value list @param1

Create another Internal Parameter on your SSRS report called @param2 and set the default value to:

=Join(Parameters!param1.value, 'XXX')

XXX can be any delimiter that you want, EXCEPT a comma (see below)

Then, you can pass @param2 to your query or stored procedure.

If you try to do it any other way, it will cause any string function that uses commas to separate arguments, to fail. (e.g. CHARINDEX, REPLACE).

For example Replace(@param2, ',', 'replacement') will not work. You will end up with errors like "Replace function requires 3 arguments".

Perfidious

Not =Join(Parameters!param1.value, 'XXX') but =Join(Parameters!param1.value, "XXX") – Ingles

Answer 3

Finally I was able to get a simple solution for this problem. Below I have provided all (3) steps that I followed.

I hope you guys will like it :)

Step 1 - I have created a Global Temp Table with one column.

CREATE GLOBAL TEMPORARY TABLE TEMP_PARAM_TABLE(
  COL_NAME  VARCHAR2(255 BYTE)
) ON COMMIT PRESERVE ROWS NOCACHE;

Step 2 - In the split Procedure, I didn't use any array or datatable, I have directly loaded the split values into my global temp table.

CREATE OR REPLACE PROCEDURE split_param(p_string IN VARCHAR2 ,p_separator IN VARCHAR2
)
IS
   v_string VARCHAR2(4000);
   v_initial_pos NUMBER(9) := 1;
   v_position NUMBER(9) := 1;
BEGIN
   v_string := p_string || p_separator;

   delete from temp_param_policy;

   LOOP
      v_position :=
                  INSTR(v_string, p_separator, v_initial_pos, 1);
      EXIT WHEN(NVL(v_position, 0) = 0);

      INSERT INTO temp_param_table
           VALUES (SUBSTR(v_string, v_initial_pos
                         , v_position - v_initial_pos));

      v_initial_pos := v_position + 1;
   END LOOP;
commit;

END split_param;
/

Step 3 - In the SSRS dataset parameters, I have used

=Join(Parameters!A_COUNTRY.Value, ",")

Step 4: In the start of your stored procedure executes the Procedure

Exec split_param(A_Country, ‘,’);

Step 5: In your stored procedure sql use the condition like below.

Where country_name in (select * from TEMP_PARAM_TABLE)

Launderette

Answer 4

When SSRS passes the parameter it is in the form: Param1,Param2,Param3.

In the procedure, you just need to put identifiers around each parameter. And also identifiers around the value that is returned by the dataset. In my case, I used semicolons.

CREATE OR REPLACE PROCEDURE user.parameter_name ( i_multivalue_parameter ) AS l_multivalue_parameter varchar2(25555) := ';' || replace(i_multivalue_parameter,',',';') || ';'; BEGIN select something from dual where ( instr(l_multivalue_parameter, ';' || database_value_that_is_singular || ';') > 0 ) END;

i_multivalue_parameter is passed in via SSRS.

l_multivalue_parameter reads the parameter passed in via SSRS and puts identifiers around each value.

database_value_that_is_singular is the value returned for each record.

So if 'Type1,Type2,Type3'is passed in via SSRS:

i_multivalue_parameter is: Type1,Type2,Type3

l_multivalue_parameter is: ;Type1;Type2;Type3;

database_value_that_is_singular is: ;Type1; or ;Type2; or ;Type3;

Instr will return a value over 0 if the parameter matches.

This works even if each parameters are similar. EG: "Type A" and "Type AA". That is "Type A" will not match "Type AA".

Sorci

When you say "identifier", do you mean "delimiter"? (Or "separator"?) Also, your answer looks like an Oracle PL/SQL procedure, while the question appears to be about a SQL Server T/SQL procedure? – Juxtapose

Answer 5

I found a simple way for my solution. Define the parameter value in the report as an expression like this

="'" + Join(Parameters!parm.Value,"','") + "'"

(in case you can't read it the first and last literals are double quote, single quote, double quote. The join literal is double quote, single quote, comma, single quote, double quote)

Then in the stored procedure you can use dynamic sql to create your statement. I did this to create a temp table of values to join to in a later query, like this:

CREATE #nametable (name nvarchar(64))

SET @sql = N'SELECT Name from realtable where name in (' + @namelist + ')'

INSERT INTO #nametable exec sp_executesql @sql

@namelist would be the name of the stored procedure parameter.

Jerrome

Answer 6

You can accomplish this using string_split function. Your stored procedure should be like the following.

CREATE PROCEDURE [dbo].[uspMyStoredProcedure] 
  (@ReportProductSalesGroupID AS VARCHAR(MAX) 
  ,@ReportProductFamilyID AS VARCHAR(MAX)
  ,@ReportStartDate AS DATETIME
  ,@ReportEndDate AS DATETIME)

--THE REST OF MY QUERY HERE WHICH PULLS ALL OF THE NEEDED COLUMNS

WHERE DateInvoicedID BETWEEN @ReportStartDate AND @ReportEndDate
AND ProductSalesGroupID IN (select [value] from string_split(@ReportProductSalesGroupID,','))
AND ProductFamilyID IN (select [value] from string_split(@ReportProductFamilyID,','))

Modify these 2 parameters in SSRS to "Allow Multiple values" and just pass the multiple parameters values and you're done.

Thermotaxis