Determine if a value in a multi value report parameter exists in an SQL field
Posted on 2013-05-13
I have a report that uses a multi valued parameter. For sake of example, lets say the parameter (@test) is a list of products: proda, prodb, prodc. Also, all 3 values have been chosen by default. I want to take @test and see if any of the parameter values exist in a field (Field1) that is a comma delimited list of products such as: "proda,prodc". The first step would be in parsing @test for its invididual values, but it fails. If I write some code to look for the comma in an sql parameter called @test it works.
DECLARE @strSingleValue INT
DECLARE @test VARCHAR(100)
SET @test = 'Value1,Value2,Value3'
SET @intStringPosition = 1
SELECT @intStringLocator = CHARINDEX(',', (@test),@intStringPosition)
select Field1=substring(@test, @intStringPosition, @intStringLocator - @intStringPosition)
However, If I use a muti value report parameter called @test and take out the DECLARE statement in the same sql code, the query fails saying the comma is invalid: Incorrect syntax near ','.
We have some VB code that does do this parsing and use it in a filer, but it is very slow. I am looking for a faster solution.
Report Builder: 3.0