Using prompt macro in native SQL

In this recipe, we will write an SQL statement straight 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 list of employee 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 against 'GO Data Warehouse (Query)' package.

How to do it...

1. Go to the Query Explorer and drag an SQL object on the Query Subject that is linked to the list (Query1 in usual case).

   

2. Select the SQL object and ensure that great_outdoor_warehouse is selected as the data source.

   

3. Open the SQL property and add the following statement:

   select distinct "Branch_region_dimension"."REGION_EN" "Region" , "Branch_region_dimension"."COUNTRY_EN" "Country" , "EMP_EMPLOYEE_DIM"."EMPLOYEE_NAME" "Employee_name" 
   from "GOSALESDW"."GO_REGION_DIM" "Branch_region_dimension...