I have a report that pulls its data from a stored procedure. It has 3 parameters, start date, end date, and advertiser. Advertiser has always been a single-valued parameter, but now the users want it to be multi-valued. I can change the parameter to allow multiple value selections and use “Join(Parameters!Advertiser.Value,”,”) to send the delimited string to SQL Server but my DBA would prefer to use a table type parameter. This is an example of what I need SSRS to send to the server. My question is how do I get the values from the report to put into the dataset query’s @Advertisers variable? declare @Advertisers Advertisers insert into @Advertisers values ('Smith'), ('Jones'), ('Green') execute dbo.p_FactModel @Advertisers = @Advertisers, @ReportStartDate = @ReportStartDate, @ReportEndDate = @ReportEndDate

HI it is easier to multivalue filter on the tablix but this link works for what you are asking http://www.adamjwright.com/post/2009/08/02/Passing-Table-Valued-Parameters-from-Reporting-Services-to-Stored-Procedures.aspx

My main dataset query is now: EXEC ( 'DECLARE @Advertisers Advertisers' +@Advertiser + 'EXEC dbo.p_FactModel @Advertiser, @ReportStartDate , @ReportEndDate') Running the report errors with “Incorrect syntax near the keyword INTO. Must declare the scalar variable @Advertiser”. The dataset parameter mapping is set as: Query parameter @Advertiser =Code.SplitMultiInsert(Join(Parameters!Advertiser.Value,",")) Any thoughts?

I get that error too, until I add the function and modify the code as the article shows. In the Solution Explorer window right click on the report and click the ?view code? menu item to see the code for the report. Find the section that looks like this view plaincopy to clipboardprint? <QueryParameters> <QueryParameter Name="@Param1"> <Value>=Parameters!Param1.Value</Value> </QueryParameter> </QueryParameters> Change the value of @Param1 to the following: view plaincopy to clipboardprint? =Code.SplitMultiInsert(Join(Parameters!Param1.Value,",")) The result should look like this. view plaincopy to clipboardprint? <QueryParameters> <QueryParameter Name="@Param1"> <Value>=Code.SplitMultiInsert(Join(Parameters!.Param1.Value,","))</Value> </QueryParameter> </QueryParameters>

I've done those modifications in the View Code window, still no luck.

you need spaces in your string EXEC ( 'DECLARE @Advertisers Advertisers ' +@Advertiser + ' EXEC

My parameter values are pulled off another stored procedure, so is this step necessary? I didn't think it was, but now I'm not so sure. Step 3. Create a Report First, create a Dataset for your Multi-Value Parameter SELECT CustomerID, FirstName + ? ? + LastName AS CustomerName FROM Customers

you do need a multivalued parameter named @Advertiser that will call your stored proc. Or update your exec statement to use the parameter that you have already.

I am able to make this work with a very simple example, but I need to be able to pass 2 scalar date parameters to the proc as well as the table parameter. Is it possible to do that?

Hello, yes it is. I am working on similar report, this is the query for the dataset. DECLARE @CD CHAR(1) = CHAR(39) EXEC ( ' DECLARE @TD TYPEdEVICe ' + @Param1 + ' EXEC [GetDevices] @TD ,' + @CD + @startdate + @CD + ',' + @CD + @EndDate + @CD )

Thanks so much, this is very helpful....i've run a profiler trace and I am not getting identifiers around my VALUES. Instead of INSERT INTO @Advertiser VALUES ('aaa'),('bbb') I have INSERT INTO @Advertiser VALUES (aaa),(bbb) . That blows up SQL Server, obviously. I have tried concatenating the single quotes into the function code, and that results in ....VALUES (' ' aaa ' '), (' ' bbb ' ' ). Any ideas how I can get the string values delimited properly in the TV parameter?

HI I am using integers for the tables and don't have the problem, change the function to output a string like this should fix it but I have not tested that from report designer. 'insert @Advertisers values(' + char(39) + 'aaa' + char(39) + ')'