Adding Parameters and Lists of Values

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 15-2 shows a text parameter definition.

Figure 15-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 15-3 shows how the Department parameter displays to the report consumer.

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

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 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 15-4 shows the menu parameter definition.

Figure 15-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 15-5. This field defaults to 100.

   Figure 15-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 15-6 and Figure 15-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 15-6 shows how the Department menu type parameter displays to the report consumer when multiple selection is not enabled.

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

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

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

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 15-8 shows the date parameter definition.

Figure 15-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 15-9 shows how the Hire Date parameter displays to the report consumer.

Figure 15-9 Hire Date Parameter

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 Using the SQL Query Builder for information on the Query Builder utility. Figure 15-11 shows a SQL query type list of values.

Figure 15-11 SQL Query Type List of Values

The SQL query shown in Figure 15-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 15-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 15-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 15-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 15-13 LOV Display Names and Values

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 15-14 shows fixed data type list of values.

Figure 15-14 Fixed Data Type List of Values

Prerequisites for Using Flexfields

To enable the flexfield type list of values, BI Publisher must be configured to use E-Business Suite Security. The flexfield must already be defined in the E-Business Suite.

For information about flexfields in the E-Business Suite, see Oracle E-Business Suite Flexfields Guide. For information about setting up E-Business Suite security for BI Publisher, see "Integrating with Oracle E-Business Suite" in Oracle Fusion Middleware Administrator's Guide for Oracle Business Intelligence Publisher.

Adding a Flexfield Parameter and List of Values

To add a flexfield parameter complete the following tasks. Each task is described in detail in the subsequent sections:

1. Add the flexfield list of values. The flexfield type list of values retrieves the flexfield metadata definition to present the appropriate values for each segment in the flexfield list of values selection dialog.

2. Add the menu parameter. Select your flexfield list of values as the source menu for the parameter. The parameter captures the values selected to pass to the Flexfield component.

3. Add the Flexfield component to the data model. Pass the parameter values to the Flexfield.

4. Reference the Flexfield in your SQL query using the &flexfield_name syntax. At runtime the &flexfield_name reference is replaced with the lexical code constructed based on the values in the Flexfield component definition.

Adding the Flexfield Component to the Data Model

To pass the parameter values to the SQL query, define a Flexfield lexcial component. To define the Flexfield component:

1. On the Data Model components pane, click Flexfields and then click Create new Flexfield.

2. Enter the following:

   • Name — Enter a name for the flexfield component.

   • Type — Select the flexfield type from the list. The type you select here determines the additional fields required. See Entering Flexfield Details.

   • Application Short Name — Enter the short name of the Oracle Application that owns this flexfield (for example, GL).

   • ID Flex Code — Enter the flexfield code defined for this flexfield in the Register Key Flexfield form (for example, GL#).

   • ID Flex Number — Enter the name of the source column or parameter that contains the flexfield structure information. For example: 101. To use a parameter, prefix the parameter name with a colon, for example: :PARAM_STRUCT_NUM.

3. In the lower region of the page, enter the details for the type of flexfield you selected. For the field that is to take the parameter value, enter the parameter name prefixed with a colon, for example, :P_FLEX_LOW.

   In Figure 15-17 the Flexfield component is defined as a where Type. The parameters :P_FLEX_LOW and :P_FLEX_HIGH are entered in the Operand1 and Operand2 fields. At runtime, values selected by the user for the parameters P_FLEX_LOW and P_FLEX_HIGH will be used to create the where clause.

   Figure 15-17 Example Flexfield Component Using Flexfield Parameters