Jump to Report Multivalue Parameter Issue - Please Help!

• KT-243628

  SSC Veteran

  Points: 254

  More actions

  November 21, 2006 at 6:39 am

  #193181

  Can anyone help?

   

  The issue we are having is passing a multivalue parameter from report A to report B using "Jump to Report". If I run report B on its own and enter a multiple values in to the parameter it returns data. If I try to pass the same values from report A to report B using the join function (i.e. join(parameters.parameter1.value,", ")), report B returns no rows.

   

  To investigate futher we set up a trace on the database to compare the queries when running report B on its own (scenario1) and when launching it from report A (scenario2). The sql that the sp_executesql run is different. For the scenario1 the multivalued parameter was included in the where clause of the @stmt statement. In the scenario2 the multivalued parameter was listed in the @param1 section.

   

  Syntax

  sp_executesql [@stmt =] stmt

  [

      {, [@params =] N'@parameter_name  data_type [,...n]' }

      {, [@param1 =] 'value1' [,...n] }

  ]

  For example:

  Scenario1

  sp_execute 'select * from table1 where field1 in (N '123', N'234')'

  Scenario2

  sp_executesql 'select * from table1 where field1 in (@param1)',

  N '@param1 integer',

  @param1 = N'123',N'234'

   

  Any ideas - is this possible, are we doing is wrong?

  TIA

  KT

• Jon Spink

  SSCarpal Tunnel

  Points: 4010

  More actions

  November 22, 2006 at 2:04 am

  #673395

  perhaps you should be passing a string...

  In similar cases I have been driven to writing this in a stored procedure:

  where (charindex(',' + field1+ ',',',' + @param + ',')>0

• KT-243628

  SSC Veteran

  Points: 254

  More actions

  December 13, 2006 at 7:49 am

  #677772

  Hi Jon

  Sorry for the delayed response, but thanks for the suggestion this works!

  Thanks again

  KT