How to Create Prompts in Cognos 12

Overview

Prompts are ways to increase report flexibility by allowing filters with varying values based on user input. User input values are stored in variables that are referenced by query filters, similarly to filter created using literal values.

Table of contents

Adding prompts is a four-step process:

  1. Add a prompt page;
  2. Choose a prompt object and create a prompt variable;
  3. Create source query (for lists of values);
  4. Add the variable to an existing query;

Optional prompts are also possible.

Process

Adding a prompt page

  1. Click the Report menu;
  2. Select Prompt Pages;
  3. Click + to add new prompt page

 The first two steps of adding a prompt page with the Report menu and Prompt Pages item highlighted.  The third step in adding a prompt page with the plus sign highlighted to add the page to the report.

Figures 1 and 2: Add prompt

(Back to the top.)

Choosing a prompt object

There are a number of prompting tools that are available to report writers for inputting prompt values. The most commonly used are text box prompt, allowing users to enter free form values such as inputting student IDs and value prompts, allowing users to select from a list of previously defined values such as selecting from a list of academic periods or list of majors. Other commonly used prompts are date prompts and interval prompts.

Drag the desired object and drop it in the prompt page. The Prompt Wizard screen appears, allowing users to create a new prompt parameter variable or use an existing prompt parameter variable. By default, the parameter variable name is Parameter#, where # is a number sequence starting with 1. Click Finish to create the input object and parameter.

Insertable objects for reports with prompting options

Figure 3: Prompt objects: Text box, Value, Search & select, Date & time, Date, Time, Interval, Tree, and Generated Prompts, and Prompt button objects.

Tip: It is good practice to give parameters a meaningful name that describes the data it contains; enterprise reports also use the prefix p_ for parameter variable names, like p_Organization_Level_3 for a level 3 org code parameter.

Creating prompts that use source data (such as value prompt that lists a set of values) requires connecting the object to the source query or a static list of values, which is done in the properties panel.

For a dynamic list (from a database), set query, use value and display value, where query is the data source for the prompt object, use value is the field value that will be passed in the parameter variable and display value are the values presented to the user. For a static list, add the list of values in the parameter static choices.

Input objects also have properties that change the user interface. Value lists may be presented as drop-down lists, list boxes, check boxes or radio buttons by changing select UI property and multi-select. Text box prompt may be set to multi-line or multi-select, and be set with formatting and data validation options.

Value prompt properties tab

Figure 4: Prompt properties: Style and rendering variables. Sorting, Data format, Query, Use value, Display value, Static choices, Rows per page, and Properties for Data Value prompt properties

(Back to the top.)

Creating a source query

Any query can be a source for parameter list values. IDR has, however, created lists of values views in every package to simplify this process.

Create a new query object and select fields from any of the various list of values. In general, the relevant data is contained in two fields: VALUE and VALUE_DESCRIPTION.

Academic Period list of values.

Figure 5: Source query - Academic Period object table with VALUE, VALUE_DESCRIPTION, and a custom variable, Term_termcode, data items selected.

To combine these two values in one field (such as term and term description, as in 201701 (Winter 2017)) add a custom field (figure 6), concatenating VALUE and VALUE_DESCRPTION using the + operator.

Custom data field Term_termcode

Figure 6: Custom field (data item) - Term_termcode derived from the expression [VALUE] + '('+ [VALUE_DESCRIPTION]+')'

(Back to the top.)

Filtering on prompt values

Using prompt variables in queries filters is similar to using literal values, with a few caveats. In your report’s Queries screen, select the query you wish to filter using a passed parameter, then follow these steps highlighted in figures 7 and 8:

  1. Drag the desired field to the Detail Filter box;
  2. Click the Parameters tab in Available Components;
  3. Drag the desired parameter to the Expression Definition box and structure your filter expression, using the appropriate operator. For single-value parameters use =, for multi-value parameters use in;

Degree Status highlighted and dragged to the Detail Filters box.  Filter of Degree Status limited to those with awarded degrees defined by Degree Status = AW

Figures 7 and 8: Data item, DEGREE_STATUS filter with parameter variable, [DEGREE_STATUS] = 'AW'.

(Back to the top.)

Optional prompts

It is possible to create optional prompts by setting two properties: in the prompt page, select the prompt object and set the property Required to No. In the query, select the desired filter and set the property Usage to Optional.

(Back to the top.)

Print Article