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 ...