4 Adding Parameters and Lists of Values

This chapter describes how to add parameters and lists of values to a BI Publisher data model.

This chapter includes the following sections:

• Section 4.1, "About Parameters"

• Section 4.2, "Adding a New Parameter"

• Section 4.3, "About Lists of Values"

• Section 4.4, "Adding Lists of Values"

4.1 About Parameters

Adding parameters to a data model enables users to interact with data when they view reports.

BI Publisher supports the following parameter types:

• Text — the user enters a text string to pass as the parameter.

• Menu — the user makes selections from a list of values. A list of values can contain fixed data that you specify or the list can be created using a SQL query that is executed against any of the defined data sources. This option supports multiple selections, a "Select All" option, and partial page refresh for cascading parameters.

  To create a menu type parameter, define the list of values first; then define the parameter and associate it to the list of values. See Section 4.4, "Adding Lists of Values."

• Date — the user selects a date as a parameter. Note that the data type must also be "Date" and the format must be Java date format.

Once you have defined the parameters in the data model, you can further configure how the parameters are displayed in the report as a report-level setting. For more information about the report-level settings, see the section "Configuring Parameter Settings for the Report" in Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.

Support for parameters varies based on the data set type. SQL Query data sets support the full set of available parameter features. Other types of data sets may support all, none, or a subset of these features. Table 4-1 summarizes what is supported for each data set type.

Table 4-1 Parameter Support by Data Set Type

Data Set Type	Parameter Support	Multiple Selection	Can Select All	Refresh Other Parameters on Change
SQL Query	Yes	Yes	Yes	Yes
MDX Query	No	No	No	No
Oracle BI Analysis	Inherited from Oracle BI Analysis	Yes (via Oracle BI Dashboards)	Yes (via Oracle BI Dashboards)	Yes (via Oracle BI Dashboards)
View Object	Yes, provided that the view object supports and is designed for it	Yes, using the proper syntax in the view object	Yes, using the proper syntax in the view object	Yes (view object parameters only)
Web Services	Yes	No	No	No
LDAP Query	Yes	No	No	No
XML File	Not applicable	Not applicable	Not applicable	Not applicable
Microsoft Excel File	Yes	No	No	No
HTTP (XML Feed)	Yes	No	No	No

4.2 Adding a New Parameter

To add a new parameter:

1. On the Data Model components pane, click Parameters and then click Create new Parameter, as shown in Figure 4-1.

   Figure 4-1 Create New Parameter

   
   

2. Enter a Name for the parameter. The name must match any references to this parameter in the data set.

   Note:

   The parameter name you choose must not exceed the maximum length allowed for an identifier by your database. Refer to your database documentation for identifier length limitations.

3. Select the Data Type from the list. A Date data type only support a Date Parameter Type. The other data types support a Parameter Type of either Text or Menu:

   • String

   • Integer

     Note:

     The Integer data type for parameters is a 64-bit sign integer. It has a value range of -9,223,372,036,854,775,808 to a maximum value of 9,223,372,036,854,775,807 (inclusive).

   • Boolean

   • Date

   • Float

4. Enter a Default Value for the parameter. This is recommended to prevent long running queries. Default parameter values are also used to preview the report output when you design report layouts using BI Publisher Layout Editor.

5. Select the Parameter Type. Supported types are:

   • Text — Allows the user to enter a text entry to pass as the parameter. See Section 4.2.1, "Defining a Text Parameter."

   • Menu — Presents a list of values to the user. See Section 4.2.2, "Defining a Menu Parameter."

   • Date — Passes a date parameter. The Data Type must also be Date. See Section 4.2.3, "Defining a Date Parameter."

   Note:

   BI Publisher supports parameters that are of type text entry or menu (list of values) but not both. That is, you cannot define a "combination" parameter that enables a user to either enter a text value or choose from a menu list of values.

6. Row Placement - this setting configures the number of rows for displaying the parameters and in which row to place each parameter. For example, if your report has six parameters, you can assign each parameter to a separate row, 1 - 6, with one being the top row; or, you can assign two parameters each to rows 1, 2, 3. By default, all parameters are assigned to row 1.

   Row placement can also be configured at the report level. The report definition supports additional display options for parameters. For more information, see "Configuring Parameter Settings for the Report" in the Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.

4.2.1 Defining a Text Parameter

The Text type parameter provides a text box to prompt the user to enter a text entry to pass as the parameter to the data source. Figure 4-2 shows a text parameter definition.

Figure 4-2 Text Parameter Definition

To define a Text type parameter:

1. Select Text from the Parameter Type list. The lower pane displays the appropriate fields for the selection.

2. Enter the Display Label. The display label is the label that displays to users when they view the report. For example: Department.

3. Enter the Text Field Size as an integer. This field determines the number of characters that the user can enter into the text box. For example: 25.

4. Enable the following Options if required:

   • Text field contains comma-separated values — Select this option to enable the user to enter multiple comma-delimited values for this parameter. The parameter in your data source must be defined to support multiple values.

   • Refresh other parameters on change — Performs a partial page refresh to refresh any other parameters whose values are dependent on the value of this one.

Figure 4-3 shows how the Department parameter displays to the report consumer.

Figure 4-3 Text Type Parameter as Displayed in the Report

4.2.2 Defining a Menu Parameter

A Menu type parameter presents a list of values to the user. You must define the list of values first. See Section 4.4, "Adding Lists of Values." The Menu type parameter supports the data types of String and Integer only.

The Menu parameter definition includes the options:

Figure 4-4 shows the menu parameter definition.

Figure 4-4 Menu Type Parameter Definition

To define a Menu type parameter:

1. Select Menu from the Parameter Type list. The lower pane displays the appropriate fields. Choose the Data Type (must be String or Integer).

2. Enter the Display Label. The display label is the label that displays to users when they view the report. For example: Department.

3. Enter the Number of Values to Display in List. If the number of values in the list exceeds the entry in this field, the user must click Search to find a value not displayed, as shown in Figure 4-5. This field defaults to 100.

   Figure 4-5 Search Feature Enabled When Number of Values Exceeds Setting

   
   

4. Select the List of Values that you defined for this parameter.

5. Enable the following Options if required:

   • Multiple Selection — Allows the user to select multiple entries from the list. Your data source must be able to support multiple values for the parameter. The display of a menu parameter that supports multiple selection differs. See Figure 4-6 and Figure 4-7.

   • Can select all — Inserts an "All" option in the list. When the user selects "All" from the list of values, you have the option of passing a null value for the parameter or all list values. Choose NULL Value Passed or All Values Passed.

     Note:

     Using * passes a null, so you must handle the null in your data source. A method to handle the null would be the standard Oracle NVL command, for example:

     where customer_id = nvl(:cstid, customer_id)

     where cstid is a value passed from the LOV and when the user selects All it passes a null value.

   • Refresh other parameters on change — Performs a partial page refresh to refresh any other parameters whose values are dependent on the value of this one.

Figure 4-6 shows how the Department menu type parameter displays to the report consumer when multiple selection is not enabled.

Figure 4-6 Department Menu Type Parameter with Multiple Selection Disabled

Figure 4-7 shows how the Department menu type parameter displays to the report consumer when multiple selection is enabled.

Figure 4-7 Department Menu Type Parameter with Multiple Selection Enabled

4.2.2.1 Customizing the Display of Menu Parameters

The display of menu parameters in the report can be further customized in the report definition. Menu type parameters support the additional display option as a static list of checkboxes or radio buttons. For more information, see "Configuring Parameter Settings for the Report" in the Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.

4.2.3 Defining a Date Parameter

The Date type parameter provides a date picker to prompt the user to enter a date to pass as the parameter to the data source. Figure 4-8 shows the date parameter definition.

Figure 4-8 Date Parameter Definition

To define a Date type parameter:

1. Select Date from the Parameter Type list. The lower pane displays the appropriate fields for your selection.

2. Enter the Display Label. The display label is the label that displays to users when they view the report. For example: Hire Date.

3. Enter the Text Field Size as an integer. This field determines the number of characters that the user can enter into the text box for the date entry. For example: 10.

4. Enter the Date Format String. The format must be a Java date format (for example, MM-dd-yyyy).

5. Optionally, enter a Date From and Date To. The dates entered here define the date range that are presented to the user by the date picker. For example if you enter the Date From as 01-01-1990, the date picker does not allow the user to select a date before 01-01-1990. Leave the Date To blank to enable all future dates.

Figure 4-9 shows how the Hire Date parameter displays to the report consumer.

Figure 4-9 Hire Date Parameter

4.3 About Lists of Values

A list of values is a defined set of values that a report consumer can select from to pass a parameter value to your data source. If you define a menu type parameter, the list of values that you define here provides the menu of choices. You must define the list of values before you define the menu parameter.

Populate the list using one of the following methods:

• Fixed Data — Manually enter the list of values.

• SQL Query — Retrieve the values from a database using a SQL query.

4.4 Adding Lists of Values

To add a List of Values:

1. On the Data Model components pane, click List of Values and then click Create new List of Values, as shown in Figure 4-10.

   Figure 4-10 Create New List of Values

   
   

2. Enter a Name for the list and select a Type: SQL Query or Fixed Data.

4.4.1 Creating a List from a SQL Query

The data engine expects a (display) name-value pair from the list of values query. In the list of values select statement, the column listed first is used as the display name (what is shown to the user) and the second is used for the value that is passed to the parameter in the data set query by the data engine.

If the query returns only one column, then the same column value is used both as the list of values display name shown to the user and as the value that is passed to the parameter.

To create a list from a SQL query:

1. Select a Data Source from the list.

2. In the lower pane, select Cache Result (recommended) if you want the results of the query cached for the report session.

3. Enter the SQL query or use the Query Builder. See Section 2.4, "Using the Query Builder" for information on the Query Builder utility. Figure 4-11 shows a SQL query type list of values.

Figure 4-11 SQL Query Type List of Values

The SQL query shown in Figure 4-11 selects only the DEPARTMENT_NAME column from the DEPARTMENTS table. In this case the list of values both displays the results of the query in the list and passes the same value to the parameter in the data set. Figure 4-12 shows the list of values display entries and the values passed to the data set. Note that the menu items and the values shown for P_DEPT are the DEPARTMENT_NAME values.

Figure 4-12 Sample Data Showing the Same LOV Display Names and Values

If instead you wanted to pass the DEPARTMENT_ID to the parameter in the data set yet still display the DEPARTMENT_NAME in the list, construct your SQL query as follows:

select     "DEPARTMENTS"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
         "DEPARTMENTS"."DEPARTMENT_ID" as "DEPARTMENT_ID"
 from    "DEMO"."DEPARTMENTS" "DEPARTMENTS

Figure 4-13 shows the list of values display entries and the values passed to the data set. Note that the menu lists the DEPARTMENT_NAME while the values shown for P_DEPT are the DEPARTMENT_ID values.

Figure 4-13 LOV Display Names and Values

4.4.2 Creating a List from a Fixed Data Set

To create a list from a fixed data set:

1. In the lower pane, click the Create new List of Values icon to add a Label and Value pair. The label is displayed to the user in the list. The value is passed to the data engine.

2. Repeat for each label-value pair required.

Figure 4-14 shows fixed data type list of values.

Figure 4-14 Fixed Data Type List of Values