ssrs multiple values parameter

Question

I know this question has been asked several times before, but the answers I have found do not work for my code, so I am posting it here. My query is in SQL Server v17.0. I have an SSRS report with a code parameter, but it only shows values when 1 code is selected. Under parameter props, I checked the 'allow multiple values' option, but that did nothing. Any suggestions?

create procedure [roo].[get_cc_serv]
( @upc_code varchar(max) = ''   )
as
select 
acct_num = ser.account_number,
status_change_date = crd.date_status_chg,
upc14 = isnull(crd.upc_14, '-')

from fact.fact_cc_serv ser
join dim.dim_cc_card crd on 
ser.dim_cc_cardholder_key = 
crd.dim_cc_cardholder_key

where ser.end_date = 99991231
and crd.upc_14 is not null
and crd.upc_14 not like 'Z%'
and crd.upc_14 in (@upc_code)

You cannot use IN with a stored procedure. See specifically: "The data source cannot be a stored procedure. Reporting Services does not support passing a multivalue parameter array to a stored procedure." docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/… — Zorkolot

Jamie F Jamie F · Accepted Answer · 2019-03-20T16:05:45

Creating the procedure for this forces the multi-select to be treated as a single value.

If you embed the SQL query in your report instead of using a proc, it should work:

Put this into a dataset in the report:

select 
acct_num = ser.account_number,
status_change_date = crd.date_status_chg,
upc14 = isnull(crd.upc_14, '-')

from fact.fact_cc_serv ser
join dim.dim_cc_card crd on 
ser.dim_cc_cardholder_key = 
crd.dim_cc_cardholder_key

where ser.end_date = 99991231
and crd.upc_14 is not null
and crd.upc_14 not like 'Z%'
and crd.upc_14 in (@upc_code)

If you must have this in a SP, you'll need to get a little fancier to hand in the multi-value parameter and handle the splitting yourself: https://stackoverflow.com/a/9862901/400375

ssrs multiple values parameter

1 Answers