CHAPTER 6 BUILDING REPORTSFigure 6-29. Report with valid year and month values
The EmployeeServiceCost_SP_Completed.rdl report in the Pro_SSRS project includes populated
parameters.
Working with Multivalued Parameters
Multivalued parameters, an enhancement released for SQL Server 2005, represent probably one of the
most awaited features for an SSRS update. Having the ability to select multiple values individually to
feed into the report is a powerful feature that most other reporting applications take for granted, but 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 may have to parse string values if you
want to use stored procedures and multivalued parameters together with SSRS. For writers/logicians like
us, 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.
SSRS does support multivalued parameters out of the box for queries as long as they are not stored
procedures. To work with multivalued parameters with queries, all that is required is to use the IN
keyword in the WHERE clause of the query that will be a dataset in the report. SSRS, in the case of
multivalued parameters used in this way, will rewrite the query for you at runtime based on the