<filter_column_transform>

Abstract

Learn to configure the <filter_column_transform> element for Pull Reports™ Ad Hoc report and data service software.

Table of Contents

Catalog Configuration Java API analog: ColumnConfigurationBuilder#setFilterColumnTransform,

A <filter_column_transform> contains a template to transform the column name when filtering on the parent <column> in the Export Report REST API via database functions and/or operations. Within the template, use the required ${this} expression to reference the column as one would in a SQL where clause. At report runtime, each ${this} will be substituted with the fully qualified column name (table alias plus column name) and the substituted transform used within the SQL where statement instead of the column name.

Usage

To understand a <filter_column_transform>, first understand how a <column> configuration relates to a the Export Report REST API filter parameter without a <filter_column_transform>.

Given this <column> definition,

<table id="mytable" name="mytable" displayName="My Table">
    <column id="foo" name="foo" displayName="Foo"/>
</table>

and this Export Report REST API filter parameter,

&filter=/mytable@foo+like+'bar%'

the generated SQL where clause associated with the filter is:

t0.foo like ?

However, after the addition a <filter_column_transform> like so,

<table id="mytable" name="mytable" displayName="My Table">
    <column id="foo" name="foo" displayName="Foo">
        <filter_column_transform>lower(${this})</filter_column_transform>
    </column>
</table>

the generated SQL where clause becomes:

lower(t0.foo) like ?

The <filter_column_transform> transforms the column reference in the filter from t0.foo to lower(t0.foo).

Note

Since the value of <filter_column_transform> is placed directly within the SQL statement, be sure that all functions and operators are compatible with the underlying database and the template is free from SQL injection risk.

Table 1. Additional examples of <filter_column_transform>s and the resulting SQL

Report XML Filter Example SQL

Report XML	Filter	Example SQL
<table id="table" displayName="Table" name="tname"> <column id="height_in" name="height_inches" displayName="Height Inches" paramType="java.lang.Double"> <filter_column_transform> ${this} / 12 </filter_column_transform> </column> </table>	`@height_in = 30`	`select t0.height_inches as t0_height_in from tname as t0 where t0.height_inches / 12 = ?`
<table id="table" displayName="Table" name="tname"> <column id="code" name="species_code" displayName="Species Code"> <filter_column_transform> substr(${this},0,1) </filter_column_transform> </column> </table>	`@code = 'A'`	`select t0.species_code as t0_code from tname as t0 where substr(t0.species_code,0,1) = ?`
Note the setting of `paramType="java.lang.Integer"` instead of `paramType="java.lang.Integer"` so that filter inputs are coerced to the Integer type required by the `months(...)` function. <table id="table" displayName="Table" name="tname"> <column id="last_updated_month" name="last_updated" displayName="Last Updated Month" paramType="java.lang.Integer"> <output_transform> month(${this}) </output_transform> <filter_column_transform> month(${this}) </filter_column_transform> </column> </table>	`@last_updated_month >= 6`	`select month(t0.last_updated) as t0_last_updated_month from tname as t0 where month(t0.last_updated) >= ?`

<table id="table" displayName="Table" 
    name="tname">
<column id="height_in" name="height_inches" 
    displayName="Height Inches" 
    paramType="java.lang.Double">
    <filter_column_transform>
        ${this} / 12
    </filter_column_transform>
</column>
</table>

@height_in = 30

select t0.height_inches as t0_height_in from tname as t0 where
t0.height_inches / 12 = ?

<table id="table" displayName="Table" 
    name="tname">
<column id="code" name="species_code" 
    displayName="Species Code"> 
    <filter_column_transform>
        substr(${this},0,1)
    </filter_column_transform>
</column>
</table>

@code = 'A'

select t0.species_code as t0_code from tname as t0 where
substr(t0.species_code,0,1) = ?

Note the setting of paramType="java.lang.Integer" instead of paramType="java.lang.Integer" so that filter inputs are coerced to the Integer type required by the months(...) function.

<table id="table" displayName="Table" 
    name="tname">
<column id="last_updated_month" 
    name="last_updated" 
    displayName="Last Updated Month" 
    paramType="java.lang.Integer">
    <output_transform>
        month(${this})
    </output_transform>
    <filter_column_transform>
        month(${this})
    </filter_column_transform>
</column>
</table>

@last_updated_month >= 6

select month(t0.last_updated) as t0_last_updated_month 
                     from tname as t0 where month(t0.last_updated) >= ?

Parents

<column>

Prev		Next
<filter>	Home	<filter_value_transform>