Chapter 4. XML Catalog Files

Pull Reports™ are configured via XML documents which define the metadata associated with a report. Pull Reports™ reads report XML files on application start up and presents an web API to query the report.

4.1. Tutorial 1: A basic <report>

Since Pull Reports™ XML Catalog files reference relational database tables, the following example will use a fictional database called class_info which contains data about an Elementary School. We'll start the database with just a single table and expand into complex relationships to demonstrate increasingly advanced Pull Reports™ features.

4.1.1. Choosing the base <table>

To begin, imagine the class_info database contains one table called student which contains information about enrolled students. It is represented by this simple UML class:

The student table will be the single, base <table> of our first report. At least one column (more on this later) from the base table is always exported in the report and all SQL joins to other relationships originate with the base table. In this way, the base table represents the core view into the data model for this report. In this trivial example, student is the only table in the class_info database, so it is the only base table candidate. In more complex databases with multiple tables, create multiple reports with differing base tables to represent different report views. Tutorial 3 demonstrates techniques for configuring multiple reports.

The columns of the student table are largely self-explanatory with the exception of the home_geom column. This column contains the point (i.e. latitude / longitude) location of the student's home address. This point value can be rendered by the Export Report REST API when the format parameter is kml or geojson. Learn more about transforming this column into exportable geometries in Tutorial 2.

Note

The base table of the report (or any reference within this tutorial to the word "table") need not be a relational database table. It may also be a database view or materialized view if supported by the underlying database.

4.1.2. Define the <catalog>

Using a text editor, create a file named class-report-catalog.xml and paste the following:

Example 4.1. class-report-catalog.xml
<?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="firstn,lastn"/>
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
            <column id="firstn" name="first_name" displayName="First Name"/>
            <column id="lastn" name="last_name" displayName="Last Name"/>
            <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
        </table>
    </report>
</catalog>

Note

The name of the Pull Reports™ XML Catalog file does not matter. However, all such Pull Reports™ XML Catalog files must be available to Pull Reports™ on start up.

The root element of every Pull Reports™ XML Catalog file is <catalog>. In this example:

<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="class" name="Class Reports">

A <catalog> groups one to many <report>s under a common URL path identified by the <catalog> id attribute. <report>s within a <catalog> may also share common configuration such as JDBC DataSource or logging configuration. Although a <catalog> may contain many <report>s, a single Pull Reports™ XML Catalog file may contain only one <catalog>. However, it is possible to split a <catalog> with the same id across many Pull Reports™ XML Catalog files.

XML Namespace and Schema Validation:

The xmlns="http://www.pullreports.com/catalog-1.4.0" defines the default namespace of the XML. This namespace maps to the pullreports-catalog-1.4.0.xsd found in the META_INF directory of the downloaded ZIP file and may be used to validate the XML document.

4.1.3. Define the <report>

Each <report> in the <catalog> defines a separate report web service available within the REST API. The URLs to the services reference the <catalog> and <report> id attributes. In this example, our report is available at the following URLs:

/[context]/pullreports/catalog/class/report/student-information

Pull Reports™ Ad Hoc Report Creator user interface which allows a user to view report metadata and select report controls like columns and filters for report export.

/[context]/pullreports/catalog/class/report/student-information.json

Serialized JSON representation of the Pull Reports™ XML Catalog file.

/[context]/pullreports/catalog/class/report/student-information/export

Exports the report. By default, requesting the /export API without additional parameters will return either all columns of the report's base table or just those specified as defaultColumns, if configured, in JSON format.

A more complicated example is the export all students with a birth date after April 23th, 1976 sorted by last name in XML format. For this example, the URL would be (unencoded):

/[context]/pullreports/catalog/class/report/student-information/export?format=xml&filter=@bdate > '1976/04/23'&sort=@lastn

What do the "@" tokens mean?

The @bdate and @lastn references in the query string are column resource paths that reference the student.birth_date and student.is_active columns respectively. Since column and table resource paths are integral to Pull Reports™ configuration and usage, please browse their definition before continuing.

4.1.4. Defining <column>s

Notice that the <report> already contains the one, required base table. In this example, it is the student table. There are also several <column>s to be returned within our report:

...
<report id="student-information" name="Student Information">
    <export_config defaultColumns='firstn,lastn'/>
    <table id="student" name="class_info.student" displayName="Student"> 
        <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
        <column id="firstn" name="first_name" displayName="First Name"/>
        <column id="lastn" name="last_name" displayName="Last Name"/>
        <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
        <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
        <column id="active" name="is_active displayName="Active?" 
            paramType="java.lang.Boolean" export="false"/>
    </table>
</report>
...

The <export_config> element's defaultColumns attribute is a comma separated list of one or more <column> ids from the report's base table - in this case the student table. The Export Report REST API will include at least these columns in the export result if no other columns from the report's base table are specified within the columns parameter. It is customary to set column(s) which uniquely identify each the base table row within the defaultColumns attribute.

It is not required to declare <column> elements for every column of a database table. For instance, this example does not declare a <column> for the last_updated nor home_geom columns. Furthermore, the student.id and is_active columns are defined with export="false". This prevents the student.id and student.is_active values from being included in any Export Report REST API export format but retains the <column>s use for filtering or for use as a template value (see <url_template> and <placemark_name_template>).

Read more about the <column> attributes in the schema reference.

4.1.5. Define a <pre_filter>

In addition to filtering Export Report REST API results via the filter parameter, add <pre_filter> elements to any <table> to configure filters that should always apply to export results. In this example, add a <pre_filter> to the student table to restrict results to active records:

<?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='firstn,lastn'/>
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
            <column id="firstn" name="first_name" displayName="First Name"/>
            <column id="lastn" name="last_name" displayName="Last Name"/>
            <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <pre_filter>@active=true</pre_filter>
        </table>
    </report>
</catalog>

Note

For more complex filter requirements such as restricting the export results based on user access controls or external configuration information, see the <subquery> element.

4.1.6. One-to-many relationship

As is, our report allows a user to query and export the columns of the base, student table. Let's expand our data model and add a one-to-many relationship to a assignment table which holds student assignments. Here is the new UML in which the assignment.student_id column holds the foreign key to student.id:

Data model relationships are configured via <relationship>s. Any <table> may have zero to many child <relationship>s and <relationship>s may be nested to any depth. <table>s within nested <relationship>s may be included in export results via the columns parameter. Here is our new Pull Reports™ XML Catalog file with the assignment table joined to student:

<?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='firstn,lastn'/>
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
            <column id="firstn" name="first_name" displayName="First Name"/>
            <column id="lastn" name="last_name" displayName="Last Name"/>
            <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <pre_filter>@active=true</pre_filter>
            <relationship join="left" cardinality="many">
                <join_column columnName="student_id" referencedColumnName="id" />
                <table id="assignment" displayName="Assignment" name="class_info.assignment">
                    <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
                    <column id="grade" name="grade" displayName="Grade"/> 
                    <column id="name" name="name" displayName="Name"/> 
                </table>
            </relationship>
        </table>
    </report>
</catalog>

Look closely at the elements that define the join relationship between student and assignment:

...
<relationship join="left" cardinality="many">
    <join_column columnName="student_id" referencedColumnName="id" />
    ...
</relationship>
...

The <relationship> is defined as join="left". This ensures that all rows from the student table will be included in the export regardless of whether or not the assignment.student_id column references a student.id. The cardinality="many" defines the relationship as a one-to-many and determines the directionality of the <join_column> columnName and referencedColumnName attributes. The cardinality value is also reflected within the Pull Reports™ Ad Hoc Report Creator to help users understand the relationships within the report data model.

The nested <join_column> defines the foreign key to referenced key relationship columns. It may also be used for many-to-one relationships. See <join_table> to define many-to-many relationships.

Returning to the example export URL, specify the columns parameter to include the <column>s of the assignment table in the export results:

/[context]/pullreports/catalog/class/report/student-information/export?columns=/assignment&filter=@bdate > '1976/04/23'&sort=@lastn

4.1.7. Many-to-one relationship

After adding the assignment table, notice the assignment.class_id column. This column represents a many-to-one relationship from assignment to the referenced class, i.e. "Algebra II", "An introduction to Computer Science", or "Cities in History". Here is the new data model which includes the class table:

The new Pull Reports™ XML Catalog file which includes the class table demonstrates both many-to-one <relationship> configuration and also how <relationship>s can be nested beneath <table> elements to any depth:

<?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="firstn,lastn"/>
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
            <column id="firstn" name="first_name" displayName="First Name"/>
            <column id="lastn" name="last_name" displayName="Last Name"/>
            <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <pre_filter>@active=true</pre_filter>
            <relationship join="left" cardinality="many">
                <join_column columnName="student_id" referencedColumnName="id" />
                <table id="assignment" displayName="Assignment" name="class_info.assignment">
                    <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
                    <column id="grade" name="grade" displayName="Grade"/> 
                    <column id="name" name="name" displayName="Name"/> 
                    <relationship join="inner" cardinality="one">
                        <join_column columnName="class_id" referencedColumnName="id" />
                        <table id="class" displayName="Class" name="class_info.class">
                            <column id="id" name="id" displayName="Class ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Class Name"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

This time, the <relationship> is defined as join="inner" because the data model requires the assignment.class_id column to be not null. Again, the cardinality="one" determines the directionality of the relationship and the definition of the <join_column> columnName and referencedColumnName attributes. Additionally, the class.id column is defined as export="false" to exclude this primary key column from the export results.

Add the columns of the class table and filter to all student assignments within an Algebra class to our export URL like so:

/[context]/pullreports/catalog/class/report/student-information/export?columns=/assignment;/assignment/class&filter=@bdate > '1976/04/23'&filter=/assignment/class@name like '%Algebra%'&sort=@lastn

4.1.8. Many-to-many relationship

The final relationship to add is a many-to-many between a student and their parents within the parent table.

In addition, when adding the new parent table, we'll also add a many-to-many relationship between a parent and their employers represented by the company table.

The new Pull Reports™ XML Catalog file uses two <join_table>s to implement the many-to-many relationships. The student_parent join table is a second <relationship> child of the base, student table and parent_company is within a nested <relationship> of the parent 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="student-information" name="Student Information">
        <export_config defaultColumns="firstn,lastn"/>
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
            <column id="firstn" name="first_name" displayName="First Name"/>
            <column id="lastn" name="last_name" displayName="Last Name"/>
            <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <pre_filter>@active=true</pre_filter>
            <relationship join="left" cardinality="many">
                <join_column columnName="student_id" referencedColumnName="id" />
                <table id="assignment" displayName="Assignment" name="class_info.assignment">
                    <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
                    <column id="grade" name="grade" displayName="Grade"/> 
                    <column id="name" name="name" displayName="Name"/> 
                    <relationship join="inner" cardinality="one">
                        <join_column columnName="class_id" referencedColumnName="id" />
                        <table id="class" displayName="Class" name="class_info.class">
                            <column id="id" name="id" displayName="Class ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Class Name"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table id="parent" displayName="Parent" name="class_info.parent">
                    <column id="id" name="id" displayName="Parent ID" paramType="java.lang.Integer"/>
                    <column id="firstn" name="first_name" displayName="First Name"/> 
                    <column id="lastn" name="last_name" displayName="Last Name"/> 
                    <column id="phone" name="phone" displayName="Phone"/> 
                    <relationship join="left" cardinality="many">
                        <join_table name="class_info.parent_company">
                            <join_columns>
                                <join_column columnName="parent_id" referencedColumnName="id" />
                            </join_columns>
                            <inverse_join_columns>
                                <join_column columnName="company_id" referencedColumnName="id" />
                           </inverse_join_columns>
                        </join_table>
                        <table id="company" displayName="Company" name="class_info.company">
                            <column id="id" name="id" displayName="Company ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Company"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

Although it is possible to add the parent and company tables into the previous export URL via this columns parameter:

columns=/assignment;/assignment/class;/parent;/parent/company

Doing so would return the product of the join to assignment, parent, and company and likely produce unhelpful results. A more helpful export might be to only include the parent and company tables via columns=/parent;/parent/company, or use the distinct parameter to only export rows from the student table with a birth date after Apr 4th, 1976 and whose parent's work the 'Full Draft' company:

/[context]/pullreports/catalog/class/report/student-information/export?distinct=true&filter=@bdate > '1976/04/23'&filter=/parent/company@name = 'Full Draft'&sort=@lastn

This concludes the first tutorial regarding how to configure a data model within one Pull Report. Continue to learn about additional <column> configuration capabilities.

4.1.9. Joins to grand-parent <table>s

In the previous examples, each <relationship> configuration defined a join relationship between a <table> and a <table> within a child <relationship>. It is also permitted to create join relationships between <table>s separated farther apart in the relationship hierarchy via the <join_column> referencedTablePath attribute. In order to demonstrate this type of join, expand the data model to include a student_assessment table which captures a student's assessment of the classes in which they enroll.

The student_assessment table may be joined as a child <relationship> of student. This would require a second many-to-one <relationship> from student_assessment to class in order to export student_assessment records by a particular class column. For this report, join student_assessment via a child <relationship> of the class table. This requires the use of <join_column>'s referencedTablePath attribute to join student_assessment.student_id to student.id since student is student_assessment's <relationship> grandparent.

The new class table configuration with child student_assessment relationship:

... 
<table id="class" displayName="Class" name="class_info.class">
    <column id="id" name="id" displayName="Class ID" 
         paramType="java.lang.Integer" export="false"/>
    <column id="name" name="name" displayName="Class Name"/> 
    <relationship join="left" cardinality="many">
        <join_column columnName="class_id" referencedColumnName="id"/>
        <join_column columnName="student_id" referencedColumnName="id" referencedTablePath="/student"/>
        <table id="student_assessment" displayName="Student Assessment" name="class_info.student_assessment">
            <column id="rating" name="rating" displayName="Rating" paramType="java.lang.Integer"/> 
            <column id="assessment" name="assessment" displayName="Assessment"/> 
            <column id="assessment_date" name="assessment_date" displayName="Assessment Date" paramType="java.sql.Date"/> 
        </table>
    </relationship>
</table>

The complete Pull Reports™ XML Catalog file is now:

<?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="firstn,lastn"/>
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
            <column id="firstn" name="first_name" displayName="First Name"/>
            <column id="lastn" name="last_name" displayName="Last Name"/>
            <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <pre_filter>@active=true</pre_filter>
            <relationship join="left" cardinality="many">
                <join_column columnName="student_id" referencedColumnName="id" />
                <table id="assignment" displayName="Assignment" name="class_info.assignment">
                    <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
                    <column id="grade" name="grade" displayName="Grade"/> 
                    <column id="name" name="name" displayName="Name"/> 
                    <relationship join="inner" cardinality="one">
                        <join_column columnName="class_id" referencedColumnName="id" />
                        <table id="class" displayName="Class" name="class_info.class">
                            <column id="id" name="id" displayName="Class ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Class Name"/> 
                            <relationship join="left" cardinality="many">
                                <join_column columnName="class_id" referencedColumnName="id"/>
                                <join_column columnName="student_id" referencedColumnName="id" referencedTablePath="/student"/>
                                <table id="student_assessment" displayName="Student Assessment" name="class_info.student_assessment">
                                    <column id="rating" name="rating" displayName="Rating" paramType="java.lang.Integer"/> 
                                    <column id="assessment" name="assessment" displayName="Assessment"/> 
                                    <column id="assessment_date" name="assessment_date" displayName="Assessment Date" paramType="java.sql.Date"/> 
                                </table>
                            </relationship>
                        </table>
                    </relationship>
                </table>
            </relationship>
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table id="parent" displayName="Parent" name="class_info.parent">
                    <column id="id" name="id" displayName="Parent ID" paramType="java.lang.Integer"/>
                    <column id="firstn" name="first_name" displayName="First Name"/> 
                    <column id="lastn" name="last_name" displayName="Last Name"/> 
                    <column id="phone" name="phone" displayName="Phone"/> 
                    <relationship join="left" cardinality="many">
                        <join_table name="class_info.parent_company">
                            <join_columns>
                                <join_column columnName="parent_id" referencedColumnName="id" />
                            </join_columns>
                            <inverse_join_columns>
                                <join_column columnName="company_id" referencedColumnName="id" />
                           </inverse_join_columns>
                        </join_table>
                        <table id="company" displayName="Company" name="class_info.company">
                            <column id="id" name="id" displayName="Company ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Company"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

4.2. Tutorial 2: Advanced <column> configuration

In this tutorial, build on the Pull Reports™ XML Catalog file from Tutorial 1 and add additional configuration elements to the <column>s.

4.2.1. Output transforms

<output_transform>s permit changing the output of an <column> via native database functions and operators. Within a <output_transform> use ${this} to refer to the database column just as one would within a SQL query.

In our first example, we'll transform the student.home_geom column into KML <Point> values. In order to accomplish this with database functions, we'll assume an underlying PostgreSQL database with the PostGIS extension and transform the student.home_geom column into KML via the st_askml function. The new <column> definition looks like this:

...
<report id="student-information" name="Student Information">
    <table id="student" name="class_info.student" displayName="Student"> 
        ...
        <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
            <output_transform>st_askml(${this})</output_transform>
        </column>
        ...
    </table>
</report>
...

In the new column, we've set export="false" to prevent the raw KML <Point> character string from exporting to non-spatial export formats such as xml or json. In the next section, we'll configure the home-kml <column> for use in the the kml export format.

A second example transforms the student.birth_date column into a birth year <column> via the PostgreSQL extract function. In this example, retain the original <column> with id="bdate"and add a <column> definition for the birth date year.

...
<report id="student-information" name="Student Information">
    <table id="student" name="class_info.student" displayName="Student"> 
        ...
        <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
        <column id="bdate_yr" name="birth_date" displayName="Birth Date Year" paramType="java.sql.Date">
            <output_transform>extract(year from ${this})</output_transform>
        </column>
        ...
    </table>
</report>
...

The complete Pull Reports™ XML Catalog file is now:

<?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="firstn,lastn"/>
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
            <column id="firstn" name="first_name" displayName="First Name"/>
            <column id="lastn" name="last_name" displayName="Last Name"/>
            <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
            <column id="bdate_yr" name="birth_date" displayName="Birth Date Year" paramType="java.sql.Date">
                <output_transform>extract(year from ${this})</output_transform>
            </column>
            <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
                <output_transform>st_askml(${this})</output_transform>
            </column>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <pre_filter>@active=true</pre_filter>
            <relationship join="left" cardinality="many">
                <join_column columnName="student_id" referencedColumnName="id" />
                <table id="assignment" displayName="Assignment" name="class_info.assignment">
                    <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
                    <column id="grade" name="grade" displayName="Grade"/> 
                    <column id="name" name="name" displayName="Name"/> 
                    <relationship join="inner" cardinality="one">
                        <join_column columnName="class_id" referencedColumnName="id" />
                        <table id="class" displayName="Class" name="class_info.class">
                            <column id="id" name="id" displayName="Class ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Class Name"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table id="parent" displayName="Parent" name="class_info.parent">
                    <column id="id" name="id" displayName="Parent ID" paramType="java.lang.Integer"/>
                    <column id="firstn" name="first_name" displayName="First Name"/> 
                    <column id="lastn" name="last_name" displayName="Last Name"/> 
                    <column id="phone" name="phone" displayName="Phone"/> 
                    <relationship join="left" cardinality="many">
                        <join_table name="class_info.parent_company">
                            <join_columns>
                                <join_column columnName="parent_id" referencedColumnName="id" />
                            </join_columns>
                            <inverse_join_columns>
                                <join_column columnName="company_id" referencedColumnName="id" />
                           </inverse_join_columns>
                        </join_table>
                        <table id="company" displayName="Company" name="class_info.company">
                            <column id="id" name="id" displayName="Company ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Company"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

4.2.2. Spatial export

Although the home-kml <column> contains KML <Point> geometries by virtue of the <output_transform>, the kml export format is still not available because Pull Reports™ does not know that this column contains KML. In order to do this, add a <kml> element like so:

...
<report id="student-information" name="Student Information">
    <export_config>
        <kml geometryColumnPath="@home-kml">
            <placemark_name_template>${@firstn} ${@lastn} Home</placemark_name_template>
        </kml>
    </export_config>
...

The geometryColumnPath="@home-kml" attribute indicates that the column identified by the @home-kml column resource path contains KML geometries. The optional <placemark_name_template> element labels each KML geometry using values from each exported row.

Exporting GeoJSON

In addition to KML, Pull Reports™ exports GeoJSON. Configure GeoJSON export via the <geojson> element.

The complete Pull Reports™ XML Catalog file is now:

<?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='firstn,lastn'>
            <kml geometryColumnPath="@home-kml">
                <placemark_name_template>${@firstn} ${@lastn} Home</placemark_name_template>
            </kml>
        </export_config>
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
            <column id="firstn" name="first_name" displayName="First Name"/>
            <column id="lastn" name="last_name" displayName="Last Name"/>
            <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
            <column id="bdate_yr" name="birth_date" displayName="Birth Date Year" paramType="java.sql.Date">
                <output_transform>extract(year from ${this})</output_transform>
            </column>
            <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
                <output_transform>st_askml(${this})</output_transform>
            </column>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <pre_filter>@active=true</pre_filter>
            <relationship join="left" cardinality="many">
                <join_column columnName="student_id" referencedColumnName="id" />
                <table id="assignment" displayName="Assignment" name="class_info.assignment">
                    <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
                    <column id="grade" name="grade" displayName="Grade"/> 
                    <column id="name" name="name" displayName="Name"/> 
                    <relationship join="inner" cardinality="one">
                        <join_column columnName="class_id" referencedColumnName="id" />
                        <table id="class" displayName="Class" name="class_info.class">
                            <column id="id" name="id" displayName="Class ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Class Name"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table id="parent" displayName="Parent" name="class_info.parent">
                    <column id="id" name="id" displayName="Parent ID" paramType="java.lang.Integer"/>
                    <column id="firstn" name="first_name" displayName="First Name"/> 
                    <column id="lastn" name="last_name" displayName="Last Name"/> 
                    <column id="phone" name="phone" displayName="Phone"/> 
                    <relationship join="left" cardinality="many">
                        <join_table name="class_info.parent_company">
                            <join_columns>
                                <join_column columnName="parent_id" referencedColumnName="id" />
                            </join_columns>
                            <inverse_join_columns>
                                <join_column columnName="company_id" referencedColumnName="id" />
                           </inverse_join_columns>
                        </join_table>
                        <table id="company" displayName="Company" name="class_info.company">
                            <column id="id" name="id" displayName="Company ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Company"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

4.2.3. Filter transforms

When the Export Report REST API applies filter parameters to limit the export results, it creates parameterized SQL "where" clauses against the database column name as configured via the <column> name attribute and coerces the parameter type to the value of the <column> paramType. For instance, given the following base table <column> definition

...
<column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
...

and filter parameter,

filter=@bdate > '1976/04/23'

...the generated SQL where clause is

birth_date > ?

with the ? parameter set to '1976/04/23' coerced to a java.sql.Date.

The <filter_column_transform> and <filter_value_transform> elements transform the column and value parts of a where clause statement respectively for greater control of export filtration.

Let's extend our previous example to use a <filter_column_transform> to filter student rows based on an Integer birth year. In the following <column> definition, notice the new <filter_column_transform> and the setting of paramType="java.lang.Integer".

...
<column id="bdate_yr" name="birth_date" displayName="Birth Date Year" paramType="java.lang.Integer">
    <output_transform>extract(year from ${this})</output_transform>
    <filter_column_transform>extract(year from ${this})</filter_column_transform>
</column>
...

Similarly, use a <filter_column_transform> in conjuction with the extract function to create a new, birth_month column to allow filtration on the integer (1-12) birth month. Since the column is set to export="false" and will thus never be included in the export results, no <output_transform> is required.

...
<column id="bdate_month" name="birth_date" displayName="Birth Date month" 
    paramType="java.lang.Integer" export="false">
    <filter_column_transform>extract(Month from ${this})</filter_column_transform>
</column>
...

Now, in order to export all students born in the months of January or February in any year after 1976, use these two filter parameters:

filter=@bdate_yr > 1976
filter=@bdate_month in (1,2)

These parameters result in a SQL where clause of

extract(year from birth_date) > ? AND extract(Month from birth_date) in (?,?)

in which the first ? query parameter is set to the java.lang.Integer, 1976, and the second and third query parameters are set to the java.lang.Integers 1 and 2 to represent January and February respectively.

Filter transforms may be applied to any <column> within the <report> In the follow example, allow for case-insensitive comparisons for a <column> in the joined parent and student tables by converting the where clause's column and value to upper case. For example:

...
<table id="parent" displayName="Parent" name="class_info.parent">
...
    <column id="lastn" name="last_name" displayName="Parent Last Name"> 
        <filter_column_transform>upper(${this})</filter_column_transform>
        <filter_value_transform>upper(?)</filter_value_transform>
    </column>
...

The complete Pull Reports™ XML Catalog file is now:

<?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='firstn,lastn'>
            <kml geometryColumnPath="@home-kml">
                <placemark_name_template>${@firstn} ${@lastn} Home</placemark_name_template>
            </kml>
        </export_config>
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
            <column id="firstn" name="first_name" displayName="First Name"/>
            <column id="lastn" name="last_name" displayName="Last Name"> 
                <filter_column_transform>upper(${this})</filter_column_transform>
                <filter_value_transform>upper(?)</filter_value_transform>
            </column>
            <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
            <column id="bdate_yr" name="birth_date" displayName="Birth Date Year" paramType="java.lang.Integer">
                <output_transform>extract(year from ${this})</output_transform>
                <filter_column_transform>extract(year from ${this})</filter_column_transform>
            </column>
            <column id="bdate_month" name="birth_date" displayName="Birth Date Month" 
                paramType="java.lang.Integer" export="false">
                <filter_column_transform>extract(Month from ${this})</filter_column_transform>
            </column>
            <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
                <output_transform>st_askml(${this})</output_transform>
            </column>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <pre_filter>@active=true</pre_filter>
            <relationship join="left" cardinality="many">
                <join_column columnName="student_id" referencedColumnName="id" />
                <table id="assignment" displayName="Assignment" name="class_info.assignment">
                    <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
                    <column id="grade" name="grade" displayName="Grade"/> 
                    <column id="name" name="name" displayName="Name"/> 
                    <relationship join="inner" cardinality="one">
                        <join_column columnName="class_id" referencedColumnName="id" />
                        <table id="class" displayName="Class" name="class_info.class">
                            <column id="id" name="id" displayName="Class ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Class Name"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table id="parent" displayName="Parent" name="class_info.parent">
                    <column id="id" name="id" displayName="Parent ID" paramType="java.lang.Integer"/>
                    <column id="firstn" name="first_name" displayName="First Name"/> 
                    <column id="lastn" name="last_name" displayName="Last Name"> 
                        <filter_column_transform>upper(${this})</filter_column_transform>
                        <filter_value_transform>upper(?)</filter_value_transform>
                    </column>
                    <column id="phone" name="phone" displayName="Phone"/> 
                    <relationship join="left" cardinality="many">
                        <join_table name="class_info.parent_company">
                            <join_columns>
                                <join_column columnName="parent_id" referencedColumnName="id" />
                            </join_columns>
                            <inverse_join_columns>
                                <join_column columnName="company_id" referencedColumnName="id" />
                           </inverse_join_columns>
                        </join_table>
                        <table id="company" displayName="Company" name="class_info.company">
                            <column id="id" name="id" displayName="Company ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Company"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

4.2.5. Label value lists

In order to provide option lists of allowable filter values within the Pull Reports™ Ad Hoc Report Creator, define <global_label_value_list>s within a <catalog> and reference them via the <global_label_value_list_ref>'s ref attribute.

Why label value lists?

Label value lists do not effect the returned results from the Export Report REST API. However, they are an important usability improvement for the Ad Hoc Report Creator in that they allow a user to select filter values from a discrete set of human readable options instead of typing database identifiers. For instance, if the Pull Reports™ XML Catalog file has a <column> for "U.S. State" which maps to a database column containing the U.S. State abbreviation (e.g. "CO"), configure a <global_label_value_list> which maps all U.S. State abbreviations to their proper name. This allows a user of the creator to select the value "Colorado" when creating a filter on the "U.S. State" <column> instead of typing "CO".

Additionally, the ability to select multiple values for a filter via the in (...) or not in (...) operators within the Ad Hoc Report Creator is only available for <column>s which define a <label_value_list> or <global_label_value_list_ref> child element.

4.2.5.1. Static label value lists

Static label value lists are defined by the <label_values> element. Use <label_values> when it is inconvenient or difficult to define the set of acceptable values via a database query.

In this example report, define a <label_values> to represent the twelve calendar months.

...
<global_label_value_list id="lvl-months">
    <label_values>
        <label_value value="1">January</label_value>
        <label_value value="2">February</label_value>
        <label_value value="3">March</label_value>
        <label_value value="4">April</label_value>
        <label_value value="6">May</label_value>
        <label_value value="7">June</label_value>
        <label_value value="8">July</label_value>
        <label_value value="9">August</label_value>
        <label_value value="10">October</label_value>
        <label_value value="11">November</label_value>
        <label_value value="12">December</label_value>
    </label_values>
</global_label_value_list>
...

Reference the new <global_label_value_list> from the birth_month <column> to allow users to choose a month proper name instead of an integer when creating a filter on the bdate_month column.

...
<column id="bdate_month" name="birth_date" displayName="Birth Date Month" 
    paramType="java.lang.String" export="false">
    <filter_column_transform>extract(Month from ${this})</filter_column_transform>
    <global_label_value_list_ref ref='lvl-months'/>
</column>
...

4.2.5.2. Query label value lists

Query label value lists create a <global_label_value_list> from a SQL query. They typically select the distinct set of possible values for a particular <column>.

In this example report, define a <label_value_query> to retreive the distinct list of student last names for the lastn column.

...
<global_label_value_list id="lvl-last_names">
    <label_value_query>
        select distinct last_name as label, last_name as value from class_info.student order by last_name 
    </label_value_query>
</global_label_value_list>
...

Reference the new <global_label_value_list> from the lastn <column> to allow users to choose a specific last name instead of having to type in a value when creating a filter on the lastn column.

...
<column id="lastn" name="last_name" displayName="Last Name">
    <global_label_value_list_ref ref='lvl-last_names'/>
...

The complete Pull Reports™ XML Catalog file is now:

<?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='firstn,lastn'>
            <kml geometryColumnPath="@home-kml">
                <placemark_name_template>${@firstn} ${@lastn} Home</placemark_name_template>
            </kml>
        </export_config>
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
            <column id="firstn" name="first_name" displayName="First Name"/>
            <column id="lastn" name="last_name" displayName="Last Name">
                <filter_column_transform>upper(${this})</filter_column_transform>
                <filter_value_transform>upper(?)</filter_value_transform>
                <url_template>
                    http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}
                </url_template>
                <global_label_value_list_ref ref="lvl-last_names"/>
            </column>
            <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
            <column id="bdate_yr" name="birth_date" displayName="Birth Date Year" paramType="java.lang.Integer">
                <output_transform>extract(year from ${this})</output_transform>
                <filter_column_transform>extract(year from ${this})</filter_column_transform>
            </column>
            <column id="bdate_month" name="birth_date" displayName="Birth Date Month" 
                paramType="java.lang.String" export="false">
                <filter_column_transform>extract(Month from ${this})</filter_column_transform>
                <global_label_value_list_ref ref="lvl-months"/>
            </column>
            <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
                <output_transform>st_askml(${this})</output_transform>
            </column>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <pre_filter>@active=true</pre_filter>
            <relationship join="left" cardinality="many">
                <join_column columnName="student_id" referencedColumnName="id" />
                <table id="assignment" displayName="Assignment" name="class_info.assignment">
                    <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
                    <column id="grade" name="grade" displayName="Grade"/> 
                    <column id="name" name="name" displayName="Name"/> 
                    <relationship join="inner" cardinality="one">
                        <join_column columnName="class_id" referencedColumnName="id" />
                        <table id="class" displayName="Class" name="class_info.class">
                            <column id="id" name="id" displayName="Class ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Class Name"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table id="parent" displayName="Parent" name="class_info.parent">
                    <column id="id" name="id" displayName="Parent ID" paramType="java.lang.Integer"/>
                    <column id="firstn" name="first_name" displayName="First Name"/> 
                    <column id="lastn" name="last_name" displayName="Last Name"> 
                        <filter_column_transform>upper(${this})</filter_column_transform>
                        <filter_value_transform>upper(?)</filter_value_transform>
                        <url_template>
                            http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}
                        </url_template>
                    </column>
                    <column id="phone" name="phone" displayName="Phone"/> 
                    <relationship join="left" cardinality="many">
                        <join_table name="class_info.parent_company">
                            <join_columns>
                                <join_column columnName="parent_id" referencedColumnName="id" />
                            </join_columns>
                            <inverse_join_columns>
                                <join_column columnName="company_id" referencedColumnName="id" />
                           </inverse_join_columns>
                        </join_table>
                        <table id="company" displayName="Company" name="class_info.company">
                            <column id="id" name="id" displayName="Company ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Company"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
    <global_label_value_list id="lvl-months">
        <label_values>
            <label_value value="1">January</label_value>
            <label_value value="2">February</label_value>
            <label_value value="3">March</label_value>
            <label_value value="4">April</label_value>
            <label_value value="6">May</label_value>
            <label_value value="7">June</label_value>
            <label_value value="8">July</label_value>
            <label_value value="9">August</label_value>
            <label_value value="10">October</label_value>
            <label_value value="11">November</label_value>
            <label_value value="12">December</label_value>
        </label_values>
    </global_label_value_list>
    <global_label_value_list id="lvl-last_names">
        <label_value_query>
            select distinct last_name as label, last_name as value from class_info.student order by last_name 
        </label_value_query>
    </global_label_value_list>
</catalog>

This concludes the second tutorial regarding advanced <column> configuration elements. Continue to learn about defining additional <report>s and managing complex Pull Reports™ XML Catalog files.

4.3. Tutorial 3: Managing Pull Reports™ XML Catalog files

Pull Reports™ XML Catalog file files can get large and repetitive across multiple <report>s. Follow this tutorial to learn strategies to re-use configuration elements.

4.3.1. A second <report>

In the data model from Tutorial 1, the student table is only one of the possible base tables for <report> creation. By creating a second report based from the parent table, the user may access a different view of the data model in which all parent rows are included in the report export by default and all relationship joins originate from the parent table. The following Pull Reports™ XML Catalog file replicates the <relationship>s from the student-information report into a new, parent-information report in the same <catalog>.

Hint: Scroll down the catalog file to see the parent-information report.

<?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='firstn,lastn'>
            <kml geometryColumnPath="@home-kml">
                <placemark_name_template>${@firstn} ${@lastn} Home</placemark_name_template>
            </kml>
        </export_config>
        <table id="student" name="class_info.student" displayName="Student"> 
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
            <column id="firstn" name="first_name" displayName="First Name"/>
            <column id="lastn" name="last_name" displayName="Last Name">
                <filter_column_transform>upper(${this})</filter_column_transform>
                <filter_value_transform>upper(?)</filter_value_transform>
                <url_template>
                    http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}
                </url_template>
            </column>
            <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
            <column id="bdate_yr" name="birth_date" displayName="Birth Date Year" paramType="java.lang.Integer">
                <output_transform>extract(year from ${this})</output_transform>
                <filter_column_transform>extract(year from ${this})</filter_column_transform>
            </column>
            <column id="bdate_month" name="birth_date" displayName="Birth Date Month" 
                paramType="java.lang.Integer" export="false">
                <filter_column_transform>extract(Month from ${this})</filter_column_transform>
            </column>
            <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
                <output_transform>st_askml(${this})</output_transform>
            </column>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <pre_filter>@active=true</pre_filter>
            <relationship join="left" cardinality="many">
                <join_column columnName="student_id" referencedColumnName="id" />
                <table id="assignment" displayName="Assignment" name="class_info.assignment">
                    <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
                    <column id="grade" name="grade" displayName="Grade"/> 
                    <column id="name" name="name" displayName="Name"/> 
                    <relationship join="inner" cardinality="one">
                        <join_column columnName="class_id" referencedColumnName="id" />
                        <table id="class" displayName="Class" name="class_info.class">
                            <column id="id" name="id" displayName="Class ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Class Name"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table id="parent" displayName="Parent" name="class_info.parent">
                    <column id="id" name="id" displayName="Parent ID" paramType="java.lang.Integer"/>
                    <column id="firstn" name="first_name" displayName="First Name"/> 
                    <column id="lastn" name="last_name" displayName="Last Name"> 
                        <filter_column_transform>upper(${this})</filter_column_transform>
                        <filter_value_transform>upper(?)</filter_value_transform>
                        <url_template>
                            http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}
                        </url_template>
                    </column>
                    <column id="phone" name="phone" displayName="Phone"/> 
                    <relationship join="left" cardinality="many">
                        <join_table name="class_info.parent_company">
                            <join_columns>
                                <join_column columnName="parent_id" referencedColumnName="id" />
                            </join_columns>
                            <inverse_join_columns>
                                <join_column columnName="company_id" referencedColumnName="id" />
                           </inverse_join_columns>
                        </join_table>
                        <table id="company" displayName="Company" name="class_info.company">
                            <column id="id" name="id" displayName="Company ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Company"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
    <report id="parent-information" name="Parent Information">
        <export_config defaultColumns='firstn,lastn'/>
        <table id="parent" displayName="Parent" name="class_info.parent">
            <column id="id" name="id" displayName="Parent ID" />
            <column id="firstn" name="first_name" displayName="First Name"/> 
            <column id="lastn" name="last_name" displayName="Last Name"> 
                <filter_column_transform>upper(${this})</filter_column_transform>
                <filter_value_transform>upper(?)</filter_value_transform>
                <url_template>
                    http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}
                </url_template>
            </column>
            <relationship join="left" cardinality="many">
                <join_table name="class_info.parent_company">
                    <join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="company_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table id="company" displayName="Company" name="class_info.company">
                    <column id="id" name="id" displayName="Company ID" 
                         paramType="java.lang.Integer" export="false"/>
                    <column id="name" name="name" displayName="Company"/> 
                </table>
            </relationship>
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table id="student" name="class_info.student" displayName="Student"> 
                    <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
                    <column id="firstn" name="first_name" displayName="First Name"/>
                    <column id="lastn" name="last_name" displayName="Last Name">
                        <filter_column_transform>upper(${this})</filter_column_transform>
                        <filter_value_transform>upper(?)</filter_value_transform>
                        <url_template>
                            http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}
                        </url_template>
                    </column>
                    <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
                    <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
                    <column id="bdate_yr" name="birth_date" displayName="Birth Date Year" paramType="java.lang.Integer">
                        <output_transform>extract(year from ${this})</output_transform>
                        <filter_column_transform>extract(year from ${this})</filter_column_transform>
                    </column>
                    <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
                        <output_transform>st_askml(${this})</output_transform>
                    </column>
                    <relationship join="left" cardinality="many">
                        <join_column columnName="student_id" referencedColumnName="id" />
                        <table id="assignment" displayName="Assignment" name="class_info.assignment">
                            <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
                            <column id="grade" name="grade" displayName="Grade"/> 
                            <column id="name" name="name" displayName="Name"/> 
                            <relationship join="inner" cardinality="one">
                                <join_column columnName="class_id" referencedColumnName="id" />
                                <table id="class" displayName="Class" name="class_info.class">
                                    <column id="id" name="id" displayName="Class ID" 
                                         paramType="java.lang.Integer" export="false"/>
                                    <column id="name" name="name" displayName="Class Name"/> 
                                </table>
                            </relationship>
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

This new report is then available via the REST API via the following URLs:

  • /[context]/pullreports/catalog/class/report/parent-information

  • /[context]/pullreports/catalog/class/report/parent-information.json

  • /[context]/pullreports/catalog/class/report/parent-information/export

4.3.2. Using <table_ref>s

Unfortunately, the second, parent-information <report> replicates several of the configuration elements from the first report. Pull Reports™ allows <table> elements to be defined globally within the <catalog> and referenced via <table_ref>s. For example, the replicated assignment table may be factored into a global table and referenced twice:

<?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">
        ...
        <table id="student" name="class_info.student" displayName="Student"> 
            ...
            <relationship join="left" cardinality="many">
                <join_column columnName="student_id" referencedColumnName="id" />
                <table_ref id="assignment" ref="assignment_global"/>
            </relationship>
            ...
        </table>
    </report>
    <report id="parent-information" name="Parent Information">
        <table id="parent" displayName="Parent" name="class_info.parent">
            ...
            <relationship join="left" cardinality="many">
                ...
                <table id="student" name="class_info.student" displayName="Student"> 
                    ...
                    <relationship join="left" cardinality="many">
                        <join_column columnName="student_id" referencedColumnName="id" />
                        <table_ref id="assignment" ref="assignment_global"/>
                    </relationship>
                    ...
                </table>
            </relationship>
        </table>
    </report>
    <table id="assignment_global" displayName="Assignment" name="class_info.assignment">
        <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
        <column id="grade" name="grade" displayName="Grade"/> 
        <column id="name" name="name" displayName="Name"/> 
        <relationship join="inner" cardinality="one">
            <join_column columnName="class_id" referencedColumnName="id" />
            <table id="class" displayName="Class" name="class_info.class">
                <column id="id" name="id" displayName="Class ID" 
                     paramType="java.lang.Integer" export="false"/>
                <column id="name" name="name" displayName="Class Name"/> 
            </table>
        </relationship>
    </table>
</catalog>

Since global tables may themselves reference other global tables, it is easy to simplify Pull Reports™ XML Catalog files by creating global table for each table in the data model and then only adding necessary <relationship> and <pre_filter> configuration to <table_ref>s within individual <report>s. By using this strategy, out Pull Reports™ XML Catalog file is transformed to:

<?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='firstn,lastn'>
            <kml geometryColumnPath="@home-kml">
                <placemark_name_template>${@firstn} ${@lastn} Home</placemark_name_template>
            </kml>
        </export_config>
        <table_ref id="student" ref="student_global">
            <pre_filter>@active=true</pre_filter>
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table_ref id="parent" ref="parent_global"/>
            </relationship>
        </table_ref>
    </report>
    <report id="parent-information" name="Parent Information">
        <export_config defaultColumns='firstn,lastn'/>
        <table_ref id="parent" ref="parent_global">
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table_ref id="student" ref="student_global"/> 
            </relationship>
        </table_ref>
    </report>
    <table id="company_global" displayName="Company" name="class_info.company">
        <column id="id" name="id" displayName="Company ID" 
             paramType="java.lang.Integer" export="false"/>
        <column id="name" name="name" displayName="Company"/> 
    </table>
    <table id="assignment_global" displayName="Assignment" name="class_info.assignment">
        <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
        <column id="grade" name="grade" displayName="Grade"/> 
        <column id="name" name="name" displayName="Name"/> 
        <relationship join="inner" cardinality="one">
            <join_column columnName="class_id" referencedColumnName="id" />
            <table_ref id="class" ref="class_global"/>
        </relationship>
    </table>
    <table id="parent_global" displayName="Parent" name="class_info.parent">
        <column id="id" name="id" displayName="Parent ID" paramType="java.lang.Integer"/>
        <column id="firstn" name="first_name" displayName="First Name"/> 
        <column id="lastn" name="last_name" displayName="Last Name"> 
            <filter_column_transform>upper(${this})</filter_column_transform>
            <filter_value_transform>upper(?)</filter_value_transform>
            <url_template>
                http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}
            </url_template>
        </column>
        <column id="phone" name="phone" displayName="Phone"/> 
        <relationship join="left" cardinality="many">
            <join_table name="class_info.parent_company">
                <join_columns>
                    <join_column columnName="parent_id" referencedColumnName="id" />
                </join_columns>
                <inverse_join_columns>
                    <join_column columnName="company_id" referencedColumnName="id" />
               </inverse_join_columns>
            </join_table>
            <table_ref id="company" ref="company_global"/>
        </relationship>
    </table>
    <table id="student_global" name="class_info.student" displayName="Student"> 
        <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
        <column id="firstn" name="first_name" displayName="First Name"/>
        <column id="lastn" name="last_name" displayName="Last Name">
            <filter_column_transform>upper(${this})</filter_column_transform>
            <filter_value_transform>upper(?)</filter_value_transform>
            <url_template>
                http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}
            </url_template>
        </column>
        <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
        <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
        <column id="bdate_yr" name="birth_date" displayName="Birth Date Year" paramType="java.lang.Integer">
            <output_transform>extract(year from ${this})</output_transform>
            <filter_column_transform>extract(year from ${this})</filter_column_transform>
        </column>
        <column id="bdate_month" name="birth_date" displayName="Birth Date Month" 
            paramType="java.lang.Integer" export="false">
            <filter_column_transform>extract(Month from ${this})</filter_column_transform>
        </column>
        <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
            <output_transform>st_askml(${this})</output_transform>
        </column>
        <column id="active" name="is_active" displayName="Active?" 
            paramType="java.lang.Boolean" export="false"/>
        <relationship join="left" cardinality="many">
            <join_column columnName="student_id" referencedColumnName="id" />
            <table_ref id="assignment" ref="assignment_global"/>
        </relationship>
    </table>
    <table id="class_global" displayName="Class" name="class_info.class">
        <column id="id" name="id" displayName="Class ID" 
             paramType="java.lang.Integer" export="false"/>
        <column id="name" name="name" displayName="Class Name"/> 
    </table>
</catalog>

Global table ids vs. report table ids

In the above Pull Reports™ XML Catalog file, the global <table>s ids are suffixed with _global. This is not a requirement and only serves to distinguish the global <table>s from <table>s defined within a <report>. Global <table> ids must be unique within the <catalog> whereas <table> or <table_ref> ids must be unique within their parent <relationship>. Furthermore, only <table_ref> ids are used within the table resource paths, never the ids of the referenced global tables.

4.3.3. Using <column_group>s

Similar to global table configuration, sets of columns may be declared globally within a <catalog> via <column_group>s and referenced within multiple <table>s via <column_group_ref>s. In this example, create a <column_group>s for the first_name and last_name columns of the student and parent global tables and then reuse this common configuration:

...
    <table id="parent_global" displayName="Parent" name="class_info.parent">
        ...
        <column_group_ref ref="contact_info_group"/>
        ...
    </table>
    <table id="student_global" name="class_info.student" displayName="Student"> 
        ...
        <column_group_ref ref="contact_info_group"/>
        ...
    </table>
    <column_group id="contact_info_group">
        <column id="firstn" name="first_name" displayName="First Name"/> 
        <column id="lastn" name="last_name" displayName="Last Name"> 
            <filter_column_transform>upper(${this})</filter_column_transform>
            <filter_value_transform>upper(?)</filter_value_transform>
            <url_template>
                http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}
            </url_template>
        </column>
    </column_group>
...

The complete Pull Reports™ XML Catalog file is now:

<?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='firstn,lastn'>
            <kml geometryColumnPath="@home-kml">
                <placemark_name_template>${@firstn} ${@lastn} Home</placemark_name_template>
            </kml>
        </export_config>
        <table_ref id="student" ref="student_global">
            <pre_filter>@active=true</pre_filter>
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table_ref id="parent" ref="parent_global"/>
            </relationship>
        </table_ref>
    </report>
    <report id="parent-information" name="Parent Information">
        <export_config defaultColumns='firstn,lastn'/>
        <table_ref id="parent" ref="parent_global">
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table_ref id="student" ref="student_global"/> 
            </relationship>
        </table_ref>
    </report>
    <column_group id="contact_info_group">
        <column id="firstn" name="first_name" displayName="First Name"/> 
        <column id="lastn" name="last_name" displayName="Last Name"> 
            <filter_column_transform>upper(${this})</filter_column_transform>
            <filter_value_transform>upper(?)</filter_value_transform>
            <url_template>
                http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}
            </url_template>
        </column>
    </column_group>
    <table id="company_global" displayName="Company" name="class_info.company">
        <column id="id" name="id" displayName="Company ID" 
             paramType="java.lang.Integer" export="false"/>
        <column id="name" name="name" displayName="Company"/> 
    </table>
    <table id="assignment_global" displayName="Assignment" name="class_info.assignment">
        <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
        <column id="grade" name="grade" displayName="Grade"/> 
        <column id="name" name="name" displayName="Name"/> 
        <relationship join="inner" cardinality="one">
            <join_column columnName="class_id" referencedColumnName="id" />
            <table_ref id="class" ref="class_global"/>
        </relationship>
    </table>
    <table id="parent_global" displayName="Parent" name="class_info.parent">
        <column id="id" name="id" displayName="Parent ID" paramType="java.lang.Integer"/>
        <column_group_ref ref="contact_info_group"/>
        <column id="phone" name="phone" displayName="Phone"/> 
        <relationship join="left" cardinality="many">
            <join_table name="class_info.parent_company">
                <join_columns>
                    <join_column columnName="parent_id" referencedColumnName="id" />
                </join_columns>
                <inverse_join_columns>
                    <join_column columnName="company_id" referencedColumnName="id" />
               </inverse_join_columns>
            </join_table>
            <table_ref id="company" ref="company_global"/>
        </relationship>
    </table>
    <table id="student_global" name="class_info.student" displayName="Student"> 
        <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
        <column_group_ref ref="contact_info_group"/>
        <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
        <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
        <column id="bdate_yr" name="birth_date" displayName="Birth Date Year" paramType="java.lang.Integer">
            <output_transform>extract(year from ${this})</output_transform>
            <filter_column_transform>extract(year from ${this})</filter_column_transform>
        </column>
        <column id="bdate_month" name="birth_date" displayName="Birth Date Month" 
            paramType="java.lang.Integer" export="false">
            <filter_column_transform>extract(Month from ${this})</filter_column_transform>
        </column>
        <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
            <output_transform>st_askml(${this})</output_transform>
        </column>
        <column id="active" name="is_active" displayName="Active?" 
            paramType="java.lang.Boolean" export="false"/>
        <relationship join="left" cardinality="many">
            <join_column columnName="student_id" referencedColumnName="id" />
            <table_ref id="assignment" ref="assignment_global"/>
        </relationship>
    </table>
    <table id="class_global" displayName="Class" name="class_info.class">
        <column id="id" name="id" displayName="Class ID" 
             paramType="java.lang.Integer" export="false"/>
        <column id="name" name="name" displayName="Class Name"/> 
    </table>
</catalog>

4.3.4. Using XML entity includes

Use XML entity includes to share configuration among multiple Pull Reports™ XML Catalog files. In this example, split the student-information and parent-information <report>s into separate <catalog>s - one for student reports and one for parent reports - and share the common, global <table> and <column_group> elements via an entity include. After the split, the reports will be available at the following URLs:

  • /[context]/pullreports/catalog/student/report/student-information

  • /[context]/pullreports/catalog/student/report/student-information.json

  • /[context]/pullreports/catalog/student/report/student-information/export

  • /[context]/pullreports/catalog/parent/report/parent-information

  • /[context]/pullreports/catalog/parent/report/parent-information.json

  • /[context]/pullreports/catalog/parent/report/parent-information/export

When to split reports into multiple catalogs?

It is useful to separate <report>s into two <catalog>s to apply different security controls to the respective <catalog> URL paths.

The complete Pull Reports™ XML Catalog files are now:

Example 4.2. student-report-catalog.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE catalog [
<!ENTITY globals SYSTEM "./global-resources.xml">
]>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="student" name="Student Reports">
    <report id="student-information" name="Student Information">
        <export_config defaultColumns='firstn,lastn'>
            <kml geometryColumnPath="@home-kml">
                <placemark_name_template>${@firstn} ${@lastn} Home</placemark_name_template>
            </kml>
        </export_config>
        <table_ref id="student" ref="student_global">
            <pre_filter>@active=true</pre_filter>
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table_ref id="parent" ref="parent_global"/>
            </relationship>
        </table_ref>
    </report>
    &globals;
</catalog>

Example 4.3. parent-report-catalog.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE catalog [
<!ENTITY globals SYSTEM "./global-resources.xml">
]>
<catalog xmlns="http://www.pullreports.com/catalog-1.4.0" id="parent" name="Parent Reports">
    <report id="parent-information" name="Parent Information">
        <export_config defaultColumns='firstn,lastn'/>
        <table_ref id="parent" ref="parent_global">
            <relationship join="left" cardinality="many">
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table_ref id="student" ref="student_global"/> 
            </relationship>
        </table_ref>
    </report>
    &globals;
</catalog>

Example 4.4. global-resources.xml
<column_group id="contact_info_group">
    <column id="firstn" name="first_name" displayName="First Name"/> 
    <column id="lastn" name="last_name" displayName="Last Name"> 
        <filter_column_transform>upper(${this})</filter_column_transform>
        <filter_value_transform>upper(?)</filter_value_transform>
        <url_template>
            http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}
        </url_template>
    </column>
</column_group>
<table id="company_global" displayName="Company" name="class_info.company">
    <column id="id" name="id" displayName="Company ID" 
         paramType="java.lang.Integer" export="false"/>
    <column id="name" name="name" displayName="Company"/> 
</table>
<table id="assignment_global" displayName="Assignment" name="class_info.assignment">
    <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
    <column id="grade" name="grade" displayName="Grade"/> 
    <column id="name" name="name" displayName="Name"/> 
    <relationship join="inner" cardinality="one">
        <join_column columnName="class_id" referencedColumnName="id" />
        <table_ref id="class" ref="class_global"/>
    </relationship>
</table>
<table id="parent_global" displayName="Parent" name="class_info.parent">
    <column id="id" name="id" displayName="Parent ID" />
    <column_group_ref ref="contact_info_group"/>
    <relationship join="left" cardinality="many">
        <join_table name="class_info.parent_company">
            <join_columns>
                <join_column columnName="parent_id" referencedColumnName="id" />
            </join_columns>
            <inverse_join_columns>
                <join_column columnName="company_id" referencedColumnName="id" />
           </inverse_join_columns>
        </join_table>
        <table_ref id="company" ref="company_global"/>
    </relationship>
</table>
<table id="student_global" name="class_info.student" displayName="Student"> 
    <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
    <column_group_ref ref="contact_info_group"/>
    <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
    <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date"/>
    <column id="bdate_yr" name="birth_date" displayName="Birth Date Year" paramType="java.lang.Integer">
        <output_transform>extract(year from ${this})</output_transform>
        <filter_column_transform>extract(year from ${this})</filter_column_transform>
    </column>
    <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
        <output_transform>st_askml(${this})</output_transform>
    </column>
    <column id="active" name="is_active" displayName="Active?" 
        paramType="java.lang.Boolean" export="false"/>
    <relationship join="left" cardinality="many">
        <join_column columnName="student_id" referencedColumnName="id" />
        <table_ref id="assignment" ref="assignment_global"/>
    </relationship>
</table>
<table id="class_global" displayName="Class" name="class_info.class">
    <column id="id" name="id" displayName="Class ID" 
         paramType="java.lang.Integer" export="false"/>
    <column id="name" name="name" displayName="Class Name"/> 
</table>

4.4. <table> and <table_ref> resource paths

Refer to <table> and <table_ref> child elements of a <report> by a forward slash (/) separated list of <table> or <table_ref> ids starting from the base table of the <report>. For instance, the path:

/student/parent/employer

refers to the employer table joined as a child <relationship> to the parent table joined as a child <relationship> to the student table.

Example usages of table and table_ref resource paths include the filter, columns, and sort parameters of the GET Export Report and attributes of <kml> and <geojson> Pull Reports™ XML Catalog file elements.

Excluding the base table path:

Since the base table id is always the first path element of a table or table_ref resource path, it may be omitted from the path for brevity. For instance, the previous path example may also be written as:

/parent/employer

since the /student base table path is always assumed.

Base table path required for <join_column> referencedTablePath attribute

The only exception to the optional inclusion of the base table path is the referencedTablePath of <join_column>. This attribute value does not support the omission of the base table path

Example 4.5. Example report configuration with table paths indicated in comments
<?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'/>
        <!-- Path: /student OR ''-->
        <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="is_active" displayName="Is Active?" paramType="java.lang.Boolean"/>
            <relationship cardinality="many">
                <join_table name="student_parent">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                    </inverse_join_columns>
                </join_table>
                <!-- Path: /student/parent OR /parent -->
                <table id="parent" displayName="Parent Details" name="parent"> 
                    <column id="id" name="id" displayName="Parent ID" paramType="java.lang.Integer"/>
                    <column id="name" name="parent_name" displayName="Parent Name"/>
                    <relationship cardinality="many">
                        <join_table name="parent_employer">
                            <join_columns>
                                <join_column columnName="parent_id" referencedColumnName="id" />
                            </join_columns>
                            <inverse_join_columns>
                                <join_column columnName="employer_id" referencedColumnName="id" />
                            </inverse_join_columns>
                        </join_table>
                        <!-- Path: /student/parent/employer OR /parent/employer -->
                        <table_ref id="employer" ref="employer_global"/>
                    </relationship>
                </table>
            </relationship>
            <relationship cardinality="many">
                <join_table name="student_employer">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="employer_id" referencedColumnName="id" />
                    </inverse_join_columns>
                </join_table>
                <!-- Path: /student/employer OR /employer-->
                <table_ref id="employer" ref="employer_global"/>
            </relationship>
        </table>
    </report>
    <!-- Path: Not Applicable. Global <table>s do not have a Path -->
    <table id="employer_global" displayName="Employer" name="employer_details">
        <column id="id" name="id" displayName="Employer ID" paramType="java.lang.Integer"/> 
        <column id="name" name="name" displayName="Employer Name"/> 
        <relationship join="inner" cardinality="one">
            <join_column columnName="state_abbreviation" referencedColumnName="abbreviation" />
            <!-- Valid paths:
            Path 1: /student/employer/state OR /employer/state
            Path 2: /student/parent/employer/state  OR /parent/employer/state
            -->
            <table_ref id="state" ref="state_global"/>
        </relationship>
    </table>
    <!-- Path: Not Applicable. Global <table>s do not have a Path -->
    <table id="state_global" displayName="State" name="state">
        <column id="abbr" name="abbreviation" displayName="State Abbreviation"/> 
        <column id="name" name="name" displayName="State"/> 
    </table>
</catalog>

Note

When creating paths with <table_ref>s, use the id of the <table_ref> instead of the id of the referred Global <table>. This keeps the path to each table reference unique.

4.5. <column> Resource Paths

Refer to <column> child elements of <table>s by prepending the parent <table> or <table_ref> resource path to the at-sign (@) plus the column id. For example, the path:

/student/parent/employer@name

refers to the column with id name of the employer table joined as a child <relationship> to the parent table joined as a child <relationship> to the base student table.

Example usages of column resource paths include the filter, columns, and sort parameters of the GET Export Report, and some attributes of the <kml>, <geojson> and <report> Pull Reports™ XML Catalog file elements.

When used with the columns Export Report REST API parameter, the column resource path may include a comma separate list of column ids like so:

/student/parent/employer@id,name,zipcode

Excluding the base table path:

Similar to table resource paths, column resource paths may exclude the base table path. For instance, the previous example may also be written as:

/parent/employer@name

because the /student base table path may be omitted. Furthermore, when referring to columns on the base table, the table path may be omitted all together. For example, the following column resources paths are equivalent paths to the name column of the student base table.

@name
/student@name

Note

Paths to child <column>s from referenced <column_group>s are constructed as if the <column> was a direct child of the <table> which contains the <column_group_ref>.

In the following Pull Reports™ XML Catalog file examples, /table@id refers to the id column of both base tables.

<?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="Report">
        <table id="table" displayName="Details" name="details">
            <column id="id" name="id" displayName="ID" paramType="integer"/> 
        </table>
    </report>
</catalog>
<?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="Report with column_group">
        <table id="table" displayName="Details" name="details">
            <column_group_ref ref="column_group"/>
        </table>
    </report>
    <column_group id="column_group">
        <column id="id" name="id" displayName="ID" paramType="integer"/> 
    </column_group>
</catalog>

4.6. Pull Reports™ Schema How To's

4.6.1. Validating Pull Reports™ XML Catalog files in Eclipse

Eclipse is a popular open source Integrated Development Environment which may be use to validate Pull Reports™ XML Catalog files by following these steps:

created with Ecplise Luna 4.4.1

  1. Open the Eclipse project which contains the Pull Reports™ XML Catalog file to validate.

  2. Open the Preferences dialog via the Window > Preferences menu item.

  3. In the left hand navigation of the Preferences dialog, select XML > XML Catalog.

  4. On the XML Catalog panel, click the Add button.

  5. In the Add XML Catalog Element dialog, create a new Catalog entry by filling out the following inputs and clicking OK.

    The Location should be the Location of the Pull Reports™ XSD file, the Key type should be Namespace name, and the Key should be http://www.pullreports.com/catalog-1.4.0.

  6. After clicking "OK" to close the Preferences dialog, open a Pull Reports™ XML Catalog file, right click on the file, and select Validate to see validation errors.