Report parameters provide a mechanism for passing values into a report. You can create a report parameter to prompt the report user to specify a value for a particular field, or you can use a hidden report parameter to pass a value into the report on a programmatic basis. For instance, a hidden parameter might be used to pass a customer’s account code into a report if you did not want a customer to look at any account data but her own.

Report parameters have global scope, which means they are available to the entire report and any report element can access a report parameter’s value. To enable user filtering, you typically bind the report parameter to a corresponding data set parameter.

When you create a report parameter, you perform two main tasks.

How to create a simple report parameter

If necessary, repeat these steps to create additional report parameters.

After you create your report parameter, insert a parameter marker in the WHERE clause of the SQL query of the data set that you want to filter with the value of the report parameter. The parameter marker, represented by a question mark (?), indicates where you want BIRT to insert the parameter value.

For example, to ask the user to specify the threshold inventory quantity for a restocking report, insert the ? parameter marker in the WHERE clause, as shown in the following example:

   WHERE quantityinstock < ?

When the report runs, if the user specifies 500 for quantityinstock, BIRT replaces ? with 500 and sends the following filter condition to the data source:

   WHERE quantityinstock < 500

If you write a filter condition that uses more than one field, think about the interaction of the field values. Each of the following WHERE clauses, for example, returns a different set of rows:

    WHERE quantityinstock < ? AND productvendor = ?

    WHERE quantityinstock < ? OR productvendor = ?

The first clause returns only those rows in which both the quantityinstock and the productvendor values match the values that replace the ? markers. The second clause returns more rows. It returns rows in which the quantityinstock value is less than the value that replaces the quantityinstock marker and rows in which the productvendor value matches the value that replaces the productvendor ? marker.

It is necessary to write filter conditions that make sense for your report. Before you complete all steps to enable filtering, you should test your filter conditions by specifying actual values in the WHERE clause to verify that the results meet your expectations.

SQL supports many options and operators for specifying filter conditions. For complete information about writing WHERE clauses and SQL statements in general, consult a book about SQL.

How to insert a parameter marker in the SQL query

This procedure assumes that you already created a data set.

At this point, you are ready to create a data set parameter for each ? parameter marker. The next section describes this task.

A data set parameter passes a value that replaces the ? parameter marker in the WHERE clause of the query when the query is run. You must create one data set parameter for each parameter marker in the query’s WHERE clause. If you do not, BIRT displays an error.

SQL uses the positions of the ? parameter markers in the WHERE clause to determine which data set parameter matches which ? marker. Therefore, the order of the data set parameters is critical. For example, you must create two data set parameters if you specify the following WHERE clause:

   WHERE quantityinstock < ? and productvendor = ?

The first parameter in the list of data set parameters must pass a value to quantityinstock < ?, and the second parameter must pass a value to productvendor = ?. Figure 16-4 shows these two data set parameters. The order of these parameters matches the order of the parameter markers’ appearance in the example WHERE clause.

Figure 16-4. Edit Data Set, with two data set parameters

A data set parameter replaces a parameter marker in the query with a value from another part of the report design. Think of the data set parameter as an intermediary—it gets a value from a report element, then it passes the value to the query. To enable users to filter data, you use a data set parameter to get a value from a report parameter. If you change the WHERE clause in the query, update the data set parameters accordingly.

The following procedure tells you how to create a data set parameter.

How to create a data set parameter

This procedure assumes that you already inserted a parameter marker in the SQL query, as described previously. If you use multiple data set parameters, create them in the order in which their corresponding parameter markers appear in the WHERE clause.

If you completed all the tasks that were described earlier in this chapter, the WHERE clause of your query updates with the specified values, and the report displays the rows that match the WHERE clause.

Just as you create a report parameter to enable user filtering at query run time, you create a report parameter to enable user filtering after the query is executed. As report parameter creation is covered earlier in this chapter, it is not repeated here.

Typically, when you specify a filter condition to display only certain data in a report, you specify the value on which to search, as in these examples:

    row["quantityinstock"] Less than 500
    row["productvendor"] Equal to "Exoto Designs"

To enable a user to filter data when the report runs, specify the report parameter as the filter value. The following expressions are examples of filter conditions whose values are set to report parameters:

    row["quantityinstock"] Less than params["RP_quantityinstock"]
    row["productvendor"] Equal to params["RP_productvendor"]

When the user runs the report and supplies a value for the report parameter, BIRT dynamically updates the filter condition with the parameter value and generates a report that displays only the specified data.

How to dynamically update a filter condition when the report runs

This procedure assumes that you already created

While it is not required, you should provide a default value for each report parameter. If you do not, the user must specify a value to generate the report. It is particularly important to specify a default value if you present the report parameter as a text box in which the user has to type a value, rather than a list box from which the user can select a value.

The default value can be any value from the data set field that is bound to the report parameter. You should, however, specify a value that most users would select, such as Active for an account status. Another option is to specify a value that appears most often. If the field contains unique values, such as an order ID or a customer ID, it is typical to specify the first ID as the default value, particularly when using a list box or combo box to display a list of values.

How to provide a default value for a report parameter

This procedure assumes you are editing a report parameter that you already created.

List boxes, combo boxes, and radio buttons are ideal mechanisms for providing a list of choices to a user. A user can make only one selection from multiple choices. The differences between these user interface elements are:

Figure 16-13 shows an example of an Enter Parameters dialog that displays a list box, combo box, and radio buttons.

Figure 16-13. Three types of selection elements in Enter Parameters

You create a list of values for a list box or a combo box using one of the following techniques:

For radio buttons, you can only create a static list of values. In a static list, you specify the values to display to the report user during report design. In a dynamic list, BIRT retrieves the values from the data source when the report runs. Create a dynamic list for values that are frequently updated in the data source. New customer names or product names, for example, are often added to a data source. If you create a static list of these values, you have to manually update the list to match the values in the data source. Creating a static list, however, provides more control over the list of values to display to the report user. For example, you might not want to present all values to the user.

    SELECT Customers.state
    FROM Customers

    SELECT Customers.name
    FROM Customers

By default, the default values you specify and the values that you import to display in a list box, combo box, or radio buttons appear exactly as they are stored in the data source. Sometimes, the values are not in a format that is appropriate for display. For example, US telephone numbers might be stored in ########## format. It is preferable to display these values in another format, such as (###) ###-####. Remember also that international telephone numbers will not appear correctly using this format.

You can specify a different format using the Format property. You format a report parameter value in the same way that you format a value that a data element displays. You select from a list of common formats, or you specify a custom format pattern.

Note that you can reformat values for display purposes only. You cannot use the Format property to require a user to type values in a particular format, nor does it affect the format of the data in the generated report.

How to format a report parameter value

This procedure assumes that you are formatting a report parameter that you already created.

You can provide users who are database-savvy with the option of selecting rows when a field has a null value. Databases use a null value to indicate that there is no data in that field. A user might find it useful, for example, to view customer data where a customer’s credit-limit field is null, because null in this field means that the customer account is new, and a credit limit has not been set.

For string data, a null value is not the same as an empty string (" "), which is an actual value. You can set properties that determine whether a user can choose to match a null value, an empty string, or both. To enable a user to match a null value or a blank value, you must use a text box or a list box for the report parameter. The combo box and radio buttons require the user to specify an actual value.

Figure 16-21 shows Enter Parameters with two parameters for which null values are enabled. The Threshold Quantity parameter uses a text box, and the Product Vendor parameter uses a list box. To specify a null value, the user selects Null Value.

Figure 16-21. Parameters with null values

How to enable a user to specify a null or blank value

This procedure assumes that you are editing a report parameter that you already created.

If you create many parameters or want to provide the user with a prompt for certain sets of parameters, consider using parameter groups. Typically, you create parameter groups to organize report parameters logically. For example, you could create a parameter group to contain the report parameters for a specific table or data set.

When prompting the user to supply parameter values, the Enter Parameters dialog displays the parameter group name and the parameters within that group. Figure 16-22 shows two parameter groups, Customer Information and Order Information. Each group contains multiple report parameters.

Figure 16-22. Parameter groups with multiple parameters in each group

You can create a parameter group before or after you create the report parameters. If you create the report parameters first, you can drag them from the report parameters list to the parameter group. If you create the parameter group first, you create the report parameters in the parameter group.

How to create a parameter group

Cascading parameters are report parameters that have a hierarchical relationship, as shown in the following three examples:

Product Type         Territory           Mutual Fund Type
   Product              Country             Fund Class
                          City                 Fund

In a group of cascading parameters, each report parameter displays a set of values. When the report user selects a value from the top-level parameter, the selected value determines the values that the next parameter displays, and so on.

The advantages of cascading parameters are obvious when you compare the alternative technique, which is the creation of separate and independent parameters. Consider the Territory-Country-City example. If you create three separate parameters, the territory parameter displays a list of all territories, the country parameter displays all countries, and the city parameter displays all cities. Figure 16-27 shows independent parameters as they appear to the report user.

Figure 16-27. Independent parameters

The user has to traverse three long lists to select the values, and there is a potential for user errors. The user can inadvertently select invalid combinations, such as Japan, USA, Paris.

Cascading parameters, on the other hand, display only relevant values based on user selections. For example, the territory parameter displays all the territories, and when the report runs, the user selects a territory, such as NA (North America), then the country parameter displays only countries in the sales territory of NA. Similarly, when the user selects USA, the city parameter displays only cities in the USA. Figure 16-28 shows cascading parameters as they appear to the report user.

Figure 16-28. Cascading parameters

Cascading parameters can be based on a single data set or multiple data sets. Use cascading parameters with a single data set when the values for all the parameters are contained in a single data set. Use cascading parameters with multiple data sets when the values for the parameter must be retrieved from more than one data set.

Before you create cascading report parameters, you must first create the data set(s) that retrieve the values with which to populate the cascading parameter lists. The query in the following example retrieves all the values from the territory, country, and city fields of the Offices table. You specify this query in a data set to populate the Territory-Country-City parameter lists.

    SELECT Offices.territory,
    Offices.country,
    Offices.city
    FROM Offices

Given that only one data set is required, Territory-Country-City would be a single data set cascading parameter.

When you create cascading parameters based on multiple data sets, you must link the data sets together by adding a data set parameter to the queries for all data sets except for the one used to value the top-level parameter. Each data set parameter must then be bound to the appropriate report parameter from the cascading parameter group in order to form the appropriate dependency relationships.

For example, if you were creating a cascading parameter that prompted the report user to select an office from the Offices table and then prompted the user to select an employee from the Employees table based on the office in which she worked, you would create an Offices data set and an Employee data set. The Employee data set would have a data set parameter bound to the value of the office-report parameter in order to filter rows for employees from the selected office.

After you create cascading parameters, bind the final report parameter in the group to a data set parameter, or specify the report parameter as a filter-condition value, depending on the user-filtering technique that you are using.

How to create single data set cascading parameters

How to create multiple data set cascading parameters

This procedure assumes that you created all the data sets that retrieve the values for the cascading parameter lists.

By default, report parameter groups and report parameters are displayed to the user in the order in which they appear on Data Explorer—Report Parameters. The list of groups and parameters in the report parameters list, in turn, appear in the order in which you created them. You can change the order of groups and report parameters.

How to change the order in which report parameters appear