CHAPTER 6 BUILDING REPORTSFigure 6-30. MVP options
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 6-31.
Because the values for the MVP will be returned as a string—taking the year, for example, as
“2007,2008”—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—2007, for example—this would be
fine because SQL would correctly evaluate the single value within the IN clause. However, when multiple
values or Select All is chosen, SSRS passes a string such as “2007,2008,2009,2010”. 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.