Adding Flexfield Parameters
Oracle E-Business Suite customers who have configured BI Publisher to use E-Business Suite security can create reports that leverage key flexfields as parameters.
When you define a data model to pass a key flexfield as a parameter, BI Publisher presents a dialog to the report consumer to make selections for the flexfield segments to pass as parameters to the report, similar to the way flexfields are presented when running reports through the concurrent manager in the E-Business Suite.
The flexfield list of values displays in the report viewer as shown below.
The flexfield list of values displays as a dialog from which you select the segment values, as shown below.
Prerequisites for Using Flexfields
When defining a list of values, E-Business Suite customers see a list Type called "Flexfield".
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 Administrator's Guide for Oracle Business Intelligence Publisher.
Adding a Flexfield Parameter and List of Values
Add flexfield parameters by adding the 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. Use the flexfield parameter to pass values to the Flexfield defined in the Data Model.
At runtime the &flexfield_name reference is replaced with the lexical code constructed based on the values in the Flexfield component definition.
- Add the flexfield list of values (LOV).
- Add a parameter and associate it with the flexfield LOV by selecting your flexfield list of values as the source menu for the parameter.
- Add the Flexfield component to the data model.
- Reference the Flexfield in your SQL query using the
&flexfield_namesyntax.
Adding the Flexfield List of Values
Add a list of values retrieved from a flexfield definition.
When you choose Flexfields as the Type, the Data Source option is no longer editable. All flexfields type lists of values use the Oracle E-Business Suite as the data source.
- In the Flex_Acct_List:Flexfields pane, enter the following:
-
Application Short Name - E-Business Suite application short name, for example: SQLGL.
-
ID Flex Code - Flexfield code defined for this flexfield in the Register Key Flexfield form, for example: GL#.
-
ID Flex Number - Name of the source column or parameter that contains the flexfield structure information, for example: 101 or :STRUCT_NUM. If you use a parameter, ensure that you define the parameter in the data model.
The image shows a sample flexfield type, LOV.
-
Adding the Menu Parameter for the Flexfield List of Values
Define the parameter to display the flexfield list of values and capture the values selected by the user.
The Flexfield type parameter definition includes an additional field called Range to support range flexfields. A range flexfield supports low and high values for each key segment rather than just single values. You can customize the default value of the flexfield and row placement in the report definition. The row placement determines where this parameter appears in the report viewer. To pass a range of flexfield segment values, see Passing a Range of Values
The following options are disabled for flexfield parameters: Number of Values to Display in List, Multiple Selection, Can select all, and Refresh other parameters on change.
The image shows a parameter definition for the flexfield list of values.
Using the Flexfield Parameter to Pass Values to a Flexfield Defined in the Data Model
After adding the Menu parameter to the flexfield list of values, you can pass the parameter values to a flexfield component in the data model.
Adding Flexfields covers adding a flexfield component in detail. The simplified procedure is provided here to complete the example.
To define the Flexfield in the data model:- 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_Acct_List.
In the figure below the Flexfield component is defined as a "Where" Type. The parameter :P_Acct_List is entered in the Operand1 field. At runtime, values selected by the user for the parameter P_Acct_List will be used to create the where clause.
Referencing the Flexfield in the SQL Query
Finally, create the SQL query against the E-Business Suite database.
Use the lexical syntax in the query as described in Adding Key Flexfields. In the figure below &Acct_Flex is the Flexfield lexical called in the where condition of the SQL query.
Passing a Range of Values
To define the parameters for the flexfield lists of values when you want to pass a range of values you create two menu parameters that both reference the same flexfield LOV.
At runtime users choose a high value from the list of values and a low value from the same list of values. These two values are then passed as operands to the flexfield component of the data model.
- Create the low range parameter following the steps in Adding the Menu Parameter for the Flexfield List of Values. For the Range field, select Low to designate this parameter as the low value. Both parameters reference the flexfield list of values that you created in Step 1. The figure below shows creating the parameters to define the range.
- Create the Flexfield in the data model, as described in Using the Flexfield Parameter to Pass Values to a Flexfield Defined in the Data Model.
In the lower region of the page, enter the details for the type of flexfield you selected. Enter the parameter prefixed with a colon for example, :P_Acct_List.
In the figure below 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.
When the report associated with this data model is displayed in the report viewer, the report consumer sees the two flexfield parameters as shown below.
When the report consumer clicks either the high or low flexfield indicator (...), a dialog launches enabling input of both the high and low values as shown below.
The display characteristics in the report viewer of the range flexfield parameter resemble closely the presentation of range flexfields in the E-Business Suite.