CHAPTER 3 INTRODUCTION TO REPORTING SERVICES DESIGN WITH SQL SERVER DATA TOOLS
Does not accept NULL values: This is important when deciding which
parameters to make multivalued, as it will influence the design of the
underlying query or stored procedure. In this case, you built logic into the
Emp_Svc_Cost stored procedure to accept NULL values and return all data when
NULL was passed in from a parameter. You will have to modify this stored
procedure to work with multivalue parameters.Will be evaluated as a String: Since a multivalue parameter returns a comma-
separated string, you will need to consider the data type assignment for the
stored procedure parameters—the report parameter and the query or stored
procedure parameter need to have the same data type to work properly.Affects performance: Multivalue parameters are best used when there is a
relatively small list of values. Choosing to allow users to select a range of
years—for example, “2010,2011,2012”—is much better than allowing them to
select 1,000 patients based on their IDs, as these will all be passed into the
stored procedure as a comma-separated string value to be evaluated with the IN
or EXISTS clause.Cannot be used in filters: Unlike single-value or nullable parameters in SSRS,
multivalue parameters can be used only to pass back to the query or stored
procedure, so you can’t use them to limit with report filters.Requires string manipulation logic in stored procedures: Stored procedures do
not evaluate multivalue parameters correctly, so using IN
(@MyReportParameterArray) in a stored procedure, for example, will not return
the expected result. This has been an issue with SQL for a long time, and
numerous ways, both good and bad, exist to work with multivalue string arrays
in stored procedures. Two possible choices are user-defined functions (UDFs)
and dynamic SQL. In Chapter 6, which covers the building of a deployable
report, we will discuss how to use a special UDF that parses the multivalue
report parameter into a table that will work effectively to limit the result set to
exactly what is expected.Setting up Filters
Like parameters, report filters can limit the results of data on a report; however, you don’t necessarily
have to use them in conjunction with a parameter. In fact, filters, which can be defined at many points in
the report, evaluate an expression and filter the results based on that execution. Filters take this form:<Filter Expression> <Operator><Filter Value>An example of a filter is one that limits the data on a report to a specific user or that is based on user
input from a parameter value.
Chapter 11 demonstrates how to use a filter that limits the report based on a built-in Global
collection, which includes the username of the person executing the report. Filters are beneficial
because once the report is rendered you can use them in conjunction with parameters to limit the data
in the report without re-querying the data source. In Figure 3-12, you can see a filter that limits the data
displayed based on a parameter called User. The logic is this: if the parameter value for User is equal to a
field value of User, then include only those records where they match. Otherwise, include all records.
Parameters and filters are included as elements of an RDL report file.