SSRS Report Execution With Multi-Valued Parameter Through Stored Procedure – Part 1
Recently, I have been racking my brains over a little SSRS inefficiency which hasn’t become apparent until I tried to use RANK() SQL function in one of the queries. As it turns out Query Designer in Report Designer does not play well with what is easily achievable in Management Studio (or other SQL editor for that matter) and common function expressions, for example RANK(), are still not supported. It is fairly well documented issue with SSRS (I’m running version 10.5 on SQL Server 2008 R2) and in most circumstances it can be rectified by building a custom view or running a query from a stored procedure (which should also positively impact execution performance). The problem with using stored procedure is that when we want to narrow the output scope by means of using selectable parameters with multivalued option (i.e. In(@Parameter) rather than =(@Parameter)), it is impossible to pass this this multivalued parameter through the very SP. This can be achieved by using a function within the SP to fetch parameter values from a table, rather than a string and passing the result into the SP for further crunching. This process seems to be more complex than it should be (why not just allow RANK() function in Report Designer!) so let me start from the beginning.
Let’s say we would like to build a report which will display top five best-selling products in their categories for a given distributor and its corresponding warehouse by calendar month. In the report itself, you would like to make the distributor the first parameter the user will need to select and the warehouse a cascading parameter which stems from the distributor parameter and is marked as multivalued (see below).
To display the results we should be able to run this simple SQL code with RANK() function to pull out top 5 products which sell best.
-------------------------------------------------------------------------------------
-- QUERY NO 1 --
-------------------------------------------------------------------------------------
SELECT * FROM
(
SELECT COUNT(Sale_ID) as Number_of_Sales, Calendar_Month_Report_Date,
Product_Category, Distributor, Warehouse,
RANK() OVER (Partition By Distributor ORDER BY COUNT(Sale_ID) DESC) as Rank_Column
FROM Sales
WHERE (Distributor = @Distributor)
AND (Warehouse = IN(@Warehouse))
AND (Calendar_Month_Report_Date = @Month)
GROUP BY Calendar_Month_Report_Date, Product_Category, Distributor, Warehouse
) as a
WHERE Rank_Column ORDER BY Number_of_Sales
GO
To turn it into a SP we prefix the above with the following:
--------------------------------------------------------------------------------------
-- QUERY NO 2 --
--------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[sp_GetTop5SalesCategories]
@Distributor varchar (30),
@Warehouse varchar (25),
@Month datetime
AS
Mind that the parameters have already been defined in the report and assigned to their respective datasets. The problem with the above two solutions is that in the first query the RANK() function is not supported in Report Designer and the second code sample works only with single-valued parameters. As our report has a “allow multiple values” enabled on @Warehouse parameter the SP will throw an error unless we “feed” it with a nifty function which converts comma-delimited strings into a table.
--------------------------------------------------------------------------------------
-- CREATE FUNCTION dbo.fn_String_To_Table --
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[fn_String_To_Table] (
@String VARCHAR(max), /* input string */
@Delimeter char(1), /* delimiter */
@TrimSpace bit ) /* kill whitespace? */
RETURNS @Table TABLE ( [Val] VARCHAR(4000) )
AS
BEGIN
DECLARE @Val VARCHAR(4000)
WHILE LEN(@String) > 0
BEGIN
SET @Val = LEFT(@String,
ISNULL(NULLIF(CHARINDEX(@Delimeter, @String) - 1, -1),
LEN(@String)))
SET @String = SUBSTRING(@String,
ISNULL(NULLIF(CHARINDEX(@Delimeter, @String), 0),
LEN(@String)) + 1, LEN(@String))
IF @TrimSpace = 1 Set @Val = LTRIM(RTRIM(@Val))
INSERT INTO @Table ( [Val] )
VALUES ( @Val )
END
RETURN
END
The beauty about this function is that it will happily take our multi-valued parameter and return a table which can be queried using “IN” statement. It also takes a “delimiter type” and “trim white spaces YES or NO” inputs, but its core functionality comes from the table output it provides for convenient query execution against its content. Once we replace the highlighted code row from Query No 1 (row 11) with a modified version which calls the function, as per the image below we should be able to execute the stored procedure and run the report as expected, regardless of whether a single or multiple values are selected from @Warehouse parameter.
--------------------------------------------------------------------------------------- -- REPLACEMENT CODE -- --------------------------------------------------------------------------------------- AND (Warehouse = IN(SELECT val FROM dbo.fn_String_to_Table (@Warehouse,',',1))) --
Please also visit the second part to this post which can be found HERE.
http://scuttle.org/bookmarks.php/pass?action=addThis entry was posted on Wednesday, February 1st, 2012 at 12:40 pm and is filed under How To's, SQL, SSRS. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
