Home / Documentation / Catalog Configuration Tutorials / Tutorial 5: Managing Complexity within XML Catalog files
«Prev   
Tutorial 5: Managing Complexity within XML Catalog files

Tutorial 5: Managing Complexity within XML Catalog files

Abstract

Learn how to use XML reference elements and entities includes to reduce redundant Pull Reports™ Ad Hoc Report and data service configuration.

Table of Contents

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

A Second Report

In the data model from Tutorial 2, the student table is only one of the possible base tables for <report> creation. By creating a second report based from the parent table, we may access a different view of the data model in which all parent rows are included in the export results by default, and all relationship joins originate from the parent table.

The following 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.7.0" id="class" name="Class Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.7.0
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.7.0.xsd">
    <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>
            <geojson geometryColumnPath="@home-geojson"/>
        </export_config>
        <table id="student" name="class_info.student" displayName="Student" primaryKeyColumns="id">
            <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>
                <label_value_list>
                    <label_value_query_default/>
                </label_value_list>
            </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">
                <output_transform>to_char(${this},'DD Mon YYYY')</output_transform>
            </column>
            <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">
                <output_transform>extract(month from ${this})</output_transform>
                <filter_column_transform>extract(month from ${this})</filter_column_transform>
                <label_value_list>
                    <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>
                </label_value_list>
            </column>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
                <output_transform>st_askml(${this})</output_transform>
            </column>
            <column id="home-geojson" name="home_geom" displayName="Home GeoJSON" export="false">
                <output_transform>st_asgeojson(${this})</output_transform>
            </column>
            <pre_filter>@active=true</pre_filter>
            <relationship>
                <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_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_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="Parent First Name"/> 
                    <column id="lastn" name="last_name" displayName="Parent Last Name"> 
                        <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_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" paramType="java.lang.Integer"/>
            <column id="firstn" name="first_name" displayName="Parent First Name"/> 
            <column id="lastn" name="last_name" displayName="Parent Last Name"> 
                <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_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_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>
                        <label_value_list>
                            <label_value_query_default/>
                        </label_value_list>
                    </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">
                        <output_transform>to_char(${this},'DD Mon YYYY')</output_transform>
                    </column>
                    <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">
                        <output_transform>extract(month from ${this})</output_transform>
                        <filter_column_transform>extract(month from ${this})</filter_column_transform>
                        <label_value_list>
                            <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>
                        </label_value_list>
                    </column>
                    <column id="active" name="is_active" displayName="Active?" 
                        paramType="java.lang.Boolean" export="false"/>
                    <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
                        <output_transform>st_askml(${this})</output_transform>
                    </column>
                    <column id="home-geojson" name="home_geom" displayName="Home GeoJSON" export="false">
                        <output_transform>st_asgeojson(${this})</output_transform>
                    </column>
                    <relationship>
                        <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>

The new parent-information report is then available via the Export Report REST API at the following URL:

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

Using Global Tables

Unfortunately, the second, parent-information <report> from the previous section replicates several of the table and column configuration elements from the first report. Fortunately, Pull Reports™ allows <table> elements to be defined globally within the <catalog> and referenced via <table_ref>s. Additionally, since global tables may themselves reference other global tables, it is easy to simplify our XML Catalog file by creating global tables for each table in the data model.

Here is the new XML Catalog file after refactoring the report table configuration into global tables and table references:

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.7.0" id="class" name="Class Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.7.0
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.7.0.xsd">
    <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>
            <geojson geometryColumnPath="@home-geojson"/>
        </export_config>
        <table_ref id="student" ref="student_global">
            <pre_filter>@active=true</pre_filter>
            <relationship>
                <join_column columnName="student_id" referencedColumnName="id" />
                <table_ref id="assignment" ref="assignment_global">
                    <relationship join="inner" cardinality="one">
                        <join_column columnName="class_id" referencedColumnName="id" />
                        <table_ref id="class" ref="class_global">
                            <relationship>
                                <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_ref>
                    </relationship>
                </table_ref>
            </relationship>
            <relationship>
                <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_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>
                        <join_column columnName="student_id" referencedColumnName="id" />
                        <table_ref id="assignment" ref="assignment_global">
                            <relationship join="inner" cardinality="one">
                                <join_column columnName="class_id" referencedColumnName="id" />
                                <table_ref id="class" ref="class_global"/>
                            </relationship>
                        </table_ref>
                    </relationship>
                </table_ref>
            </relationship>
        </table_ref>
    </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"/> 
    </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>
    <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="Parent First Name"/> 
        <column id="lastn" name="last_name" displayName="Parent Last Name"> 
            <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_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>
    <table id="student_global" name="class_info.student" displayName="Student" primaryKeyColumns="id">
        <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>
            <label_value_list>
                <label_value_query_default/>
            </label_value_list>
        </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">
            <output_transform>to_char(${this},'DD Mon YYYY')</output_transform>
        </column>
        <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">
            <output_transform>extract(month from ${this})</output_transform>
            <filter_column_transform>extract(month from ${this})</filter_column_transform>
            <label_value_list>
                <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>
            </label_value_list>
        </column>
        <column id="active" name="is_active" displayName="Active?" 
            paramType="java.lang.Boolean" export="false"/>
        <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
            <output_transform>st_askml(${this})</output_transform>
        </column>
        <column id="home-geojson" name="home_geom" displayName="Home GeoJSON" export="false">
            <output_transform>st_asgeojson(${this})</output_transform>
        </column>
    </table>
</catalog>

Global table ids vs. report table ids

In the above 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 table resource paths, never the ids of the referenced global tables.

Using Column Groups

Similar to global table configuration, lists 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 our report configuration, reuse the column configuration for the lastn and firstn columns of the student and parent tables via a <column_group>.

The new XML Catalog file with the name_column_group <column_group> is now:

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.7.0" id="class" name="Class Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.7.0
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.7.0.xsd">
    <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>
            <geojson geometryColumnPath="@home-geojson"/>
        </export_config>
        <table_ref id="student" ref="student_global">
            <pre_filter>@active=true</pre_filter>
            <relationship>
                <join_column columnName="student_id" referencedColumnName="id" />
                <table_ref id="assignment" ref="assignment_global">
                    <relationship join="inner" cardinality="one">
                        <join_column columnName="class_id" referencedColumnName="id" />
                        <table_ref id="class" ref="class_global">
                            <relationship>
                                <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_ref>
                    </relationship>
                </table_ref>
            </relationship>
            <relationship>
                <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_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>
                        <join_column columnName="student_id" referencedColumnName="id" />
                        <table_ref id="assignment" ref="assignment_global">
                            <relationship join="inner" cardinality="one">
                                <join_column columnName="class_id" referencedColumnName="id" />
                                <table_ref id="class" ref="class_global"/>
                            </relationship>
                        </table_ref>
                    </relationship>
                </table_ref>
            </relationship>
        </table_ref>
    </report>
    <column_group id="name_column_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>
            <label_value_list>
                <label_value_query_default/>
            </label_value_list>
        </column>
    </column_group>
    <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"/> 
    </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>
    <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="name_column_group"/>
        <column id="phone" name="phone" displayName="Phone"/> 
        <relationship>
            <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>
    <table id="student_global" name="class_info.student" displayName="Student" primaryKeyColumns="id">
        <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
        <column_group_ref ref="name_column_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">
            <output_transform>to_char(${this},'DD Mon YYYY')</output_transform>
        </column>
        <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">
            <output_transform>extract(month from ${this})</output_transform>
            <filter_column_transform>extract(month from ${this})</filter_column_transform>
            <label_value_list>
                <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>
            </label_value_list>
        </column>
        <column id="active" name="is_active" displayName="Active?" 
            paramType="java.lang.Boolean" export="false"/>
        <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
            <output_transform>st_askml(${this})</output_transform>
        </column>
        <column id="home-geojson" name="home_geom" displayName="Home GeoJSON" export="false">
            <output_transform>st_asgeojson(${this})</output_transform>
        </column>
    </table>
</catalog>

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. Then share the common, global <table> and <column_group> elements via an entity include. After the split into two <catalog>s, the reports will be available at the following URLs:

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

and

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

Why split reports into multiple catalogs?

It is useful to separate <report>s into two <catalog>s to apply different administrative controls such security, JDBC DataSource, or logging configuration.

The complete Pull Reports™ XML Catalog files are now:

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.7.0" id="student" name="Student Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.7.0
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.7.0.xsd">
    <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>
            <geojson geometryColumnPath="@home-geojson"/>
        </export_config>
        <table_ref id="student" ref="student_global">
            <pre_filter>@active=true</pre_filter>
            <relationship>
                <join_column columnName="student_id" referencedColumnName="id" />
                <table_ref id="assignment" ref="assignment_global">
                    <relationship join="inner" cardinality="one">
                        <join_column columnName="class_id" referencedColumnName="id" />
                        <table_ref id="class" ref="class_global">
                            <relationship>
                                <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_ref>
                    </relationship>
                </table_ref>
            </relationship>
            <relationship>
                <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>

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.7.0" id="parent" name="Parent Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.7.0
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.7.0.xsd">
    <report id="parent-information" name="Parent Information">
        <export_config defaultColumns="firstn,lastn"/>
        <table_ref id="parent" ref="parent_global">
            <relationship>
                <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>
                        <join_column columnName="student_id" referencedColumnName="id" />
                        <table_ref id="assignment" ref="assignment_global">
                            <relationship join="inner" cardinality="one">
                                <join_column columnName="class_id" referencedColumnName="id" />
                                <table_ref id="class" ref="class_global"/>
                            </relationship>
                        </table_ref>
                    </relationship>
                </table_ref>
            </relationship>
        </table_ref>
    </report>
    &globals;
</catalog>

global-resources.xml

<column_group id="name_column_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>
        <label_value_list>
            <label_value_query_default/>
        </label_value_list>
    </column>
</column_group>
<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"/> 
</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>
<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="name_column_group"/>
    <column id="phone" name="phone" displayName="Phone"/> 
    <relationship>
        <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>
<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="name_column_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">
        <output_transform>to_char(${this},'DD Mon YYYY')</output_transform>
    </column>
    <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">
        <output_transform>extract(month from ${this})</output_transform>
        <filter_column_transform>extract(month from ${this})</filter_column_transform>
        <label_value_list>
            <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>
        </label_value_list>
    </column>
    <column id="active" name="is_active" displayName="Active?" 
        paramType="java.lang.Boolean" export="false"/>
    <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
        <output_transform>st_askml(${this})</output_transform>
    </column>
    <column id="home-geojson" name="home_geom" displayName="Home GeoJSON" export="false">
        <output_transform>st_asgeojson(${this})</output_transform>
    </column>
</table>
Prev   
Tutorial 4: Adding KML and GeoJSON Export Home 
© Full Draft, LLC All Rights Reserved | Privacy Policy | Terms of Use