Parameterizing Your Reports
End users of your reports are always going to ask for changes to existing reports. Wouldn’t it be
nice if you could give them the ability to make changes to the reports themselves? With report
parameters, you can. With parameters on a report, users can use drop-down menus and text
fields to enter options to change a report to show different date ranges, hide columns, and choose
a completely different set of data to show on the report.
These parameters can be available from a drop-down menu with a list of values or a free-form
text box that allows the user to enter any value. The parameters can also have a default value
that loads automatically when the report loads. These options give the report a lot of flexibility.
To create parameters on a report, you can alter the query on the report, choose a stored pro-
cedure that has parameters, or manually create parameters. The best practice is to use stored
procedures for all your queries. This gives you the ability to make changes to the dataset of a
report without having to re-deploy it. Now, with shared datasets, you have this ability inside
reporting services. Several reports may use the same shared dataset and show the data in a
different layout, maybe one with a matrix, one with a table, and one with a chart. This new
feature will be discussed more in later lessons.
CREATING PARAMETERS
To show how to place parameters on a report, this lesson will use the matrix report from the
previous lesson. The first example will be altering the query of the report to add parameters.
This is a simple way to add them, but goes against the best practice of using stored procedures.
Nevertheless, if your business does not like to use stored procedures for some reason, the report
query option is available.
To place a query on the matrix report you will need to alter the query to match the following code:
SELECT FactResellerSales.OrderQuantity, FactResellerSales.SalesAmount,
DimReseller.ResellerName, DimGeography.City,
DimGeography.StateProvinceName,
DimGeography.EnglishCountryRegionName,
DimDate.CalendarQuarter, DimDate.CalendarYear
24
889633c24.indd 215 8/17/10 10:42:03 AM
216
LESSON 24 Parameterizing Your rePorts
FROM FactResellerSales INNER JOIN
DimDate ON FactResellerSales.OrderDateKey =
DimDate.DateKey INNER JOIN
DimReseller ON FactResellerSales.ResellerKey =
DimReseller.ResellerKey INNER JOIN
DimGeography ON DimReseller.GeographyKey =
DimGeography.GeographyKey
WHERE (DimGeography.EnglishCountryRegionName = ‘United States’) AND
DimDate.CalendarYear in (@Year) and
DimGeography.StateProvinceName in (@State)
Notice that at the bottom of the query are two extra where-clause comparisons. The state and the
year now need to be entered into the parameters at the top of the report before the report will load.
The report will show only the year and state the users enter. For this example the report is not using
a shared dataset: that way the report can be altered easily. It is still using the AWDW shared data
source, though. You could use the equals sign instead of the
in comparison function in the where
clause. Using the
in function gives you the ability to change the parameter to a multi-value param-
eter, which gives users the ability to select more than one value in a single parameter.
When you enter the preceding query as the dataset in the report, it will automatically create the two
parameters in the Parameters folder in the Report Data window, as shown in Figure 24-1.
At run time the report will load the preview screen, but you will see no data because the dataset is
waiting for the parameters to be entered. You can see these two parameters at the top of the report.
In this example the report has Florida entered as the state and 2001 as the year. In Figure 24-2 you
can see what the report looks like and you can see the two parameters at the top of the report. Notice
that only the data for 2001 and for Florida shows on the report.
FIGURE 241
DEFAULT PARAMETER VALUES
The end users are able to enter the year and the state they want to see on the report. Most users have
a value they always use in a report, and, therefore, would like to have the report load with that value
already in the parameter. To give your parameters default values, right-click the Year parameter in
the Parameter folder in the Report Data window and select Parameter Properties. Click the Default
FIGURE 242
889633c24.indd 216 8/17/10 10:42:03 AM

Get Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint® now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.