Using filter macros with SQL queries

Filter macros are pieces of code that are replaced at the run time to produce dynamic results. They are replaced in the SQL query according to the parameters from the data mart.

The following types of filter macros can be used in the SQL query:

Simple filter macros: Enables you to specify the name of the parameter and the column (optional) to be used for filtering.
Custom filter macros: Enables you to customize the filtering condition.

Additional template macros include:

Time filter macros: Lets you add time filtering.
ACL filter macros: Lets you add filtering for entities belonging to domains where a user exists.
View macros: Lets you include the SQL code of a data mart within another data mart.

Important

Domain filter macros are not supported. If you migrate from TrueSight Capacity Optimization 20.02, replace domain filter macros with ACL filter macros.
View template macros are deprecated.

Simple filter macros

Simple filter macros let you insert dynamic content in most SQL queries. Their generic format is:

${FILTER(<parameter-ID>[, <column-name>])}

The macro is substituted in the following way:

AND <parameter-ID> = <parameter-value>

Or, if the optional parameter <column-name> is specified, the macro is substituted as:

AND <column-name> = <parameter-value>

Example

To write a query that is filtered for a specific system, you can use the following View template:

SELECT * FROM ER_V_CAPACITY_POOLS_LIST WHERE 1=1 ${FILTER(SYSID)}

At execution time, all macros will be replaced according to the parameter values, dynamically changing the query. Assuming that the current value for the SYSID parameter is "109278", the resulting query will be:

SELECT * FROM ER_V_CAPACITY_POOLS_LIST WHERE 1=1 AND SYSID=109278

Custom filter macros

Custom filter macros allow you to apply dynamic changes using a customized where condition. Their generic format is:

${CUST_FILTER(<parameter-id>, <replacement>)}

The macro is substituted in the following way:

AND <replacement>

Example

To write a query that is filtered for a specific system, you can use the following View template:

SELECT * FROM ER_V_CAPACITY_POOLS_LIST t0 WHERE 1=1 ${CUST_FILTER(SYSID, AND t0.SYSID = ?)}

SELECT * FROM ER_V_CAPACITY_POOLS_LIST t0 WHERE 1=1 AND t0.SYSID = 1000

Dashboard parameters

The following table lists all Dashboard parameters that can be used with filter macros:

Dashboard parameters

Parameter Name	Parameter ID
Domain	APPID
Location	LOCATIONID
Report ID	REPID
Report URL	REPURL
Resource	METRIC
Series ID	SERIESID
Subresource	SUBMETRICNAME or SUBOBJNAME
System	SYSID
System Series ID	SYSMETRICID or SYSOBJID
Tad Id	TAGID
Business Driver	WKLDID
Business Driver Series ID	WKLDMETRICID or WKLDOBJID

For custom parameters, the parameter ID is simply the parameter name. For example, if you defined a custom filter named MY_CUSTOM_FILTER and you want to filter on a column named MY_COLUMN, you can use the following macro:

${CUST_FILTER(MY_CUSTOM_FILTER, AND MY_COLUMN = ?)}

Important

Filter macros are case-sensitive. Use the correct case for macros and parameter IDs. Use the parameter IDs specified in the preceding table for standard parameters.

Time filter macros

Time filter macros allow you to add time filtering on the date column. Their generic format is:

${TIMEFILTER[(<column-name>)]}

Example 1

To write a query that is filtered according to a specific time filter, you can use the following View template:

SELECT * FROM MYTABLE WHERE 1=1 ${TIMEFILTER}

At the run time, the time filter is evaluated, dynamically changing the query. The resulting query will be similar to the following:

SELECT * FROM MYTABLE WHERE 1=1 AND
ts between to_date('06/04/2024','DD/MM/YYYY')
and to_date('07/04/2024','DD/MM/YYYY')

You can also specify a column name; for instance:

SELECT * FROM MYTABLE WHERE 1=1 ${TIMEFILTER(myTable.myCustomTSColumn)}

In this case, the myTable.myCustomTSColumn column will be used instead of the default ts, resulting in the following query:

SELECT * FROM MYTABLE WHERE 1=1 AND
myTable.myCustomTSColumn between
to_date('06/04/2009','DD/MM/YYYY')
and to_date('07/04/2009','DD/MM/YYYY')

Example 2

To filter the data based on the monthly and quarterly rolling periods, you can use the following SQL query:

SELECT t.* FROM (SELECT t.* FROM
            ER_V_ACME_MONTHLYROLL_3 t
        WHERE
            30=${MONTHLY_ROLL()}
        UNION ALL SELECT t.* FROM
            ER_V_ACME_MONTHLYROLL_4 t
        WHERE
            90=${MONTHLY_ROLL()}
    ) T

ACL filter macros

This macro is automatically applied to a query to filter the results for the current user. Use this macro in SQL code to improve the query performance.

Definition	Display only those entities belonging to domains that the user can access.
Syntax	${ ACL_FILTER(TYPE,ALIAS_COLUMN_NAME)}
Parameters	TYPE can be any of the following: SYS: If entities extracted by SQL are systems WKLD: If entities extracted by SQL are business drivers APP: If entities extracted by SQL are domains ALIAS_COLUMN_NAME (optional): Used to specify an alias for the column to be filtered if it is different from the default (ENTID for SYS; WKLD, APPID for APP)
Example	Select * from ER_V_CAPACITY_POOLS_LIST t0 where 1=1 ${ACL_FILTER(SYS,t0.SYSID)}

View macros

View macros allow you to include the SQL code of a data mart inside another data mart. Their generic format is:

${VIEW(<identifier>[, <table-alias>]}

where <identifier> is the data mart identifier to include and <table-alias> is the alias to be used for the data mart template.

Example

SELECT * FROM ER_V_CAPACITY_POOLS_LIST t1 WHERE EXISTS
( SELECT null FROM ${VIEW(ER_V_MYDATAMART_11,mytable)} WHERE mytable.sysid=t1.sysid )