CODEWITHSUNDEEP
sqlreporting-services
user3641778
user3641778

Reputation: 61

SSRS Multivalue Parameter in Dataset Query issue

I Have an embedded dataset in my report which I pass parameters into.

This works fine for a single select using the = Sign in my And line I would of thought and google results seem to be saying the same that i can just change the = sign to 'IN'

FROM [database].[dbo].[itemTable]
 right Outer Join [database].[dbo].[CategoryTable]
  on  [database].[dbo].[itemTable].Category= [database].[dbo].[CategoryTable].Category And ([database].[dbo].[itemTable].Region = @pRegion) And ([database].[dbo].[itemTable].CategoryLN = @pCategoryLN )
where [database].[dbo].[CategoryTable].Category != 'RETIRED'

Above works fine but if I change to

[database].[dbo].[itemTable].Region IN @pRegion'

The query window says Incorrect syntax near '@pRegion'.

Upvotes: 0

Views: 114

Answers (2)

fbouck
fbouck

Reputation: 11

We've resolved this issue by using a database table-valued function (probably found somewhere on the internet, but I can't remember where)

    CREATE FUNCTION [database].[dbo].[ParamSplit]
    (
      @List nvarchar(max), -- string returned from multivalue report parameter
      @SplitOn nvarchar(5) -- separator character
    )  
    RETURNS @RtnValue table 
    (

      Id int identity(1,1),
      Value nvarchar(100)
    ) 
    AS  
    BEGIN
      While (Charindex(@SplitOn,@List)>0)
      Begin 
        Insert Into @RtnValue (value)
        Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
        Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
      End 

      Insert Into @RtnValue (Value)
        Select Value = ltrim(rtrim(@List))
      Return
    END

Then you can use it in your dataset query.

where [database].[dbo].[itemTable].Region IN  (Select [dbo].[ParamSplit].[Value] from [database].[dbo].[ParamSplit](@pRegion,','))

Upvotes: 1

Anup Agrawal
Anup Agrawal

Reputation: 6669

Looks like all you are missing is brackets around the parameter.

[database].[dbo].[itemTable].Region IN (@pRegion)

Also make sure you don't edit/parse the parameter values.

Upvotes: 1

Related Questions