Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Multi-valued Parameters problem in Reporting Services

By Alex Grinberg,

Recently, I had a problem with calling a stored procedure where the parameter was set to the Multi-value option. I admit that Microsoft did an enormous job in order to make this feature available within SQL Server Reporting Services (SSRS). However, in using the Multi-value option with a stored procedure call I found a few issues. The tricky part was when I used embedded SQL within a Dataset, the Multi-value option works just fine. I needed to make sure that the IN statement had been placed in the WHERE clause instead of an equals (=) sign.

Everything worked just fine at this point, but when I converted the T-SQL into a stored procedure, I was say… disappointed that its execution retrieved nothing. The stored procedure call worked for a single selected value only. When I selected more than one value the report raised an error, which does not make any logical sense to me. The error was “execution failed … “Incorrect syntax near ‘=’”. What can you say in this case? Most likely, the answer is nothing.

When I researched the problem, I found that I was not alone in this situation. Many developers described the problem, but not many valuable solutions were suggested. Logically thinking, when the Multi-value option is selected, then the report converts all selected values into an array. Therefore, for embedded SQL, the report compiler could easily convert an array into a string line and then run the query multiple times. A stored procedure cannot take the array as a parameter, so I needed to do this job manually. In other words – pass the parameter string to the stored procedure and then parse the string into a table.

Now, let’s go to the solution. Problem #1: how do you tell the report not to pass the array? We need to open the report’s code (right mouse click on a report, then select View Code). The report appears to us as XML. The next step is we need to find a place where the report assigns a value to the parameter. I would suggest opening search box (Ctrl+F) when typing the name of the parameter. You will find this in the QueryParameters XML section. The section looks like sample the below:

 <QueryParameters>
  <QueryParameter Name="@CategoryName">
     <Value>=Parameters!CategoryName.Value</Value>
  </QueryParameter>
 </QueryParameters>

Take a closer look at <Value>=Parameters!CategoryName.Value</Value>. Now I understand why the error message says “Incorrect syntax near ‘=’”. The array is not compatible with a stored procedure parameter. I needed to stop the report to generate the array. To fix the problem, we need to use the Join function. Let’s correct the Value line using a Join function like this :

 <Value>=Join(Parameters!CategoryName.Value, "~") </Value>.

The Join function has two parameters: the first parameter is the array and the second is delimiter. This way we convert an array into a parameter string. The next step is parsing the parameter string. I used CTE to accomplish this task, but if you are not comfortable with CTE or you do not like this technique, use a WHILE loop. The final decision is left up to you.

My example based on the Northwind database.

CREATE PROC GetProductByCategory
             @CategoryName nvarchar(4000)
AS
 DECLARE     @d char(1)
SET @d = '~';
WITH CSVCte (StartPos, EndPos) AS
( SELECT 1 AS StartPos, CHARINDEX(@d , @CategoryName + @d) AS EndPos
             UNION ALL
  SELECT EndPos + 1 AS StartPos , CHARINDEX(@d,@CategoryName + @d , EndPos + 1
) AS EndPos FROM CSVCTE
             WHERE CHARINDEX(@d, @CategoryName + @d, EndPos + 1) <> 0)
SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock,
             Discontinued = CASE Discontinued WHEN 0 THEN 'NO' ELSE 'YES' END
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
             JOIN CSVCte ON SUBSTRING(@CategoryName, StartPos,EndPos-StartPos) = Categories.CategoryName

Conclusion

SQL Server Reporting Services is a great product, which gives us easy report building features and at the same time flexibility to modify the report code. I would not consider the problem calling a stored procedure where the parameter was set to Multi-value option an SSRS bug. As long as we can modify the report code, we still have control over the reports. As a final word, I would also like to add that .NET knowledge is required if you want to be a successful SQL Server 2005 (and up) DBA.

Total article views: 13545 | Views in the last 30 days: 19
 
Related Articles
FORUM

procedure

how to send array as a parameter to procedure

FORUM

Passing multi-valued parameter selections in to stored procedures

Problems with passing multi-valued parameter selections as a csv string in to stored procedure param...

FORUM

Passing a List/Array Parameter to Procedure

Array/List from VBA to SQL

FORUM

Execute Stored Procedure with Select Statement as Input Parameters

Execute Stored Procedure with Select Statement as Input Parameters

FORUM

Optional grouping depending on selected parameter in reporting services

Optional grouping depending on selected parameter in reporting services

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones