Designing Data Access - Professional SQL Server 2005 Reporting Services - page 132

By passing selection criteria parameters at the database object level, network traffic can be greatly
reduced and the report is rendered more efficiently. However, if the user will be providing different
parameter values to render several views of the same report within a session, the database will be
queried repeatedly, perhaps resulting in longer overall wait times and much of the same data will be
moving across the network multiple times. In Figure 5-8, a larger volume of data is returned from the
database server since it is unfiltered. Filtering then occurs by using report parameters on the Report
Server.

Database Server
(data provider)

Report Server
(data co n sumer)

Report

Database Result Set Dataset

Network
Traffic

Stored
Procedure
or Query

U n filtered Data

Parameters

Figure 5-8

If all of the data necessary for each query to be executed in a user's session is obtained in one result set,
it will result in a greater volume of network traffic for a single execution. However, it may reduce subse-
quent report rendering times.

Selection parameters may be applied to data at the report level rather than at the data source. Since all of
the data is cached (held in memory), reports will be rendered much faster. This technique can reduce the
overall network traffic and rendering time.

You certainly don't want to retrieve unnecessary data from the data source, so a combination of these
two techniques may be the appropriate solution, depending upon specific reporting needs. For example,
if you are a regional sales manager and you wish to get sales summaries for each of the territories within
your region, you may begin your session by retrieving all of the regional sales data for a range of dates.
For each territory report, this data is simply filtered down to the territory level.

Parameter Concepts
Although I don't believe this to be overly complicated, at first I found the whole parameter puzzle to be
a little confusing until I had a chance to do some creative things with parameters in both queries and
report expressions. To lessen your agony (and hopefully shorten your learning experience,) I'll explain
how parameters are defined in simple queries and reports, and I'll also explain how you may need to
use (and define) parameters in more complex reports.