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 a New Parameter

Add a New Parameter

Create a 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.

When you design report layouts using the Layout Editor, the preview of the report output uses the default values of the parameters.

You can configure row placement at the report level. The report definition supports additional display options for parameters.

To add a new parameter:

  1. On the Data Model components pane, click Parameters and then click Create new Parameter.
  2. Enter a Name for the parameter.
    The name must match any references to this parameter in the dataset.
  3. Select the Data Type from the list. A Date data type only supports a Date Parameter Type.
  4. Enter a Default Value for the parameter. This is recommended to prevent long running queries.
  5. Select the Parameter Type.
  6. To mark the parameter as mandatory, select Mandatory.
    Without providing values for the mandatory parameters, you can't test a report using the View Data option, or run the report online, or schedule the report.
  7. In the Row Placement setting configure the number of rows for displaying the parameters and in which row to place each parameter.

    For example, if your report has six parameters, you can assign each parameter to a separate row, 1 - 6, with one being the top row; or, you can assign two parameters each to rows 1, 2, 3. By default, all parameters are assigned to row 1.

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

To create a text parameter:
  1. Select Text from the Parameter Type list.
  2. Enter the Display Label. For example, Department.
  3. Enter the Text Field Size as an integer. This field determines the size (width) of the field, but doesn't limit the number of characters that the user can enter into the text box.
  4. Enable the following Options if required:

    • Text field contains comma-separated values - Enables the user to enter multiple comma-delimited 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.

Create a Menu Parameter

A Menu type parameter presents a list of values to the user.

You must define the list of values first. The Menu type parameter supports the data types of String and Integer only. If the number of values in the list exceeds 999, then use a Search parameter instead of a Menu type parameter.

To create a menu parameter:

  1. Select Menu from the Parameter Type list. The lower pane displays the appropriate fields.
  2. In Data Type, select String or Integer.
  3. Enter the Display Label. The display label is the label that displays to users when they view the report. For example: Department.
  4. Select the List of Values that you defined for this parameter.
  5. Enter the Number of Values to Display in List. If the number of values in the list exceeds the entry in this field, the user must click Search to find a value not displayed, as shown in the figure below. This field defaults to 100.
  6. 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 differs. See the two figures below.
    • Can select all - Inserts an All option in the list.

      When the user selects All from the list of values, you can pass a null value for the parameter.

      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 list of values, and when the user selects All it passes 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 figure below shows how the Department menu type parameter displays to the report consumer when multiple selection isn't enabled.

Description of xdo11g_dm_parm_menu2a.gif follows
Description of the illustration xdo11g_dm_parm_menu2a.gif

The figure below shows how the Department menu type parameter displays to the report consumer when multiple selection is enabled.

Description of xdo11g_dm_parm_menu_mult1.gif follows
Description of the illustration xdo11g_dm_parm_menu_mult1.gif

Customize the Display of Menu Parameters

The display of menu parameters in the report can be further customized in the report definition.

Menu type parameters support the additional display option as a static list of checkboxes or radio buttons.

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

  1. Select Date from the Parameter Type list. The lower pane displays the appropriate fields for your selection.
  2. Enter the Display Label. The display label is the label that displays 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 can enter into the text box for the date entry. For example: 10.
  4. Optional: Select Ignore User Timezone if you want to display the date parameter value in UTC.
  5. Enter the Date Format String. The format must be a Java date format, for example, MM-dd-yyyy.
    To avoid the UTC time zone setting in the server and retain the user preference time zone, add Z to the date format. For example, MM-dd-yyyyZ.
  6. Optional: Enter a Date From and Date To. The dates entered here define the date range that are presented to the user by the date picker. For example if you enter the Date From as 01-01-1990, the date picker doesn't allow the user to select a date before 01-01-1990. Leave the Date To blank to enable all future dates.

The figure shows how the Hire Date parameter displays to the report consumer.


Description of xdo11g_dm_parm_date2a.gif follows
Description of the illustration xdo11g_dm_parm_date2a.gif

Create a Point of View Parameter (POV)

The POV (Point of View) type parameter provides you a multidimensional view.

You can directly specify a POV parameter in an MDX query and create POV parameter, or you can create a POV parameter first and then update an MDX query to include the POV parameter in the WHERE clause. Before you create a POV type parameter in a data model, define the list of values for the POV parameter.

To create a POV parameter:
  1. On the Data Model components pane, click Parameters.
  2. Click Create New Parameter.
  3. Enter a name for the parameter in the Name field.
  4. Select POV from the Parameter Type list.
  5. From the Data Type list, select Text.
  6. Enter a default value for the parameter in the Default Value field.
  7. In the Row Placement setting, configure the number of rows for displaying the parameters.
  8. Use the Reorder column to specify the order for the parameter.
  9. Enter a label in the Display Label field.
  10. Select a LOV for the POV parameter from the List of Values list.

Include a POV Parameter Value in an MDX Query

You can search a dimension in a cube and include a POV parameter value in an MDX query.

If you include a POV parameter in an MDX query of a data model, and if that POV parameter doesn’t exist in the data model, Publisher creates that POV parameter in the data model when you save the query.

To include a POV parameter value in an MDX query:

  1. On the component pane of the data model editor, click Datasets.
  2. Click the MDX Query dataset that you want to edit.
  3. Click Edit Selected Dataset.
  4. In the WHERE clause of the MDX query, enter the name of the POV parameter enclosed in ${ }.
    For example, if you want to create a POV parameter with name as P_Year or if the P_Year POV parameter exists in the data model, specify the condition as WHERE ([Year].[${P_Year}]
  5. In the parameter value binding dialog, click the search icon next to the parameter.
  6. In the select dimension dialog, select the cube, dimension, and value for the parameter.
  7. Click Select.
  8. Click OK.
  9. Save the data model.

Create a Search Parameter

You can use the Search type parameter to provide a box for entering search text and a search icon to search and list the values that match the search so that users can select.

Use the Search type parameter to find a value within a long list of values. You must create a LOV for the parameter before you define the Search type parameter.

To create a Search type parameter:

  1. On the Data Model components pane, click Parameters, and then click Create new Parameter.
  2. Enter a name for the parameter, select String from the Data Type list, and enter a default value for the parameter.
  3. Select Search from the Parameter Type list.
  4. Enter a label for the parameter in the Display Label field.
  5. Select the LOV for the parameter from the List of Values list.
  6. Optional: Select Refresh other parameters on change.