March 7, 2007 at 8:29 am
Does anyone know the c# code required to run a report using web services for SQL2005, specifically how to pass multivalue parameters.
For a single parameter I use the following
parameters[0] = new TMSReportExecution2005.ParameterValue();
parameters[0].Name = Categories;
parameters[0].Value = 1;
For the last line how do I pass more than one value if it is a multivalue parameter
March 12, 2007 at 8:00 am
This was removed by the editor as SPAM
March 13, 2007 at 11:31 am
Try building a list (string) of categories and pass it to a T-SQL Function which will then parse each one by one. Call that function inside the SP that will return the Dataset.
Here is a sample function (extracted from the SQL Server Magazine: http://www.sqlmag.com/Articles/ArticleID/48596/pg/2/2.html)
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(10)
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(Cast(@Piece AS INT))
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
March 14, 2007 at 2:06 am
Thanks, I agree with what you're saying, but the problem is on the code side.
I'll elaborate
In report - create a multivalue parameter and set "available values" to "From query".
The parameter is passed to the report as a comma delimited string and so in the SQL you have to use a function to parse the string (similar to your example).
Fine so far, but the code (using webservices) doesn't let you do this.
eg
if paramstring = "1,2,3"
parametersExec[param].Value = paramstring;
this fails and from my testing it is because it breaks the valid values property
ie only a parameter of "1" or "2" or "3" is valid, it can't handle a multivalue parameter
"1,2,3" does not equal any of "1" or "2" or "3".
It looks like a bug, but this is the first time I've worked with reporting services and the webservices side so I just may not be doing it correctly.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply