Report Parameterization

Defining Report Parameters

To define report parameters within Report Designer, follow the steps below:

1. Select Edit → Parameter from the Designer menu. This opens the ‘Parameter Definition’ dialog box with its Parameters tab displayed. This tab displays all existing report and data-binding parameters.
2. Click ‘New’, and specify the name of the new parameter in the dialog box. Then click ‘OK’.
3. Click the ‘Set Selection List’ button to open the ‘Selection List’ dialog box. (The button’s text is displayed in green if the selected parameter already has a list associated.)
4. To enter a fixed set of labels/values for the parameter, follow these steps:
1. Click the List tab.
2. Click ‘Add’ to add a new label/value pair.
3. Enter the desired ‘Label’ and ‘Value’. (The ‘Label’ text is displayed to the user in the interface element; the corresponding ‘Value’ is assigned to the parameter.)
5. To acquire parameter labels/values from a query at runtime, follow these steps:
1. Click the Query tab.
2. Select the query that contains the label/value pairs. The results in the first column of the query will be used as the parameter values; the results in the second column of the query will be used as the labels. (Query conditions can reference other report parameters if those parameters possess default values.)
6. (Optional) Select ‘Allow Multiple Selection’ to permit the user to select multiple values for the parameter.
7. Click ‘OK’ to close the ‘Selection List’ dialog box.
8. (Optional) In the ‘Parameter Definition’ dialog box, select ‘As customization parameter’ to expose the parameter as a customization parameter. Customization parameters are displayed when the user clicks the ‘Customize’ button on the report toolbar.
9. (Optional) Select ‘Prompt User’ if the user should be automatically prompted to enter a value for this parameter. (Disable this option for parameters set by scripts and drill-down reports.)
10. (Optional) Select ‘Sort Values’ if you want the list to be sorted alphabetically.
11. Click ‘OK’ to close the ‘Selection List’ dialog box.

Parameterized Query

Parameters specified for a query condition or data binding condition are italicized in the Parameters tab. You can modify a query parameter’s default value and type through the Parameters tab. Query parameters can be used in the query selection conditions. A parameter can be either based on a subquery, or get its value from the end user at runtime. If a user parameter is used in a query, the application that performs the query needs to collect the parameter values before the query can be executed.

Multiple queries may have parameters with the same name. In this case, you can specify whether parameters sharing a name should be treated as the same parameter. To treat them as the same parameter (and prompt the user only once) select ‘Preferences’ from the Tools menu, click the Misc tab, and enable the ‘Parameter Name Unique’ option (default).

Parameter Sheets

If query or report parameters are defined for a report (see Parameterization above), then a default parameter screen opens when the user first launches the report. This screen prompts the user for all of the parameters which have the ‘Prompt User’ option enabled. (The prompts are displayed as a dialog box when the report is previewed in Designer.) You can create custom parameter prompting by designing a form using Designer’s form elements. Unlike the default parameter prompting, the form you design will appear as a regular report in the report viewer, and will be replaced by the new report when the form is submitted.

Staged Parameters

When you prompt the user for a series of related parameters, it is often desirable to guide the user through these choices in a certain order. This is called parameter staging. This technique allows you to constrain the options presented to the user at each stage so that only compatible parameter combinations can be selected.

For example, consider a Parameter Sheet that has two menus, one for ‘State’ and one for ‘City’. Initially, you want only the ‘State’ menu to be enabled, so that the user is forced to select a state first. After the user selects a state, you want to then enable the ‘City’ menu, but restrict its values to those cities that fall within with the selected state.

There are therefore several aspects to parameter staging, as described in the following sections:

• Creating a Parameter Sheet Layout Add controls, and set ‘Field’ values to map to corresponding parameters.
• Defining a Parameter Flow Specify the order for parameter control activation.
• Filtering Parameter Options Populate parameter controls with options that are consistent with previous selections.

When Do You Want to Parameterize a Report?

Parameterizing a report is a valuable technique in business intelligence and reporting, allowing you to create dynamic, customizable, and interactive reports. This approach is particularly useful when users need the flexibility to tailor report outputs based on specific criteria or preferences. Below are scenarios when parameterizing a report is beneficial:

1. Filtering Data Dynamically

• Use Case: When you want users to view subsets of data without creating multiple reports.
• Example: A sales report where the user selects a specific region, product category, or date range to focus on.
• Benefit: Reduces the need for static, predefined reports, providing flexibility and user control.

2. Customizing Output Formats

• Use Case: When users need reports in different formats or layouts.
• Example: Allowing users to select between summary tables, detailed views, or visual charts.
• Benefit: Accommodates varied user preferences without duplicating effort in report design.

3. Improving Performance

• Use Case: When datasets are large, and loading all the data is resource-intensive.
• Example: Instead of loading data for all departments, the report parameter limits output to a single department, reducing processing time.
• Benefit: Enhances performance by fetching only the necessary data.

4. Supporting What-If Analysis

• Use Case: When users want to test different scenarios or assumptions.
• Example: A financial report allowing users to adjust parameters like interest rates or growth percentages to see potential outcomes.
• Benefit: Empowers decision-makers with insights into multiple scenarios.

5. Securing Access to Data

• Use Case: When sensitive data needs to be restricted based on user roles or permissions.
• Example: A report parameter for user login, showing data only for their department or assigned region.
• Benefit: Ensures compliance with data security protocols.

6. Enhancing User Interaction

• Use Case: When you want to make reports more engaging and intuitive.
• Example: A dashboard with dropdowns, sliders, or checkboxes allowing users to manipulate data views in real time.
• Benefit: Creates a better user experience by empowering users to explore data independently.

7. Simplifying Complex Reports

• Use Case: When reports contain multiple layers of data that may overwhelm users.
• Example: A multi-tab report where parameters control which sections or details are displayed.
• Benefit: Reduces clutter, making reports more accessible and easier to understand.

8. Centralizing Report Management

• Use Case: When maintaining multiple versions of similar reports becomes cumbersome.
• Example: A single parameterized report template that can adapt to different reporting needs, such as monthly, quarterly, or annual views.
• Benefit: Streamlines report maintenance, reducing duplication and saving time.

9. Encouraging Collaboration and Sharing

• Use Case: When different stakeholders have specific data requirements but need to work from a unified report.
• Example: A shared project status report where team members can filter updates relevant to their tasks.
• Benefit: Promotes alignment and efficiency across teams.

Considerations for Parameterizing Reports

• Data Source Compatibility: Ensure the data source supports dynamic queries.
• Ease of Use: Design parameters to be intuitive for end-users.
• Performance Impact: Optimize queries and backend systems to handle parameter-driven data requests efficiently.

Previous: Advanced Enterprise Reporting Topics

Next: Staged Flows in Interactive Reports