Tutorial 3: Add Transformation, Hyperlink, and Label Value Column Configuration

Abstract

Learn how to add transformation, drill through hyperlinks, and label value lists to Ad Hoc Report and data service configuration in Pull Reports™.


In this tutorial, we build on the catalog configuration from Tutorial 2 by adding additional configuration elements to the <column>s.

Output Transforms

By default, the Export Report REST API exports database <column> values in their raw format. You may use an <output_transform> to change the exported value of a column via native database functions and operators. Within an <output_transform> use ${this} to refer to the database column just as one would within a SQL query.

A common usage of output transformation is to transform columns of date or time data type into human readable character strings. Additionally, an output transform may be used to extract part of the date value into a completely new <column>. In our report, we will transform the base, student table's birth_date column into a DD Mon YYYY character string and make two additional columns to export the birth date year and month respectively. In this example, we assume the underlying database is PostgreSQL and use the to_char and extract functions within our transformations.

Here is the new XML Catalog file with the two, additional bdate_yr and bdate_month columns plus the output transformation on the bdate column:

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.5.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">
                <output_transform>to_char(${this},'DD Mon YYYY')</output_transform>
            </column>
            <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="bdate_month" name="birth_date" displayName="Birth Date Month" paramType="java.sql.Date">
                <output_transform>extract(month from ${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_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"/> 
                    <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>
</catalog>

Now, requesting the report's Export Report REST API with this columns parameter exports the students' birth date, birth year, and birth month as three separate columns.

columns=/student@bdate,bdate_yr,bdate_month

Filter Transformation

When the Export Report REST API applies filter parameters to limit the export results, it creates parameterized SQL "where" clauses against the filtered column.

For instance, in our example report, the following filter parameter:

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

generates this SQL where clause:

where birth_date > ?

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

At times it is useful to transform the column and/or value parts of the where clause with a database function or operation. For example, it is useful to be able to filter the new bdate_yr and bdate_month columns we added in the previous section by an Integer year or month respectively instead of a Date value.

To demonstrate filter transformation, will we expand our example with <filter_column_transform> elements within both the bdate_yr and bdate_month columns. In the respective <column> definitions, notice we also change the paramType to java.lang.Integer because the <filter_column_transform> functions change the filter parameter type from the birth_date column's native, Date type to an Integer.

Here is the new XML Catalog file with the <filter_column_transform> configuration:

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.5.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">
                <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>
            </column>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <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"/> 
                    <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>
</catalog>

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

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

It is also possible to transform the value term in a filter via database functions or operations through the use of a <filter_value_transform>. Within a <filter_value_transform> use ? to refer to the parameterized value just as one would within a SQL query.

A common use of a <filter_value_transform> in conjunction with a <filter_column_transform> is to transform a provided filter value to uppercase in order to do case-insensitive string comparison. For example, to allow for case-insensitive comparisons on the student table's lastn column, covert the filter column and value to uppercase via the SQL upper function. By uppercasing both the column and value, any filters applied to the lastn column become case-insensitive.

Here is the new Pull Reports™ XML Catalog file with the <filter_column_transform> and <filter_value_transform> applied to the lastn column:

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.5.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">
                <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">
                <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>
            </column>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <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"/> 
                    <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>
</catalog>

Now, in order to export all students whose last name starts with 'N', this filter parameter would suffice regardless of whether or not the first letter of the last name was capitalized:

filter like 'n%'

The parameter results in a SQL where clause of

where upper(last_name) like upper(?)

in which the ? query parameter is set to n%.

Label Value Lists

Label value lists 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 having to "manually" type filter values. For instance, in our example report, associating a label value list with the bdate_month column allows a user of the creator to select the month value (e.g. "March") when creating a filter on the bdate_month <column> instead of typing "3".

Label value lists are either "static", meaning that the values are listed within the catalog configuration, or "query", meaning that the values are queried from the database. Define static label value lists with the <label_values> element. Use <label_values> when it is inconvenient or difficult to define the set of acceptable values via a database query. Define query label value lists with a <label_value_query> or <label_value_query_default> element. Both elements retrieve the label values from a SQL query.

In the new XML Catalog file below, we use a <label_values> element to represent the twelve calendar months for the bdate_month column and a <label_value_query_default> to retrieve the distinct list of student last names for the lastn 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.5.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">
                <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"/>
            <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>
</catalog>

Column Configuration in the Catalog Configuration Java API

So far in this tutorial, all examples have been XML Catalog files. Here is a Catalog Configuration Java API CatalogConfigurationFactory which is identical to the final XML Catalog file.

package com.pullreports.examples.tutorial;

import java.util.Arrays;
import java.util.List;

import javax.servlet.ServletContext;

import com.pullreports.export.request.Operator;
import com.pullreports.model.Cardinality;
import com.pullreports.model.CatalogId;
import com.pullreports.model.ColumnId;
import com.pullreports.model.Join;
import com.pullreports.model.JoinColumn;
import com.pullreports.model.JoinTable;
import com.pullreports.model.LabelValue;
import com.pullreports.model.ParamType;
import com.pullreports.model.ReportId;
import com.pullreports.model.TableId;
import com.pullreports.model.TablePath;
import com.pullreports.model.config.CatalogConfiguration;
import com.pullreports.model.config.CatalogConfigurationFactory;
import com.pullreports.model.config.ColumnConfiguration;
import com.pullreports.model.config.ColumnConfiguration.ColumnConfigurationBuilder;
import com.pullreports.model.config.PlaceholderDefaultQueryLabelValuesProvider;
import com.pullreports.model.config.PreFilter;
import com.pullreports.model.config.RelationshipConfiguration;
import com.pullreports.model.config.JoinColumnsRelationshipConfiguration.JoinColumnsRelationshipConfigurationBuilder;
import com.pullreports.model.config.JoinTableRelationshipConfiguration.JoinTableRelationshipConfigurationBuilder;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.ReportConfiguration.ReportConfigurationBuilder;
import com.pullreports.model.config.TableConfiguration.TableConfigurationBuilder;
import com.pullreports.model.exportconfig.ExportConfiguration.ExportConfigurationBuilder;
import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;

public class StudentTutorial3CatalogConfigurationFactory implements CatalogConfigurationFactory {

    @Override
    public CatalogConfiguration makeCatalog(ServletContext servletContext) {

        ReportConfiguration studentReportConfiguration = new StudentReportConfigurationBuilder().build();
        
        List<ReportConfiguration> reportConfigurations = Arrays.asList(
            new ReportConfiguration[]{studentReportConfiguration});

        return new CatalogConfiguration(new CatalogId("class"),"Class Reports",reportConfigurations);
    }

    private class StudentReportConfigurationBuilder extends ReportConfigurationBuilder {
        StudentReportConfigurationBuilder(){
            super(new ReportId("student-information"),"Student Information"
                ,new StudentTableConfigurationBuilder().build());
            setExportConfiguration(
                new ExportConfigurationBuilder().setDefaultColumns(Arrays.asList(
                    new ColumnId[]{StudentTableConfigurationBuilder.FIRSTN_COLUMN_CONFIGURATION.getId()
                        ,StudentTableConfigurationBuilder.LASTN_COLUMN_CONFIGURATION.getId()}))
                .build()).build();
        }
    }

    private final static class StudentTableConfigurationBuilder extends TableConfigurationBuilder {

        static final ColumnConfiguration FIRSTN_COLUMN_CONFIGURATION
            ,LASTN_COLUMN_CONFIGURATION,ACTIVE_COLUMN_CONFIGURATION;

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

        static {
            ColumnConfiguration idColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("id"),"id").setDisplayName("Student ID")
                .setParamType(ParamType.INTEGER).setExport(false).build();

            FIRSTN_COLUMN_CONFIGURATION = new ColumnConfigurationBuilder(
                new ColumnId("firstn"),"first_name").setDisplayName("First Name").build();

            LASTN_COLUMN_CONFIGURATION = new ColumnConfigurationBuilder(
                new ColumnId("lastn"),"last_name").setDisplayName("Last Name")
                .setFilterColumnTransform("upper(${this})")
                .setFilterValueTransform("upper(?)")
                .setUrlTemplate("http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}")
                .setLabelValuesProvider(new PlaceholderDefaultQueryLabelValuesProvider()).build();

            ColumnConfiguration heightInColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("height_in"),"height_inches").setParamType(ParamType.DOUBLE)
                .setDisplayName("Height (in)").build();

            ColumnConfiguration bdateColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("bdate"),"birth_date").setParamType(ParamType.DATE)
                .setOutputTransform("to_char(${this},'DD Mon YYYY')")
                .setDisplayName("Birth Date").build();

            ColumnConfiguration bdateYrColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("bdate_yr"),"birth_date").setParamType(ParamType.INTEGER)
                .setOutputTransform("extract(year from ${this})")
                .setFilterColumnTransform("extract(year from ${this})")
                .setDisplayName("Birth Date Year").build();

            ColumnConfiguration bdateMonthColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("bdate_month"),"birth_date").setParamType(ParamType.INTEGER)
                .setOutputTransform("extract(month from ${this})")
                .setFilterColumnTransform("extract(month from ${this})")
                .setDisplayName("Birth Date Month")
                .setLabelValuesProvider((DataSource ds, HttpServletRequest hsr) -> {
                    LabelValue[] labelValues = new LabelValue[]{
                        new LabelValue("1","January")
                        ,new LabelValue("2","February")
                        ,new LabelValue("3","March")
                        ,new LabelValue("4","April")
                        ,new LabelValue("5","May")
                        ,new LabelValue("6","June")
                        ,new LabelValue("7","July")
                        ,new LabelValue("8","August")
                        ,new LabelValue("9","September")
                        ,new LabelValue("10","October")
                        ,new LabelValue("11","November")
                        ,new LabelValue("12","December")
                    };
                    return Arrays.asList(labelValues);
            }).build();
            
            ACTIVE_COLUMN_CONFIGURATION = new ColumnConfigurationBuilder(
                new ColumnId("active"),"active").setDisplayName("Active?")
                .setParamType(ParamType.BOOLEAN).setExport(false).build();

            COLUMN_CONFIGURATIONS = Arrays.asList(
                new ColumnConfiguration[]{
                    idColumnConfiguration,FIRSTN_COLUMN_CONFIGURATION,LASTN_COLUMN_CONFIGURATION
                    ,heightInColumnConfiguration,bdateColumnConfiguration,bdateYrColumnConfiguration
                    ,bdateMonthColumnConfiguration,ACTIVE_COLUMN_CONFIGURATION});
        }

        StudentTableConfigurationBuilder(){
            super(new TableId("student"),"Student",COLUMN_CONFIGURATIONS);
            setName("class_info.student");
            PreFilter activePreFilter = new PreFilter(ACTIVE_COLUMN_CONFIGURATION.getId()
                ,Operator.EQUALS, Arrays.asList(new Boolean[]{Boolean.TRUE}));
            setPreFilters(Arrays.asList(new PreFilter[]{activePreFilter}));

            RelationshipConfiguration assignmentRelationship = 
                new JoinColumnsRelationshipConfigurationBuilder(new AssignmentTableConfigurationBuilder().build()
                ,Arrays.asList(new JoinColumn[]{new JoinColumn("student_id","id")})).build();

            RelationshipConfiguration parentRelationship = 
                new JoinTableRelationshipConfigurationBuilder(new ParentTableConfigurationBuilder().build()
                ,new JoinTable("class_info.student_parent"
                    ,Arrays.asList(new JoinColumn[]{new JoinColumn("student_id","id")})
                    ,Arrays.asList(new JoinColumn[]{new JoinColumn("parent_id","id")}))).build();

            setRelationshipConfigurations(Arrays.asList(new RelationshipConfiguration[]{
                assignmentRelationship,parentRelationship}));
        }
    }

    private final static class AssignmentTableConfigurationBuilder extends TableConfigurationBuilder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

        static {
            ColumnConfiguration idColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("id"),"id").setDisplayName("Assignment ID")
                .setParamType(ParamType.INTEGER).build();

            ColumnConfiguration gradeColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("grade"),"grade").setDisplayName("Grade").build();

            ColumnConfiguration nameColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("name"),"name").setDisplayName("Name").build();

            COLUMN_CONFIGURATIONS = Arrays.asList(
                new ColumnConfiguration[]{
                    idColumnConfiguration,gradeColumnConfiguration,nameColumnConfiguration});
        }

        AssignmentTableConfigurationBuilder(){
            super(new TableId("assignment"),"Assignment",COLUMN_CONFIGURATIONS);
            setName("class_info.assignment");

            RelationshipConfiguration classRelationship = 
                new JoinColumnsRelationshipConfigurationBuilder(new ClassTableConfigurationBuilder().build()
                ,Arrays.asList(new JoinColumn[]{new JoinColumn("class_id","id")}))
                    .setJoin(Join.INNER).setCardinality(Cardinality.ONE).build();

            setRelationshipConfigurations(Arrays.asList(new RelationshipConfiguration[]{
                classRelationship}));
        }

    }

    private final static class ClassTableConfigurationBuilder extends TableConfigurationBuilder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

        static {
            ColumnConfiguration idColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("id"),"id").setDisplayName("Class ID")
                .setParamType(ParamType.INTEGER).setExport(false).build();

            ColumnConfiguration nameColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("name"),"name").setDisplayName("Class Name").build();

            COLUMN_CONFIGURATIONS = Arrays.asList(
                new ColumnConfiguration[]{
                    idColumnConfiguration,nameColumnConfiguration});
        }

        ClassTableConfigurationBuilder(){
            super(new TableId("class"),"Class",COLUMN_CONFIGURATIONS);
            setName("class_info.class");

            RelationshipConfiguration studentAssessmentRelationship = 
                new JoinColumnsRelationshipConfigurationBuilder(new StudentAssessmentTableConfigurationBuilder().build()
                ,Arrays.asList(new JoinColumn[]{new JoinColumn("class_id","id")
                    ,new JoinColumn("student_id","id",new TablePath("/student"))})).build();

            setRelationshipConfigurations(Arrays.asList(new RelationshipConfiguration[]{
                studentAssessmentRelationship}));
        }
    }

    private final static class StudentAssessmentTableConfigurationBuilder extends TableConfigurationBuilder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

        static {
            ColumnConfiguration ratingColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("rating"),"rating").setDisplayName("Rating")
                .setParamType(ParamType.INTEGER).build();

            ColumnConfiguration assessmentColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("assessment"),"assessment").setDisplayName("Assessment").build();

            ColumnConfiguration assessmentDateColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("assessment_date"),"assessment_date").setParamType(ParamType.DATE)
                .setDisplayName("Assessment Date").build();

            COLUMN_CONFIGURATIONS = Arrays.asList(
                new ColumnConfiguration[]{
                    ratingColumnConfiguration,assessmentColumnConfiguration
                    ,assessmentDateColumnConfiguration});
        }

        StudentAssessmentTableConfigurationBuilder(){
            super(new TableId("student_assessment"),"Student Assessment",COLUMN_CONFIGURATIONS);
            setName("class_info.student_assessment");
        }
    }

    private final static class ParentTableConfigurationBuilder extends TableConfigurationBuilder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

        static {

            ColumnConfiguration idColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("id"),"id").setDisplayName("Parent ID")
                .setParamType(ParamType.INTEGER).build();

            ColumnConfiguration firstnColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("firstn"),"first_name").setDisplayName("First Name").build();

            ColumnConfiguration lastnColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("lastn"),"last_name").setDisplayName("Last Name")
                .setUrlTemplate("http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}").build();

            ColumnConfiguration phoneColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("phone"),"phone").setDisplayName("Phone").build();

            COLUMN_CONFIGURATIONS = Arrays.asList(
                new ColumnConfiguration[]{
                    idColumnConfiguration,firstnColumnConfiguration
                    ,lastnColumnConfiguration,phoneColumnConfiguration});
        }

        ParentTableConfigurationBuilder(){
            super(new TableId("parent"),"Parent",COLUMN_CONFIGURATIONS);
            setName("class_info.parent");

            RelationshipConfiguration companyRelationship = 
                new JoinTableRelationshipConfigurationBuilder(new CompanyTableConfigurationBuilder().build()
                ,new JoinTable("class_info.parent_company"
                    ,Arrays.asList(new JoinColumn[]{new JoinColumn("parent_id","id")})
                    ,Arrays.asList(new JoinColumn[]{new JoinColumn("company_id","id")}))).build();

            setRelationshipConfigurations(Arrays.asList(new RelationshipConfiguration[]{
                companyRelationship}));
        }
    }

    private final static class CompanyTableConfigurationBuilder extends TableConfigurationBuilder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

        static {
            ColumnConfiguration idColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("id"),"id").setDisplayName("Company ID")
                .setParamType(ParamType.INTEGER).setExport(false).build();

            ColumnConfiguration nameColumnConfiguration = new ColumnConfigurationBuilder(
                new ColumnId("name"),"name").setDisplayName("Company").build();

            COLUMN_CONFIGURATIONS = Arrays.asList(
                new ColumnConfiguration[]{
                    idColumnConfiguration,nameColumnConfiguration});
        }

        CompanyTableConfigurationBuilder(){
            super(new TableId("company"),"Company",COLUMN_CONFIGURATIONS);
            setName("class_info.company");
        }
    }
}

This concludes the third tutorial regarding advanced <column> configuration. Continue to Tutorial 4 to learn about configuring spatial export.