In this recipe, we will see how to dynamically change the field on which filter is being applied using macro. We will use prompt macro to generate one of the possible tokens and then use it in the query.
Create a list report based on native SQL similar to the previous recipe. We will use the same query that works on the product tables but filtering will be different. For that, define the SQL as following:
select distinct "SLS_PRODUCT_LINE_LOOKUP"."PRODUCT_LINE_EN" "Product_line" , "SLS_PRODUCT_LOOKUP"."PRODUCT_NAME" "Product_name" , "SLS_PRODUCT_COLOR_LOOKUP"."PRODUCT_COLOR_EN" "Product_color" , "SLS_PRODUCT_SIZE_LOOKUP"."PRODUCT_SIZE_EN" "Product_size"
from "GOSALESDW"."SLS_PRODUCT_DIM" "SLS_PRODUCT_DIM", "GOSALESDW"."SLS_PRODUCT_LINE_LOOKUP" "SLS_PRODUCT_LINE_LOOKUP", "GOSALESDW"."SLS_PRODUCT_TYPE_LOOKUP" "SLS_PRODUCT_TYPE_LOOKUP", "GOSALESDW"."SLS_PRODUCT_LOOKUP" "SLS_PRODUCT_LOOKUP", "GOSALESDW"."SLS_PRODUCT_COLOR_LOOKUP" "SLS_PRODUCT_COLOR_LOOKUP", "GOSALESDW"."SLS_PRODUCT_SIZE_LOOKUP" "SLS_PRODUCT_SIZE_LOOKUP", "GOSALESDW"."SLS_PRODUCT_BRAND_LOOKUP" "SLS_PRODUCT_BRAND_LOOKUP"
where "SLS_PRODUCT_LOOKUP"."PRODUCT_LANGUAGE" = N'EN' and "SLS_PRODUCT_DIM"."PRODUCT_LINE_CODE" = "SLS_PRODUCT_LINE_LOOKUP"."PRODUCT_LINE_CODE" and "SLS_PRODUCT_DIM"."PRODUCT_NUMBER" = "SLS_PRODUCT_LOOKUP"."PRODUCT_NUMBER" and "SLS_PRODUCT_DIM"."PRODUCT_SIZE_CODE" = "SLS_PRODUCT_SIZE_LOOKUP"."PRODUCT_SIZE_CODE" and "SLS_PRODUCT_DIM"."PRODUCT_TYPE_CODE" = "SLS_PRODUCT_TYPE_LOOKUP"."PRODUCT_TYPE_
CODE" and "SLS_PRODUCT_DIM"."PRODUCT_COLOR_CODE" = "SLS_PRODUCT_COLOR_LOOKUP"."PRODUCT_COLOR_CODE" and "SLS_PRODUCT_BRAND_LOOKUP"."PRODUCT_BRAND_CODE" = "SLS_PRODUCT_DIM"."PRODUCT_BRAND_CODE"
and
#prompt ('Field','token','"SLS_PRODUCT_LINE_LOOKUP"."PRODUCT_LINE_EN"')# like #prompt ('Value','string')#This is the same basic query that joins the product related tables and fetches required columns. The last statement in WHERE clause uses two prompt macros. We will talk about it in detail.
- We have already created a list report based on an SQL query subject as mentioned previously. Drag the columns from the query subject on the list over the report page.
- Now create a new prompt page.
- Add a value prompt on the prompt page. Define two static choices for this.
Display value
Use value
Filter on product line
"SLS_PRODUCT_LINE_LOOKUP"."PRODUCT_LINE_EN"Filter on product name
"SLS_PRODUCT_LOOKUP"."PRODUCT_NAME - Set the parameter for this prompt to 'Field'. This will come pre-populated as existing parameter, as it is defined in the query subject.
- Choose the UI as radio button group and Filter on Product Line as default selection.
- Now add a text box prompt on to the prompt page.
- Set its parameter to Value which comes as a choice in an existing parameter (as it is already defined in the query).
- Run the report to test it. You will see an option to filter on product line or product name. The value you provide in the text box prompt will be used to filter either of the fields depending on the choice selected in radio buttons.
The data type (second argument) of Prompt()
function determines how the value is returned. For string type, the value is returned within a single quote. However, there is a data type called 'Token'. When you use this, function accepts a string value and puts it literally within the expression, that is, without quotes.
Here we have used this functionality to dynamically change the field on which filter is applied. The two possible tokens are defined in the USE VALUE of the radio button. Depending on the user's choice, one of the token will be placed in the query and will form the left part of filter expression.
Right part of the filter is a standard string parameter. Whatever value the user types in the text box prompt will be wrapped in single quotes and then placed in the SQL statement.
Effectively, the resulting expression will be something like this:
"SLS_PRODUCT_LINE_LOOKUP"."PRODUCT_LINE_EN" = 'XYZ'