<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 XML	SQL
<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 XML sort param SQL

Report XML	`sort` param	SQL
<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@updated	`select 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@updated	`select 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`

<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@updated

select
 
    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@updated

select
 
    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.

Prev		Next
<label_value_query_default>	Home	<placemark_name_template>