Solved

How to pass multiple values in one selection in Reporting Services

Posted on 2009-05-03
6
419 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

19 Experts available now in Live!

Get 1:1 Help Now