• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 437
  • Last Modified:

How to pass multiple values in one selection in Reporting Services

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
macecase
Asked:
macecase
2 Solutions
 
PockyMasterCommented:
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
 
macecaseAuthor Commented:
Can you elaborate on that?  Specifically what you mean by string-to-table function?  Myabe provide examples.
0
 
Auric1983Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
PockyMasterCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now