?
Solved

How to pass multiple values in one selection in Reporting Services

Posted on 2009-05-03
6
Medium Priority
?
430 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1000 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 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 1000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

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…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

752 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