Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

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