CHAPTER 6 BUILDING REPORTS
selections you choose in the report. This is a straightforward and well-documented process. However,
we like using stored procedures for reasons already covered, and thus we have to make special
accommodations, which you will see.
To accurately demonstrate how to use a store procedure 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 multivalues. 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 6-3.Listing 6-3. Logic to Evaluate Year and Month Parameters Without MVPs1=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
ENDHowever, 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 2007, 2008, 2009, and 2010 as valid values, you could either select all the values by
selecting NULL or select only one value to filter the data. You could not have selected 2008 and 2009.
With multivalued parameters 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 which can be seen by opening up the EmployeeServiceCost_MVP.rdl report
in Pro_SSRS project from the book source code.
Let’s say you make the Year and Month report parameters multivalued parameters. You can do this
quite simply by checking the Allow Multiple Values box in the Report Parameter Properties window, as
shown in Figure 6-30. Notice also that the Allow Null Value checkbox is unchecked. The Allow Null Value
option cannot be checked if you want MVPs to work.