Reporting Services and Multivalued Parameters

Adding multivalued parameters to existing reports that use embedded queries is as simple as selecting one option in the parameters’ properties. However, making reports that use stored procedures use multivalued parameters presents a challenge.The difficulty essentially boils down to the fact that a Reporting Services report uses an array to store a multivalued parameter’s values, but T-SQL doesn’t directly support arrays. If, for example, a stored procedure uses a field with an integer (int) data type, passing in a string array, such as ‘1,2,3,4,5’, causes the stored procedure to fail with a data type conversion error. . . .

Currently followed:

CREATE PROCEDURE [DBO].[Test_MVP]
@ProductCategory Int ,
@ProductSubCategory Int,
@StartDate datetime,
@EndDate datetime
AS
SELECT TOP 5 C.LastName, C.FirstName, E.EmployeeID, SUM(SOH.SubTotal)
   AS SaleAmount
FROM
Sales.SalesPerson SP
   INNER JOIN HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID
   INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
   INNER JOIN Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID
   INNER JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID
   INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID
   INNER JOIN Production.ProductSubCategory PS ON P.ProductSubCategoryID =
      PS.ProductSubCategoryID
   INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID =
      PC.ProductCategoryID
WHERE
(PC.ProductCategoryID = @ProductCategory)
AND (PS.ProductSubCategoryID IN (@ProductSubCategory))
AND (SOH.OrderDate > @StartDate) AND (SOH.OrderDate < @EndDate)
GROUP BY
C.LastName, C.FirstName, E.EmployeeID, PC.ProductCategoryID, PS.ProductSubCategoryID

 

Let’s now create a Function to split our arrays into TSQL readable format

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

Changed Version:

ALTER PROCEDURE [DBO].[Test_MVP]
@ProductCategory Int ,
@ProductSubCategory varchar(100),
@StartDate datetime,
@EndDate datetime
AS
SELECT TOP 5 C.LastName, C.FirstName, E.EmployeeID, SUM(SOH.SubTotal)
   AS SaleAmount
FROM
Sales.SalesPerson SP
   INNER JOIN HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID
   INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
   INNER JOIN Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID
   INNER JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID
   INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID
   INNER JOIN Production.ProductSubCategory PS ON P.ProductSubCategoryID =
      PS.ProductSubCategoryID
   INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID =
      PC.ProductCategoryID
WHERE
(PC.ProductCategoryID = @ProductCategory)
AND (PS.ProductSubCategoryID IN (Select Param from fn_MVParam
  (@ProductSubCategory,’,’)))

AND (SOH.OrderDate > @StartDate) AND (SOH.OrderDate < @EndDate)
GROUP BY
C.LastName, C.FirstName, E.EmployeeID, PC.ProductCategoryID, PS.ProductSubCategoryID

– Ananth Ramasamy Meenachi www.msarm.com

Advertisements

About msarm

Aspiring Enterprise Architect.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s