Lesson 5: Adding a Report Parameter
In this lesson, you add a report parameter to filter the data that is returned by the query. The report filters data based on a department chosen by the user.
The dynamic query in this tutorial is constructed based on the value of a parameter. It is important that you use an available values list (also known as valid values) if you incorporate parameter data directly into a dynamic query; otherwise, your report server could be vulnerable to attack. With an available values list, the report server validates user input against the list and allows only values from the list to be used. In this way, you can control the values that are used in the dynamic query, and the report is secure.
However, if you do not use an available values list (Available values set to Non-queried, and the list of values left blank) and the data type is String, the user is presented with a text box that can take any value. In this case, it is possible for a report user to type SQL syntax into the text box, potentially opening the report and your server to a SQL injection attack. If the user has sufficient permissions to execute the new SQL statement, it may produce unwanted results on the server. In any query that directly incorporates data from a parameter, be sure to use an available values list, and ensure that any user running the report has only the permissions required to view the data in the report.
To add a parameter
On the Report menu, click Report Parameters.
Note
If the Report menu is not available, click the Layout tab.
Click Add to add a new report parameter.
Set parameter properties as follows:
- For Name and Prompt, type Department.
- For Data type, select String.
- Clear the checkboxes: Hidden, Internal, Multi-value, Allow null value, and Allow blank value.
- For Available values, select From query.
- For Dataset, select Departments.
- For Value field, select DepartmentID.
- For Label field, select Name.
- For Default values, select Non-queried.
- In the box next to Non-queried, type 0.
The following image shows how the values appear in the Report Parameter dialog box.
.gif)
Click OK.
Click the Preview tab. The table should display a limited list of employees based on the static query (DepartmentID = 1). The parameter does not filter data at this point.
Next Steps
After you specify a report parameter and preview the report, you can continue to the next step: Lesson 6: Updating the Employees Dataset with a Dynamic Query.
See Also
Concepts
Tutorial: Using a Dynamic Query in a Report
Other Resources
Using Dynamic Queries
Using Parameters in a Report