Solved

Multi Value parameters in SSRS 2008

Posted on 2010-08-30
18
1,503 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

14 Experts available now in Live!

Get 1:1 Help Now