Chapter 8. Schema Reference

The Pull Reports™ Schema defines the XML elements available to configure reports. Pull Reports™ XML Catalog files begin with the <catalog> element and must contain at least on <report>.

8.1. <catalog>

The root element of a Pull Reports™ XML Catalog file. One <catalog> groups one to many reports into a common resource path within the Export Report REST API. This allows all <report>s within a <catalog> to share a common JNDI javax.sql.DataSource and security constraint if required.

Example 8.1. <catalog> with three <report>s
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Class Reports">
<report id="class-information" name="Class Information">
...
</report>
<report id="student-information" name="Student Information">
...
</report>
<report id="school-budget" name="School Budget">
...
</report>
</catalog>

Children

<report>+
<column_group>*
<table>* (Global tables)
<global_label_value_list>+

Attributes

id

The unique id of the catalog within the Pull Reports™ installation. This id forms part of the Export Report REST API and is thus a useful way to organize reports under a common security constraint. For example:

<catalog id="financial" ...>

creates REST endpoint:

/[context]/pullreports/catalog/financial/report/[report id]

Catalog id's may be composed of alphabetical characters, digits, or the - and _ characters.

Spreading a <catalog> id across many Pull Reports™ XML Catalog files

Although it is common to place all <report>s for one <catalog> into one Pull Reports™ XML Catalog file, it is permitted to duplicate a <catalog> id across two or more Pull Reports™ XML Catalog files. In this case, the report parser will combine the <report>s as if they had been defined in a single Pull Reports™ XML Catalog file. However, the following rules apply:

  • The parser only honors the <catalog> name attribute of the first Pull Reports™ XML Catalog file with the same <catalog> id.

  • The parser only honors the first <report> of a unique id found within a <catalog>, even if that catalog id is found in multiple Pull Reports™ XML Catalog files.

name

The human readable catalog name.

8.2. <column>

Represents a relational database column. If the export attribute is true, the default, the column will be included in the output of the Export Report REST API if the parent <table> is the <report>'s base table or included via the columns HTTP parameter.

Example 8.2. <column> usage

This database table:

CREATE TABLE person
(
person_id integer primary key,
first_name varchar(200) not null,
last_name varchar(200) not null, 
birth_date date,
sex char(1) not null
);

May be mapped with this simple Pull Reports™ XML Catalog file:

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" 
    id="person_catalog" name="Person Catalog">
    <report id="person" name="Person Report">
        <export_config defaultColumns="id,fname,lname"/>
        <table id="person" name="person" displayName="Person Info"> 
            <column id="id" name="person_id" paramType="java.lang.Integer"/>
            <column id="fname" name="first_name"/>
            <column id="lname" name="last_name"/>
            <column id="bdate" name="birth_date" paramType="java.sql.Date">
                <description>The birth date of the person as entered via their personnel record.</description>
            </column>
            <column id="sex" name="sex" />
        </table>
    </report>
</catalog>

Children

<description>?
<output_transform>?
<filter_column_transform>?
<filter_value_transform>?
<url_template>?
( <global_label_value_list_ref> | <label_value_list> )?

Parents

<column_group>
<table>

Attributes

displayName (Optional)

The human readable name of the column.

Security Warning

If displayName is not defined, the <column>'s name attribute will be used to label the column within the Export Report REST API. Since the use of the name attribute exposes database definition information which can be used in SQL injection attacks, it is a good practice to define a displayName different from the name.

export (Default: true)

Set to false to exclude this column from export results. The column may still be used in filters and <url_template> and <placemark_name_template> definitions.

Note

Even if export="false", the <column> may be used as the geometryColumnPath within the <kml> and <geojson> elements. Setting columns which contain raw KML and GeoJSON geometries to export="false" is a convenient way to remove the often extremely large Geometry character strings from non-GIS capable export formats.

id

The unique id of the column within the parent <table> or <column_group>. The id value is used with the Export Report REST API to reference the column for such features as filters and sorting in the Export Report REST API.

name

The name of the column in the relational database. Do not prepend the table name to the column name. Pull Reports™ will prepend an auto-generated table alias to the column name at query time.

paramType (Default: java.lang.String)

The Java type to which to coerce filter parameter values when setting JDBC query placeholders in the Export Report REST API. The paramType often corresponds to the database column type but may vary when using <filter_column_transform> or <filter_value_transform> child elements to customize the SQL where statement.

Permitted values are:

  • java.lang.Boolean

  • java.lang.Long

  • java.sql.Date

  • java.sql.Time

  • java.sql.Timestamp

  • java.lang.Double

  • java.lang.Float

  • java.lang.Integer

  • java.lang.String

8.3. <column_group>

Collection of <column>s to be included within a <table> via a <column_group_ref>. <column_group>s are declared globally within a <catalog> after any global <table> definitions. Use <column_group>s to reuse <column> configuration among two or more <table>s and thus avoid repeating complex configuration.

Example 8.3. <column_group> usage

In this example, the employee and customer tables share eight columns in common and are joined via the employee_customer many-to-many indirection table. A <column_group> is used to consolidate the redundant <column> configuration.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" 
    id="contacts" name="Contact Catalog">
    <report id="employee" name="Account Contact Report">
        <export_config defaultColumns="id,fname,lname"/>
        <table id="employee" name="employee" displayName="Employee"> 
            <column_group_ref ref="contact_info"/>
            <column id="salary" name="annual_salary" paramType="java.lang.Double"/>
            <relationship cardinality="many">
                <join_table name="employee_customer">
                    <join_columns>
                        <join_column columnName="employee_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="customer_id" referencedColumnName="oid" />
                   </inverse_join_columns>
                </join_table>
                <table id="customer" name="customer" displayName="Customer">
                    <column_group_ref ref="contact_info"/>
                    <column id="active" name="is_active" paramType="java.lang.Boolean"/>
                </table>
            </relationship>
        </table>
    </report>
    <column_group id="contact_info"> 
        <column id="id" name="id" paramType="java.lang.Integer"/>
        <column id="fname" name="first_name"/>
        <column id="lname" name="last_name">
            <url_template>http://www.mycompany.com/person/${@@id}</url_template>
        </column>
        <column id="phone" name="mobile_phone"/>
        <column id="address_street" name="address_1"/>
        <column id="address_street2" name="address_2"/>
        <column id="state" name="state"/>
        <column id="zip" name="zipcode" paramType="java.lang.Integer"/>
    </column_group>
</catalog>

Warning: column id collision

Pull Reports™ will error when parsing an Pull Reports™ XML Catalog file if any <column> id attribute within a referred <column_group> matches a <column> id attribute of the referring <table>.

This means the following will error when parsed:

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="catalog-id" name="Catalog Name">
    <report id="report-id" name="Invalid Report">
        <table id="table" displayName="Details" name="details">
            <column id="details_id" name="id" displayName="ID" paramType="integer"/> 
            <column_group_ref ref="bad_ref"/>
        </table>
    </report>
    <column_group id="bad_ref">
        <!--  Oops! Do not include a <column> with the same id into the same <table> twice. -->
        <column id="details_id" name="id" displayName="ID" paramType="integer"/> 
    </column_group>
</catalog>

Children

<column>+

Parents

<catalog>

Attributes

id

The unique id within the parent <catalog>.

8.4. <column_group_ref>

A reference to a <column_group> whose <column> elements should be included within the parent <table>.

Parents

<table>

Attributes

ref

Reference to a <column_group> id.

8.5. <description>

Contains a textual description of the parent element. This text is reflected within the Pull Reports™ Ad Hoc Report Creator and may be used to further explain a parent <report>, <table>, <table_ref>, or <column> elements. May contain HTML.

Parents

<column>
<report>
<subquery>
<table>
<table_ref>

8.6. <export_config>

Parent element for export renderer configuration within the Export Report REST API.

Children

<kml>?
<geojson>?

Parents

<report>

Attributes

defaultColumns (Optional)

Comma separated list of one or more exportable, @ prefixed <column> ids from the <report>'s base table. "exportable" means that the <column> does not set export='false'.

The Export Report REST API will include these base table columns within the export result if no other base table columns are specified within the columns parameter. See the columns documentation for more information.

defaultSort (Optional)

The default sort of this report for the Export Report REST API if the sort HTTP parameter is not provided. The format adheres to the rules of the Export Report sort parameter with the exception that all column resource paths must reference a <column> on the base table of the <report>.

Note

A single sort clause will only be applied if the sorted <column> is present within the export results.

Example 8.4. defaultSort usage
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Class Reports">
    <report id="student-information" name="Student Information">
        <export_config defaultSort="@name;@bdate desc" defaultColumns='id,name'/> 
        <table id="student" displayName="Student Details" name="student_details"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer"/>
            <column id="name" name="student_name" displayName="Student Name"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
        </table>
    </report>
</catalog>

The example defaultSort attribute above equates to SQL statement:

order by student_name, birth_date desc

Additionally the defaultSort value of:

/student-information@name;/student-information@bdate desc

would give identical results since the inclusion of the <report>'s base table path is optional.


8.7. <filter_column_transform>

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.

Example 8.5. <filter_column_transform> usage

Given this <column> definition:

<column id="foo" name="foo" displayName="Foo"/>

And this filter expression:

filter=@foo like 'bar%'

The generated SQL where clause for the filter is:

t0.foo like ?

However, with the use of a <filter_column_transform>:

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

The generated SQL where clause becomes:

lower(t0.foo) like ?


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 8.1. Additional examples of <filter_column_transform>s and the resulting SQL
Report XMLFilterExample 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 = 30select 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 >= 6select month(t0.last_updated) as t0_last_updated_month from tname as t0 where month(t0.last_updated) >= ?

Parents

<column>

8.8. <filter_value_transform>

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.

Example 8.6. <filter_value_transform> usage

Given this <column> definition:

<column id="foo" name="foo" displayName="Foo"/>

And this filter expression:

filter=@foo like 'bar%'

The generated SQL where clause for the filter is:

t0.foo like ?

However, with the use of a <filter_value_transform>:

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

The generated SQL where clause becomes:

t0.foo like 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 8.2. Additional examples of <filter_value_transform>s and the resulting SQL
Report XMLFilterExample 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(?))

Parents

<column>

8.9. <geojson>

Required configuration element for the parent <report> to support the GeoJSON and Map format types.

Example 8.7. <geojson> usage
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Land Ownership Reports">
    <report id="parcel-information" name="Parcel Information">
        <export_config defaultColumns='id'>
            <!-- "/parcel@parcel-geometry-geojson is also valid since the base table path is optional -->
            <geojson geometryColumnPath="@parcel-geometry-geojson"/>
       </export_config>
        <table id="parcel" displayName="Parcel" name="parcel"> 
            <column id="id" name="id" displayName="Parcel ID" paramType="java.lang.Integer"/>
            <column id="parcel-geometry-geojson" name="geom_geojson" displayName="GeoJSON" />
        </table>
    </report>
</catalog>

In this example, the parcel table could have row:

Table 8.3. parcel table row
idgeom_geojson
39{"type": "Point", "coordinates": [-105.0906,40.5656]}

Alternatively, if the database contains geometry information but not raw GeoJSON, use an <output_transform> and a GeoJSON transform function such as PostGIS' ST_AsGeoJSON.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Land Ownership Reports">
    <report id="parcel-information" name="Parcel Information">
        <export_config defaultColumns='id'>
            <geojson geometryColumnPath="@parcel-geometry-geojson"/>
       </export_config>
        <table id="parcel" displayName="Parcel" name="parcel"> 
            <column id="id" name="id" displayName="Parcel ID" paramType="java.lang.Integer"/>
            <column id="parcel-geometry-geojson" name="geom" displayName="GeoJSON" >
                <output_transform>ST_AsGeoJSON(${this})</output_transform>
            </column>
        </table>
    </report>
</catalog>

Parents

<export_config>

Attributes

geometryColumnPath

The column resource path to the column in the report's base table which contains the row's GeoJSON Geometry as a java.lang.String. Currently, Pull Reportsonly supports paths to columns in the parent <report>'s base table.

8.10. <global_label_value_list>

Configures a list of labels and corresponding values which represent the possible values of a <column>. Used by the Pull Reports™ Ad Hoc Report Creator to display the available values for a filter parameter in the Export Report REST API.

Children

( <label_value_query> | <label_values> )

Parents

<catalog>

Attributes

id

The unique id of the list within the parent <catalog>. Referenced by <global_label_value_list_ref>'s ref attribute.

8.11. <global_label_value_list_ref>

Parents

<column>

Attributes

ref

The id of the <global_label_value_list> whose values represent the unique set of values for the parent <column>. Used by the Pull Reports™ Ad Hoc Report Creator to display a restricted set of values for column filtering within the Export Report REST API.

8.12. <inverse_join_columns>

Contains the foreign key <join_column>s which reference the child <table> of the parent many-to-many <relationship>.

Parents

<join_table>

Children

<join_column>+

8.13. <join_column>

Specifies the columns involved in a join condition between a <table> and a child <relationship>.

Example 8.8. <join_column> usage
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="example" name="join_column_example">
    <report id="home" name="Home Report">
        <export_config defaultColumns='id'/>
        <table id="home" name="realestate.home" displayName="Home"> 
            <column id="id" name="id" paramType="java.lang.Integer"/>
            <column id="address_id" name="address_id" paramType="java.lang.Integer"/>
            <!-- A Home has One to Many Past Sales 

            SQL: realestate.home left outer join realestate.sale 
                     on realestate.sale.home_id = realestate.home.id -->
            <relationship join="left" cardinality="many">
                <join_column columnName="home_id" referencedColumnName="id" />
                <table id="sale" displayName="Sale" name="realestate.sale">
                    <column id="sale_id" name="sale_id" paramType="java.lang.Integer"/>
                    <column id="home_id" name="home_id" paramType="java.lang.Integer"/>
                    <column id="amount" name="amount" paramType="java.lang.Double"/>
                </table>
            </relationship>
            <!-- A Home has One to One Addresses

            SQL: realestate.home inner join realestate.address 
                     on realestate.address.aid = realestate.home.address_id -->
            <relationship join="inner" cardinality="one">
                <join_column columnName="address_id" referencedColumnName="aid" />
                <table id="address" displayName="Address" name="realestate.address">
                    <column id="aid" name="id" paramType="java.lang.Integer"/>
                    <column id="street" name="street" /> 
                </table>
            </relationship>
            <!-- A Home has Many to Many Owners

            SQL: realestate.home left outer join realestate.home_owner 
                     on realestate.home_owner.home_id = realestate.home.id
                 left outer join realestate.owner 
                     on realestate.home_owner.owner_id = realestate.owner.oid -->
            <relationship cardinality="many">
                <join_table name="realestate.home_owner">
                    <join_columns>
                        <join_column columnName="home_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="owner_id" referencedColumnName="oid" />
                   </inverse_join_columns>
                </join_table>
                <table id="owner" displayName="Owner" name="realestate.owner">
                    <column id="oid" name="id" paramType="java.lang.Integer"/>
                    <column id="first_name" name="first_name"/> 
                    <column id="last_name" name="last_name"/> 
                    <!-- An owner has a many-to-one relationship with a realtor. This 
                     This example demonstrates a compound key with two <join_column>s.

                     SQL: realestate.owner left outer join realestate.owner_realtor 
                            on realestate.owner.first_name = realestate.owner_realtor.o_first_name and
                               realestate.owner.last_name = realestate.owner_realtor.o_last_name -->
                    <relationship join="left" cardinality="one">
                        <join_column columnName="first_name" referencedColumnName="o_first_name" />
                        <join_column columnName="last_name" referencedColumnName="o_last_name" />
                        <table id="realtor" displayName="Realtor" name="realestate.owner_realtor">
                            <column id="owner_first_name" name="ofirst_name"/>
                            <column id="owner_last_name" name="olast_name"/>
                            <column id="realtor_name" name="realtor_name"/>
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

Example 8.9. <join_column> with referencedTablePath usage

This advanced example shows how to use the referencedTablePath attribute within one-to-many and many-to-many relationships to join to a grandparent table.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="example" 
    name="join_column_referencedTablePath_example">

    <report id="student" name="Student Report">
        <export_config defaultColumns="id,fname,lname"/>
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer"/>
            <column id="fname" name="first_name" displayName="First Name"/>
            <column id="lname" name="last_name" displayName="Last Name"/>
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_class">
                    <join_columns>
                        <join_column columnName="student_id" 
                           referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="class_id" 
                           referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table id="class" displayName="Class" name="class_info.class">
                    <column id="id" name="id" displayName="ID" export="false"/> 
                    <column id="name" name="name" displayName="Name"/> 
                    <column id="teacher" name="teacher" displayName="Teacher"/> 
                    <relationship join="left" cardinality="many">
                        <join_column columnName="student_id" 
                                     referencedColumnName="id"
                                     referencedTablePath="/student" />
                        <join_column columnName="class_id" 
                                     referencedColumnName="id" />
                        <table id="student_assessment" displayName="Student Assessment" 
                            name="class_info.student_assessment">
                            <column id="assessment" name="assessment" /> 
                        </table>
                    </relationship>
                    <relationship join="left" cardinality="many">
                        <join_table name="class_info.student_class_group_project">
                            <join_columns>
                                <join_column columnName="student_id" 
                                   referencedColumnName="id" 
                                   referencedTablePath="/student"/>
                                <join_column columnName="class_id" 
                                   referencedColumnName="id" />
                            </join_columns>
                            <inverse_join_columns>
                                <join_column columnName="group_project_id" 
                                   referencedColumnName="id" />
                           </inverse_join_columns>
                        </join_table>
                        <table id="group_project" displayName="Group Project" 
                            name="class_info.group_project">
                            <column id="name" name="name" /> 
                            <column id="grade" name="grade" /> 
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

Sending the following export URL to this report:

/[context]/pullreports/catalog/example/report/student/export?columns=/class/student_assessment

Results in the following SQL joins:

class_info.student as t0 
left join class_info.student_class as t1 on t0.id = t1.student_id 
left join class_info.class as t2 on t1.class_id = t2.id
left join class_info.student_assessment as t3 on t3.student_id = t0.id and t3.class_id = t2.id


Note

The names specified by the columnName and referencedColumnName attributes are database column names and not <column> ids. Furthermore, it is not required that the specified columns be included in the referenced <table> elements as <column>s.

Parents

<relationship>
<join_columns>
<inverse_join_columns>

Attributes

columnName

The name of the foreign key column. The table which contains the column depends on the nature of the relationship.

If this <join_column> has an immediate <relationship> parent with a cardinality of many, the <relationship> is a one-to-many and the foreign key column is on the child <table> of the relationship.

If this <join_column> has an immediate <relationship> parent with a cardinality of one, the <relationship> is a many-to-one or one-to-one and the foreign key column is on the parent <table> of the relationship.

If this <join_column> has an immediate <join_columns> or <inverse_join_columns> parent within a <join_table>, the <relationship> is a many-to-many and the foreign key column is on the join table.

referencedColumnName

Then name of the column referenced by this foreign key. The table which contains the column depends on the nature of the relationship.

If this <join_column> has an immediate <relationship> parent with a cardinality of many, the <relationship> is a one-to-many and the referenced column is on the parent <table> of the relationship or the table specified by referencedTablePath if present.

If this <join_column> has an immediate <relationship> parent with a cardinality of one, the <relationship> is a many-to-one or one-to-one and the referenced column is on the child <table> of the relationship.

If this <join_column> has an immediate <join_columns> parent within a <join_table>, the <relationship> is a many-to-many and the referenced column is on the parent <table> of the relationship or the table specified by referencedTablePath if present.

If this <join_column> has an immediate <inverse_join_columns> parent within a <join_table>, the <relationship> is a many-to-many and the referenced column is on the child <table> of the relationship.

referencedTablePath (Optional)

The table resource path to the <table> or <table_ref> which contains the referencedColumnName column. The referencedTablePath attribute allows joins to a table higher in the relationship hierarchy than the immediate relationship parent table - such as a grandparent table.

There are three rules governing the referencedTablePath value.

  • Rule 1: The table resource path must reference a table equal to or higher in the path hierarchy than the parent table of the encompassing <relationship> in order to guarantee that the table will be present in the export SQL query. For example, if the <join_column> which specifies the referencedTablePath exists within a <relationship> of a <table> with path /foo/bar, then valid paths are /foo and /foo/bar but not /foo/baz.

  • Rule 2: Unlike other table resource paths, the base table of the report may not be excluded from the path. For example, if the base table of the report has id="foo", than the first path element must be /foo.

  • Rule 3: The referencedTablePath attribute is only permitted if the <join_column> is a direct child of a <relationship> of cardinality="many" or a direct child of a <join_columns>.

8.14. <join_columns>

Contains the foreign key <join_column>s which reference the parent <table> of the many-to-many <relationship>.

Parents

<join_table>

Children

<join_column>+

8.15. <join_table>

Specifies the indirection table in a many-to-many <relationship>.

Children

<join_columns>
<inverse_join_columns>

Parents

<relationship>

Attributes

name

The name of the indirection table. The table name may be prefixed with the schema name if necessary. For example, schema_name.table_name.

8.16. <kml>

Required configuration element for the parent <report> to support the KML format type.

Example 8.10. <kml> usage
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Land Ownership Reports">
    <report id="parcel-information" name="Parcel Information">
        <export_config defaultColumns='id'>
            <!-- "/parcel@parcel-geometry-kml is also valid since the base table path is optional -->
            <kml geometryColumnPath="@parcel-geometry-kml">
                <placemark_name_template>Parcel ID: ${@id}</placemark_name_template>
            </kml>
       </export_config>
        <table id="parcel" displayName="Parcel" name="parcel"> 
            <column id="id" name="id" displayName="Parcel ID" paramType="java.lang.Integer"/>
            <column id="parcel-geometry-kml" name="geom_kml" displayName="KML" />
        </table>
    </report>
</catalog>

In this example, the parcel table could have row:

Table 8.4. parcel table row
idgeom_kml
39<Point>
<coordinates>-105.0994311,40.5673064,0</coordinates>
</Point>


Alternatively, if the database contains geometry information but not raw KML, use an <output_transform> and a KML transform function such as PostGIS' ST_AsKML.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Land Ownership Reports">
    <report id="parcel-information" name="Parcel Information">
        <export_config defaultColumns='id'>
            <kml geometryColumnPath="@parcel-geometry-kml"/>
       </export_config>
        <table id="parcel" displayName="Parcel" name="parcel"> 
            <column id="id" name="id" displayName="Parcel ID" paramType="java.lang.Integer"/>
            <column id="parcel-geometry-kml" name="geom" displayName="KML" >
                <output_transform>ST_AsKML(${this})</output_transform>
            </column>
        </table>
    </report>
</catalog>

Children

<placemark_name_template>?

Parents

<export_config>

Attributes

geometryColumnPath

The column resource path to the column in the report's base table which contains the row's KML Geometry as a java.lang.String. Currently, Pull Reportsonly supports paths to columns in the base table.

8.17. <label_value>

Represents one static label/value pair for use within a <global_label_value_list> or <label_value_list>. Analogous to an HTML <option> element.

The element content represents the "label" and the value attribute holds the value.

Parents

<label_values>
<label_value_group>

Attributes

value

The value of the element. Typically represents a data key value which corresponding to the human readable content of the element.

8.18. <label_values>

A static list of labels and values to be used within a <global_label_value_list> or <label_value_list>.

Example 8.11. <label_values> basic usage

In this example, <label_value> elements are used as direct children of <label_values>. This results in no grouping.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="city" name="Cities of the World">
    <report id="demographic-information" name="Demographics">
        <export_config defaultColumns="name"/>
        <table id="city" displayName="City" name="cities"> 
            <column id="name" name="city_name" displayName="City Name">
               <global_label_value_list_ref ref="city_list"/>
            </column>
            <column id="last_census" name="last_census_total" displayName="Last Population" 
               paramType="java.lang.Integer"/>
            <column id="sq_miles" name="sq_miles" displayName="Area (Sq. Miles)" 
               paramType="java.lang.Double"/>
        </table>
    </report>
    <global_label_value_list id="city_list">
        <label_values>
            <label_value value="BE">Beijing</label_value>
            <label_value value="BO">Boston</label_value>
            <label_value value="CA">Caracas</label_value>
            <label_value value="HK">Hong Kong</label_value>
            <label_value value="LN">London</label_value>
            <label_value value="LA">Los Angeles</label_value>
            <label_value value="NA">Nairobi</label_value>
            <label_value value="ND">New Dehli</label_value>
            <label_value value="NW">New York</label_value>
            <label_value value="PA">Paris</label_value>
            <label_value value="RJ">Rio_de_Janeiro</label_value>
            <label_value value="VA">Vancouver</label_value>
        </label_values>
    </global_label_value_list>
</catalog>

Example 8.12. <label_values> usage with <label_value_group>

In this example, some <label_value> elements are grouped with <label_value_group>s.

Note

Note that even when using <label_value_group>s, it is still permitted to use <label_value> elements as direct children of <label_values>. In this example, the <label_value> Future Space Port on Mars is included outside a <label_value_group>.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="city" name="Cities of the World">
    <report id="demographic-information" name="Demographics">
        <export_config defaultColumns="name"/>
        <table id="city" displayName="City" name="cities"> 
            <column id="name" name="city_name" displayName="City Name">
                <global_label_value_list_ref ref="city_list"/>
            </column>
            <column id="last_census" name="last_census_total" displayName="Last Population" 
               paramType="java.lang.Integer"/>
            <column id="sq_miles" name="sq_miles" displayName="Area (Sq. Miles)" 
               paramType="java.lang.Double"/>
        </table>
    </report>
    <global_label_value_list id="city_list">
        <label_values>
            <label_value_group label="Africa">
                <label_value value="NA">Nairobi</label_value>
            </label_value_group>
            <label_value_group label="Asia">
                <label_value value="BE">Beijing</label_value>
                <label_value value="HK">Hong Kong</label_value>
                <label_value value="ND">New Dehli</label_value>
            </label_value_group>
            <label_value_group label="Europe">
                <label_value value="LN">London</label_value>
                <label_value value="PA">Paris</label_value>
            </label_value_group>
            <label_value_group label="North America">
                <label_value value="BO">Boston</label_value>
                <label_value value="LA">Los Angeles</label_value>
                <label_value value="NW">New York</label_value>
                <label_value value="VA">Vancouver</label_value>
            </label_value_group>
            <label_value_group label="South America">
                <label_value value="CA">Caracas</label_value>
                <label_value value="RJ">Rio_de_Janeiro</label_value>
            </label_value_group>
            <label_value value="MR">Future Space Port on Mars</label_value>
        </label_values>
    </global_label_value_list>
</catalog>

Children

( <label_value>* | <label_value_group>* )

Parents

<global_label_value_list>
<label_value_list>

8.19. <label_value_group>

Groups multiple <label_value> elements into a group defined by this elements' label attribute. Analogous to the HTML <optgroup> element.

Parents

<label_values>

Attributes

label

The label of the group.

8.20. <label_value_list>

Configures a list of labels and corresponding values which represent the possible values of a <column>. Used by the Pull Reports™ Ad Hoc Report Creator to display the available values for a filter parameter in the Export Report REST API.

Children

( <label_value_query> | <label_values> )

Parents

<column>

8.21. <label_value_query>

Configures a SQL query used to retrieve a list of labels and values to be used within a <global_label_value_list> or <label_value_list>.

The element may be used in two ways. If a SQL query is specified as the content of the element, the groupColumn, valueColumn, and labelColumn attributes are used to map the query results to a label value list.

Alternatively, if the element content is empty (e.g. <label_value_query/>), Pull Reports™ will retrieve the list of labels and values as the distinct list of values for the parent <column> with the following template query:

select distinct [column.name] from [table.name] where [column.name] is not null order by 1

In this later case, the groupColumn, valueColumn, and labelColumn attributes are ignored.

In both cases, the query will use the same JNDI DataSource as the enclosing <report>.

Example 8.13. <label_value_query> usage
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="city" name="Cities of the World">
    <report id="demographic-information" name="Demographics">
        <export_config defaultColumns="name"/>
        <table id="city" displayName="City" name="cities"> 
            <column id="name" name="city_name" displayName="City Name">
                <global_label_value_list_ref ref="city_list"/>
            </column>
            <column id="state" name="state_abbrev" displayName="State">
                <label_value_list>
                    <!--  
                    This is the same as specifying 
                    select distinct state_abbrev from city where state_abbrev is null order by 1
                    -->
                    <label_value_query/>
                </label_value_list>
            </column>
            <column id="last_census" name="last_census_total" displayName="Last Population" 
               paramType="java.lang.Integer"/>
            <column id="sq_miles" name="sq_miles" displayName="Area (Sq. Miles)" 
               paramType="java.lang.Double"/>
        </table>
    </report>
    <global_label_value_list id="city_list">
        <label_value_query>
            select city_name as label, city_name as value, continent as group from cities order by continent
        </label_value_query>
    </global_label_value_list>
</catalog>

Parents

<global_label_value_list>
<label_value_list>
groupColumn (Optional)

The name of the column within the SQL query which contains the label/value group. If specified, the query results should be ordered by the groupColumn so as to display within the same HTML <optgroup> within the Pull Reports™ Ad Hoc Report Creator.

labelColumn (Default: label)

The name of the column within the SQL query which contains the label.

valueColumn (Default: value)

The name of the column within the SQL query which contains the value.

8.22. <output_transform>

Template to transform the value of this 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) and the substituted transform used within the report SQL statement instead of the column name.

Table 8.5. 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

Table 8.6. 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

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.

Parents

<column>

Attributes

applyToSort (Default: true)

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

An popular usage of applyToSort is when applying a <output_transform> to transform a Date column into a human readable format (e.g. MM-dd-YYYY) while retaining the sorting characteristics of the raw Date.

8.23. <placemark_name_template>

KML <Placemarks>'s are labeled via their child <name> element. Use the <placemark_name_template> element to customize the Placemark name per result row. The value is a template String which may include any column resource path to a column in the <report>'s base <table> within dollar sign prefixed curly brackets (${...}). Column resource paths within ${...} are substituted with the actual value of the column for each row of the export results at runtime. See the "behind the scenes" note to learn more about template processing.

Example 8.14. <placemark_name_template> basic usage
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Land Ownership Reports">
    <report id="parcel-information" name="Parcel Information">
        <export_config defaultColumns='id,oname'>
            <kml geometryColumnPath="@parcel-geometry-kml">
                <placemark_name_template>${/parcel@oname} - ${/parcel@zip} (ID: ${/parcel@id})</placemark_name_template>
            </kml>
       </export_config>
        <table id="parcel" displayName="Parcel" name="parcel"> 
            <column id="id" name="id" displayName="Parcel ID" paramType="java.lang.Integer"/>
            <column id="oname" name="owner_name" displayName="Owner Name"/>
            <column id="street" name="street_address" displayName="Street" />
            <column id="state" name="state" displayName="State" />
            <column id="zip" name="zip" displayName="Zip" />
            <column id="parcel-geometry-kml" name="geom_kml" displayName="KML" />
        </table>
    </report>
</catalog>

Each Placemark in the returned KML will be named:

[Owner Name] - [ZIP] (ID: [ID])

For instance, if the parcel table has rows:

Table 8.7. parcel table row
idowner_namestreet_addressstatezipgeom_kml
39Carrie Franklin49 West SheelyVermont98833...
40Marjo Hoff104 JuniperVermont98833...

Then the Placemark name values for the rows will be:

Carrie Franklin - 98833 (ID: 39)
Marjo Hoff - 98833 (ID: 40)

Figure 8.1. Two KML Placemarks with a custom name
Two KML Placemarks with a custom name


Example 8.15. Default KML <Placemark> name if no <placemark_name_template> specified
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Land Ownership Reports">
    <report id="parcel-information" name="Parcel Information">
       <export_config defaultColumns='id,oname'>
            <kml geometryColumnPath="@parcel-geometry-kml"/>
       </export_config>
        <table id="parcel" displayName="Parcel" name="parcel"> 
            <column id="id" name="id" displayName="Parcel ID" paramType="java.lang.Integer"/>
            <column id="oname" name="owner_name" displayName="Owner Name"/>
            <column id="street" name="street_address" displayName="Street" />
            <column id="state" name="state" displayName="State" />
            <column id="zip" name="zip" displayName="Zip" />
            <column id="parcel-geometry-kml" name="geom_kml" displayName="KML" />
        </table>
    </report>
</catalog>

Each Placemark in the returned KML will be named with the following, default template:

[Report Name] Geometry

In this case:

Parcel Information Geometry

Figure 8.2. Two KML Placemarks with the default name
Two KML Placemarks with the default name


Example 8.16. Using a static method and Groovy operators in a <Placemark> name

In addition to simple substitution of column values, the placemark_name_template may contain other constructs supported by Groovy's SimpleTemplateEngine. For instance, use Groovy or Java math, logic, or String operators and invoke static method calls such as System.getProperty(String) or org.apache.commons.lang3.StringUtils.substring(String, int). The later example requires the commons-lang3.jar on the JVM classpath.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Land Ownership Reports">
    <report id="parcel-information" name="Parcel Information">
        <export_config defaultColumns='id,oname'>
            <!--  
            The placemark_name_template abbreviates owner name to 20 characters
            and appends the sale value of the parcel expressed in 1000's of dollars.
            The Groovy elvis operator, ?:, is used to transform @oname to the empty 
            String, '', and @saleValue to 0 if either value is null for a particular row.
             -->
            <kml geometryColumnPath="@parcel-geometry-kml">
                <placemark_name_template>
                    ${org.apache.commons.lang3.StringUtils.abbreviate(@oname?:'',20)} : \$${(@saleValue?:0) / 1000}K
                </placemark_name_template>
            </kml>
       </export_config>
        <table id="parcel" displayName="Parcel" name="parcel"> 
            <column id="id" name="id" displayName="Parcel ID" paramType="java.lang.Integer"/>
            <column id="oname" name="owner_name" displayName="Owner Name"/>
            <column id="street" name="street_address" displayName="Street" />
            <column id="state" name="state" displayName="State" />
            <column id="zip" name="zip" displayName="Zip" />
            <column id="saleValue" name="sale" displayName="Sale Value" paramType="java.lang.Integer"/>
            <column id="parcel-geometry-kml" name="geom_kml" displayName="KML" />
        </table>
    </report>
</catalog>

<placemark_name_template>: behind the scenes

<placemark_name_template> values are processed into Groovy GStrings and filled by the Groovy SimpleTemplateEngine for each row of results in the Export Report service.

However, since column resource paths within ${...} expressions are not valid Groovy variable names, each ${...} expression within the template is transformed in two steps:

  1. Column resource paths to base table columns which contain the table id are simplified to only refer to the column id. For example: ${/baseTableId@columnId} becomes ${@columnId}

  2. All column id references have their @ character substituted with two underscore (__) characters. For example: ${@columnId} becomes ${__columnId}

In this way, a <placemark_name_template> of

${/account@id}: ${@full_name} (${@created_date})

becomes:

${__id}: ${__full_name} (${__created_date})

Then, at report fill time, each value of the base table result row is added to a java.util.Map keyed by '__' prefixed column ids. The SimpleTemplateEngine uses this Map to fill the processed <placemark_name_template> and create each <Placemarks> <name>

Parents

<kml>

8.24. <pre_filter>

Filter statement to apply to all queries against the encompassing <table> in the Export Report REST API. <pre_filter>s are specified similarly to Export Report REST API filter HTTP parameters except without the table path preceding the '@' symbol. The pattern is:

@[ColumnId] [Operator] (Values)?

Where ColumnId must reference a <column> id attribute of the encompassing <table> or a <column> of a referenced <column_group>.

Example 8.17. <pre_filter> usage
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Class Reports">
    <report id="student-information" name="Student Information">
        <export_config defaultColumns='id,name'/>
        <table id="student" displayName="Student Details" name="student_details"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer"/>
            <column id="name" name="student_name" displayName="Student Name"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
            <column id="active" name="active" displayName="Active" paramType="java.lang.Boolean"/>
            <pre_filter>@active = true</pre_filter>
            <pre_filter>@bdate > '2001-11-02'</pre_filter>
            <relationship join="inner" cardinality="many">
                <join_column columnName="student_id" 
                             referencedColumnName="id" />
                <table id="grade" displayName="Grade" name="class_info.grade">
                    <column id="id" name="id" displayName="Grade ID" paramType="java.lang.Integer"/>
                    <column id="grade" name="grade" displayName="Grade"/> 
                    <column_group_ref ref="example_group"/>
                    <pre_filter>@year is not null</pre_filter>
                </table>
            </relationship>
        </table>
    </report>
    <!-- Demonstrates that <pre_filter>s may reference a <column> within a <column_group> -->
    <column_group id="example_group">
        <column id="year" name="year" displayName="Year"/> 
    </column_group>
</catalog>

Parents

<table>
<table_ref>

8.25. <relationship>

Specifies a join relationship between two <table>s. <relationship>s joins are always directional from the parent <table> of the <relationship> to the child <table> of the <relationship>. <relationship> elements may be nested indefinitely to support deep joining within a complex relational graph. The table resource path documentation explains how nested <relationship>s are referenced within the Export Report REST API.

Example 8.18. <relationship> usage
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="example" name="join_column_example">
    <report id="home" name="Home Report">
        <export_config defaultColumns='id'/>
        <table id="home" name="realestate.home" displayName="Home"> 
            <column id="id" name="id" paramType="java.lang.Integer"/>
            <column id="address_id" name="address_id" paramType="java.lang.Integer"/>
            <!-- A Home has One to Many Past Sales 

            SQL: realestate.home left outer join realestate.sale 
                     on realestate.sale.home_id = realestate.home.id -->
            <relationship join="left" cardinality="many">
                <join_column columnName="home_id" referencedColumnName="id" />
                <table id="sale" displayName="Sale" name="realestate.sale">
                    <column id="sale_id" name="sale_id" paramType="java.lang.Integer"/>
                    <column id="home_id" name="home_id" paramType="java.lang.Integer"/>
                    <column id="amount" name="amount" paramType="java.lang.Double"/>
                </table>
            </relationship>
            <!-- A Home has One to One Addresses

            SQL: realestate.home inner join realestate.address 
                     on realestate.address.aid = realestate.home.address_id -->
            <relationship join="inner" cardinality="one">
                <join_column columnName="address_id" referencedColumnName="aid" />
                <table id="address" displayName="Address" name="realestate.address">
                    <column id="aid" name="id" paramType="java.lang.Integer"/>
                    <column id="street" name="street" /> 
                </table>
            </relationship>
            <!-- A Home has Many to Many Owners

            SQL: realestate.home left outer join realestate.home_owner 
                     on realestate.home_owner.home_id = realestate.home.id
                 left outer join realestate.owner 
                     on realestate.home_owner.owner_id = realestate.owner.oid -->
            <relationship cardinality="many">
                <join_table name="realestate.home_owner">
                    <join_columns>
                        <join_column columnName="home_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="owner_id" referencedColumnName="oid" />
                   </inverse_join_columns>
                </join_table>
                <table id="owner" displayName="Owner" name="realestate.owner">
                    <column id="oid" name="id" paramType="java.lang.Integer"/>
                    <column id="first_name" name="first_name"/> 
                    <column id="last_name" name="last_name"/> 
                    <!-- An owner has a many-to-one relationship with a realtor. This 
                     This example demonstrates a compound key with two <join_column>s.

                     SQL: realestate.owner left outer join realestate.owner_realtor 
                            on realestate.owner.first_name = realestate.owner_realtor.o_first_name and
                               realestate.owner.last_name = realestate.owner_realtor.o_last_name -->
                    <relationship join="left" cardinality="one">
                        <join_column columnName="first_name" referencedColumnName="o_first_name" />
                        <join_column columnName="last_name" referencedColumnName="o_last_name" />
                        <table id="realtor" displayName="Realtor" name="realestate.owner_realtor">
                            <column id="owner_first_name" name="ofirst_name"/>
                            <column id="owner_last_name" name="olast_name"/>
                            <column id="realtor_name" name="realtor_name"/>
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

Pull Reports™ and the Java Persistence API

Because of the similarity in purpose between <relationship>s and object relational mapping (ORM) technology, the Pull Reports™ Schema vocabulary as it relates to table joins is intentionally similar to the Java Persistence API (JPA) mapping vocabulary.

Children

(<join_column>+ | <join_table>)
(<table> | <table_ref>)

Parents

<table>

Attributes

cardinality

Specifies the cardinality of the join between the parent <table> of the <relationship> and the child <table> of the <relationship>. Permitted values are one or many.

join (default: left)

Specifies the join type. Permitted values are left or inner. right joins are not supported.

8.26. <report>

Configures one Pull Report. A report consists of a exactly one <table> or <table_ref> element that is the base <table> of the report.

Example 8.19. <report> usage with <table>
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Class Reports">
    <report id="class-information" name="Class Information">
        <description>A simple report with one base &lt;table&gt;.</description>
        <table id="cd" displayName="Class Details" name="class_details"> 
            <column id="id" name="id" displayName="Class ID" paramType="integer"/>
            <column id="teacher" name="teacher_name" displayName="Teacher Name" paramType="string"/>
        </table>
    </report>
</catalog>

Example 8.20. <report> usage with <table_ref>
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Class Reports">
    <report id="class-information" name="Class Information">
        <description>A simple report with one base &lt;table_ref&gt;.</description>
        <table_ref id="cd" ref="global_cd"/>
    </report>
    <table id="global_cd" displayName="Class Details" name="class_details"> 
        <column id="id" name="id" displayName="Class ID" paramType="integer"/>
        <column id="teacher" name="teacher_name" displayName="Teacher Name" paramType="string"/>
    </table>
</catalog>

Example 8.21. Multiple <report> usage
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Class Reports">
<report id="class-information" name="Class Information">
...
</report>
<report id="student-information" name="Student Information">
...
</report>
<report id="school-budget" name="School Budget">
...
</report>
</catalog>

Children

<description>?
<export_config>?
<table> | <table_ref>

Parents

<report>

Attributes

id

The unique id of the report within the parent <catalog> This id forms part of the Export Report REST API since reports are identified by their id. For example:

<report id="salary_summary" ...>

creates REST endpoint:

/[context]/pullreports/catalog/[catalog id]/report/salary_summary

Report id's may be composed of alphabetical characters, digits, or the - and _ characters.

name

The human readable name of the report.

8.27. <subquery>

The <subquery> element specifies a query to represent the parent <table> within the Export Report REST API SQL from clause instead of the parent <table>'s name attribute. The <subquery> value may be dynamically generated through the use of a Groovy template or factory class.

Example 8.22. Basic <subquery> usage

To demonstrate <subquery> element usage, take with this simple XML Catalog file which specifies a base <table> of order containing two <column>s: item_number and item_name.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="customer" name="Customer Reports">
    <report id="order" name="Customer Orders">
        <table id="order" name="cust_order" displayName="Order"> 
            <column id="item_number" name="item_number" displayName="Item Number" paramType="java.lang.Long"/>
            <column id="item_name" name="item_name" displayName="Item Name"/>
        </table>
    </report>
</catalog>

In this example, the following HTTP request to the Export Report REST API results in the following SQL query.

/[context]/pullreports/catalog/customer/report/order/export

select t1.item_number,t1.item_name from cust_order t1

Using a <subquery> permits more flexible query definition. For example, if the canceled_orders table contains those orders which are canceled, the following XML Catalog file excludes the canceled orders from the export results.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="customer" name="Customer Reports">
    <report id="order" name="Customer Orders">
        <table id="order" displayName="Order"> 
            <subquery>
            select o.item_number,o.item_name 
            from cust_order o left outer join order_canceled oc on o.id = oc.id
            where oc.id is null
            </subquery>
            <column id="item_number" name="item_number" displayName="Item Number" paramType="java.lang.Long"/>
            <column id="item_name" name="item_name" displayName="Item Name"/>
        </table>
    </report>
</catalog>

Note

The <subquery> select clause must contain all the <column> children of the parent <table>.

Now, the same HTTP request to the Export Report REST API results in the following SQL query. Note the embedded subquery in the SQL from clause.

/[context]/pullreports/catalog/customer/report/order/export

select t1.item_number,t1.item_name 
from 
(select o.item_number,o.item_name 
 from cust_order o left outer join order_canceled oc on o.id = oc.id
 where oc.id is null) t1

Example 8.23. Adding a <description> to a <subquery>

Adding a child <description> element to a <subquery> will output that description within the Export Report REST API when the parent <table> is included in the columns parameter. A child <description> element is typically used to communicate the subquery behavior to Export Report REST API users.

See the format parameter documentation for how each export format outputs <subquery> descriptions.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="customer" name="Customer Reports">
    <report id="order" name="Customer Orders">
        <table id="order" displayName="Order"> 
            <subquery>
                <description>Only orders that are not canceled are present in the export result.</description>
                select o.item_number,o.item_name 
                from cust_order o left outer join order_canceled oc on o.id = oc.id
                where oc.id is null
            </subquery>
            <column id="item_number" name="item_number" displayName="Item Number" paramType="java.lang.Long"/>
            <column id="item_name" name="item_name" displayName="Item Name"/>
        </table>
    </report>
</catalog>

Example 8.24. Dynamically creating the <subquery> with a Groovy Template

The <subquery> element may contain any construct supported by Groovy's SimpleTemplateEngine. For instance, expressions embedded within the <subquery> may use Groovy or Java operators and invoke static method calls such as java.lang.System.getProperty(String). Additionally, the HttpServletRequest associated with the Export Report REST API request is available as a the request template binding variable. This allows the <subquery> value to be responsive to HttpServletRequest state.

In the following <subquery> example, the subquery uses both a java.lang.System property and an HttpSession attribute. The example assumes both the System property and HttpSession attribute are set by external-to-Pull Reports™ configuration. For instance, java.lang.System properties may be set by passing a -D prefixed argument to the JVM on start up. HttpSessionListeners are commonly used to set HttpSession attributes.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="customer" name="Customer Reports">
    <report id="order" name="Customer Orders">
        <table id="order" displayName="Order"> 
            <subquery>
            select o.item_number,o.item_name 
            from cust_order o 
            where 
            o.item_date > '${System.getProperty('min.item.date')}'
            and
            o.user_id = ${request.session.getAttribute('user.id')}
            </subquery>
            <column id="item_number" name="item_number" displayName="Item Number" paramType="java.lang.Long"/>
            <column id="item_name" name="item_name" displayName="Item Name"/>
        </table>
    </report>
</catalog>

Assuming the JVM was passed -Dmin.item.date=1976-04-23 on start up and the user.id session attribute has a value of 100, the following request to the Export Report REST API results in the following SQL query.

/[context]/pullreports/catalog/customer/report/order/export

select t1.item_number,t1.item_name 
from 
(select o.item_number,o.item_name 
from cust_order o 
where 
o.item_date > '1976-04-23'
and
o.user_id = 100) t1
Example 8.25. Dynamically creating the <subquery> with a factory class

If a Groovy template is not expressive enough to create the <subquery> value, use the factory attribute to specified the fully qualified name of a Java class to create the <subquery>.

The requirements of the factory class are:

  1. The class must have a public constructor which takes a HttpServletRequest as its single argument.

  2. The class must have a public, no-argument method called makeSubquery which returns a java.lang.String which is the subquery.

  3. If the makeSubquery method returns null, Pull Reports™ will use the value of the parent <table>s name attribute to represent the table within the SQL from clause. In this case, Pull Reports™ will thrown an exception if the parent <table>'s name attribute is missing.

  4. The class may optionally have a public, no-argument method called makeDescription which returns a java.lang.String which is the subquery description.

In the following <subquery> example, the <table> element specifies a name attribute that will be used within the Export Report REST API SQL from clause if the makeSubquery method of the factory class returns null.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="customer" name="Customer Reports">
    <report id="order" name="Customer Orders">
        <table id="order" name='cust_order' displayName="Order"> 
            <subquery factory='org.acme.OrderSubQueryFactory'/>
            <column id="item_number" name="item_number" displayName="Item Number" paramType="java.lang.Long"/>
            <column id="item_name" name="item_name" displayName="Item Name"/>
        </table>
    </report>
</catalog>
package org.acme;

import javax.servlet.http.HttpServletRequest;            

public class OrderSubQueryFactory {
    private HttpServletRequest request;

    public OrderSubQueryFactory(HttpServletRequest request){
        this.request=request;
    }

    public String makeDescription(){
        if (request.isUserInRole("SUPERUSER"){
            return null;
        } else {
            return "The result is restricted to your orders.";
        }
    }

    public String makeSubquery(){
        if (request.isUserInRole("SUPERUSER"){
            // Super users may see all records. Returning null will use the
            // <table>'s name attribute.
            return null;
        } else {
            return "select o.item_number,o.item_name " +
            "from cust_order o " + 
            "where " + 
            "o.user_id = " + request.getSession().getAttribute("user.id");
        }
    }
}

Parents

<table>

Children

<description>?

Attributes

factory

The fully qualified name of a Java class to create the subquery value. See Dynamically creating the <subquery> with a factory class for a description of the factory class requirements.

If the factory attribute is specified, the <subquery> element may not have content.

8.28. <table>

Represents one relational database table within the report configuration. Every <report> has exactly one <table> or <table_ref> immediate child called the base table. <table>s can also be joined to one another via <relationship>s.

A <table> maps to one TABLE (or VIEW or MATERIALIZED VIEW) in the underlying relational database via the name attribute. However, a <table> need not include all of the real TABLES's columns and a single database TABLE can be referenced in multiple <table> elements.

Global tables: A <table> declared as a direct child of a <catalog> is a global table and allows one <table> configuration to be used multiple times within a <catalog>. Global <table>s are referenced via <table_ref>s. For a detailed example of global table usage, see Pull Reports™ XML Catalog file tutorial 3.

Example 8.26. <table> usage
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="example" name="table_example">
    <report id="animals" name="Zoo Animals Report">
        <!-- base table -->
        <table id="animal" name="animal" displayName="Animal"> 
            <description>A simple description of the animal table.</description>
            <column id="id" name="id" paramType="java.lang.Integer"/>
            <column id="name" name="name"/>
            <relationship join="inner" cardinality="one">
                <join_column columnName="trainer_id" referencedColumnName="emp_id" />
                <table_ref id="main_trainer" ref="trainer">
                    <!-- <relationship> defined within a table_ref. This <relationship> is in addition
                    to any relationships defined within the referenced, global, "trainer" <table>. -->
                    <relationship join="left" cardinality="one">
                        <join_column columnName="trainer_id" referencedColumnName="emp_id" />
                        <table id="technique" name="technique" displayName="Training Technique">
                            <column id="name" name="technique_name" />
                        </table>
                    </relationship>
                </table_ref>
            </relationship>
            <relationship join="left" cardinality="many">
                <join_column columnName="animal_id" referencedColumnName="id" />
                <!-- table within a child relationship -->
                <table id="feedings" name="feeding" displayName="Feedings">
                    <column id="feed" name="feed_type" displayName="Feed Type"/>
                    <column id="amount" name="amount" paramType="java.lang.Double"/>
                </table>
            </relationship>
            <relationship cardinality="many">
                <join_table name="secondary_trainer">
                    <join_columns>
                        <join_column columnName="animal_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="trainer_id" referencedColumnName="emp_id" />
                   </inverse_join_columns>
                </join_table>
                <table_ref id="secondary_trainer" ref="trainer" displayName="Secondary Trainer"/>
            </relationship>
        </table>
    </report>
    <!-- global table used twice in the 'animals' report -->
    <table id="trainer" name="employee" displayName="Trainer"> 
        <column id="id" name="emp_id" paramType="java.lang.Integer"/>
        <column id="first_name" name="first_name"/>
        <column id="last_name" name="last_name"/>
    </table>
</catalog>

Children

<description>?
<subquery>?
(<column> | <column_group_ref>)+
<pre_filter>*
<relationship>*

Parents

<catalog>
<relationship>
<report>

Attributes

id

Specifies the id of the <table> within table resource paths statements.

If the <table> is a global table, the id must be unique within the parent <catalog>. Additionally, <table>s and <table_ref>s elements with the same <table> grand-parent (their parent <relationship>'s parent <table>) must have unique id values. This requirement ensures that each table resource path uniquely identifies one <table> or <table_ref> within a <report>.

displayName

The human readable table name.

name (Optional)

The name of the table in the database. This name may be preceded with a schema name. For instance, schema_name.table_name.

The name attribute is required unless the parent <table> has a child <subquery> element.

8.29. <table_ref>

Reference to a global <table>. May be used in place of a <table> element as a <report>'s base table or <relationship>'s join table. Use <table_ref>s to reuse configuration within one <catalog>.

<table_ref>s may define their own <relationship>s and <pre_filter>s which are added to any <relationship>s and <pre_filter>s of the referenced global table.

If a <table_ref> defines a child <description> element, its value overrides any description defined in the referenced global table.

Example 8.27. <table_ref> usage
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="example" name="table_example">
    <report id="animals" name="Zoo Animals Report">
        <!-- base table -->
        <table id="animal" name="animal" displayName="Animal"> 
            <description>A simple description of the animal table.</description>
            <column id="id" name="id" paramType="java.lang.Integer"/>
            <column id="name" name="name"/>
            <relationship join="inner" cardinality="one">
                <join_column columnName="trainer_id" referencedColumnName="emp_id" />
                <table_ref id="main_trainer" ref="trainer">
                    <!-- <relationship> defined within a table_ref. This <relationship> is in addition
                    to any relationships defined within the referenced, global, "trainer" <table>. -->
                    <relationship join="left" cardinality="one">
                        <join_column columnName="trainer_id" referencedColumnName="emp_id" />
                        <table id="technique" name="technique" displayName="Training Technique">
                            <column id="name" name="technique_name" />
                        </table>
                    </relationship>
                </table_ref>
            </relationship>
            <relationship join="left" cardinality="many">
                <join_column columnName="animal_id" referencedColumnName="id" />
                <!-- table within a child relationship -->
                <table id="feedings" name="feeding" displayName="Feedings">
                    <column id="feed" name="feed_type" displayName="Feed Type"/>
                    <column id="amount" name="amount" paramType="java.lang.Double"/>
                </table>
            </relationship>
            <relationship cardinality="many">
                <join_table name="secondary_trainer">
                    <join_columns>
                        <join_column columnName="animal_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="trainer_id" referencedColumnName="emp_id" />
                   </inverse_join_columns>
                </join_table>
                <table_ref id="secondary_trainer" ref="trainer" displayName="Secondary Trainer"/>
            </relationship>
        </table>
    </report>
    <!-- global table used twice in the 'animals' report -->
    <table id="trainer" name="employee" displayName="Trainer"> 
        <column id="id" name="emp_id" paramType="java.lang.Integer"/>
        <column id="first_name" name="first_name"/>
        <column id="last_name" name="last_name"/>
    </table>
</catalog>

Warning: <relationship> table id collision

An error results if any child <relationship> from the referenced global table has a child <table> with an id value matching any <table> id of a <table_ref>'s <relationship>s. The error occurs because child <relationship> <table>s of the same parent must have identical <table> ids in order to keep table resource paths unique.

This means the following will error when parsed:

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="id" name="name">
    <report id="test" name="Test">
        <table_ref id="details" displayName="Details" ref="global">
            <relationship cardinality="many">
                <join_table name="foo_bar">
                    <join_columns><join_column columnName="details_id" referencedColumnName="id" /></join_columns>
                    <inverse_join_columns><join_column columnName="same_id" referencedColumnName="id" /></inverse_join_columns>
                </join_table>
                <table id="same" displayName="Same" name="same">
                    <column id="id" name="id" displayName="ID"/> 
                </table>
            </relationship>
        </table_ref>
    </report>
    <table id="global" displayName="Global" name="global">
        <column id="details_id" name="id" displayName="ID"/> 
        <relationship cardinality="many">
            <join_table name="foo_bar2">
                <join_columns><join_column columnName="details_id" referencedColumnName="d" /></join_columns>
                <inverse_join_columns><join_column columnName="same_2_id" referencedColumnName="id" /></inverse_join_columns>
            </join_table>
            <!--  Invalid: Even though the two relationship tables reference database tables with different names, 
                  "same" vs "same_2", the fact that the <table> id attributes are both "same" causes an error. -->
            <table id="same" displayName="Same" name="same_2">
                <column id="id" name="id" displayName="ID"/> 
            </table>
        </relationship>
    </table>
</catalog>

Children

<description>
<pre_filter>
<relationship>

Parents

<relationship>
<report>

Attributes

id

Specifies the id of the <table_ref> within table resource paths statements.

<table>s and <table_ref>s elements with the same <table> grand-parent (their parent <relationship>'s parent <table>) must have unique id values. This requirement ensures that each table resource path uniquely identifies one <table> or <table_ref> within a <report>.

displayName (Optional)

The human readable name. If not specified, uses the displayName attribute of the referenced global <table>.

ref

Specifies the id of the referenced global <table>.

8.30. <url_template>

Template for a URL to associate with the column value in the Export Report REST API. Useful for creating drill-through reports. The URL association depends on the format of the exported report. For instance, the htmltable format type outputs the URL as a HTML anchor (<a>) element surrounding the column value while the csv format type outputs the URL as an additional column. See the Export Report REST API chapter for information.

8.30.1. Referencing a column value

The value of the <url_template> element is a String which may include references to other column values via dollar sign prefixed curly brackets (${...}). Each reference will be substituted with the actual value of the column for each row of the export results at runtime. Only references to columns which are guaranteed to be present at report runtime are permitted. These include:

  • Any base table columns:  Any column resource path to a column in the report's base table. For example: ${/baseTableId@name} or simply ${@name} assuming a base table of id="baseTableId" which has a <column> with id="name".

  • Any <column> of the same parent <table> as the <url_template> Any <column> id in the parent <table> of the <url_template>'s parent <column> prefixed with @@. For example: ${@@type} assuming a <column> with id="type" in the parent <table>. In this way, the second @ symbol is a placeholder for the parent <table> resource path.

<url_template>s in the report's base table, @ or @@?

When creating <url_template>s for <column>s within the <report>'s base table, refer to base table columns prefixed with @ or @@. The former always refers to columns within the base table, the later always refers to columns within the parent table - which is the base table in this case. The choice between the two prefixes is unimportant unless the base table is a global table which is itself referenced by base and non-base <table_ref>s. In this case, prefix column references in the global table's <url_template>s with @@ to have them refer to <column> idss within the global table.

Requiring a column to be non-null:  To only export a URL when a referenced column is non-null, add ! after the @ or @@ characters in the column reference. For example, in this <column> definition, the URL value is only exported if the @addressId column is non null. Otherwise, the value of the @addressStreet column is exported as is.

<column id="addressStreet" name="address_street" displayName="Street Address"> 
    <url_template>/addressLookup?id=${@!addressId}</url_template>
</column>

8.30.2. Additional references

In addition to column value references, the following variable references may be used with within <url_template> expressions.

Table 8.8. Available <url_template> variable references
ReferenceDescription
requestThe HttpServletRequest associated with the Export Report REST API request.

8.30.3. Exceptions when processing <url_template> values

Since <url_template> values are filled for each result row, any java.lang.Exception thrown when processing the template will disrupt the HTTP response stream and cause an invalid response. For this reason, Pull Reports™ catches all <url_template> processing Exceptions, logs the Exception, and outputs "ERROR. Check log." instead of the true column value.

In order to prevent repetitive log entries, Pull Reports™ logs only one <url_template> processing Exception per export request.

8.30.4. <url_template> examples

Example 8.28. <url_template> usage

This example demonstrates two <url_template> elements, one associated with the <report>'s base table and one associated with a child <relationship>. The former demonstrating a hyperlink into an external URL and the later a hyperlink into another Pull Report within the same <catalog>.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class_info" name="Class Information">
    <report id="student" name="Student Report" >
        <export_config defaultColumns='id,complete_name'/>
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer"/>
            <column id="complete_name" name="complete_name" displayName="Complete Name">
                <!--  ${@id} references column class_info.student.id. ${@@id} would also be valid. -->
                <url_template>https://www.school-district.com/rest/api/student/${@id}.html</url_template>
            </column>
            <relationship join="left" cardinality="many">
                <join_column columnName="student_id" referencedColumnName="id" />
                <table id="grade" displayName="Grade" name="class_info.grade">
                    <column id="id" name="id" displayName="Grade ID" paramType="java.lang.Integer" export="false"/>
                    <column id="grade" name="grade" displayName="Grade"/> 
                    <column id="year" name="year" displayName="Year" paramType="java.lang.Integer"> 
                        <!--  ${@!id} references column class_info.student.id. ${/student@!id} is also valid. 
                              ${@@!year} references column class_info.grade.year 
                              
                              Since both ${@!id} and ${@@!year} contain the "!" character after "@", the
                              value of the class_info.student.id and class_info.student.complete_name
                              column must be non-null for Pull Reports to output the URL. 
                              
                              Although it is not used in this <url_template> example,
                              ${@@id} would reference class_info.grade.id 
                        -->
                        <url_template><![CDATA[
                        //${request.serverName}/${request.contextPath}/pullreports/catalog/class_info/report/evidence/export?format=htmltable&columns=%2Fevidence&filter=%40student_id%3D${@!id}&filter=%2Fevidence%40year%3D${@@!year}
                        ]]></url_template>
                    </column>
                </table>
            </relationship>
        </table>
    </report>
    <report id="evidence" name="Grade Evidence Report" >
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="student_id" name="id" displayName="Student ID" paramType="java.lang.Integer"/>
            <column id="complete_name" name="complete_name" displayName="Complete Name"/>
            <relationship join="inner" cardinality="many">
                <join_column columnName="student_id" referencedColumnName="id" />
                <table id="evidence" displayName="Evidence" name="class_info.grade_evidence">
                    <column id="year" name="year" displayName="Year" paramType="java.lang.Integer"/>
                    <column id="evidence" name="evidence_text" displayName="Evidence"/>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

To demonstrate the actual URL output, start with the <report>'s base, student table with rows:

Table 8.9. student table rows
idcomplete_name
1Sylvia Martinez
2Roger Mullins
3Jackie Besanceney

Then the URL values for the rows will be:

Table 8.10. student table with URL values
idcomplete_namecomplete_name's URL
1Sylvia Martinezhttps://www.school-district.com/rest/api/student/1.html
2Roger Mullinshttps://www.school-district.com/rest/api/student/2.html
3Jackie Besanceneyhttps://www.school-district.com/rest/api/student/3.html

Then assume a grade table with rows:

Table 8.11. grade table rows
idstudent_idgradeyear
1001A2014
1011A-2015
1022B2015

The two tables may be joined via the Export Report REST API URL:

/[context]/catalog/class_info/report/student/export?columns=/grade

And the joined result rows will be:

Table 8.12. student to gradetable left join with <url_template>s applied
idcomplete_namecomplete_name's URLgradeyearyear's URL
1Sylvia Martinezhttps://www.school-district.com /rest/api/student/1.htmlA2014//[serverName]/[context]/pullreports/catalog/class_info/report/evidence/export? format=htmltable &columns=%2Fevidence &filter=%40student_id%3D1 &filter=%2Fevidence%40year%3D2014
1Sylvia Martinezhttps://www.school-district.com /rest/api/student/1.htmlA-2014//[serverName]/[context]/pullreports/catalog/class_info/report/evidence/export? format=htmltable &columns=%2Fevidence &filter=%40student_id%3D1 &filter=%2Fevidence%40year%3D2015
2Roger Mullinshttps://www.school-district.com /rest/api/student/2.htmlB2015//[serverName]/[context]/pullreports/catalog/class_info/report/evidence/export? format=htmltable &columns=%2Fevidence &filter=%40student_id%3D2 &filter=%2Fevidence%40year%3D2015
3Jackie Besanceneyhttps://www.school-district.com /rest/api/student/3.html   


Example 8.29. <url_template> advanced usage

In addition to simple substitution of column values, <url_template>s may contain other constructs supported by Groovy's SimpleTemplateEngine. For instance, use Groovy or Java math, logic, or String operators and invoke static method calls such as java.net.URLEncoder.encode(String,String), System.getProperty(String) or org.apache.commons.lang3.StringUtils.substring(String, int). The last example requires the commons-lang3.jar on the JVM classpath.

The following Pull Reports™ XML Catalog file demonstrates some of these advanced expressions.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="catalog" name="Catalog">
    <report id="report" name="Report" >
        <export_config defaultColumns='id'/>
        <table id="some_table" name="schema.table1" displayName="Table1"> 
            <column id="id" name="id" displayName="ID" paramType="java.lang.Integer"/>
            <column id="date1" name="date1_field" displayName="Date 1" paramType="java.sql.Date"/>
            <column id="string1" name="string1_field" displayName="String 1">
                <!-- Demonstrates use of Groovy '.format(...)' method for formatting java.sql.Dates
                to Strings and URL encoding a parameter with the java.net.URLEncoder.encode(String,String)
                static method call. 
                 -->
                <url_template><![CDATA[
                /foo/bar?param1=${@date1.format('yyyy')}&param2=${URLEncoder.encode(@string1,'UTF-8')}
                ]]></url_template>
            </column>
            <column id="string2" name="string2_field" displayName="String 2">
                <!-- Demonstrates conditional logic with ternary operator -->
                <url_template><![CDATA[
                /foo2/bar2/${@id}${(@date1)?'/' + @data1.format('MM'):''}
                ]]></url_template>
            </column>
            <column id="integer1" name="integer1_field" displayName="Integer 1" paramType="java.lang.Integer"/>
            <relationship join="left" cardinality="many">
                <join_column columnName="ref_id" referencedColumnName="id" />
                <table id="table2" displayName="Table2" name="schema.table2">
                    <column id="code" name="code" displayName="Code"/>
                    <column id="name" name="name" displayName="name">
                        <!-- Demonstrates the static java.lang.System.getProperty(...) method call and 
                        a reference to columns on this relationship table and the <report>'s base table -->
                        <url_template><![CDATA[
                        ${System.getProperty('report.http.protocol')}://www.acme.com/path1?code=${@@code}&date1=${@date1}
                        ]]></url_template>
                    </column>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

<url_template>: behind the scenes

<url_template> values are processed into Groovy GStrings and filled by the Groovy SimpleTemplateEngine for each row of results in the Export Report service.

However, since column resource paths within ${...} expressions are not valid Groovy variable names, each ${...} expression within the template is transformed in two steps:

  1. Column resource paths to base table columns which contain the table id are simplified to only refer to the column id. For example: ${/baseTableId@columnId} becomes ${@columnId}

  2. All column id references to the <report>'s base table have their @ character substituted with two underscore (__) characters. For example: ${@columnId} becomes ${__columnId}

  3. All column id references to a non-base table have their two @@ characters substituted with three underscore (__) characters. For example: ${@@columnId} becomes ${___columnId}

In this way, a <url_template> of

${/account@id}: ${@full_name} (${@@created_date})

becomes:

${__id}: ${__full_name} (${___created_date})

Then, at report fill time, each value of the base table result row is added to a java.util.Map keyed by '__' prefixed column ids. Then, if the <url_template> is a child of a relationship <table> included via the columns parameter, each value the relationship <table> result row is also added to the Map keyed by '___' prefixed column ids. The SimpleTemplateEngine uses this Map to fill the processed <url_template> and create each URL value.

Parents

<column>