[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register 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,167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

656 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