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.
By default, the Export Report REST API exports database <column> values
in their raw format. You may use <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 birthdate 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.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"/> <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"/> <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" primaryKeyColumns="id"> <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' birthdate, birth year, and birth month as three separate columns.
columns=/student@bdate,bdate_yr,bdate_month
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.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"/> <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"/> <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" primaryKeyColumns="id"> <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.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"/> <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> </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" primaryKeyColumns="id"> <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 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%.
To create "drill-through" export results in which column values are hyperlinks to additional content,
use <url_template> to dynamically create URLs based on exported column
values and Groovy expressions.
To demonstrate, we will add <url_template> to the lastn <column>s of
the parent and student tables to create a URL to a fictional,
external contact management system.
In the <url_template>s, the ${@@firstn} and ${@@lastn}
expressions refer to the associated table's first_name and last_name
columns respectively. When the report is exported, these expressions are filled with the actual column
values from the exported row.
Here is the new Pull Reports™ XML Catalog file with the <url_template> elements applied to the
lastn columns.
<?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"/> <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> </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" primaryKeyColumns="id"> <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>
Now, requesting the report's Export Report REST API with this columns
parameter exports the students' last name column in which each value contains a custom hyperlink
to the http://www.pleasantville-schools.org/contacts/search URL.
columns=/student@lastn
For example, in the xml export format, if a
student table row had first_name value 'Tony' and last_name
value 'Martinez', the exported lastn column value would be:
<v url="http://www.pleasantville-schools.org/contacts/search?q=first_name%3DTony+and+last_name%3DMartinez">Martinez</v>
Label value lists are
an important usability improvement for the 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
<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.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"/> <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"/> <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" primaryKeyColumns="id"> <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>
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 com.pullreports.export.request.FilterValue;
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.JoinColumnsRelationshipConfiguration;
import com.pullreports.model.config.JoinTableRelationshipConfiguration;
import com.pullreports.model.config.PlaceholderDefaultQueryLabelValuesProvider;
import com.pullreports.model.config.PreFilter;
import com.pullreports.model.config.RelationshipConfiguration;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.TableConfiguration;
import com.pullreports.model.exportconfig.ExportConfiguration;
import jakarta.servlet.ServletContext;
import jakarta.servlet.http.HttpServletRequest;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
public class StudentTutorial3CatalogConfigurationFactory implements CatalogConfigurationFactory {
@Override
public CatalogConfiguration makeCatalog(ServletContext servletContext) {
ReportConfiguration studentReportConfiguration = new StudentReportConfigurationBuilder().build();
List<ReportConfiguration> reportConfigurations = Collections.singletonList(studentReportConfiguration);
return new CatalogConfiguration(new CatalogId("class"),"Class Reports",reportConfigurations);
}
private static class StudentReportConfigurationBuilder extends ReportConfiguration.Builder {
StudentReportConfigurationBuilder(){
super(new ReportId("student-information"),"Student Information"
,new StudentTableConfigurationBuilder().build());
setExportConfiguration(
new ExportConfiguration.Builder().setDefaultColumns(Arrays.asList(
StudentTableConfigurationBuilder.FIRSTN_COLUMN_CONFIGURATION.getId()
,StudentTableConfigurationBuilder.LASTN_COLUMN_CONFIGURATION.getId()))
.build()).build();
}
}
private final static class StudentTableConfigurationBuilder extends TableConfiguration.Builder {
static final ColumnConfiguration ID_COLUMN_CONFIGURATION,FIRSTN_COLUMN_CONFIGURATION
,LASTN_COLUMN_CONFIGURATION,ACTIVE_COLUMN_CONFIGURATION;
private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;
static {
ID_COLUMN_CONFIGURATION = new ColumnConfiguration.Builder(
new ColumnId("id"),"id").setDisplayName("Student ID")
.setParamType(ParamType.INTEGER).setExport(false).build();
FIRSTN_COLUMN_CONFIGURATION = new ColumnConfiguration.Builder(
new ColumnId("firstn"),"first_name").setDisplayName("First Name").build();
LASTN_COLUMN_CONFIGURATION = new ColumnConfiguration.Builder(
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 ColumnConfiguration.Builder(
new ColumnId("height_in"),"height_inches").setParamType(ParamType.DOUBLE)
.setDisplayName("Height (in)").build();
ColumnConfiguration bdateColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("bdate"),"birth_date").setParamType(ParamType.DATE)
.setOutputTransform("to_char(${this},'DD Mon YYYY')")
.setDisplayName("Birth Date").build();
ColumnConfiguration bdateYrColumnConfiguration = new ColumnConfiguration.Builder(
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 ColumnConfiguration.Builder(
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 ColumnConfiguration.Builder(
new ColumnId("active"),"active").setDisplayName("Active?")
.setParamType(ParamType.BOOLEAN).setExport(false).build();
COLUMN_CONFIGURATIONS = Arrays.asList(
ID_COLUMN_CONFIGURATION,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, FilterValue.asList(Boolean.TRUE));
setPreFilters(Collections.singletonList(activePreFilter));
RelationshipConfiguration assignmentRelationship =
new JoinColumnsRelationshipConfiguration.Builder(new AssignmentTableConfigurationBuilder().build()
,Collections.singletonList(new JoinColumn("student_id","id"))).build();
RelationshipConfiguration parentRelationship =
new JoinTableRelationshipConfiguration.Builder(new ParentTableConfigurationBuilder().build()
,new JoinTable("class_info.student_parent"
,Collections.singletonList(new JoinColumn("student_id","id"))
,Collections.singletonList(new JoinColumn("parent_id","id")))).build();
setRelationshipConfigurations(Arrays.asList(assignmentRelationship,parentRelationship));
setPrimaryKeyColumnIds(Collections.singleton(ID_COLUMN_CONFIGURATION.getId()));
}
}
private final static class AssignmentTableConfigurationBuilder extends TableConfiguration.Builder {
private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;
static {
ColumnConfiguration idColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("id"),"id").setDisplayName("Assignment ID")
.setParamType(ParamType.INTEGER).build();
ColumnConfiguration gradeColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("grade"),"grade").setDisplayName("Grade").build();
ColumnConfiguration nameColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("name"),"name").setDisplayName("Name").build();
COLUMN_CONFIGURATIONS = Arrays.asList(
idColumnConfiguration,gradeColumnConfiguration,nameColumnConfiguration);
}
AssignmentTableConfigurationBuilder(){
super(new TableId("assignment"),"Assignment",COLUMN_CONFIGURATIONS);
setName("class_info.assignment");
RelationshipConfiguration classRelationship =
new JoinColumnsRelationshipConfiguration.Builder(new ClassTableConfigurationBuilder().build()
,Collections.singletonList(new JoinColumn("class_id","id")))
.setJoin(Join.INNER).setCardinality(Cardinality.ONE).build();
setRelationshipConfigurations(Collections.singletonList(classRelationship));
}
}
private final static class ClassTableConfigurationBuilder extends TableConfiguration.Builder {
private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;
static {
ColumnConfiguration idColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("id"),"id").setDisplayName("Class ID")
.setParamType(ParamType.INTEGER).setExport(false).build();
ColumnConfiguration nameColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("name"),"name").setDisplayName("Class Name").build();
COLUMN_CONFIGURATIONS = Arrays.asList(
idColumnConfiguration,nameColumnConfiguration);
}
ClassTableConfigurationBuilder(){
super(new TableId("class"),"Class",COLUMN_CONFIGURATIONS);
setName("class_info.class");
RelationshipConfiguration studentAssessmentRelationship =
new JoinColumnsRelationshipConfiguration.Builder(new StudentAssessmentTableConfigurationBuilder().build()
,Arrays.asList(new JoinColumn("class_id","id")
,new JoinColumn("student_id","id",new TablePath("/student")))).build();
setRelationshipConfigurations(Collections.singletonList(studentAssessmentRelationship));
}
}
private final static class StudentAssessmentTableConfigurationBuilder extends TableConfiguration.Builder {
private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;
static {
ColumnConfiguration ratingColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("rating"),"rating").setDisplayName("Rating")
.setParamType(ParamType.INTEGER).build();
ColumnConfiguration assessmentColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("assessment"),"assessment").setDisplayName("Assessment").build();
ColumnConfiguration assessmentDateColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("assessment_date"),"assessment_date").setParamType(ParamType.DATE)
.setDisplayName("Assessment Date").build();
COLUMN_CONFIGURATIONS = Arrays.asList(
ratingColumnConfiguration,assessmentColumnConfiguration
,assessmentDateColumnConfiguration);
}
StudentAssessmentTableConfigurationBuilder(){
super(new TableId("student_assessment"),"Student Assessment",COLUMN_CONFIGURATIONS);
setName("class_info.student_assessment");
}
}
private final static class ParentTableConfigurationBuilder extends TableConfiguration.Builder {
private static final ColumnConfiguration ID_COLUMN_CONFIGURATION;
private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;
static {
ID_COLUMN_CONFIGURATION = new ColumnConfiguration.Builder(
new ColumnId("id"),"id").setDisplayName("Parent ID")
.setParamType(ParamType.INTEGER).build();
ColumnConfiguration firstnColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("firstn"),"first_name").setDisplayName("First Name").build();
ColumnConfiguration lastnColumnConfiguration = new ColumnConfiguration.Builder(
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 ColumnConfiguration.Builder(
new ColumnId("phone"),"phone").setDisplayName("Phone").build();
COLUMN_CONFIGURATIONS = Arrays.asList(
ID_COLUMN_CONFIGURATION,firstnColumnConfiguration
,lastnColumnConfiguration,phoneColumnConfiguration);
}
ParentTableConfigurationBuilder(){
super(new TableId("parent"),"Parent",COLUMN_CONFIGURATIONS);
setName("class_info.parent");
RelationshipConfiguration companyRelationship =
new JoinTableRelationshipConfiguration.Builder(new CompanyTableConfigurationBuilder().build()
,new JoinTable("class_info.parent_company"
,Collections.singletonList(new JoinColumn("parent_id","id"))
,Collections.singletonList(new JoinColumn("company_id","id")))).build();
setRelationshipConfigurations(Collections.singletonList(companyRelationship));
setPrimaryKeyColumnIds(Collections.singleton(ID_COLUMN_CONFIGURATION.getId()));
}
}
private final static class CompanyTableConfigurationBuilder extends TableConfiguration.Builder {
private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;
static {
ColumnConfiguration idColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("id"),"id").setDisplayName("Company ID")
.setParamType(ParamType.INTEGER).setExport(false).build();
ColumnConfiguration nameColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("name"),"name").setDisplayName("Company").build();
COLUMN_CONFIGURATIONS = Arrays.asList(
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.