<filter_value_transform>

Abstract

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

Table of Contents

Catalog Configuration Java API analog: ColumnConfigurationBuilder#setFilterValueTransform,

A <filter_value_transform> contains a template to transform the value when filtering on the parent <column> in the Export Report REST API via database functions and/or operations. Within the template, use the required ? to reference the parameter value just as one would in a SQL where clause.

Usage

To understand a <filter_value_transform>, first understand how a <column> configuration relates to a the Export Report REST API filter parameter without a <filter_value_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_value_transform> like so,

<table id="mytable" name="mytable" displayName="My Table">
    <column id="foo" name="foo" displayName="Foo">
        <filter_value_transform>lower(?)</filter_value_transform>
    </column>
</table>

the generated SQL where clause becomes:

t0.foo like lower(?)

The <filter_value_transform> transforms the value reference in the filter from ? to lower(?).

Note

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

Table 1. Additional examples of <filter_value_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="full_name" name="complete_name" displayName="Full Name"> <filter_column_transform> lower(${this}) </filter_column_transform> <filter_value_transform> lower('%' \|\| ? \|\| '%') </filter_value_transform> </column> </table>	`@full_name like 'jack'`	`select t0.complete_name as t0_full_name from tname as t0 where lower(t0.complete_name) like lower('%' \|\| ? \|\| '%')`
This advanced example uses the OpenGIS Simple Features `ST_Y` and `ST_GeomFromText` functions to convert a Geometry column into a latitude and receive Well-known text as filter input. <table id="table" displayName="Table" name="tname"> <column id="latitude" name="point_geom" displayName="Latitude"> <output_transform> ST_Y(${this}) </output_transform> <filter_column_transform> ST_Y(${this}) </filter_column_transform> <filter_value_transform> ST_Y(ST_GeomFromText(?)) </filter_value_transform> </column> </table>	`@latitude > 'POINT(-100,40)'`	`select ST_Y(t0.point_geom) as t0_latitude from tname as t0 where ST_Y(t0.point_geom) > ST_Y(ST_GeomFromText(?))`

<table id="table" displayName="Table" 
name="tname">
    <column id="full_name" name="complete_name" 
    displayName="Full Name"> 
        <filter_column_transform>
        lower(${this})
        </filter_column_transform>
        <filter_value_transform>
        lower('%' || ? || '%')
        </filter_value_transform>
    </column>
</table>

@full_name like 'jack'

select t0.complete_name as t0_full_name from tname as t0 where
lower(t0.complete_name) like lower('%' || ? || '%')

This advanced example uses the OpenGIS Simple Features ST_Y and ST_GeomFromText functions to convert a Geometry column into a latitude and receive Well-known text as filter input.

<table id="table" displayName="Table" 
name="tname">
    <column id="latitude" name="point_geom" 
    displayName="Latitude"> 
        <output_transform>
        ST_Y(${this})
        </output_transform>
        <filter_column_transform>
        ST_Y(${this})
        </filter_column_transform>
        <filter_value_transform>
        ST_Y(ST_GeomFromText(?))
        </filter_value_transform>
    </column>
</table>

@latitude > 'POINT(-100,40)'

select ST_Y(t0.point_geom) as t0_latitude from tname as t0 
                     where
ST_Y(t0.point_geom) > ST_Y(ST_GeomFromText(?))

Parents

<column>

Prev		Next
<filter_column_transform>	Home	<geojson>