Single parameter multiple values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Single parameter multiple values

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-09-06 : 08:53:45

Hi,

This maybe quite simple to some but im having issues with the following:

I have 2 parameters on a report.

P1: select distinct board_code
from staesube

--

P2: select distinct centre_code
from staesubx
where (board_code = @board_code)

--

P1. you can select multiple values, but I get a parse error of ',' Ive tried to used a stored procedure where input is an array of the parameter value and coma seperation (value(i), ',') but wouldnt work.

Any ideas?

Much appreciated.

Cipriani

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-09-07 : 17:15:41

Try:
select distinct centre_code
from staesubx
where board_code IN (@board_code)

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-07 : 20:15:24

If you would like to use a stored procedure with multi-valued selections in reporting services then create this function and use as follows:

1. Function (note change Use [] to your DB)

USE [VC]
GO
/****** Object: UserDefinedFunction [dbo].[fn_MVParam] Script Date: 09/08/2008 09:36:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_MVParam](@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @VALUES TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(4000)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @VALUES(Param) VALUES(@Piece)
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END

2. Use like this:

SELECT distinct centre_code
FROM staesubx
WHERE board_code IN (Select Param From fn_MVParam (@board_code,','))

Note this uses a , as the delimiter however this can be changed above.

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-09-08 : 01:13:29

Note that if you use an SP in a data set, for MVP to work, you must select Command Type = "StoredProceure" & enter "usp_mySP" as the commend text, rather han using Command Type = "Text" & putting command as "exec usp_mySP @MVP".

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-09-08 : 10:50:44

Hi thanks for the help.

I used a similar SP before but I am getting the same error when trying to test the stored procedure maybe I am doing the wrong thing. But the input asks for the values and delimiator and I get same error message:

'The request for procedure 'fn_MVParam' failed because 'fn_MVParam' is a table valued function object. (Error: 2809)

Thanks

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-09 : 02:27:35

Hi,

cipriani1984 did you solve this problem? If so how did you get it to work?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-09 : 03:37:09

quote:
Originally posted by dexter.knudson

Try:
select distinct centre_code
from staesubx
where board_code IN (@board_code)

That wont work
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-09-09 : 08:58:58

No I didnt solve this problem, I kept getting the error I posted above that it is a function etc. Im looking into the link the guy has provided. Have you seen my error before?

quote:
Originally posted by harlingtonthewizard

Hi,

cipriani1984 did you solve this problem? If so how did you get it to work?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-09-09 : 09:21:37

Hi, thanks for the msg. Im alittle confused alot of those are stored procedures and I call to them right? They are not really generic if you are setting the fields @orderID, @Name etc.

Im afraid I am abit new to stored procedure calls, If you could just explain in simplicity (/me sighs) that would be great, im not looking for the direct answer just easy path to the answer.

That would be great.

Cipriani

quote:
Originally posted by madhivanan

quote:
Originally posted by dexter.knudson

Try:
select distinct centre_code
from staesubx
where board_code IN (@board_code)

That wont work
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-09-09 : 17:21:30

It sounds like you are trying to use the function as a stored procedure.
Using stored procs is a good approach generally as they facilitate re-use, run faster & the sql is in one place. But in your case, for you to get this working, I recommend just using SQL directly in your data set instead of trying to use a stored procedure.
All you need to do in this case is put this code in your reporting services data set:
-----------------------------
select distinct centre_code
from staesubx
where board_code IN (@board_code)
---------------------------------
and make @board_code a multi-value parameter- very simple.

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-09-09 : 17:26:47

If you really, really need to use a stored procedure. Pls paste the following in here:
1. Your CREATE stored procedure code
2. What you have in your data set

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-09 : 20:04:37

I had the same problem as I started with entering sql straight into reporting services and switched to stored proecedures down the track. This is when I found the reports through reporting services would run with a single value but not with multi-values. This is where I got the info from and it fixed my problem, I am using reporting services 2005 and have now used this function through many stored procedures without a problem. Can you run the stored procedure from management studio? I test all my procedures here before using them to create a dataset for a report. Also, are you declaring your parameters within your stored procedure?

http://en.csharp-online.net/Building_Reports_in_SQL_Server_2005—Working_with_Multivalued_Parameters

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-09 : 21:33:19

madhivanan do you see anything wrong with what I am using? As above. I do see one problem, the function will only work with string and not int datatype right?

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-09 : 23:39:02

Does anyone know how to change this function or use so it works with int datatype from reporting services. It works fine for multivalued strings but I ran into trouble as soon as I started using int datatypes in my report parameters within reporting services 2005:)

USE [VC]
GO
/****** Object: UserDefinedFunction [dbo].[fn_MVParam] Script Date: 09/08/2008 09:36:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_MVParam](@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @VALUES TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(4000)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @VALUES(Param) VALUES(@Piece)
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END

This is the use in the stored proc
EventID int

WHERE EventID IN (Select Param From fn_MVParamET (@EventID,','))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-10 : 02:41:54

<<
I do see one problem, the function will only work with string and not int datatype right?
>>

Did you get an error?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-10 : 02:47:26

quote:
Originally posted by dexter.knudson

It sounds like you are trying to use the function as a stored procedure.
Using stored procs is a good approach generally as they facilitate re-use, run faster & the sql is in one place. But in your case, for you to get this working, I recommend just using SQL directly in your data set instead of trying to use a stored procedure.
All you need to do in this case is put this code in your reporting services data set:
-----------------------------
select distinct centre_code
from staesubx
where board_code IN (@board_code)
---------------------------------
and make @board_code a multi-value parameter- very simple.

As I told you, it wont work

declare @id varchar(100)
set @id='4,5,7'
select * from sysobjects
where id in (@id)

You get an error

The correct method is to use

select * from sysobjects
where ','+@id+',' like '%,'+cast(id as varchar(10))+',%'

Madhivanan

Failing to plan is Planning to fail

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-10 : 04:52:24

<<
I do see one problem, the function will only work with string and not int datatype right?
>>

Did you get an error?

Not when I run the stored procedures but yes when the report is run from visual studio. The error is: Query execution failed for data set 'TotalSepDuration'. Error converting data type nvarchar to int.

Here is the procedure.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[TotalSepDuration] Script Date: 09/10/2008 17:49:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TotalSepDuration]

@SiteID int,
@EventType nvarchar(max),
@DateRange nvarchar(30),
@DBName nvarchar (max)

AS

--SET NOCOUNT ON
Declare @sql nvarchar(max)
Declare @ParamDefinition AS nvarchar(max)
Select @sql=''
Select @sql=@sql+ 'SELECT SUM(TotalSeconds) / 86400 AS Days, SUM(TotalSeconds) % 86400 / 3600 AS Hours, SUM(TotalSeconds) % 3600 / 60 AS Minutes, SUM(TotalSeconds) % 60 AS Seconds, SUM(TotalSeconds) AS TSec, SiteName, TransmitterType, '''+name+''' AS DatabaseName, Count(EventID) As EventCount
FROM (SELECT EventID, DATEDIFF(second, StartTime, EndTime) AS TotalSeconds, SiteName, TransmitterType
FROM (SELECT EventDetails.EventID, SiteDetails.SiteName, SiteDetails.TransmitterType,
CASE
WHEN EventDetails.EventStartTime < StartDateRange THEN StartDateRange
WHEN EventDetails.EventStartTime IS NULL THEN StartDateRange
ELSE EventDetails.EventStartTime
END AS StartTime,
CASE
WHEN EventDetails.EventEndTime > EndDateRange THEN EndDateRange
WHEN EventDetails.EventEndTime IS NULL THEN EndDateRange
ELSE EventDetails.EventEndTime
END AS EndTime
FROM (SELECT StartDateRange, EndDateRange, DateRangeSelection
FROM (SELECT CAST('''+@DateRange+''' AS nvarchar(30)) AS DateRangeSelection,
CASE
WHEN '''+@DateRange+''' = ''Current Hour'' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last Hour'' THEN DATEADD(HOUR, - 1, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last 12 Hours'' THEN DATEADD(HOUR, - 12, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last 24 Hours'' THEN DATEADD(HOUR, - 24, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Today'' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN '''+@DateRange+''' = ''Yesterday'' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 1)
WHEN '''+@DateRange+''' = ''Last Weekend'' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5))
WHEN '''+@DateRange+''' = ''Current Week'' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last 7 Days'' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), - 7)
WHEN '''+@DateRange+''' = ''Last 5 Week Days'' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last Week'' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last 2 Weeks'' THEN DATEADD(WEEK, - 2, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Current Month'' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last 30 Days'' THEN DATEADD(DAY, - 30, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last 90 Days'' THEN DATEADD(DAY, - 90, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last Month'' THEN DATEADD(MONTH, - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last 3 Months'' THEN DATEADD(MONTH, - 3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Current Quarter'' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last Quarter'' THEN DATEADD(QUARTER, - 1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Current Year'' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last Year'' THEN DATEADD(YEAR, - 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END AS StartDateRange,
CASE
WHEN '''+@DateRange+''' = ''Current Hour'' THEN GETDATE()
WHEN '''+@DateRange+''' = ''Last Hour'' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last 12 Hours'' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last 24 Hours'' THEN DATEADD(HOUR, 0, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Today'' THEN GETDATE()
WHEN '''+@DateRange+''' = ''Yesterday'' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN '''+@DateRange+''' = ''Last Weekend'' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 7))
WHEN '''+@DateRange+''' = ''Current Week'' THEN GETDATE()
WHEN '''+@DateRange+''' = ''Last 7 Days'' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN '''+@DateRange+''' = ''Last 5 Week Days'' THEN DATEADD(WEEK, - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5))
WHEN '''+@DateRange+''' = ''Last Week'' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last 2 Weeks'' THEN DATEADD(WEEK, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Current Month'' THEN GETDATE()
WHEN '''+@DateRange+''' = ''Last 30 Days'' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN '''+@DateRange+''' = ''Last 90 Days'' THEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
WHEN '''+@DateRange+''' = ''Last Month'' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Last 3 Months'' THEN DATEADD(MONTH, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Current Quarter'' THEN GETDATE()
WHEN '''+@DateRange+''' = ''Last Quarter'' THEN DATEADD(QUARTER, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
WHEN '''+@DateRange+''' = ''Current Year'' THEN GETDATE()
WHEN '''+@DateRange+''' = ''Last Year'' THEN DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
END AS EndDateRange) AS DateRangeSel) AS DateLimit CROSS JOIN
'+name+'.dbo.EventDetails INNER JOIN
'+name+'.dbo.SiteDetails ON '+name+'.dbo.EventDetails.SiteID = '+name+'.dbo.SiteDetails.SiteID
WHERE ((
(
(NOT((EventDetails.EventStartTime < DateLimit.StartDateRange) AND (EventDetails.EventEndTime < DateLimit.StartDateRange)))
AND
(NOT((EventDetails.EventStartTime > DateLimit.EndDateRange) AND (EventDetails.EventEndTime > DateLimit.EndDateRange)))
AND
(NOT((EventDetails.EventStartTime > DateLimit.EndDateRange) AND (EventDetails.EventEndTime IS NULL)))
)
OR
(
((EventDetails.EventStartTime < DateLimit.EndDateRange) AND (EventDetails.EventEndTime IS NULL))
)
)
AND SiteDetails.SiteID IN (Select Param From fn_MVParam ('''+@SiteID+''','',''))
AND EventDetails.EventType IN (Select Param From fn_MVParam ('''+@EventType+''','',''))
AND '''+name+''' IN (Select Param From fn_MVParam ('''+@DBName+''','',''))))
AS DateDif) AS TotalSum
GROUP BY SiteName, TransmitterType
' from sys.databases where name='VC' or name like 'VCA%'

Set @ParamDefinition =
' @SiteID int,
@EventType nvarchar(50),
@DateRange nvarchar(30),
@DBName nvarchar (100)'

Create table #t([Days] int, [Hours] int, [Minutes] int, [Seconds] int, [TSec] int, [SiteName] nvarchar(50), [TransmitterType] nvarchar(50), [DatabaseName] nvarchar(100), [EventCount] int)
Insert into #t

Execute sp_Executesql @sql, @ParamDefinition, @SiteID, @EventType, @DateRange, @DBName
Print @sql

Select DISTINCT [Days], [Hours], [Minutes], [Seconds], [TSec], [SiteName], [TransmitterType], [DatabaseName], [EventCount]
From #t
ORDER BY DatabaseName, TSec DESC

Drop Table #t

--SET NOCOUNT OFF

Return

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-10 : 05:09:30

So I changed all the declares for SiteID from int to nvarchar in the above proc also in reporting services and now it works. But how the field SiteID is int datatype in the database? Is this because the parameter values are coming from reporting services and it passes them as a sting? But then how does it do a compare against in the WHERE statement if the actual data in the datbase is int and the parameter is nvarchar? What I am missing here:)

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-09-10 : 05:20:14

Hi, This is the one I was using above as I explained in my previous msgs. But I always get same error 'Procedure or Function fn_MVP has too many arguements specified'

Any thoughts?

Ill explain what im doing.

I have 1 data set (1) which is the basic form. The parameter is a seperate data set(2). In a where clause in data set (1) i am calling for the fn_MVParam like in the tutorial on the link you provided, and provides the same error msg.

quote:
Originally posted by harlingtonthewizard

I had the same problem as I started with entering sql straight into reporting services and switched to stored proecedures down the track. This is when I found the reports through reporting services would run with a single value but not with multi-values. This is where I got the info from and it fixed my problem, I am using reporting services 2005 and have now used this function through many stored procedures without a problem. Can you run the stored procedure from management studio? I test all my procedures here before using them to create a dataset for a report. Also, are you declaring your parameters within your stored procedure?

http://en.csharp-online.net/Building_Reports_in_SQL_Server_2005—Working_with_Multivalued_Parameters

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-09-10 : 05:26:57

Hi Madhivanan, The following works for me when I enter it in the *dataset* as I instructed:
select * from table_1
where id in (@id)
This works for both int & strings.

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-09-10 : 05:50:33

Hi Harlington,
Your function works for me on int params when I call it in this simple SP:
----------------------------------
CREATE PROCEDURE usp_table_1
@ints varchar(4000)
AS
SELECT *
from Table_1
where id in (select param from dbo.fn_MVParam(@ints,','))
---------------------------
However, it is essential that the data set commandType is StoredProcedure and the command is just: usp_table_1

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources