IBM Cognos Report Studio Tip: Using Query Macros for Prompted Multiple Operator Selection

January 20, 2017 | Todd Moody

The Issue

IBM Cognos report users would like to have the flexibility to select different operators (=, contains, starts with, etc.) in their Cognos Report Studio prompted reports. Out of the box, Cognos doesn’t provide a control to select operators from. Typically, a single operator is set in the parameterized filter to drive the prompts, and the user doesn’t have any operator selection.

The Solution

Leveraging Query Macros and value prompts, we can give the users a prompt in which they can select operators along with a prompt to select the value(s) of the query item they are filtering. Query Macros are fragments of code that can be used in the Select statement of a query or the expression of data items and filters. In our case, we will be using the macro function prompt.  

Building the Solution

The prompt function contains the following required and optional parameters:

Note: Each parameter must be contained in single quotes.
  • Name (required) – author defined, also represents the parameter name in the report
  • Datatype (optional) – default datatype is string
  • DefaultText (optional) – default value/text
  • Text (optional) – text that will precede any user supplied values
  • QueryItem (optional)
  • TextFollowing (optional)

We only need to worry about the first three in this example.

We will be using the prompt macro in the expression of a parameterized filter in the report. The expression will essentially contain two parameters; one for the operator prompt and one for the prompt of the data item we are filtering.

Using the IBM Cognos Samples package “GO Sales (query),” we will create a simple report and then create a prompt page containing prompts for the operator selection and the value selection.

1. Launch Report Studio and create a simple List report.
EKSH - tech tip - simple list report

2. Navigate to Query design view and create a detail filter with the following expression:

[Order number] #prompt('Op_OrderNum', 'token', 'in')# ?OrderNum?
  • The first argument is the name of the parameter for our operator selection prompt.
  • Datatype is “token”.  
    • Token is not an actual datatype and doesn’t pass values. Instead, it allows us to pass SQL, or in this case predefined values we will define in the prompt.
  • The final parameter/argument is the default text that will be used. If the user makes no selection in the operator prompt, our expression will be:

[Order number] in ?OrderNum?.

3. Create a prompt page and add a 3x1 table.

4. Remove the 100% value from the width in the “Size and Overflow” property of the table.

5. Drag a text item to the first column of the table with the text “Order Number.” 
EKSH - tech tip

6. Add a value prompt to the second column of the table, complete as in the image below, and select “Finish.”
EKSH - tech tip - value prompt

7. Add a Text Box Prompt to the third column of the table, complete as in the image below, and select “Finish.”
EKSH - tech tip - text box prompt

This is what you should have so far:

EKSH - tech tip - order number

8. In the “Static Choices” property, make additions as in the image below.
EKSH - tech tip - static choices

9. Set the “Required” property to “Yes” and in the “Default Selections” property add “=.”

10. Go into the query and cast Order Number as varchar(6) (for demo purposes).

11. Run the report, select “Starts With” for the first prompt, and type “7” into the second.  

12. Continue trying different combinations.

Note: A multi-select prompt can be used for the ?OrderNum? parameter by using the IN operator instead of the = operator.  =, Starts With, and Contains will only allow single select.  

(For the project this technique was used on, the original reports allowed users to select multiple values on the “=” operator. We were able to replicate by using “IN” for the “Use Value” and “=” for the “Display Value” of the Static Choices property.)