SSRS: Parameters

Providing control to the user to determine which records should be returned.

As with most reports, reports might contain thousands of records, many of which the user doesn’t desire to view.

To allow the user to decide upon a smaller subset of records, reports should be equipped with parameters commonly used with stored procedures.

To illustrate this feature, I’ll begin with a simple sproc I’ve created that returns a subset of fields from a View.

1-parameters

When I execute the query, it prompted me with a value for the City parameter it was expected, so I entered Seattle.

By looking in Report->Parameters, I see the report already knows of my City parameter.
However, I don’t wish to use the CustomerID parameter so I’ll delete it.
Also, when the user is prompted for a city, I want it to have proper case so I’ll update the City’s prompt field.

2-parameters

In case the user prints a report and later needs to remember how they received the results, I want whatever they enter for the City parameter to display in the report’s header.

Therefore, I’ll add a couple textbox controls to display the label and value provided by the user.

3-parameters

Now that I have added some fields from my dataset and prepared the header, I’ll execute the report.

4-parameters

You’ll notice no records display, this is due to no City value being provided.

By entering “Seattle” I now receive a large recordset.

5-parameters

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s