Building Reports in SQL Server 2005—Working with Multivalued Parameters

Visual C# Tutorials

Database Tutorials

Building Reports Report Wizard Build Report from Scratch Format the Output Add Subtotals Interactivity § Set Report Parameters Apply a Filter Add a Chart Final Touches Multivalued Parameters Summary

© 2006 R. Landrum, W. J. Voytek

Working with Multivalued Parameters

Multivalued parameters are an enhancement to SSRS for SQL Server 2005 that is probably one of the most awaited features for an SSRS update. Having the ability to individually select values to feed into the report is a powerful feature that most other reporting applications take for granted and that was not available in SSRS for SQL Server 2000. Working with multivalued parameters to achieve the most usefulness from them, however, requires special design considerations, as mentioned in Chapter 3. The reason for this, especially when working with stored procedures, is that the multivalued parameters are passed back to the stored procedure as a string value. The only way to work effectively with multivalued parameters is to know that the query or stored procedure will evaluate all, one, or multiple values returned to it based on user selection. Because SQL Server does not evaluate a string in the same way it does a single value in a stored procedure, which honestly has been the bane of SQL developers for years, you have to go into multivalued parameters knowing that you will have to parse string values. For writers/logicians like ourselves, this is a fun game. For others, who have to develop reports with multivalued input parameters for a large audience, this can be a nightmare. Rest assured that once you understand string manipulation techniques, multivalued parameters will be a worthwhile time investment.

To accurately demonstrate how to work with multivalued parameters, which we will affectionately refer to as MVPs henceforth, let’s take a copy of the Employee Service Cost report with the assumption that you will redesign it to accept the Year and Month parameters as multivalue. To begin, you will have to first modify your base stored procedure. Previously it was fine to evaluate the expression of your Year and Month parameters with the logic in Listing 4-3.

Listing 4-3. Logic to Evaluate Year and Month Parameters Without MVP

1=Case
      When ( @ServiceYear IS NULL) then 1
      When ( @ServiceYear IS NOT NULL) AND @ServiceYear =
      Cast(DatePart(YYYY,ChargeServiceStartDate) AS int) then 1
   else 0
   End
AND
1=Case
      When (@ServiceMonth IS NULL) then 1
      When (@ServiceMonth IS NOT NULL) AND @ServiceMonth =
      Cast(DatePart(MM,ChargeServiceStartDate) AS int) then 1
   else 0
END

However, now that you will be using MVPs, NULL values are not acceptable. The value of NULL in your logic was to select all values. This precluded you from accepting more than one value. For example, if you had the years 2002, 2003, 2004, and 2005 as valid values, you could either select all the values by selecting NULL or select only one value to narrow the data. You could not have selected 2003 and 2004. Now you can. The only way to effectively use MVPs is through the WHERE clause of the query or stored procedure, with parameters, that feeds the report data. You will have to take advantage of the IN clause of T-SQL to make the best use of MVPs. Unfortunately, though, it is not as simple as modifying the stored procedure to say Where value IN (@MyParameter), because SQL does not evaluate the IN clause as a string when using a stored procedure parameter. We can best explain this with the following example.

Let’s say you make the Year and Month report parameters multivalued parameters. You can do this quite simply by checking the Multi-value box in the Report Parameters dialog box, as shown in Figure 4-25. Notice also that the Allow Null Value checkbox is unchecked. Allow Null Values cannot be checked if you want MVPs to work.

Figure 4-25. Report rendered in the browser

If you were to execute the report now, you would see, as you did in the previous example using a dataset to populate the available values, that you are able to select one or more or all values for the year and month options, as shown in Figure 4-26.

Figure 4-26. Multiple parameter selection

Because the values for the MVP will be returned as a string—taking the year, for example, as "2003,3004"—this will not work with the stored procedure logic that you have defined. You will need to modify the stored procedure to use the IN clause so that the value will be equivalent to the following expression:

WHERE
1=Case
      When Cast(DatePart(YYYY,ChargeServiceStartDate) AS varchar(20)) IN (@Year)
   End

The problem here is that the variable @Year will be evaluated as a string and not an integer as it is defined in the stored procedure. If you were to select a single value—2003, for example—this would be fine because SQL would correctly evaluate the single value within the IN clause. SSRS, however, when multiple values or Select All is chosen, passes a string such as "2002,2003,2004,2005". When evaluated within the stored procedure, the query will fail. You need to first change the datatype of Year and Month to be a character or string value. So, you will choose varchar(20) for your stored procedure and parse out the values as they are passed in. Using varchar(20) will allow you to select a wide enough range to cover the Year and Month value strings.

The best way to parse the string as it is returned from the report is another decision you must make both for performance and versatility. You have two effective methods for doing this, either dynamic SQL or a UDF. Creating dynamic SQL, which is essentially building a variable SQL expression using variables defined by user input, is cumbersome and syntactically challenging. Wrapping SQL statements within quotes and programmatically concatenating variables is time-consuming and often frustrating, yielding unpredictable results. What is worse is that it opens itself up to SQL injection hacks where users can interject values as strings that may execute statements that the developer did not intend. The best way to handle string values for MVPs is through a UDF to parse the individual values and feed these into the IN clause of the query. Knowing that the values will always be returned in a comma-separated string makes loading the values into an accessible table much easier by using a function designed for this purpose. This type of function is called a table-valued function, because the parsed rows of the input string are loaded into a table that can then be referenced as a subquery in the calling stored procedure. Let’s take a look at a parsing function that you will use in your stored procedure while working with MVPs. Listing 4-4 defines the UDF called fn_MVParam. This function is in the Pro_SSRS database that you have been using.

Listing 4-4. fn_MVParam, String-Parsing Function

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(4000)
   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(@Piece)
         SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
         IF LEN(@RepParam) = 0 BREAK
      END
   RETURN
END

This function, when called from your Emp_Svc_Cost_MVP stored procedure, will return the parsed values from SSRS’s multivalued parameter selection and allow you to use this as criteria for selecting data to include in the report. The key point of this function is that it uses several T-SQL functions itself, such as CHARINDEX, LEN, and LEFT, to populate the @Values table with the individual items from your report parameter string. The following modification to the base Emp_Svc_Cost stored procedure, as shown in Listing 4-5, will be required to make the Emp_Svc_Cost_MVP stored procedure effectively work with the MVPs.

Listing 4-5. Modification to WHERE Clause for MVP

1=Case
When Cast(DatePart(YYYY,ChargeServiceStartDate)
AS varchar(20)) IN (SELECT Param FROM fn_MVParam(@ServiceYear,',')) then 1
   else 0
   End
AND
   1=Case
      When Cast(DatePart(MM,ChargeServiceStartDate)
      AS varchar(20)) IN (SELECT Param FROM fn_MVParam(@ServiceMonth,',' )) then 1
   else 0
END

Notice that instead of saying IN (@Year), for example, which will not work, you are calling your function fn_MVParam. The function takes two values, the string and the delimiter. In this case, you are using a comma as the delimiter.

When the report is run and the new function is called, you can see that you can select one, two, any combination, or all values from the populated drop-down, and you know that your stored procedure will effectively handle the parsing, evaluating, and criteria to deliver only the data that you want to see in the report, as shown in Figure 4-27.

Figure 4-27. Report generated with multiple selection criteria

The completed report for multivalued parameters in the Pro_SSRS project is called EmployeeServiceCost_MVP.rdl.