Solved

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

Posted on 2013-05-13
12
2,742 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
Comment Utility
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 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
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
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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
Comment Utility
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
Comment Utility
Please specify the error with code that will be better to understand.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Randyg1414
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Thank you.  That answers that question.  I appreciate the help.
0
 

Author Comment

by:Randyg1414
Comment Utility
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
Comment Utility
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
Comment Utility
My apologies.  It should be worth 500 points!  It looks like it is now accepted directly.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now