• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1515
  • Last Modified:

Multi Value parameters in SSRS 2008

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
Rhonda Carroll
Asked:
Rhonda Carroll
  • 7
  • 6
  • 4
  • +1
1 Solution
 
Auric1983Commented:
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
 
Rhonda CarrollAuthor Commented:
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
 
Auric1983Commented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
itcoupleCommented:
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
 
Auric1983Commented:
Ah! Thanks Emil, I forgot about that option.
0
 
ValentinoVBI ConsultantCommented:
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
 
Rhonda CarrollAuthor Commented:
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
 
ValentinoVBI ConsultantCommented:
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
 
Rhonda CarrollAuthor Commented:
Is there a way to do this in the query.  I can't use a stored procedure ...
0
 
ValentinoVBI ConsultantCommented:
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
 
ValentinoVBI ConsultantCommented:
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
 
Rhonda CarrollAuthor Commented:
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
 
Auric1983Commented:
I wonder if you just populate the @Item set with a *?
0
 
ValentinoVBI ConsultantCommented:
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
 
Rhonda CarrollAuthor Commented:
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
 
ValentinoVBI ConsultantCommented:
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
 
Rhonda CarrollAuthor Commented:
Fantastic ... thanks so much
0
 
ValentinoVBI ConsultantCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 7
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now