How to allow multiple values for a parameter 1

[dianemarie]

Hello, I currently have this where clause in my query string, creating a parameter to select the customer number. I would like to be able to allow more than one customer number. I've been through help but can't find anything.

where customer = @CustomerNumber

I tried where customer in @CustomerNumber, and then separating the numbers by commas in the parameter but that didn't work. I'm brand new to SRS, from a Crystal background. Thank you for any help.

 

[dianemarie]

Also, I changed the parameter name from CustomerName to CustomerNumber, but now I have two parameters, CustomerName and CustomerNumber. How do I get rid of the (now renamed) CustomerName parameter? I tried saving, rerunning the query, getting in and out of the report. Still there.

 

[dianemarie]

Took the parameters out completely. Still there even though there are no parameters on the parameter tab when I click on the dataset ellipsis.

 

[xlbo]

right click in the report area and choose "parameters" - you will see them there

In terms of your original question, are you using 2000 or 2005?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dianemarie]

Hi Geoff, I apologize, I'm using 2005. Also, where do I right-click? On the Preview tab, or the Layout tab? I've tried both. I've tried rt-clicking in a List box in the Layout tab, and just on the report canvas. I'm not getting a Parameters option from the shortcut menu. Thanks again.

 

[SgtJarrow]

Start with the Layout tab. On the top right, there is a little box. Right click this and select Parameters. Remove any there you don't need.

You may also need to check the data set...Go to the Data tab, select the dataset and click the ellipse. Look for the Parameters tab and remove any not needed.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[dianemarie]

I don't see a little box in the upper right of the Layout tab. I'm either missing it or it's not there. I right-clicked everywhere I could think but I'm not seeing an option for Parameters coming up. I have already removed the parameters from the Parameters box accessed from the Dataset ellipsis. I still have both parameters showing up in Preview and the report requires that I enter both to return data.

 

[SgtJarrow]

Sorry, that's top left....I attached an image.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SgtJarrow]

By the way, you can also use the menu to get to the report parameters...

While on the Layout tab, click

Report | Report Parameters

in the Main Menu

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[dianemarie]

They're gone!! Thanks so much. A picture's worth a thousand words, although I should have seen that. I've been using Access for years. I wasn't able to find it by right-clicking here however. I had to go to Report/Report Parameters. So while I'm bugging you...any ideas on how to set up the where clause so that a user can enter more than one customer number? We have Crystal reports from stored procedures where they can enter multiple criteria in parameters separated by a comma. I tried duplicating the join function that was shown a few posts down from mine but I don't think I can join in the query string, or can I? (other people wrote the sprocs, I just designed the reports from them)

 

[SgtJarrow]

Here's how we do it here. First, you need this UDF (User Defined Function). It needs to be added into your database...

GO
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 04/10/2008 09:42:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[Split]
(
	@ItemList NVARCHAR(4000), 
	@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50) PRIMARY KEY CLUSTERED)  
AS      

BEGIN    
	DECLARE @tempItemList NVARCHAR(4000)
	SET @tempItemList = @ItemList

	DECLARE @i INT    
	DECLARE @Item NVARCHAR(4000)

	SET @tempItemList = REPLACE (@tempItemList, ' ', '')
	SET @i = CHARINDEX(@delimiter, @tempItemList)

	WHILE (LEN(@tempItemList) > 0)
	BEGIN
		IF @i = 0
			SET @Item = @tempItemList
		ELSE
			SET @Item = LEFT(@tempItemList, @i - 1)
		INSERT INTO @IDTable(Item) VALUES(@Item)
		IF @i = 0
			SET @tempItemList = ''
		ELSE
			SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
		SET @i = CHARINDEX(@delimiter, @tempItemList)
	END 
	RETURN
END

Then in your reports datasets, you need something like this:

SELECT DISTINCT e.RecKey 'Route'
	, c.RecKey 'CustomerID'
	, c.RecName 'CustomerName'
	, o.DeliverySequence
	, Sum(ol.QtyShipped) 'Qty'
	,'' 'Count'
FROM OrderLines ol
	INNER JOIN Orders o ON ol.OrderTicketNumber = o.TicketNumber
	INNER JOIN Customers c ON o.ToCusNid = c.CusNid 
	INNER JOIN Employees e ON o.DrvEmpNid = e.EmpNid
WHERE o.SalesHistoryDate BETWEEN dbo.sc_DateOnly(@Date) AND DATEADD(ms,-2,DATEADD(DAY,1,dbo.sc_DateOnly(@Date)))
[COLOR=red]AND e.RecKey IN (SELECT Item FROM dbo.Split(@Routes, ','))[/color]
GROUP BY e.RecKey, c.RecKey, c.RecName, o.DeliverySequence

Note the line in RED. The key here is to pass the parameter (in my example @Routes) in as comma sepearated (again, my example would be like '201,202,203,204,205,206,207'). The Split function does all the work for you to put everything in the correct SQL syntax.

I found this solution using a web search about a year or so ago. You can probably find it or something similar but if not let me know and I will try to track it down if you need more information.

Hope this helps.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[dianemarie]

%-) Yikes! Where's the little dropdown where I answer "Yes" to "Allow multiples". I believe we are already using the split function thing for our stored procedures currently being used for some of our Crystal reports. I will have the senior Crystal programmer help me figure out how to use this in the SRS reports. Thank you for pointing me in the right direction.

Diane

 

[SgtJarrow]

I understand the Yikes....but if you already have the Split function in place, it really is not too bad. You just need to have the red line above in your SQL statements and pass the comma separated values. Everything else is done for you. Good luck.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[dianemarie]

Hey!! It worked. I found the function in the database (fn_split) ready to go and set it up as you showed. What a confidence booster. Thanks so much for all your help.

 

[SQLWilts]

One caveat to all this.....

If you have parameters with spaces in them (I know, I know but sometimes it is unavoidable) then remove the spaces in the proc - don't forget to allow for the "All" possibility...

SELECT	Field1 
FROM	tblTestParms 
WHERE	REPLACE(Field1, ' ', '') IN 
	(
		SELECT * FROM dba.dbo.split(@params, ',')
	)
		or (@params is null)