Solved

How to pass multiple values in one selection in Reporting Services

Posted on 2009-05-03
6
418 Views
Last Modified: 2012-05-06
Ok, I'm trying to build a report that users can lookup orders that have items on it in various status. There are easily 40 different status without about 5 relating to "shipped" (drop shipped, regular shipped, expedited shipped, etc).

So, I want to have a drop-down that is populated by a query that lists all status. However, if you simply want to find the "shipped" orders, you don't want to click five statuses. In addition, some users might not think to click all of them.

So, I want to have a "shipped" selection and I want the values to be 'S' and 'U'

My question is, how do I pass a report parameter value that is equal to two or three of these values listed below. Since I'm using an IN () selection in the sql query for the data source, I would have thought that I could simply make the value of the parameter S, U but that doesn't work. I have played with 'S', 'U' and such but doesn't seem to trigger it.

Any thoughts?
SELECT     FULLORDERNO, SHIPDATE, ITEMNO, DESCRIPTION, ITEMQTYS, BIGSTATUS, FullName

FROM         WhoOrderedWhatItems

WHERE     (ITEMNO LIKE UPPER(@ItemNo) + '%') AND (BIGSTATUS IN (@BigStatus))

ORDER BY SHIPDATE DESC, FULLORDERNO DESC
 
 

Here are the Statuses:
 

	2	Announced to Ship on     

	D	At Drop Shipper          

	7	Auth. for Return         

	Z	Auto-Checked             

	b	Back Ord-Not Alcd        

	B	Backordered              

	d	CC Auth Declined         

	a	Continuity Header        

	p	Continuity Ready         

	C	Customer Canceled        

	U	Drop Shipped             

	E	Exchanged                

	u	Forced Drop Ship         

	8	Full Credit Issued       

	J	Gift Prod With Inv       

	G	Gift Product-No Inv      

	W	In The Warehouse         

	N	Item Canceled            

	F	Item Canceled            

	M	Misc. CC Credit          

	L	Multi-Rack               

	P	Not Picked               

	9	Partial Crdt. Issued     

	1	Partial Exchange         

	6	Partial Refused          

	4	Partial Return           

	5	Partial Undeliv          

	O	Point of Sale            

	t	Rdy to Pick-Not Alct     

	Y	Refused Return           

	R	Returned                 

	v	Shipment Stopped         

	V	Shipment Stopped         

	S	Shipped                  

	Q	To Be Canceled If OS     

	H	To Be Drop Shipped       

	A	To Be Drop Shipped       

	T	To Be Picked             

	q	To Cancel-Not Alcd       

	c	To be Qualified          

	X	Tracer Issued            

	I	Undeliverable Return     

	K	Warehouse Backorder      

	NULL	NULL

Open in new window

0
Comment
Question by:macecase
6 Comments
 
LVL 14

Expert Comment

by:PockyMaster
ID: 24290349
What I've done in the past is to pass the multivalues as a comma seperated string, so... e.g. S,U
and then use a custom string-to-table function in SQL which can be joined to the resultset.

Or you could create a mapping table with simplified search words, and map these into the combination of statuses.
0
 

Author Comment

by:macecase
ID: 24290369
Can you elaborate on that?  Specifically what you mean by string-to-table function?  Myabe provide examples.
0
 
LVL 16

Accepted Solution

by:
Auric1983 earned 250 total points
ID: 24295933
macecase,

Have you verified that your query works in management studio?  

The Bigstatus in @BigStatus piece of code is correct.  Is it possible that the field is a Char datatype and has preceeding whitespace? example "A" is not the same as "A     "

A string to table function would allow you to pass in a comma seperated string and return a table of values for it.  see http://blogs.x2line.com/al/articles/150.aspx for a sample custom string to table function (note: I haven't tried this script myself)

I'd also look at creating a table of values but this would need to be maintained as status are updated.

Two columns

Name/Label StatusList
Shipped   S,U

You could then use your "label" in the report parameter so they select Shipped, but in the background it would use the StatusList as the parameter value.
0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 250 total points
ID: 24300725
Below is an example function that will take a comma separated string and turn it into a table of varchar values.  See if it works for you.
So,
'S, U'
becomes
'S'
'U'
in the output table.
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create FUNCTION [dbo].[fnParseList]

(

	@List varchar(500)

RETURNS 

@ParsedList table

(

	StrVar VARCHAR(50) -- set this as desired

)

AS

BEGIN

	DECLARE @ListValue varchar(10), @Pos int), @Delim varchar(1)
 

	SET @Delim = ',' -- set this to what you need if different

	SET @List = LTRIM(RTRIM(@List))+ @Delim

	SET @Pos = CHARINDEX(@Delim, @List, 1)
 

	IF REPLACE(@List, @Delim, '') <> ''

	BEGIN

		WHILE @Pos > 0

		BEGIN

			SET @ListValue = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))

			IF @ListValue <> ''

			BEGIN

				INSERT INTO @ParsedList (StrVar) 

				VALUES (@ListValue) 

			END

			SET @List = RIGHT(@List, LEN(@List) - @Pos)

			SET @Pos = CHARINDEX(@Delim, @List, 1)

		END

	END	

	RETURN

END

GO

Open in new window

0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 24305467
You can use the sample above, or you can write a CLR function to do the same quite easily. I was convinced I've seen once in the samples you get with SQL 2005, but could not find it just...
0

Featured Post

IT, Stop Being Called Into Every Meeting

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: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

22 Experts available now in Live!

Get 1:1 Help Now