Determine if a value in a multi value report parameter exists in an SQL field

Hello,

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.

SQL: 2008
Report Builder: 3.0
Randyg1414Asked:
Who is Participating?
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
When you pass a multi-valued parameter into a dataset, it's not a real comma-separated string by default (unless you're using a stored proc).  You need to take care of that yourself, which can be done using the Join function.

To get this to work, open up the Parameters page of your dataset and replace the Parameter Value with an expression such as:

=Join(Parameters!YourParam.Value, ",")

This will ensure a comma-separated value string gets passed into the query parameter.  It will work with your initial statement as well (though you seem to be missing a couple of declare statements).
0
 
Randyg1414Author Commented:
I must apologize.  The above request needs some simplification.  Here's the question:

Are multi value report parameters passed into SQL equivalent to SQL report parameters?

Regards,

Randy.
0
 
Scott PletcherSenior DBACommented:
In SSRS (at least in SQL 2005), you just used "=" for the multi-valued parameter: SSRS itself is coded to realize that it is a list, so SQL will write the code to expand it to check for every value.


Thus, multi-valued parameters passed into a stored proc, outside of SSRS, for example, are very different from SSRS parameters, because you must handle the splitting yourself.

However, this is a very fast, table-valued function you can use to split strings in your db.  And since the input string -- "@test" or "@params" doesn't change after it's initially set, you can split the @input once, stored it in a keyed table, then use it to match to the split values from the table-valued function.

If I'm on the right track, and you want to pursue something like this, just let me know.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sarabhaiCommented:
CREATE FUNCTION dbo.Split (@seprator char(1), @str varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@seprator, @str)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@seprator, @str, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@str, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO  


SELECT * FROM dbo.Split(',', 'Value1,Value2,Value3')
SELECT * FROM dbo.Split('|', 'Value1|Value2|Value3')
SELECT * FROM dbo.Split(' ', 'Value1 Value2 Value3')
GO
0
 
ValentinoVBI ConsultantCommented:
A while ago I wrote an article about how SSRS treats multi-valued parameters in the context of a stored procedure. You'll probably find it an interesting read: Reporting On Data From Stored Procedures (part 2)
0
 
Randyg1414Author Commented:
That is a great function, and it would definitely be part of the solution.  I tried this code in a report and printed the results successfully in a grid.

DECLARE @test1 VARCHAR(100)
SET @test1 = 'value1,value2,value3'

SELECT * FROM dbo.Split(',', @test1)

Now, when I modify the code to use a report parameter @test1 like this:


USE hsd_clientsw
SELECT * FROM dbo.Split(',', @test1)

It succeeds as a long as test1 is set as a single value parameter.  When I change @test1 to have the values 2011 and 2012 as defaults in the parameters definition (through the report writer ui), I get the following error message:

Procedure or function dbo.Split has too many arguments specified.
----------------------------
Query execution failed for dataset 'test1'.
----------------------------
An error has occurred during report processing.
----------------------------
An error occurred during local report processing.

Whether or not I run the dbo.split function as a function or just run the code directly, I get the same results.  This is what I was explaining at the very beginning of my request (though in a quite convoluted way!).
0
 
sarabhaiCommented:
Please specify the error with code that will be better to understand.
0
 
Randyg1414Author Commented:
I gave it above, but here it is:

Procedure or function dbo.Split has too many arguments specified.
----------------------------
Query execution failed for dataset 'test1'.
----------------------------
An error has occurred during report processing.
----------------------------
An error occurred during local report processing.
0
 
Randyg1414Author Commented:
Thank you.  That answers that question.  I appreciate the help.
0
 
Randyg1414Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Randyg1414's comment #a39191035

for the following reason:

I tested it and it worked very simply.  It is what I was looking for.
0
 
ValentinoVBI ConsultantCommented:
Glad I could help!  But it looks like you selected the wrong comment as answer...  I'll object so it can get corrected.
0
 
Randyg1414Author Commented:
My apologies.  It should be worth 500 points!  It looks like it is now accepted directly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.