Solved

Multi Value parameters in SSRS 2008

Posted on 2010-08-30
18
1,508 Views
Last Modified: 2012-05-10
I have a query/report that pass parameters to a REPORT,  the parameters are selected by the user using a drop down window (data for the parameters are in various data sets).  I now need to create a parameter that allows a user to enter in multiple item numbers  (could be 100 item numbers) ...
i.e. SMN-10050, SMN-1053, SMN-1897 .  How can i set this up in SSRS 2008
0
Comment
Question by:Rhonda Carroll
  • 7
  • 6
  • 4
  • +1
18 Comments
 
LVL 16

Expert Comment

by:Auric1983
ID: 33566222
Is it possible to get this list of items from a database so the user isn't entering them?

If the user enters them into a textbox you will need to do some custom code to convert that long string into a set.  In this case I would try to do something with a multi-select parameter.

Keep in mind you can use cascading parameters too that coudl help narrow down what the users are selecting.

0
 

Author Comment

by:Rhonda Carroll
ID: 33566751
This list would be a massive list ... they don't want to have to scroll down through a large list and have to select items ... they want to enter them as a comma delimited string ... in 2000 we used to use something like ('" & Replace(Replace(Parameters!cust_number.Value," ",""),",","', '") & "')    but I can't get that to work in 2008
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 33566959
Well, you need to do something to convert that comma delimited string that would be in the format of

'customer1,customer2,customer3' into
'customer1','customer2','customer3' so that SQL can use the set.

Best way to do that is with a custom CSV to set for example. NB I didn't write the code it's something I've used in the past from another site.
CREATE FUNCTION dbo.ParseString (@string varchar(500), @delimeter char(1))
RETURNS table

Or do the same type thing using a stored procedure, whatever floats your boat.  You'll juse have to make some small modifications to the code.


Here is the code:



Parse a comma-delimeted string

-- This would be the input parameter of the stored procedure, if you want to do it that way, or a UDF
declare @string varchar(500)
set @string = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'


declare @pos int
declare @piece varchar(500)

-- Need to tack a delimiter onto the end of the input string if one doesn't exist
if right(rtrim(@string),1) <> ','
 set @string = @string  + ','

set @pos =  patindex('%,%' , @string)
while @pos <> 0 
begin
 set @piece = left(@string, @pos - 1)
 
 -- You have a piece of data, so insert it, print it, do whatever you want to with it.
 print cast(@piece as varchar(500))

 set @string = stuff(@string, 1, @pos, '')
 set @pos =  patindex('%,%' , @string)
end
 
The above code outputs:

ABC
DEF
GHIJK
LMNOPQRS
T
UV
WXY
Z

Open in new window

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 10

Expert Comment

by:itcouple
ID: 33567416
Hi

To do that. Create a parameter and set tick 'allow multiple values'
You will get a drop down box where you can type your numbers. You separate them using enters (one row per line. You don't use commas). This is equivalent of having data in the drop down box using dataset.

I've just tested it and filtering dataset with new parameter with WHERE test in (@Test) works as intended.

Regards
Emil
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 33567453
Ah! Thanks Emil, I forgot about that option.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 33574490
Emil has posted an interesting option there!  Using that method means that you don't need to worry about the users entering a space following the comma (as you're showing in your example - SMN-10050, SMN-1053, SMN-1897).  Knowing users, some would enter the spaces and some wouldn't.  Maybe others would even use semi-colons :-)

I'd also like to point you to the following article: http:/A_2002.html
It demonstrates how multi-value parameters can be used in combination with stored procedures, could be interesting in your situation.

Regards,
Valentino.
0
 

Author Comment

by:Rhonda Carroll
ID: 33592639
I like that idea ... and it will work however is there a way to have the value left blank and it would therefore take all item numbers...below is the selection criteria ... all parameters with the exception of item_number are coming from a query so there is a list in the drop down menu ... they also want to be able to select item numbers or leave them out ... in the parameter section of SSRS i can select multi and therefore display the dropdown box but I can't also select 'Allow Null Values'  is there a way to do this

WHERE      SOP_TYPE = 'Order'
           AND (Sales_Rep in (@SM))
           AND (ITEM_Number in (@Item))
           AND (CREATED_By  in (@OC))
           AND (Sales_Region in (@SR))
           AND (doc_type in (@DT))          
           AND ((ORDER_DATE BETWEEN CONVERT(smalldatetime, @Begin_Date + ' 00:00:00', 20) AND                  CONVERT(smalldatetime, @End_Date + ' 23:59:59', 20)) or
               (ship_date between CONVERT(smalldatetime, @Begin_Ship_Date + ' 00:00:00', 20)                 AND CONVERT(smalldatetime, @End_Ship_Date + ' 23:59:59', 20)))
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 33593969
If you use a stored procedure (SP), in the code of that SP you can test whether or not your incoming parameter contains any values.  That allows you to use a different query (without the test on Item_Number) in case of an empty @Item parameter.
0
 

Author Comment

by:Rhonda Carroll
ID: 33595158
Is there a way to do this in the query.  I can't use a stored procedure ...
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 33595190
maybe something like this?

WHERE      SOP_TYPE = 'Order'
           AND (Sales_Rep in (@SM))
           AND (ITEM_Number in (@Item) or LEN(ITEM_Number) = 0)
           AND (CREATED_By  in (@OC))
           AND (Sales_Region in (@SR))
           AND (doc_type in (@DT))          
           AND ((ORDER_DATE BETWEEN CONVERT(smalldatetime, @Begin_Date + ' 00:00:00', 20) AND                  CONVERT(smalldatetime, @End_Date + ' 23:59:59', 20)) or
               (ship_date between CONVERT(smalldatetime, @Begin_Ship_Date + ' 00:00:00', 20)                 AND CONVERT(smalldatetime, @End_Ship_Date + ' 23:59:59', 20)))

Open in new window

0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 33595219
Woops, copy/paste issue in my previous comment.  See the first line in the following snippet for the correct expression (sorry about that).

Also, that comment assumes that the incoming parameter contains an empty string when no values are entered.  I'm not sure if that is actually the case.  In case that the value is NULL, you can/should change the expression as shown on the last line in the snippet below.

AND (ITEM_Number in (@Item) or LEN(@item) = 0)

AND (ITEM_Number in (@Item) or LEN(@item) is null)

Open in new window

0
 

Author Comment

by:Rhonda Carroll
ID: 33595392
OK,  I have tried the script above and it is returning nothing  I only added the
AND (ITEM_Number in (@Item) or LEN(@item) is null) line of code.

What I need to do is have the user select a bunch of fields (as outlined in a previous post )but the Item_Number field would be optional ... If they enter items it returns the items they entered ... if they leave it blank it returns everything.  Is this possible.
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 33595408
I wonder if you just populate the @Item set with a *?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 33595540
Really good proposal Auric, and that works (did a quick test).

Rhonda, you can set the default value for the param to * and change the query like in the snippet.

There's no need to use an asterisk btw, if you prefer something like '<All>', just change the default value and the value in the query to that, should work fine.

AND (ITEM_Number in (@Item) or @Item = '*'

Open in new window

0
 

Author Comment

by:Rhonda Carroll
ID: 33595928
When I use the script above, if I let it default to '*' or 'ALL'  I capture all data as expected.  However when I select some multiple values for the item field I get the following error ....

An expression of non-boolean type specified in a context where a condition is expected, near',' ...
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 33596667
Hi Rhonda,

You're right, that one indeed does not work when entering multiple items.  Try the following version, that seems to work in all cases (phieuw :-))

Regards,
Valentino.

AND (ITEM_Number in (@Item) or '*' in (@Item)

Open in new window

0
 

Author Closing Comment

by:Rhonda Carroll
ID: 33596885
Fantastic ... thanks so much
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 33598046
You're welcome!  I now remember why in the past I've switched to using a stored procedure when I needed the "All' functionality on multi-value params :-)

I'm glad we finally found a method that actually works without needing SPs!
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

740 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