<url_template>

Abstract

Learn to configure the <url_template> element for Pull Reports™ Ad Hoc report and data service software.


Table of Contents

Catalog Configuration Java API analog:  ColumnConfigurationBuilder#setUrlTemplate

A <url_template> configures a template URL for a <column> value in the Export Report REST API. <url_template>s are useful for creating drill-through reports. The URL to value association depends on the format of the exported report. For instance, the htmltable format type outputs the URL as an HTML anchor (<a>) element surrounding the column value while the csv format type outputs the URL as an additional CSV column to the right of the value's column. See the Export Report REST API chapter for more information.

Usage

Creating a <url_template>

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

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

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

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

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

Requiring a column to be non-null:

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

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

Additional references

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

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

Exceptions when processing <url_template> values

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

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

Behind the scenes with <url_template>

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

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

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

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

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

In this way, a <url_template> of

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

becomes:

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

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

Basic <url_template> usage

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

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

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

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

Then the URL values for the rows will be:

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

Then assume a grade table with rows:

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

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

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

And the joined result rows will be:

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

Advanced <url_template> usage

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

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

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

Parents

<column>