Skip to main content

Report Tag

Reporting made easy. Set it once and it is done!

Home  Products  Documentation  Videos  Downloads  Purchase  What's new  Technical Support  About Us  Contact Us   
Multivalue parameters and SQL Server stored procedure (Crystal reports and SSRS samples)

September 14th 2008
 
Since version 3.8 R-Tag Report Manager provides the interface, which supports the use of different methods to pass multiple values to SQL server stored procedures.
The report parameter should be set as "Multivalue parameter" and one of the available methods should be selected.


In the report filter, the parameter will be presented as a checked combo box. This will allow any combination of categories to be selected. Depending of the chosen multivalue scenario application will send to the report array,comma separated, xml, bitwise or table value. The value will be send to the stored procedure in case the report uses push method.

 
For the purpose of this article I will use NWind database, tables Products and Categories.
These 2 tables are related through column CategoryID. The SQL which will return all products and categories is:
SELECT c.CategoryID,c.CategoryName,p.ProductID,p.ProductName
FROM dbo.Categories c
    INNER JOIN dbo.products pON p.CategoryID = c.CategoryID 


My goal is to create a report, which will show products filtered by one or more categories. For example Grains/Cereals and Dairy Products.
The SQL generated by crystal reports is:
SELECT "categories"."CategoryID","categories"."CategoryName","products"."ProductID","products"."ProductName"
FROM "NWind"."dbo"."products" "Products"
     INNER JOIN "NWind"."dbo"."categories" "Categories"ON "products"."CategoryID" = "categories"."CategoryID" 


After creating a multiple value parameter “Category” and setting values to 4 and 5  (4 = Dairy Products  5= Grains/Cereals) Crystal reports will modify the SQL to:
SELECT "categories"."CategoryID","categories"."CategoryName","products"."ProductID","products"."ProductName"
FROM "NWind"."dbo"."products" "Products"
     INNER JOIN "NWind"."dbo"."categories" "Categories"ON "products"."CategoryID" = "categories"."CategoryID"
 WHERE ( "categories"."CategoryID" = OR "categories"."CategoryID" = 5 ) 

.. and will return just the products from the selected categories. This works well for simple reports. I cannot use stored procedure to speed up my report or to make complex calculations because SQLServer cannot accept array as parameter value.
 
Here are some alternatives, which will allow me to pass multiple values to a crystal report based on a SQLServer stored procedure:
 
Comma separated text
R-Tag Report Manager will format the values as a string like this '4,5'. In the stored procedure the value can be parsed to a table and joined with Categories table. A sample stored procedure is provided bellow:
CREATE PROCEDURE spGetProductsByCategory_Comma
   @CategoryID VARCHAR(MAX)
AS
  BEGIN
  SELECT c.CategoryID,c.CategoryName,p.ProductID,p.ProductName
  FROM dbo.Categories c
      INNER JOIN dbo.Products ON p.CategoryID = c.CategoryID
      INNER JOIN (
                  SELECT
 CONVERT(int, ID) AS CategoryID
                  FROM dbo.fnTextToTable(@CategoryID)
                  )
 cat  ON c.CategoryID = cat.CategoryID
  END

GO

This stored procedure uses a function to parse parameter value to a table.  
The SQLServer 2008 function is:

CREATE FUNCTION [dbo].[fnTextToTable2008]
  (@Data VARCHAR(MAX))

RETURNS
 @Tbl TABLE (ID VARCHAR(8000))

AS

  BEGIN
      DECLARE @Value VARCHAR(8000)

      WHILE LEN(@Data) > 0
        BEGIN
            SET @Value = LEFT(@Data, ISNULL(NULLIF(CHARINDEX(',', @Data) - 1, -1)LEN(@Data)))
            SET @Data = STUFF(@Data, 1, DATALENGTH(@Value) + 1, '')

            IF RTRIM(@Value) <> '' 
              INSERT INTO @Tbl (IDVALUES(@Value)
        END

      RETURN
  END 


In SQLServer 2000 the procedure will be a little bit more complicate since the data will be a text variable which will be processed on chunks
CREATE FUNCTION [dbo].[fnTextToTable]
  (
@Data TEXT)

RETURNS
 @Tbl TABLE (ID VARCHAR(8000))

AS

  BEGIN
      DECLAREPstn BIGINT,
              @Value VARCHAR(8000),
              @NewText VARCHAR(8000)

      SELECT @NewText = '/',@Pstn = 0

      WHILE RTRIM(@NewText) <> ''
        BEGIN
            SET @NewText = SUBSTRING(@Data, @Pstn + 1, 7900)

            IF DATALENGTH(@NewText) = 7900
              SET @NewText = SUBSTRING(@NewText, 1, CHARINDEX(',', @NewText ',',DATALENGTH(@NewText) - 1))+ ','
            ELSE
              SET @NewText = @NewText + ','

            SET @Pstn = @Pstn + DATALENGTH(@NewText)

            WHILE RTRIM(@NewText) <> ''
              BEGIN
                  SET @Value = SUBSTRING(@NewText, 1, CHARINDEX(',', @NewText, 1) - 1)
                  SET @NewText = STUFF(@NewText, 1, DATALENGTH(@Value) + 1, '')

                  IF RTRIM(@Value) <> ''
                    INSERT INTO @Tbl (ID)VALUES(@Value)
              END
        END

      RETURN
  END 
 


XML
R-Tag Report Manager will format the values as a string like this '<ROOT><ITEM ID="4"/><ITEM ID="5"/></ROOT>'.
In the stored procedure the value can be parsed to a table and joined with Categories table. A sample stored procedure is provided bellow:

CREATE PROCEDURE spGetProductsByCategory_XML
  (
@CategoryID XML)

AS

  BEGIN
      SELECT c.CategoryID,c.CategoryName,p.ProductID,p.ProductName
        FROM dbo.Categories c
             INNER JOIN dbo.Products pON p.CategoryID = c.CategoryID
             INNER JOIN (
                         SELECT
 CategoryData.T.value('@ID', 'int') AS ID
                         FROM @CategoryID.nodes('ROOT/ITEM') AS CategoryData(T)
                         ) 
catON c.CategoryID = cat.ID
  END

GO 


Bitwise numbers
The example may not be entirely relevant. Since the number of the categories can be higher than 30 BITWISE case is not good approach at all. I only tried to show different options with a popular database.
This method will require specific ID values: 1,2,4,8,16 … 2^n
Values like this can be combined and separated with BITWISE operators
 
Obviously my previous examples for filtering Category ID  4 and 5 will be not applicable here , but if you plan your data retrieval during the initial database design you can create the IDs in such way so they will be BITWISE compatible. This method is applicable for optional values, which are limited to certain number and can be combined. Keep in mind that the numbers will grow really fast:
 
2^0 = 1
2^1 = 2
2^2 = 4
2^3 = 8
...
2^20 = 1,048,576
...
2^30 = 1,073,741,824
...
2^60 = 1,152,921,504,606,850,000

2^100 = 1,267,650,600,228,230,000,000,000,000,000
Just to make this clear 2^31-1 is the max INT value and 2^63-1 is the max BIGINT value in SQLServer
 
Let’s assume that the number of categories will never be higher than 30 and assign BITWISE compatible IDs
 
 Old IDName New ID 

Beverages 

Condiments 

Confections 

Dairy Products 

Grains/Cereals 

16 

Meat/Poultry 

32 

Produce 

64 

Seafood 

128 

R-Tag Report Manager will combine the values for Combination Dairy Products and Grains/Cereals and will pass to the report one value 12   (4 | 8)
 
The following stored procedure will return all categories contained in the @CategoryID value
CREATE PROCEDURE spGetProductsByCategory_BITWISE (@CategoryID INT)
AS
  BEGIN
      SELECT c.CategoryID,c.CategoryName,p.ProductID,p.ProductName
        FROM dbo.Categories c
             INNER JOIN dbo.Products p
                     ON p.CategoryID = c.CategoryID
       WHERE @CategoryID & c.CategoryID = c.CategoryID
  END

GO
  

SQL Server table
SQLServer 2008 and above introduced table valued parameters. Parameter values might be passed directly to the stored procedure by declaring a type and use it as a table valued parameter:
 
CREATE TYPE IDTable AS TABLE ( ID INT );

CREATE PROCEDURE spGetProductsByCategory_TABLE @table IDTable READONLY
AS
  BEGIN
      SELECT c.CategoryID,c.CategoryName,p.ProductID,p.ProductName
        FROM dbo.Categories c
             INNER JOIN dbo.Products p
                     ON p.CategoryID = c.CategoryID
             INNER JOIN @table t
                     ON t.ID = c.CategoryID
  END

GO
  

 
Using multiple values from Crystal Reports
Comma separated values and BITWISE parameters can be used directly from crystal reports. The user will need to know the IDs of the categories and how to combine them, which is not very convenient. XML and Table parameters cannot be implemented directly at all. As a workaround you can create a formula in the main report which will join the values either in a comma separated string, BITWISE number or XML and pass it to a sub-report which will call the stored procedure. Because a subreport is already used to pass the joined values your actual report cannot use subreports. If this is not a problem you can use these functions to format the values on crystal reports side and the stored procedures provided above to process the values on the SQL Server side.

Sample functions to format the values:
//create a comma separated string from a string array
Join({?Category},',')

//create a XML from a string array'<ROOT><ITEM ID="' + Join({?Category},'"><ITEM ID="') + '"></ROOT>'
//create a comma separated string from a numeric array
NumberVar i;
NumberVar iLen := Ubound({?CategoryInt});
StringVar strData:="";

For i := 1 To iLen do
(
    strData := strData + "," + ToText({?CategoryInt}[i],0);
);
strData:=MID(strData, 2);
strData;

//create a XML from a numeric array
NumberVar i;
NumberVar iLen := Ubound({?CategoryInt});
StringVar strData:="";
For i := 1 To iLen do
(
    strData := strData + '"><ITEM ID="' + ToText({?CategoryInt}[i],0);
);
strData:='<ROOT'+ MID(strData,2) +'"></ROOT>';
strData;

//create a BITWISE number
NumberVar i;
NumberVar iLen := Ubound({?CategoryInt});
NumberVar intData:=0;

For i := 1 To iLen do
(
    intData := intData + {?CategoryInt}[i];
);
intData;