none
SSRS 2008 table type parameter?

    Question

  • 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
    

     

    Wednesday, February 09, 2011 4:42 PM

All replies

  • 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
    • Proposed as answer by Antoine F Wednesday, February 09, 2011 6:10 PM
    • Unproposed as answer by madams999 Thursday, February 10, 2011 2:43 PM
    Wednesday, February 09, 2011 5:50 PM
  • 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?

    Wednesday, February 09, 2011 9:10 PM
  • 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

    1. <QueryParameters>  
    2.   <QueryParameter Name="@Param1">  
    3.    <Value>=Parameters!Param1.Value</Value>  
    4.   </QueryParameter>  
    5. </QueryParameters>  

    Change the value of @Param1 to the following:

    1. =Code.SplitMultiInsert(Join(Parameters!Param1.Value,","))  

    The result should look like this.

    1. <QueryParameters>  
    2.   <QueryParameter Name="@Param1">  
    3.    <Value>=Code.SplitMultiInsert(Join(Parameters!.Param1.Value,","))</Value>  
    4.   </QueryParameter>  
    5. </QueryParameters>  

    Thursday, February 10, 2011 5:09 PM
  • I've done those modifications in the View Code window, still  no luck.
    Thursday, February 10, 2011 5:20 PM
  • you need spaces in your string EXEC ( 'DECLARE @Advertisers Advertisers ' +@Advertiser + ' EXEC 
    Thursday, February 10, 2011 6:26 PM
  • 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
    
    Thursday, February 10, 2011 6:57 PM
  • 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.
    Thursday, February 10, 2011 7:12 PM
  • 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?
    Thursday, February 10, 2011 8:59 PM
  • 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 )

     

     

    Thursday, February 10, 2011 10:45 PM
  • 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?

    Friday, February 11, 2011 5:45 PM
  • 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) + ')'

    Friday, February 11, 2011 6:40 PM