Link to home
Start Free TrialLog in
Avatar of Beverly Penney
Beverly PenneyFlag for Canada

asked on

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
Avatar of Auric1983
Auric1983
Flag of Canada image

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.

Avatar of Beverly Penney

ASKER

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
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

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
Ah! Thanks Emil, I forgot about that option.
Avatar of ValentinoV
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.
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)))
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.
Is there a way to do this in the query.  I can't use a stored procedure ...
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

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

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.
I wonder if you just populate the @Item set with a *?
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

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',' ...
ASKER CERTIFIED SOLUTION
Avatar of ValentinoV
ValentinoV
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Fantastic ... thanks so much
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!