FYI: Multiple values in an SRS parameter
-
I am writing this as a solution for others so that no one has to go through what I just went through. This one has been driving me crazy for the last 2 weeks.
Imagine my surprise when I found out I couldn't put multiple numbers into a parameter in SQL Reporting Services (SRS).
I knew there must be a way to do this, but I couldn't find it on many SQL forums nor was there anything in Google. If it's out there I probably over looked it, but I figured that the solution for this needs to be on SQL Server Central.
I needed the ability to put a string of numbers into an SRS parameter in the following format.
'1,2,3,4'
This could then be put into a sub query in an SRS data set like this:
(note the jobs table is in the pubs database)
select * from jobs
where Job_ID in (@string)
Problem, if I tried make the statement into a SQL string and execute it then SRS couldn't display the columns for the data set.
Example:
Exec ('select * from jobs')
Works just fine where as:
exec ('select * from jobs where job_ID in (' + @string + ') ')
doesn't work(outside the dataset). I assume this is because the value in the variable is not available to the dataset. (I even tried putting a default value in but it didn't work.)
So I went the messy route of executing a stored procedure in the first data set that would put my values from the parameter into a table then I would do a sub query on the table in the other datasets. This involved putting waitfor delay on every data set to give the procedure long enough time to execute. I hated this because it was messy, but it worked.
(By the way if you are wondering in what order SRS executes its datasets basically it executes in the order they are created. If you put a "waitfor delay" in say the second dataset, it will not delay the other data sets after it only the one you put the waitfor delay in. So you could also say it executes them all at the same time. Though we know that's impossible
)Someone though pointed me in the right direction. Instead of a table and procedure I could combine the two using a user defined function.
The function could be treated like a table and return the values in a variable data type of table or SQL Array. (See article: The Array in SQL Server for details)
Example:
select * from jobs
Where job_ID in (select * from FN_NumberBreaker('1,2,3,4'))
So I wasn't too far off on the solution. SQL server just had a cleaner way to do this.
The function looks like this:
(This was created from my original stored procedure)
Note to newbies, to make this work, copy the code below, open query analyzer in your favorite database and paste the code below in to it and execute it. Note, if you don't have rights to create functions in that database your either out of luck or you need to go beg the admin to put it in for you.
--------------
create function FN_NumberBreaker (@numbers varchar(8000))
returns @NumbersBroken table (numbers int)
AS
begin
declare @length as int
declare @index as int
declare @current as varchar(100)
set @length=len(@numbers)
set @index=1
set @current=''
while @index<= @length
begin
If substring(@numbers,@index,1)<>','
set @current=@current+(substring(@numbers,@index,1) )
else
begin
insert into @NumbersBroken (numbers) values(@current)
set @current=''
End
set @index=@index+1
end
--- this part looks for the last number on the end of the string.
set @current=@current+cast(substring(@numbers,@index,1) as int)
insert into @NumbersBroken (numbers) values(@current)
return
end
-----------------
As a bonus this function protects you from SQL injections via SRS.
The limit to length of characters you can put into the SRS parameter appears to be the same as SQL itself which is 8000.
(In my case this includes numbers and commas)
Hopefully you won't need to put that many in, but that is the reason I set the varchar value of the input parameter on the function to 8000 because the project I am working on will have its values passed from a web site to the report.
This may require a large string because users could select something that includes everything available. I doubt we will every hit the 8000 character limit, but we needed to know the breaking point.
Anyway that's my story. I hope this helps others who are stumbling around looking for this answer.
-
Keith
Hopefully your exercise has taught you not to go crazy for two weeks.
Searching for 'multiple values in report parameter' right here at SQLServerCentral would have produced a resolution a lot quicker.
eg: one of the results returned from the search is this discussion,
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=150&messageid=225774
--------------------
Colt 45 - the original point and click interface
-
Ha! I knew it has to be somewhere. Guess my search needed to be "multivalue" and not multi parameter. Try as I might to search it always comes down to a simple turn of the phrase that I did not think of
Thanks!
.
-
Sorry
But I'm not getting any results. It's giving me a bad head ache.
I have a report. I use a stored procedure.
CREATE
PROCEDURE [dbo].[sp_sepia_Financieel_Ouderdomsanalyse]
-- Add the parameters for the stored procedure here
varchar(30),
@Method
varchar(10)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
--set dateformat dmy
DECLARE @SQL VARCHAR(MAX)
DECLARE @dtNow DATETIME
DECLARE @dtMin30 DATETIME
DECLARE @dtMin60 DATETIME
DECLARE @dtMin90 DATETIME
DECLARE @dtMin120 DATETIME
DECLARE @dtMin150 DATETIME
DECLARE @dtMin180 DATETIME
DECLARE @Opend varchar(5)
set dateformat dmy
SET @dtNow = getdate()
SET @dtMin30 = DATEADD(DAY,-30,@dtNow)
SET @dtMin60 = DATEADD(DAY,-60,@dtNow)
SET @dtMin90 = DATEADD(DAY,-90,@dtNow)
SET @dtMin120 = DATEADD(DAY,-120,@dtNow)
SET @dtMin150 = DATEADD(DAY,-150,@dtNow)
SET @dtMin180 = DATEADD(DAY,-180,@dtNow)
SET @Opend = 1
-- Insert statements for procedure here
/*print @dtNow
print convert(varchar(10),@dtNow,103)
print @dtMin30
print convert(varchar(10),@dtMin30,103)
Print @dtMin60
print convert(varchar(10),@dtMin60,103)
Print @dtMin90
print convert(varchar(10),@dtMin90,103)
Print @dtMin120
print convert(varchar(10),@dtMin120,103)
Print @dtMin150
print convert(varchar(10),@dtMin150,103)
Print @dtMin180
print convert(varchar(10),@dtMin180,103)
print @Opend*/
set
@sql=' set dateformat dmy
SELECT No_,Name, [Payment Method Code] as Betalingsconditie, CustMain.[Responsibility Center],Blocked,
(SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] as CLE
join dbo.[FIN SEPIA$Sales Invoice Header] as IH
on CLE.[Document No_] = IH.No_
join dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] as DCLE
on DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) and ([Open] = '''
+ @Opend + ''')) as [Total],
(SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] as CLE
join dbo.[FIN SEPIA$Sales Invoice Header] as IH
on CLE.[Document No_] = IH.No_
join dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] as DCLE
on DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) and ([Open] = '''
+ @Opend + ''') and (IH.[Due Date] between convert(datetime,'''+ convert(varchar(10),@dtMin30,103) + ''',103) and convert(datetime,''' + convert(varchar(10),@dtNow,103) + ''',103))) as [<30],
(SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] as CLE
join dbo.[FIN SEPIA$Sales Invoice Header] as IH
on CLE.[Document No_] = IH.No_
join dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] as DCLE
on DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) and ([Open] = '''
+ @Opend + ''') and (IH.[Due Date] between convert(datetime,''' + convert(varchar(10),@dtMin60,103)+ ''',103) and convert(datetime,''' + convert(varchar(10),@dtMin30,103)+ ''',103))) as [30-60],
(SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] as CLE
join dbo.[FIN SEPIA$Sales Invoice Header] as IH
on CLE.[Document No_] = IH.No_
join dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] as DCLE
on DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) and ([Open] = '''
+ @Opend + ''') and (IH.[Due Date] between convert(datetime,'''+ convert(varchar(10),@dtMin90,103)+ ''',103) and convert(datetime,''' + convert(varchar(10),@dtMin60,103)+ ''',103))) as [60-90],
(SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] as CLE
join dbo.[FIN SEPIA$Sales Invoice Header] as IH
on CLE.[Document No_] = IH.No_
join dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] as DCLE
on DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) and ([Open] = '''
+ @Opend + ''') and (IH.[Due Date] between convert(datetime,''' + convert(varchar(10),@dtMin120,103)+ ''',103) and convert(datetime,''' + convert(varchar(10),@dtMin90,103)+ ''',103))) as [90-120],
(SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] as CLE
join dbo.[FIN SEPIA$Sales Invoice Header] as IH
on CLE.[Document No_] = IH.No_
join dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] as DCLE
on DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) and ([Open] = '''
+ @Opend + ''') and (IH.[Due Date] between convert(datetime,''' + convert(varchar(10),@dtMin150,103)+ ''',103) and convert(datetime,''' + convert(varchar(10),@dtMin120,103)+ ''',103))) as [120-150],
(SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] as CLE
join dbo.[FIN SEPIA$Sales Invoice Header] as IH
on CLE.[Document No_] = IH.No_
join dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] as DCLE
on DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) and ([Open] = '''
+ @Opend + ''') and (IH.[Due Date] between convert(datetime,''' + convert(varchar(10),@dtMin180,103)+ ''',103) and convert(datetime,''' + convert(varchar(10),@dtMin150,103)+ ''',103))) as [150-180],
(SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] as CLE
join dbo.[FIN SEPIA$Sales Invoice Header] as IH
on CLE.[Document No_] = IH.No_
join dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] as DCLE
on DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) and ([Open] = '''
+ @Opend + ''') and (IH.[Due Date] <= convert(datetime,''' + convert(varchar(10),@dtMin180,103)+ ''',103))) as [>=180]
FROM dbo.[FIN SEPIA$Customer] as CustMain
--where CustMain.[Responsibility Center] in ('''
+ @rc + ''') and [Payment Method Code] in (''' + @Method + ''')
where CustMain.[Responsibility Center] in (SELECT Item FROM dbo.Split('''
+ @rc + ''', ' + ''',''' + ')) and
[Payment Method Code] in (SELECT Item FROM dbo.Split('''
+ @Method + ''', ' + ''',''' + '))
ORDER by No_
'
exec
(@sql)
END
------------------------------------------This is my split function :
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
FUNCTION [dbo].[Split]
(
@ItemList
NVARCHAR(4000),
@delimiter
CHAR(1)
)
RETURNS
@IDTable TABLE (Item VARCHAR(50))
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
What is wrong ???
Thx in advance. I need multivalues a lot in reports.
Don't get it why Microsoft does not support this to stored procs
Kind regards
El Jefe
JV
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply