[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2013-05-13
12
Medium Priority
?
3,207 Views
Last Modified: 2013-05-30
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
0
Comment
Question by:Randyg1414
  • 6
  • 3
  • 2
  • +1
12 Comments
 

Author Comment

by:Randyg1414
ID: 39162994
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39163244
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
 
LVL 9

Expert Comment

by:sarabhai
ID: 39163678
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 37

Expert Comment

by:ValentinoV
ID: 39163709
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
 

Author Comment

by:Randyg1414
ID: 39164885
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
 
LVL 9

Expert Comment

by:sarabhai
ID: 39167088
Please specify the error with code that will be better to understand.
0
 

Author Comment

by:Randyg1414
ID: 39168714
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
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 39190086
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
 

Author Comment

by:Randyg1414
ID: 39191035
Thank you.  That answers that question.  I appreciate the help.
0
 

Author Comment

by:Randyg1414
ID: 39191382
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39191383
Glad I could help!  But it looks like you selected the wrong comment as answer...  I'll object so it can get corrected.
0
 

Author Comment

by:Randyg1414
ID: 39207914
My apologies.  It should be worth 500 points!  It looks like it is now accepted directly.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question