Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher Release 11g (11.1.1) Part Number E18862-01

Contents

Previous

Next

Adding Parameters and Lists of Values

This chapter covers the following topics:

• About Parameters
• Adding a New Parameter
• About Lists of Values
• Adding Lists of Values

About Parameters

Adding parameters to your data model enables users to interact with data when they submit or view reports.

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 Configuring Parameter Settings for the Report, Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.

BI Publisher supports the following parameter types:

• Text - enter a text entry to pass as the parameter.

• Menu - pass parameters by making selections from a list of values. This option supports multiple selections, a "Select All" option, and partial page refresh for cascading parameters. Define the properties for the list of values in the report definition. A list of values can contain fixed data that you specify or the list can be created via a SQL query executed against any of the defined data sources.

  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 Adding Lists of Values.

  If the menu list is very long, consider using the Search type parameter.

• Date - enter a date as a parameter. Note that the data type must also be "Date" and the format must be Java date format.

• Search - use the search parameter type to enable your users to search for the parameter value from a defined list. Define the list of values first; then define the parameter and associate it to the list of values. See Adding Lists of Values.

Adding a New Parameter

On the Data Model components pane, click Parameters and then click Create new Parameter, as shown in the following figure:

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

   Tip: When defining a parameter name, ensure you are aware of your database limitations for the maximum length of an identifier.

2. Select the Data Type from the list:

   (String, Integer, Boolean, Date, or Float).

   • 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

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

4. Select the Parameter Type. Supported types are:

   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.

   • Text - allows the user to enter a text entry to pass as the parameter. See Defining a Text Parameter.

   • Menu - presents a list of values to the user. See Defining a Menu Parameter.

   • Date - passes a date parameter. See Defining a Date Parameter.

   • Search - enables the user to enter a string to search for a value from a defined list of values. See Defining a Search Parameter.

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. The following figure shows a text parameter definition:

To define a Text type parameter:

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

2. Enter the Display Label. The display label is the label that will display 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 will be able to 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-separated 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.

The following figure shows how the Department parameter will display to the user in the report:

Defining a Menu Parameter

A Menu type parameter presents a list of values to the user to from which to select a value or values to pass to the data source. You must define the list of values first. See Adding Lists of Values. The following figure shows the menu parameter definition:

To define a Menu type parameter:

1. Select Menu from the Parameter Type list. The lower pane will display the appropriate fields for your selection.

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

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

4. 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 will differ. See the following figures.

   • 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 will pass 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.

The following figure shows how the Department menu type parameter will display to the user in the report when multiple selection is not enabled:

The following figure shows how the Department menu type parameter will display to the user in the report when multiple selection is 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. The following figure shows the date parameter definition:

To define a Date type parameter:

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

2. Enter the Display Label. The display label is the label that will display 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 will be able to 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 will be presented to the user by the date picker. For example if you enter the Date From as 01-01-1990, the date picker will not allow the user to select a date before 01-01-1990. Leave the Date To blank if you wish to enable all future dates.

The following figure shows how the Hire Date parameter will display to the user in the report:

Defining a Search Parameter

A Search parameter enables the user to enter a string to search for a value from a defined list of values. You must define the list of values first. See Adding Lists of Values. The following figure shows the search parameter definition:

To define a Search type parameter:

1. Select Search from the Parameter Type list. The lower pane will display the appropriate fields for your selection.

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

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

The search parameter will display as a search box. The following figure shows how the Department search type parameter will display to the user in the report:

When the user clicks the Search icon, a Search dialog launches. The user can then enter a search string. The % can be used as a wild card. The search will return results when the value matches the initial string. Note that the Search feature is case sensitive.

For example, in the preceding figure the entry "Ma" returns the values "Marketing" and "Manufacturing". The search entry "%ing" would return "Accounting", "Manufacturing", and "Purchasing".

The user can then select the value from the returned list and click OK.

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 or search 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 or search 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.

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 the following figure:

   

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

Creating 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 Query Builder for information on the Query Builder utility. The following figure shows a SQL query type list of values:

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

2. Repeat for each label-value pair required.

The following figure shows fixed data type list of values:

Copyright © 2010, 2011, Oracle and/or its affiliates. All rights reserved.