<output_transform>

Abstract

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


Table of Contents

Catalog Configuration Java API analog:  ColumnConfigurationBuilder#setOutputTransform

An <output_transform> contains a template to transform the value of the parent <column> in the Export Report REST API via database functions and/or operations. Within the template, use the required ${this} to reference the column just as one would in a SQL select clause. At report runtime, each ${this} of the output transform will be substituted with the fully qualified column name (table alias plus column name). The substituted transform will then be used within the report SQL statement instead of the column name.

Use an <output_transform> when the native transformation of the raw database data type to a String is undesirable. A common example is the transformation of database date or time type to a date formatted string.

Note

Since the value of <output_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.

Usage

The following table contains Pull Reports™ XML Catalog file fragments configured with <output_transform>s and the resultant SQL query used by the Export Report REST API.

Table 1. Examples of <output_transform>s and the resulting SQL
Report XMLSQL
<table id="table" displayName="Table" name="tname">
<column id="height_ft" name="height_inches" 
    displayName="Height Feet" 
    paramType="java.lang.Double">
    <output_transform>
        case when ${this} is null 
        then null else ${this} / 12 end
    </output_transform>
</column>
</table>
select
case when t0.height_inches is null then null
else t0.height_inches / 12 end as t0_height_ft
from tname as t0
<table id="table" displayName="Table" name="tname">
<column id="updated" name="last_updated" 
    displayName="Last Updated"
    paramType="java.sql.Timestamp">
    <output_transform>
        to_char(${this},'YYYY-MM-DD')
    </output_transform>
</column>
</table>
select
to_char(t0.last_updated,'YYYY-MM-DD') as t0_updated
from tname as t0
<table id="table" displayName="Table" name="tname">
<column id="upper_fullname" name="full_name" 
    displayName="Full Name">
    <output_transform>upper(${this})</output_transform>
</column>
</table>
select
upper(t0.full_name) as t0_upper_fullname
from tname as t0
<table id="table" displayName="Table" name="tname">
<column id="twice_amount" name="amount" 
    displayName="Twice Amount">
    <output_transform>
        ${this} + ${this}
    </output_transform>
</column>
</table>
select
t0.amount + t0.amount as t0_twice_amount
from tname as t0
<table id="table" displayName="Table" name="tname">
<column id="bbox" name="geom" 
    displayName="Bounding Box KML">
    <output_transform>
        st_askml(st_envelope(${this}))
    </output_transform>
</column>
</table>
select
st_askml(st_envelope(t0.geom)) as t0_bbox
from tname as t0

The following table demonstrates how the applyToSort attribute effects the resultant Export Report REST API SQL query when used in conjunction with a sort parameter on the same <column>.

Table 2. Examples of <output_transform> applyToSort attribute and the resulting SQL
Report XMLsort paramSQL
<table id="table" displayName="Table"
   name="tname">
<column id="updated" name="last_updated" 
    displayName="Last Updated"
    paramType="java.sql.Timestamp">
    <output_transform>
        to_char(${this},'MM-DD-YYYY')
    </output_transform>
</column>
</table>
sort=/table@updatedselect
to_char(t0.last_updated,'MM-DD-YYYY') as t0_updated
from tname as t0 order by 1
<table id="table" displayName="Table"
   name="tname">
<column id="updated" name="last_updated" 
    displayName="Last Updated"
    paramType="java.sql.Timestamp">
    <output_transform applyToSort="false">
        to_char(${this},'MM-DD-YYYY')
    </output_transform>
</column>
</table>
sort=/table@updatedselect
to_char(t0.last_updated,'MM-DD-YYYY') as t0_updated_0 ,t0.last_updated as t0_updated_1
from tname as t0 order by t0.last_updated

Parents

<column>

Attributes

applyToSort (Default: true)

If false, the <output_transform> is not applied to the SQL order by clause when the parent <column> is included within the Export Report REST API sort parameter or <export_config> defaultSort attribute.

It is common to set applyToSort='false' when applying an <output_transform> to a <column> of paramType='java.sql.Date'. In this case, the <output_transform> may transform the date to a human readable format (e.g. MM-dd-YYYY), but the applyToSort='false' retains the sorting behavior of the raw Date.