Oracle BI as a reporting tool provides multiple features related to user prompting/filtering of report data.  However, many of these features are not implemented in the PCM14 report printing interface.  However, to replicate the User Prompt feature found in the older PCM Infomaker versions, Oracle has implemented a PROMPT CRITERIA function that does work in PCM 14.

Oracle documentation states that this function only works with Date Prompts, but it appears that if you follow the syntax instructions below, that this prompt can be used for other data types.

Note: If you wish to prompt more than one criteria, you must string them together in the same parameter field:


  1. Open BI
    1. Edit the Data Model for the report you want the prompt added.
    2. In Parameters, click the + (add) to add a new parameter
      1. Name: CRITERIA (in all CAPS)
      2. Data Type: String
      3. Default Value: type in table.fieldname*datetime^
        1. Note: replace table.fieldname with the table and field you want the user to prompt (i.e. sbmt.required_date)
        2. Make sure you type in the *datetime^ as shown
      4. Parameter type: text
    3. Click the SAVE to save the Data Model
    4. Edit the Report and click on Properties
      1. Modify the Report description to identify it is prompted
    5. Exit BI
  1. Open Contract Management Administration
    1. Click on Server Configuration
    2. Click Import Forms and Reports and click Yes to pop up window.
    3. When done, close CM Administration
    4. Note: you will need to do this import every time you add or modify the prompting.
  2. Open PCM 14 to Test
    1. Select the report modified and click Preview
    2. This should open a prompt window

Criteria Options

Below are the formats to use for different types of criteria:

  1. Dates:
    1. field_name*datetime^
  2. Text (e.g. document number, vendor abbreviation, status codes, etc.):
    1. Table.field_name*char(n)*
      1. Where n=number of characters in the field (e.g. submittal_number = 15)
  3. Number (dollar) fields:
    1. Table.field_name*numeric(p,s)^
      1. P=precision (how many numbers 1 to 38(
      2. S=decimal (0 to 3, etc.)

Note:  Oracle states that only item 1 (Dates) is supported, so use Text and Number criteria at your own risk.  Please take care that the parameters in those must be accurate to the database or you may not get the results you expect.

As mentioned earlier, you can only have ONE CRITERIA prompt, so you cannot mix Date/Text/Number types together in one prompt.

As with the Infomaker version, all prompts provide input field that must be typed in—it does not provide the ability for dropdown lists, for example.

Last, if you prompt for a company abbreviation or a contract number, the value typed in must match EXACT what is found in CM.  If the contract number is “6338-001”, typing in “6338” or “6338-01” or “6338001” will return nothing.

Entering a Date Range in the Date Prompt

As with the Infomaker version, you will get one date prompt pop up window and to enter a date RANGE, you must type in the dates as follows:

    1. Criteria:
      1. “>1/1/12” (if you want greater or equal to use “>=1/1/12”)
    2. Or:
      1. “AND <12/31/12” (if you want greater or equal to use “AND <=12/31/12”)

Example of Date Range (greater than and less than):

Example of Date Range (greater than or equal to):


  1. If you do not type in the > and < symbols, you will retrieve ONLY the date you enter.
  2. If you don’t use the = symbol it will exclude the date you typed.
  3. If you do not type the “AND” in the Or field, you won’t get the range. Instead you’ll get the first criteria OR the second criteria, which might return all values instead of only those inside your date range.
  4. If you type just one date in the box (or use the date select) you will return ONLY the one date.


Oracle Knowledgebase Article:  1491300.1