March 10, 2009 at 10:28 am
Subject : SSRS issues using multi-value report parameters with a stored procedure
----
SQL Server 2005 ENT (9.0.3042)
Visual Studio 2005 PRO (8.0.50727.762)
.NET Framework 2.0.50727 SP2
SSRS Designer 9.00.4035.00
----
SSRS Report
Report uses a Stored Procedure for the Query.
When Query ran in Management Studio Query window it returns data fine.
Getting it to work in Report Designer gives issues.
Is there a way of changing the multi-value delimiter from comma to pipe without having to use the REPLACE command later? This would solve all my issues below if I could.
----
Report Parameters
@Product is a string, multi-value, with a list of text values to choose from.
@ActivityDateFrom & @ActivityDateTo are both datetime types.
Example data in the parameters (changed of course to not reveal any company info)...
@Product = 'BSQ Prod Area 1, BSQ Prod Area 2, BSQ Prod Area 3'
@ActivityDateFrom = '2009-01-03 00:00:00'
@ActivityDateTo = '2009-01-05 00:00:00'
----
Data Tab
Unaltered code which fails....
* Works fine when you click on the Data set, Right Click and choose Refresh on the Data tab.
* But does not work in Preview tab inside Report Designer
EXEC [dbo].[RPT_CustomerVIPReports_Report01] @Product, @ActivityDateFrom, @ActivityDateTo
gives the following error when Previewing the report.....
Error Message :- Procedure of function RPT_CustomerVIPReports_Report01 has too many arguments specified.
My opinion :- Does not work as @Product contains multiple values separated by commas. Therefore it gets confused thinking there are more parameters when there are not. If I just put one value in @Product (no commas) it works fine.
Ideal Code fails....
* Works fine in a Management Studio Query Window
* Works fine when you click on the Data set, Right Click and choose Refresh on the Data tab.
* But does not work in Preview tab inside Report Designer
DECLARE @Products varchar(255)
SET @Products = REPLACE(@Product, CHAR(44), '|')
EXEC [dbo].[RPT_CustomerVIPReports_Report01] @Products, @ActivityDateFrom, @ActivityDateTo
gives the following error when Previewing the report.....
Error Message :- The replace function requires 3 argument(s).
My opinion :- Preview tab is expecting stored procedure call only and not the lines of code before it.
Alternative 1 but still fails....
* But does not work in Data tab inside Report Designer (when you Refresh the Data Set)
EXEC [dbo].[RPT_CustomerVIPReports_Report01] (REPLACE(@Product, CHAR(44), '|') ), @ActivityDateFrom, @ActivityDateTo
gives the following error when Refreshing the data set on the Data tab in the report.....
Error Message :- Incorrect syntax near 'REPLACE'. (Microsoft SQL Server, Error: 102)
My opinion :- Putting an expression where a parameter is expected should work as it resolves to the data type expected. However I think the syntax checker does not allow this.
Alternative 2 but still fails....
* Works fine when you click on the Data set, Right Click and choose Refresh on the Data tab.
* But does not work in Preview tab inside Report Designer
BEGIN TRAN T1;
DECLARE @Products varchar(255);
SET @Products = REPLACE(@Product, CHAR(44), '|') ;
EXEC [dbo].[RPT_CustomerVIPReports_Report01] @Products, @ActivityDateFrom, @ActivityDateTo;
COMMIT TRAN T1;
gives the following error when Previewing the report.....
Error Message :- The replace function requires 3 argument(s).
My opinion :- Preview tab is expecting stored procedure call only and not the lines of code before it.
NOTE for all above :- CHAR(44) is a comma.
----
Referenced Stored Procedure
CREATE PROCEDURE [dbo].[RPT_CustomerVIPReports_Report01]
-- Add the parameters for the stored procedure here
@Product varchar(255),
@ActivityDateFrom datetime,
@ActivityDateTo datetime
AS
BEGIN
...
INNER JOIN Charlist_to_tbl(@Product, '|') s ON fin.ProductName = s.str
...
END
----
Referenced Function used by Stored Procedure
CREATE FUNCTION [dbo].[Charlist_to_tbl]
(@list nvarchar(MAX),
@delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000) NOT NULL,
nstr nvarchar(2000) NOT NULL) AS
BEGIN
DECLARE @endpos int,
@startpos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
-- For each position in String (with multiple values inside)
WHILE @textpos <= datalength(@list) / 2
BEGIN
-- Get the next chunk of data
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @startpos = 0
-- Search for next delimiter (default of comma)
SET @endpos = charindex(@delimiter COLLATE Latin1_General_CS_AS, @tmpstr)
-- Whilst not on last string in string
WHILE @endpos > 0
BEGIN
-- Get the string before the next string (before delimiter)
SET @tmpval = ltrim(rtrim(substring(@tmpstr, @startpos + 1,
@endpos - @startpos - 1)))
-- Place string in to table (creates new row)
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
-- Look for next delimiter (comma by default)
SET @startpos = @endpos
SET @endpos = charindex(@delimiter COLLATE Latin1_General_CS_AS,
@tmpstr, @startpos + 1)
END
-- Get last string after final delimiter
SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
END
-- Place last string in to table (creates new row)
INSERT @tbl(str, nstr)
VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
RETURN
END
Regards,
Richard.
March 11, 2009 at 6:55 am
FYI, if you want the average working stiff, like me, who just has a couple of minutes a day to help people out, to answer, you're going to have to make your posts much more concise. I was going to suggest the function to split the values, but it looks like you've tried that already. I assume you have commas in your products and that's why you can't use the comma separated list? Are there commas in your list index (like the Product code)? Again, sorry I didn't take the time to read the whole post, just trying to help out with the amount of time I have.
Rick Todd
March 11, 2009 at 9:06 am
Basically it is a pain to get multi-value parameters to work with stored procedures. Attached is a zip file that includes:
a simple report that uses a multi-value parameter
the datasource
the sp being called
In the sp I use a Tally CTE to split the parameter. This method will out-perform the looping function you currently have. This is from Jeff Moden's article on Tally tables, http://qa.sqlservercentral.com/articles/TSQL/62867/.
I have run this report successfully with a variety of selections in the parameter from ALL to 1 selection.
I hope this helps.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 12, 2009 at 3:46 am
Thank you all for your suggestions.
I got my answer in the end from another forum.
See below for solution ......
BEGIN
You don't put anything but the stored procedure. RS goes out and discovers
what the parameters are.
Just put RPT_CustomerVIPReports_Report01
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"rmsterling" wrote in message
news:EE125C09-1CFF-4E8C-B117-9139631E2B68@microsoft.com...
> Found out how to change to stored procedure.
>
> Placed name of stored procedure followed by parameters after it.
>
> When I go to preview screen it says 'Could not find stored procedure ''.'
>
> What have I done wrong?
>
> "rmsterling" wrote:
>
>> How do I change the Data Set type to Stored Procedure?
>>
>> "Bruce L-C [MVP]" wrote:
>>
>> > I pass multi-parameters all the time. You should not be given an error
>> > about
>> > the number of parameters. Is your statement all on one line? It should
>> > be.
>> > Also, you don't need to be using Exec. Change the dataset type to
>> > stored
>> > procedure and just put in the name of the stored procedure and RS
>> > figures
>> > out the stored procedures parameters.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply