New Prompt dialog

Use this dialog to define the column, variable, or currency prompt.

For more information, see "Creating a Column Prompt".

This dialog is also displayed as:

Edit Prompt
Edit Prompt: Column dialog
New Prompt: Column dialog

Components Common to Column, Currency, and Variables Prompts

Label

Use this field to enter an appropriate label for the prompt. For example, "Select a date range," "Select a currency," or "Select a percentage by which to calculate the region's dollars." You can include HTML markup tags in the caption, such as <b>, <font>, and <table>.

Description

Use this field to enter a short description of the prompt. This description is displayed as tool tip text, which is displayed when the user mouses over the prompt's label in the dashboard or analysis.

Additional Components for Column and Variable Prompts

Prompt for Column

This field displays for column prompts, only.

Use this field to view information about the column that you selected. You can edit the formula that displays in this field by clicking the Edit Formula button. For more information, see "Edit Column Formula dialog".

Edit Formula

This field displays for column prompts, only.

This button displays for attribute and measure column types, but not for hierarchical columns; however, if you are creating a column or variable prompt and choose to modify the column's formula, you can specify a hierarchical column in the column's formula.

Use this button to display the "Edit Column Formula dialog" where you can modify the column's formula for use with the prompt.

If you added a SQL case statement to the analysis' column instance and you want to use that case statement within the prompt, then you must specify the same SQL case statement in the prompt's column formula.

For example, on the analysis' column, you can use the Edit Formula functionality to create a "Beverage Type" bin to group a long list of beverages into two categories called "Carbonated Beverages" and "Fruit Juices and Non-Carbonated Beverages." When building the prompt, you then add the same bin or SQL case statement to the prompt's column formula. When the prompt is completed and previewed, the "Carbonated Beverages" and "Fruit Juices and Non-Carbonated Beverages" items are displayed in the prompt's value list.

Data Time Zone

Use this field to set the time zone.

Prompt for

This field displays for variable prompts, only.

Use this field to select the type of variable to create and to type a name for the variable. This variable name is the name that you add to the analysis or dashboard where you want the variable prompt's value specified by the user to display. Currently, you can create only presentation variables.

Included Code Column

This field displays for display columns, only.

Displays the name of code column that is mapped to the display column.

For more information on display and code columns, see "Understanding the Double Column Feature".

Operator

This field displays for column prompts, only.

Use this list to select the prompt's operator. For more information about each operator, see "Operators".

Note the following items:

• If you want the user to select the operator at runtime, then select the *Prompt User operator.

• If you are creating a dashboard prompt for an Oracle Business Intelligence Publisher report that receives its data from an Oracle BI Server or SQL Server, then you must set the operator to is equal to/is in.

• If you are creating a prompt that includes a group in the prompt's value selection list, the Operator must be set to either is equal to/is in or is not equal to/is not in. For more information about groups, see "Working with Groups and Calculated Items".

• If the repository is configured for double columns, and you are creating the prompt on a display column and select the contains all, contains any, does not contain, begins with, ends with, is LIKE (pattern match), or is not LIKE (pattern match) operator, then filtering is always done by display values, not code values. For all other operators, filtering is done by code values. For more information on double columns, see "Understanding the Double Column Feature".

User Input

This field displays for column and variable prompts.

Use this list to specify the user interface component where the user selects the prompt values (for example, prompt the user with radio buttons to select only one prompt value or prompt the user with a text box that requires the user to enter one or more specific prompt values). The input option that you select determines the fields that are displayed in the dialog. For a description and example of each user input option type, see "What Types of User Input Options Can I Use With a Column or Variable Prompt?"

User Input Values

This field displays for column and variable prompts.

Use this field to specify which data values you want to display in the user input method that you selected. For example, if you selected the Choice List user input method and the All Column Values option, then the user selects the prompt's data value from a list that contains all of the data values contained in the data source.

Note that for attribute columns and measures column, all choice list values type are available.

The User Input Values options are:

• All Column Values – Indicates that users can choose from a list that contains all the existing values or members.

  If the number of column values exceed the number that is specified in the Page Values box, the list that displays in the view contains a Search link that the user clicks to access the Select Values dialog. Users can use the search to locate more values.

• Custom Values – Indicates that users can select from a list of prompt values that you created rather than the values supplied by the column.

• Specific Column Values – For attribute columns and measures column, this option indicates that users can choose from a list that displays the values that you have selected for the column. This option is useful when prompting for multiple columns, or when the results contain a filter. It prevents the user from choosing values that would result in no data. For example, suppose an analysis contains a country column and a city column, and contains the filter Country=France. By selecting limited values for the city column prompt, the choices are constrained to cities in France only, which prevents the possibility of the user choosing a city that is not located in France, such as Country=France and City=Rome.

  This list can also contain a group as a selection option. Users can also click Search from the list of values to search for a value that does not display in the list of values. You can click the Plus button to display the Select Values dialog where you can search for and select the desired values to display to users. In the Column Values pane, you see all values for the column and you can select only those that you want users to choose from. If you are using either the is equal to/is in or the is not equal to/is not in operator, then you can expand the Catalog pane to browse and add a saved group. When you select a group, it is presented as one value in the list of values. For example, if the group is named Sales by Region and contains the Eastern and Southern members, within the prompt's list of values you see Sales by Region and when you select this value, the analysis contains the Eastern and Southern members.

  For hierarchical columns, this choice indicates that a list is displayed from which users can choose among the members that you have selected for the column. This list can also contain a group as a selection option. See the above paragraph for more information.

• SQL Results – Indicates that users can choose from a list that contains the values that you have specified through a SQL statement. A box is displayed where you can enter a SQL statement that creates the list of values from which users can choose. You likely want to enter a WHERE clause as part of the SQL statement to limit the number of values from which users can select and include sorting commands to have the values display in a logical order.

  If the SQL statement returns a list of values that exceeds the number that is specified in the Page Values box, then a Search link is displayed and when the user clicks this link, the Select Values dialog is displayed. Users cannot search in the Select Values dialog, but they can display more values to select from.

• Members of Groups – This option is not available for hierarchical columns.

  Indicates that users can choose from a list that contains all members included in a specified group or groups. For example, if the group you select is named Sales by Region and contains the Eastern and Southern members, at runtime you see Eastern and Southern regions listed in the prompt's list of values.

• All Column Values and Specific Groups – This option is not available for hierarchical columns. This option is available for the is equal to/is in or the is not equal to/is not in operators, only.

  Indicates that users can choose from a list that contains all column values and the groups that you have selected. When you select a group, it is presented as one value in the list of values. For example, if the group is named Sales by Region and contains the Eastern and Southern members, within the prompt's list of values you see Sales by Region and when you select this value, the analysis contains the Eastern and Southern members.

  
  

  Note: If you have a prompt with a choice list value that contains a group and the prompt is then used to override a Keep only or Remove selection step, then Oracle BI EE ignores the group at runtime. If the override selection step is an Add selection step, then Oracle BI EE uses the group.

  
  

Select Values or Remove selected value buttons

These buttons display for column and variable prompts. These buttons display if you selected Custom Values, Specific Column Values, Members of Groups, or All Column Values and Specific Groups in the User Input Values field.

Use the Select Values button to open the "Select Values dialog" to search for and add values or groups to the Select List. The prompt presents this values list to the user. This functionality is useful when you want to limit the number of user choices to a small, intuitive list. For example, if you chose Radio Buttons as the user input method and created a specific values list of five dates, the prompt presents the user with five radio buttons, one for each date. If you selected Custom Values in the User Input Values field, the Enter Value dialog displays rather than the Select Values dialog, where you enter a custom value.

Use the Remove selected value button to remove the selected value.

SQL Statement

This field displays if you selected SQL Results in the User Input Values field.

Use this field to create or edit a SQL statement. When run, the SQL statement produces a group of values that is displayed in the Select List.

Include "All Choices" choice in the list

This box displays if you selected either the Choice List, List Box, or Radio Buttons user input type.

Use this box to display the "All Choices" option in the value list. Users can select the "All Choices" option to select all of the values in the list.

Limit values by

This box displays if you selected either the Check Box, Choice List, List Box, or Radio Buttons user input type.

Use this box to select an existing prompt column or columns to limit the values that display in the select lists for the prompt column. This type of limitation is also known as a cascading prompt. Oracle suggests that you limit values by selecting multiple specific prompt columns rather than by selecting All Prompts.

Consider the following examples:

• Suppose you build a prompt containing the following columns: Year, Quarter, and Month. Then you limited the Quarter column by Year but do not limit the Month column. At runtime, if the user specifies a Year value of 2009, then the Quarter values available will be 2009 Q1, 2009 Q2, 2009 Q3, and 2009 Q4, only, and the Month values will be for all years (for example, 2006/01, 2007/02, and so on).

• Suppose you build a prompt containing the following column prompts: Year, Quarter, and Month. Then you limit the Quarter column by Year and the Month column by Year and Quarter. At runtime, if the user specifies a Year value of 2009, but does not specify a Quarter value, then the Month values available will be determined by the year (for example, 2009/01, 2009/02, and so on). If the user specifies a Year value of 2009 and a Quarter value of 2009 Q2, then the Month values will be limited to 2009/04, 2009/05, and 2009/06.

Enable user to select multiple values

This box displays if you selected either the Choice List or List Box user input type.

Use this box to allow the user to select multiple prompt values from the list of values. If you selected the Choice List user input option, then the prompt value selection list displays a check box before each prompt value.

If you selected the List Box option, then to select:

• Multiple values, the user presses the Control key, then clicks the values

• A range of values, the user clicks the first value in the range, presses the Shift key, then clicks the last value in the range

If you are creating the prompt on a hierarchical column, then this option is selected and you cannot deselect it. If you are creating a prompt with only one value, then use the "Select Values dialog" to select one value.

Enable user to type values

This box displays if you selected the Choice List user input type.

Use this box to allow the user to choose values from the choice list or place the cursor and type a value into the selection field. For example, select this box when the prompt's value list is lengthy and the user does not want to scroll through or search the list, or the user knows the specific prompt value. This option is useful when the prompt contains non-measure numeric values (for example, a product ID or employee ID) that cannot be easily or quickly located in a list of values.

If this option and the Enable user to select multiple values option are selected, users can enter multiple values (using the semicolon delimiter) into the prompt's selection field.

Require user input

This box displays for all user input types.

Use this box to make the prompt required. When the prompt is required, users cannot click the Go button to execute the prompt query until they have selected a prompt value. When you select this option, an asterisk displays to the left of the prompt label to indicate to the user that the prompt is required.

Slider Values

This field displays for the Slider user input type, only.

Use this field to specify the prompt values and value range to display in the slider. Currently, Within Specific Limits is the only available Slider Values option.

Lower limit

This field displays for the Slider user input type, only.

Use this field to specify the beginning prompt value that is displayed on the left-end of the slider. This prompt value is the lowest value that the user can select.

Upper limit

This field displays for the Slider user input type, only.

Use this field to specify the ending prompt value that is displayed on the right-end of the slider. This prompt value is the highest value that the user can select.

Show Spinbox

This box displays for the Slider user input type, only.

Use this box to display a spinbox where the user can click arrows to select a value. When the user clicks the spinbox's arrows, the slider resets to the same value. Deselect this box to remove the spinbox from the prompt.

Compress Values

This box displays for the Slider user input type, only.

Use this box to remove the zeros from values of 1,000 and over. For example, 1,000 displays as 1K and 1,000,000 displays as 1M. If you select this option and are using a non-English environment, the compressed value label is not translated. The value label is displayed in English (for example, 1K or 1M).

Slider Orientation

This field displays for the Slider user input type, only.

Use this field to specify whether you want the slider to be displayed horizontally or vertically on the prompts page.

Label location

This field displays for the Slider user input type, only.

Use this field to specify where you want the prompt's label to display in relation to the slider. Currently, Left of Slider is the only available Label location option.

Slider size

This field displays for the Slider user input type, only.

Use this field to choose the size of the slider. You can select Small, Medium, or Large.

Default selection

Use this field to specify the prompt value that you want users to see initially. First select one of the following options, then (except for the None option) specify the value in the box that is displayed:

• None – Select this option if you do not want to specify a default selection.

• Specific Values – Select this option to choose a specific default value from the complete prompt list that Oracle Business Intelligence generates based on the option you selected in the Choice List Values field. When you select this option, the Default value field, the Select values button, and the Remove selected value button are displayed. Click the Select values button to access the "Select Values dialog" dialog, where you can search for, select, and deselect a default value. Use the Remove selected value button to remove the selected value from the Default value field.

• All Column Values — This option is available only when the Include "All Choices" choice in the list option is selected. Select this option to specify the "All Choices" default value for the prompt.

• Variable Expression — Select this option to specify a variable or an expression containing a variable. When you select this option, the Default value field is displayed in which you enter the variable or expression.

  For guidelines on referencing a variable in an expression, see Table 2-2. For more information on variables, see "Using Variables".

• Server Variable — Select this option to specify a session or repository variable as the default value. When you select this option, the Default value field is displayed in which you enter the name of the variable. For a session variable, prefix the name with NQ_SESSION and a period, for example NQ_SESSION.USER.

  For guidelines on referencing a server variable, see Table 2-2. For more information on variables, see "Using Variables".

  
  

  Note: If you specify a session variable, and a request variable exists with the same name, the value of the request variable is used instead of the value of the session variable. For more information on request variables, see "What Are Request Variables?"

  
  

• SQL Results — Select this option to set the prompt's default value based on the results of a logical SQL statement. If necessary, a presentation or server variable can be used in the logical SQL statement (for example, variables can be referenced in the "where" clause to constrain the default value based on another prompt).

  Each row of data returned by the SQL statement is treated as a value, and each row can have one or two columns. If each row contains one column, this would be both the display value and code value. If each row contains two columns, the first column is the display value and the second column is the code value. If the SQL statement returns multiple rows but the user interface can only accept one value, then the first row is used.

  If your repository is configured for double columns, and you are creating the prompt on a display column and specify a SQL statement containing the code column, then filtering is done on code values, and not display values. Consider the following SQL syntax:

  SELECT display_column, code_column FROM subject_area_name [Optional Where Clause]

  For example,

  SELECT companyName, companyID FROM SampleSales

Enable user to select by Code Column

This option displays for display columns, only.

Select this option to add a check box to the prompt that enables the user to specify whether to select prompt values by display values (for example, "Beverages") or by code-display values (for example, "1-Beverages"). This box is automatically labeled with the partial caption of "Select by." If you select this option, then add a label in the Label field that identifies what the user is selecting by.

Label

This field displays for display columns, only.

Use this field to complete the "Select by" label for the check box that enables the user to specify whether to select prompt values by display values (for example, "Beverages") or by code-display values (for example, "1-Beverages"). For example, you might enter "product codes" so that the check box label is "Select by product codes."

Choice List Width

Use this field to specify the width of the prompt's field that displays on the prompts page. This field determines the width of the area where the prompt's member values display and does not include the field's label. Note the following options:

• Select Dynamic if you want Oracle BI EE to automatically size the field width to accommodate the prompt's member values. Note that Oracle BI EE might truncates the member values. A user can mouse-over a truncated member value and Oracle BI EE displays the member's full value.

• Select Pixels and specify the size in pixels to which you want Oracle BI EE to adjust the field width. Note that if member values are larger than the pixel value that you specify, then Oracle BI EE truncates the member name. A user can mouse-over a truncated member value and Oracle BI EE displays the member's full value. If you selected the is between operator for the prompt, then both fields ("from" and "to") are adjusted to the pixel size that you specify. This field's default size is 250 pixels. Note that this value differs if the Administrator modified the default pixel value in the Oracle BI EE configuration file.

Note that setting the Set width of all prompts to field on the "Edit Page Settings dialog" overwrites what you specify in the Choice List Width field. Likewise, specifying preferences for the individual prompt in the Choice List Width field deselects the Set width of all prompts to check box in the "Edit Page Settings dialog". For more information, see the help for the "Edit Page Settings dialog."

Set a variable

Use this field to create a variable that this column prompt is to populate.

Select the type of variable, either Presentation Variable or Request Variable, then, enter a name for the variable. Note if you selected Request Variable, you must use the name of the session variable that you want this request variable to override.

For more information about using variables, see "Using Variables".

Column

This field displays if you selected either All Column Values, Specific Column Values, or Members of Groups from the User Input Values field.

This field displays the name of the column upon which you selected to populate the variable prompt's list of values. This field is read-only. To specify a different column name to display in this field, you must click the Select Column button and browse for and select another column.

Select Column

Use this button to display the "Select Column dialog" where you can browse for and select the column to use to generate the prompt's list of values.