Solved

How to pass multiple values in one selection in Reporting Services

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 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