Building Reports in SQL Server 2005—Working with Multivalued Parameters
|Visual C# Tutorials|
|© 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
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
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
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.
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.
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
Month to be a character or string value. So, you will
varchar(20) for your stored procedure and parse out the values as they are passed in.
varchar(20) will allow you to select a wide enough range to cover the
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
Pro_SSRS database that you have been using.
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
LEFT, to populate the
with the individual items from your report parameter string. The following modification to
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
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.
The completed report for multivalued parameters in the
Pro_SSRS project is called