Adding Parameters and Lists of Values

This chapter covers the following topics:

About Parameters
Adding Parameters
Adding Lists of Values

About Parameters

Adding parameters to your data model enables users to interact with data when they 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.

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 add a parameter as a menu, define the list of values first; then define the parameter and associate it to the list of values. See Adding Lists of Values.
Date - enter a date as a parameter. Note that the data type must also be "Date" and the format must be Java date format.

Adding Parameters

Click Parameters and then click New.

Enter a name Identifier and the Data Type (String, Integer, Boolean, Date, or Float).

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).
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.
Select the Parameter Type:

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 - this type allows the user to enter a text entry to pass as the parameter. Enter the Display Label for the parameter and the Text Field Size in characters. You may also enable the following options:
  - Text field contains comma-separated values - select this option to enable the user to enter multiple comma-separated values for this parameter.
  - 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.
- Menu - this type presents a list of values to the user. You must define the list of values first. See Adding Lists of Values. Enter the Display Label and select from the lists you defined in the previous step. You may also enable the following options:
  - Multiple Selection - allows the user to select multiple entries from the list.
  - 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.
- Date - passes a date parameter. If you select a Parameter Type of Date, the Data Type automatically defaults to Date. Enter the following:
  - Display Label and Text Field Size in as a number of characters.
  - Date Format String - enter a format string for the date. It must be a Java date format (for example, MM-dd-yyyy).
  - Date From and Date To - enter the to and from dates to which you want to restrict the calendar date picker.

Adding Lists of Values

Click List of Values and then click the Create New List of Values icon. This will create a new entry in the table.
Enter a Name for the list and select a Type: SQL Query or Fixed Data.

If you select SQL Query:
- Select a Data Source from the list.
- In the lower pane, select Cache Result (recommended) if you want the results of the query cached for the report session.
- Enter the SQL query or use the Query Builder. See Using the Query Builder for information on the Query Builder utility.
If you select Fixed Data:
- Click the Create New icon in the lower pane to add a Label and Value pair for the LOV.
- Repeat for each label-value pair required.