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

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'):

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".

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)

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".

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.