IBM Cognos 10 Report Studio Cookbook - Second Edition

Using the Prompt macro in native SQL

In this recipe, we will write an SQL statement to be fired on the data source. We will use the Prompt macro to dynamically change the filter condition.

We will write a report that shows a list of employees by region and country. We will use the Prompt macro to ask the users to enter a country name. Then, the SQL statement will search for the employee belonging to that country.

Getting ready

Create a new blank list report with the GO Data Warehouse (query) package.

How to do it...

In this recipe we will see how to use macros to pass a parameter to an SQL statement, as follows:

1. Go to Query Explorer and drag an SQL object onto the query subject that is linked to the list (Query1 in most cases):
2. Select the SQL object ...