Using filter macros with SQL queries
Filter macros are pieces of code that are replaced at execution time to produce dynamic results. They are replaced in the SQL query according to the parameters that come from the Dashboard.
The different types of filter macros that can be used in the SQL code of a View template are:
- Simple filter macros: Enables you to specify the name of the parameter and the column (optional) that should 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.
- Domain filter macros: Lets you add domain filtering.
- ACL filter macros: Lets you add filtering for entities belonging to domains that a user can exist.
- View macros: Lets you include the SQL code of a data mart within another data mart.
- (Deprecated) View template macros: Lets you include the SQL code of a View template within another View template.
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>
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>
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 |
Tag 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 = ?)}
Note
Filter macros are case sensitive: use the correct case for macros and parameter IDs. For standard parameters, use the parameter IDs as specified in the preceding table.
Time filter macros
Time filter macros allow you to add time filtering on the date column. Their generic format is:
${TIMEFILTER[(<column-name>)]}
Domain filter macros
Definition | Enables you to display entities that belong to a specific domain and its sub-domains. |
---|---|
Syntax |
|
Parameters |
|
Example | Consider the following query: Select * from MY_MART tab where tab.entid in (select sysid from PV_APPL_SYS aps where 1 = 1 ${CUST_FILTER(APPID, AND aps.appid IN (select * from table (get_appid_child_list (?, 1))))} With the use of the DOMAIN_FILTER macro, the Select * from MY_MART tab where 1=1 ${DOMAIN_FILTER(SYS)} |
Note
For the proper data mart column names, BMC recommends to use the Labels option in the Output Columns section instead of using table column aliases in the SQL query.
ACL filter macros
Definition | Display only those entities belonging to domains that the user can access. |
---|---|
Syntax | ${ ACL_FILTER(TYPE,ALIAS_COLUMN_NAME)} |
Parameters |
|
Example | Select * from PV_SYSTEM 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.
View template macros
The view template macro is deprecated in TrueSight Capacity Optimization version 11.5.01. BMC recommends that you use the VIEW macro that provides the same capabilities with a simplified workflow. See Deprecated and dropped features and components.
View template macros allow you to include the SQL code of a View template inside another View template. Their generic format is:
${VTEMPL(<erid>[, <table-alias>]}
where <erid>
is the View template ID to include and <table-alias>
is the alias to be used for the data mart template.
Comments
Log in or register to comment.