Adding parameters to a data model enables users to interact with data when they view reports.
Oracle BI Publisher supports the following parameter types:
Text - Enables entering a text string to pass as the parameter.
Menu - Enables making selections from a list of values. A list of values can contain fixed data that you specify or a list created using a SQL query that is executed against any of the defined data sources. This option supports multiple selection, a Select All option, and partial page refresh for cascading parameters.
To create a menu type parameter, define the list of values, and then define the parameter and associate it to the list of values. See Adding Lists of Values.
Date - Enables the user to select a date as a parameter. You must use the data type Date and the Java date format.
After defining the parameters in the data model, you can configure how the parameters are displayed in the report as a report-level setting.
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 might support all, none, or a subset of these features. The table below summarizes the support for each 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 (using Oracle BI Dashboards) |
Yes (using Oracle BI Dashboards) |
Yes (using Oracle BI Dashboards) |
View Object |
Yes, provided that the view object supports and is designed for it |
No |
No |
Yes (view object parameters only) |
Web Service |
Yes |
No |
No |
No |
LDAP Query |
Yes |
No |
No |
No |
XML File |
No |
No |
No |
No |
Microsoft Excel File |
Yes |
No |
No |
No |
CSV File |
No |
No |
No |
No |
HTTP (XML Feed) |
Yes |
No |
No |
No |
Create a new parameter by assigning it a name and other properties.
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.
Supported types are:
Text - Allows the user to enter a text entry to pass as the parameter. See Creating a Text Parameter.
Menu - Presents a list of values to the user. See Creating a Menu Parameter.
Date - Passes a date parameter. The Data Type must also be Date. See Define a Date Parameter.
Default parameter values are also used to preview the report output when you design report layouts using Oracle BI Publisher Layout Editor.
BI Publisher supports parameters that are of type text entry or menu (list of values) but not both. You cannot define a combination parameter that enables a user to enter a text value or choose from a menu list of values.
You can configure row placement at the report level. The report definition supports additional display options for parameters.
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.
The figure below shows a text parameter definition.
The figure below shows how the Department parameter displays to the report consumer.
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 various options, as shown in the figure below.
The figure below shows how the Department menu type parameter displays to the report consumer when multiple selection is not enabled.
The figure below shows how the Department menu type parameter displays to the report consumer when multiple selection is enabled.
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.
The figure shows how the Hire Date parameter displays to the report consumer.
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 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
Retrieves the values from a database using a SQL query.
Flexfield
Retrieves the values from a key flexfield defined in Oracle E-Business Suite. The flexfield option is only available when Oracle BI Publisher is using the Oracle E-Business Suite security model, see Adding Flexfield Parameters.
You can create lists of SQL Query or Fixed Data values .
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 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.
The SQL query shown below 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. The figure below shows the list of values display entries and the values passed to the data set. The menu items and the values shown for P_DEPT are the DEPARTMENT_NAME values.
If instead you wanted to pass the DEPARTMENT_ID to the parameter in the data set, and 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
The figure below shows the list of values display entries and the values passed to the data set. The menu lists the DEPARTMENT_NAME while the values shown for P_DEPT are the DEPARTMENT_ID values.
Create a list from a fixed data set for each label-value pair required.
When you create a label-value pair, the label is displayed to the user in the list. The value is passed to the data engine.
The figure below shows fixed data type list of values.
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.
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.
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.
&flexfield_name
syntax.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.
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.
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: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.
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.
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.