1. Designing Pixel-Perfect Reports in Oracle Analytics Cloud
  2. Build Data Models for Pixel-Perfect Reports
  3. Add Parameters and Lists of Values
  4. Add Flexfield Parameters

Add Flexfield Parameters

Oracle E-Business Suite customers who have configured 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, 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.

Description of flex_lov_disp1a.gif follows
Description of the illustration flex_lov_disp1a.gif

The flexfield list of values displays as a dialog from which you select the segment values, as shown below.


Description of flex_lov_disp.gif follows
Description of the illustration flex_lov_disp.gif

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, configure to use E-Business Suite Security. The flexfield must already be defined in the E-Business Suite.

Add 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.

  1. Add the flexfield list of values (LOV).
  2. Add a parameter and associate it with the flexfield LOV by selecting your flexfield list of values as the source menu for the parameter.
  3. Add the Flexfield component to the data model.
  4. Reference the Flexfield in your SQL query using the &flexfield_name syntax.

Add 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.

  1. On the Data Model components pane, click List of Values and then click Create new List of Values.
  2. Enter a Name for the list and choose Flexfields as the Type.
  3. In the Flex_Acct_List: Type: 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.


    Description of flex_parm_deff.gif follows
    Description of the illustration flex_parm_deff.gif

Add 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.

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.

  1. On the Data Model components pane, click Parameters and then click Create new Parameter.
  2. Select Menu from the Parameter Type list.
  3. Choose String or Integer as the Data Type.
  4. Enter a Default Value for the flexfield parameter.
  5. Enter the Row Placement.
  6. Enter the Display Label. The display label is the label that displays to users when they view the report. For example: Account From.
  7. Select the List of Values that you defined for this parameter.
    When you select a list of values that is the Flexfield type, an additional field labeled Range displays.

The image shows a parameter definition for the flexfield list of values.


Description of flex_parm_def_parmf.gif follows
Description of the illustration flex_parm_def_parmf.gif

Use 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.

To define the Flexfield in the data model:
  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.

    • Application Short Name — Enter the short name of the Fusion Applications Suite 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_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.


    Description of flex_parm_flexcc.gif follows
    Description of the illustration flex_parm_flexcc.gif

Reference the Flexfield in the SQL Query

Finally, create the SQL query against the E-Business Suite database.

Use the lexical syntax in the SQL query. In the figure below &Acct_Flex is the Flexfield lexical called in the where condition of the SQL query.


Description of flex_in_sql_sing.gif follows
Description of the illustration flex_in_sql_sing.gif

Pass 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.

  1. Create one flexfield LOV.
  2. Create the high range parameter. For the Range field, select High to designate this parameter as the high value.
  3. Create the low range parameter. 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.
  4. Create the Flexfield 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.


    Description of flex_parm_def_ff.gif follows
    Description of the illustration flex_parm_def_ff.gif

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.


Description of flex_range_disp.gif follows
Description of the illustration flex_range_disp.gif

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.


Description of flex_range_disp_all.gif follows
Description of the illustration flex_range_disp_all.gif

The display characteristics in the report viewer of the range flexfield parameter resemble closely the presentation of range flexfields in the E-Business Suite.