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:
Go to Query Explorer and drag an SQL object onto the query subject that is linked to the list (Query1 in most cases):
Select the SQL object and ensure that great_outdoor_warehouse is selected as the data source.
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...
