GET Export Report


Overview

The GET Export Report API is the core endpoint of the Pull Reports™ REST API. It exports the Pull Report identified by the [catalog id] and [report id] path elements to the export format identified by the format parameter. The export results may be further customized via the filter, columns, sort, distinct, limit, and offset parameters.

URL

/[context]/pullreports/catalog/[catalog id]/report/[report id]/export

Components

/[context]

The web application context path of the WAR which contains the Pull Reports™ JAR. Typically this is the name of the WAR file.

/pullreports

The URL path to the PullReportsServlet. This path is configured automatically web.xml when installing Pull Reports™.

/catalog/[catalog id]

The id of the <catalog> as configured in Pull Reports™ XML Catalog files.

/report/[report id]

The id of the <report> as configured in the encompassing <catalog>.

/export

The export endpoint.

Related Endpoints

The GET Export Report endpoint is supported by other endpoints within the REST API.

The GET Export Parse Parameters endpoint receives the same parameter API as does GET Export Report but returns a JSON representation of the parsed parameters and includes any errors encountered when parsing. Use this endpoint to validate GET Export Report parameters without receiving an 400 status error.

The GET Export SQL endpoint also receives the same parameter API as does GET Export Report but returns a JSON representation of the SQL and SQL parameters used to fulfill the export result. Use this endpoint to retrieve the SQL associated with a GET Export Report request.

Parameters

This section defines the HTTP parameters available within the Export Report API. For users unfamiliar with the API, the best introduction is to use the Pull ReportsAd Hoc Report Creator to create a valid API request and then customize the request further based on this documentation.

columns

The value of the columns parameter is a semicolon (;) separated list of table resource paths or column resource paths to include, in order, in the export results. Specify a table resource path to include all exportable columns from the referenced table. Use a column resource path to control the exact columns to be included and column order. For users familiar with SQL, the columns parameter is analogous to the columns listed within a SQL "select" clause.

At least one column from the report's base table will always be included within the columns parameter. The inclusion is either explicit, meaning one or more base table column resource paths are specified within the parameter, or implicit, meaning the base table columns are not specified within the parameter but are include by the default behavior detailed below.

If one or more table or column resource paths to non-base table columns are specified within the columns parameter, Pull Reports™ will construct all necessary SQL joins to the referenced database tables based on the report's <relationship> configuration.

Example 1. columns parameter usage

The following example exports, in order,

  • the name <column> from the base <table>,

  • the name <column> from the employer relationship <table>,

  • and then the id, address_street, and address_state <column>s from the base <table>.

columns=@name;/employer@name;@id,address_street,address_state

columns parameter rules

Default behavior if the columns parameter is not specified

If the columns parameter is not specified, then the export result will contain all exportable <column>s from the report's base table. "Exportable" columns are those columns whose export attribute is true.

Alternatively, if the report's <export_config> defaultColumns attribute is specified, then the export result will contain only the specified default columns.

Base table columns are added if missing base table path

If the columns parameter is specified but the value contains no table or column resource path to the report's base table, then all exportable <column>s from the report's base table are included as the first columns within the export result.

Alternatively, if the report's <export_config> defaultColumns attribute is specified, then the specified default columns are included as the first columns within the export result.

For example, given a base table path of /base, a child, relationship table path of /base/child, and a defaultColumns value of id,name, these columns parameter values are equivalent because the defaultColumns attribute prepends the base table's id and name columns to the export results if no other base table columns are specified.

columns=/base@id,name;/base/child
columns=/base/child
Table resources paths without columns are expanded to include all exportable columns

Table resource paths specified within the columns parameter without additional column ids result in all exportable <column>s from the referenced table to be included within the export result. "Exportable" columns are those columns whose export attribute is true.

For users familiar with SQL, specifying a table resource path without additional column ids is analogous to the SELECT t.* FROM tablename AS t SQL syntax.

For example, given a base table path of /person with three exportable <column>s with ids of id, name, and age, these columns parameter values are equivalent:

columns=/person
columns=/person@id,name,age
Specifying non-exportable columns returns 400 error

If a column resource path within the columns parameter references a non-exportable column, a 400 status code is returned. "Non-exportable" columns are those columns whose export attribute is false.

For example, if the /client table's is_active column is attributed with export='false', the following columns parameter will return a 400 status code:

columns=/client@id,name,is_active #Bad!

distinct

The distinct parameter removes duplicative records from the export results if set to true. For users familiar with SQL, the behavior of the distinct parameter is analogous to the SQL distinct keyword such as SELECT distinct t.name FROM tablename as t.

If not specified, the distinct parameter's default value is false.

Example 2. distinct parameter usage

One common use of the distinct parameter is to remove duplicate records from an export result caused by a relational join in which the returned columns specified by the columns parameter do not uniquely identify each result row.

For example, imagine a data domain in which a person may own zero to many houses. Each person has a first and last name attribute, and each house has an address attribute which includes the street and U.S. State at which the house is located. A Pull Report configured for this domain could have a base table of person with a child relationship table of house.

Given this report configuration, the following HTTP request parameters to the GET Export Report API would return one row per person-house relationship.

columns=/person@first_name,last_name;/person/house@street,state

To export just the person's name and house's state, remove the street column from the /person/house table resource path. However, in the export result, a person's name and state will be repeated if the person owns multiple houses in a single state.

columns=/person@first_name,last_name;/person/house@state

If the desired export result is to only have one row per distinct person name to state relationship regardless of the number of houses a person owns in a state, add the distinct parameter.

columns=/person@first_name,last_name;/person/house@state&distinct=true

filter

The filter parameter limits the export results to those rows which match the filter condition(s). The filter parameter may be specified multiple times. Each filter is independently AND'ed to the Export Report SQL query. Additionally, a single filter parameter may have multiple filter terms separated by the or keyword. For users familiar with SQL, the filter parameter is analogous to the terms within a SQL WHERE clause.

Export results may also be filtered via the <pre_filter> catalog configuration element. If present, filters applied via the filter HTTP parameter are always applied in addition to<pre_filter>s.

Filter Syntax

A single filter parameter follows this pattern:

[Filter Term] (or [Filter Term])*

where a Filter Term is:

[Column Resource Path] [Operator] (Values)?

Within one [Filter Term], the [column resource path] is the path to the <column> on which to apply the filter. The [Operator] is the comparison function to apply to the filter. The (Values)? clause is required for some Operators but not for others. For instance, the not null operator does not take a value while the equality, =, operator does.

Multiple [Filter Term]s may be separated by the or keyword in a single filter parameter to indicate the terms should be logically or'ed together in the resultant query.

Example 3. filter parameter usage

The following filter parameter example assumes a data domain of three tables. The first, student, contains information about students within a school. The second, parent, contains information about the student's parents. The third, student_parent, is a many-to-many indirection table relating students to parents.

The following Pull Reports™ XML Catalog file creates a Pull Report on this data domain:

<?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 Report">
        <export_config defaultColumns="id"/>
        <table id="student" name="person" displayName="Person Info"> 
            <column id="id" name="student" paramType="java.lang.Integer"/>
            <column id="age" name="age" paramType="java.lang.Integer"/>
            <column id="birth_date" name="bdate" paramType="java.sql.Date"/>
            <column id="address_street" name="street"/>
            <column id="address_state" name="state"/>
            <relationship join="left" cardinality="many">
                <join_table name="student_parent">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                    </inverse_join_columns>
                </join_table>
                <table id="parent" name="parent" displayName="Parent">
                    <column id="id" name="student" paramType="java.lang.Integer"/>
                    <column id="first_name" name="first_name"/>
                    <column id="last_name" name="last_name"/>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

To begin, create a filter to limit the export results to records within the student table whose age column has a value greater than 8.

filter=/student@age > 8

This second filter finds student records joined to a parent table row which has a last_name column starting with the letter 'H' or student table records which have a state column value of 'Colorado' or 'Wyoming':

filter=/student/parent@last_name like 'H%' or /student@address_state in ('Colorado','Wyoming')

Specifying the two filters in a single Export Report URL query string logically AND's the filters together. This means each record in the export result must match the criteria of both filters.

filter=/student@age > 8&filter=/student/parent@last_name like 'H%' or /student@address_state in ('Colorado','Wyoming')

Here are the same filter parameters URL encoded.

filter=%2Fstudent%40age%20%3E%208&filter=%2Fstudent%2Fparent%40last_name%20like%20%27H%25%27%20or%20%2Fstudent%40address_state%20in%20%28%27Colorado%27%2C%27Wyoming%27%29

Continue reading to learn about additional filter operators and how to correctly express filter values.


Filter Operators

The following table lists the available filter operators.

Table 1. Filter Operators
OperatorDescription
=, !=, <>

Equality / Inequality.

Example: /student@id = 145

<, >, <=, >=

Comparison.

Example: /student/parent@first_name > 'F'

between X and Y

Range Comparison.

Example: /student@birth_date between '2001-01-01' and '2001-12-31'

in (...)

Equal to at least one of.

Example: /student@address_state in ('Missouri','Kansas')

not in (...)

Unequal to all of.

Example: /student@address_state not in ('Kentucky','Delaware')

like, not like

String pattern search.

Example: /student@address_street like '%Cherry%'

is null, is not null

Null value.

Example: /student@birth_date is not null


Value Syntax

Values within filters parameters are of one of the following types:

Table 2. Filter value types
TypeDescription
Numeric

Any integer or floating point number. For example: 22, 31.72, or -0.22.

Commas (,) within numeric values are not supported.

String

Any single quoted value. For example: 'Foo'.

Escape single quotes (') within a String with a second a single quote. For example: 'Mister Jones'' watermelon'.

See Filter Value Coercion below for how Strings may be coerced info Dates.

Booleantrue or false. Because Boolean values are case insensitive, TRUE or FALSE are also valid.

Filter Value Coercion

Pull Reports™ coerces filter values to the referenced <column>'s paramType via the following rules before passing the value to the JDBC driver.

The Export Report API will return a 400 HTTP response if it encounters an error coercing a filter value.

Table 3. Filter Value Coercion Rules
Value Type<column> paramTypeCoercion
Numericjava.lang.StringNumber will be turned into String
 java.sql.Date, java.sql.Time, java.sql.Timestamp

The long (64 bit) value of the number is understood to be the number of milliseconds since the Epoch (January 1st, 1970) and converted to a date.

For instance: 100000 is Friday, January 2, 1970 3:46:40 AM GMT

 java.lang.BooleanIf the value equals 1, coerce to true. If the value equals 0, coerce to false.
Stringjava.lang.Long, java.lang.Integer, java.lang.Float, or java.lang.DoubleThe String will be parsed to a Number
 java.sql.Date

The String will be parsed to a java.sql.Date by the Java java.text.SimpleDateFormat class using the yyyy-MM-dd format pattern.

The value must match the yyyy-MM-dd pattern. For example: '2010-08-11'.

 java.sql.Time

The String will be parsed to a Date by the Java java.text.SimpleDateFormat class using one of the following format patterns determined by the character length of the String.

If the character length is 8 characters, the String will be parsed with the HH:mm:ss pattern. For example: '23:14:43'.

If the character length is 12 characters, the String will be parsed with the HH:mm:ss.SSS pattern. For example: '04:00:01.332'.

 java.sql.Timestamp

The String will be parsed to a Timestamp by the Java java.text.SimpleDateFormat class using one of the following format patterns determined by the character length of the String.

If the character length is 10 characters, the String will be parsed with the yyyy-MM-dd pattern. For example: '1975-12-28'.

If the character length is 19 characters, the String will be parsed with the yyyy-MM-dd HH:mm:ss pattern. For example: '2008-12-02 08:23:00'.

If the character length is 23 characters, the String will be parsed with the yyyy-MM-dd HH:mm:ss.SSS pattern. For example: '2004-09-28 10:53:20.921'.

 java.lang.BooleanIf the String equals '1' or case insensitive 'true', then the value will be coerced to true. Otherwise, the value will be coerced to false.
Booleanjava.lang.Long, java.lang.Integer, java.lang.Float, or java.lang.DoubleIf the value equals true, then the value will be coerced to 1. If value equals false, then the value will be coerced to 0.
 java.lang.StringIf value equals true, then the value will be coerced to 'true'. If value equals false, then the value will be coerced to 'false'.

format

The format parameter specifies the format and content type of the exported results. Valid values are xml ,json ,html ,htmltable ,kml ,geojson ,map ,or csv. If not specified, the default is json.

See the response documentation for detailed information about format response structure.

Example 4. format parameter usage

The following format parameter exports the results in the map format:

format=map


limit

The limit parameter is a non-negative integer which limits the maximum number of export results returned. Note that regardless of the value of the limit parameter, the maximum number of exports results may never exceed the export.report.maxResults installation parameter.

Example 5. limit parameter usage

The following limit parameter limits the export results to 500 records:

limit=500


offset

The offset parameter is a non-negative integer indicating the offset of the export results.

Note

Some database vendors do not support offset without also specifying the limit parameter. In this case, a 500 HTTP response status is returned.

Example 6. offset parameter usage

The following offset parameter excludes the first 50 records from the export result:

offset=50


sort

The value of the sort parameter is a semicolon (;) separated list of column resource paths which specify, in order, the desired sort of the export results.

Each sorted column must be included in the export results either by specific inclusion in the columns parameter or by the default exported columns if the columns parameter is not specified. The Export Report API will return a 400 HTTP status code if the sort parameter references a column not included within the export results.

Appending desc or asc to a column resource path will set the sort order to be descending or ascending, respectively. The default sort order is ascending.

If the sort parameter is not specified, the results will sort based on the defaultSort attribute of the <report>'s <export_config>, if specified.

The base table path is optional in sort parameter

It is unnecessary to include the base table path within the sort parameter column resource paths. Because of this, the following sort parameter values are equivalent if the <report>'s base table has id="base" with a <column> of id="name":

  • sort=@name

  • sort=/base@name

  • sort=/base@name asc

Example 7. sort parameter usage

The following sort parameter example sorts the export results first by ascending name column on the base table and then descending birth_date column on the parent relationship table:

sort=@name;/parent@birth_date desc


Response

XML Format

The xml format writes XML to the HTTP response body.

Format Parameter:  xml

Response Content-Type:  application/xml

Response Content-Disposition:  The Content-Disposition HTTP header is set to the value of the <report> name attribute with the underscore character (_) substituted for any space, comma, or semicolon character plus the .xml extension.

OK Response

Status Code:  200

XML responses without error have this DTD structure:

<!DOCTYPE results [
<!ELEMENT results (meta,data)>

<!-- meta: contains meta information about the results. -->
<!ELEMENT meta (filters?,subqueries?,columns,totalCount)>

<!-- filters: (Optional) contains information about the filters applied to this 
     export result if applicable. -->
<!ELEMENT filters (filter+)>

<!-- filter: Contains information about one filter applied to this export result. -->
<!ELEMENT filter (source,readable)>

<!-- source: String representation of the original filter parameter. -->
<!ELEMENT source (#PCDATA)>

<!-- readable: Contains the readable filter String with Table and Column 
     DisplayName's substituted for id's. -->
<!ELEMENT readable (#PCDATA)>

<!-- subqueries: (Optional) contains information about the subqueries applied to this 
     export result. Only present if a <subquery>'s parent <table> is included 
     within the export result and the <subquery> has a description. -->
<!ELEMENT subqueries (subquery+)>

<!-- subquery: meta information about subquery used to fill the export result -->
<!ELEMENT subquery (tablePath,displayName,description)>

<!-- columns: meta information about the columns of the export result. The order 
     of child <column> elements matches the order of <v> elements in each 
     <data> <row>. -->
<!ELEMENT columns (column+)>

<!-- column: meta information about one the columns of the export result. -->
<!ELEMENT column (id,tablePath,displayName)>

<!-- id: The id of the column -->
<!ELEMENT id (#PCDATA)>

<!-- tablePath: The table resource path -->
<!ELEMENT tablePath (#PCDATA)>

<!-- displayName: The displayName of the column or table -->
<!ELEMENT displayName (#PCDATA)>

<!-- description: descriptive text -->
<!ELEMENT description (#PCDATA)>

<!-- totalCount:  The total number of results to be returned based on the 
     filter and columns parameters. This can differ from the length of the 
     data array if the limit and offset parameters are specified. -->
<!ELEMENT totalCount (#PCDATA)>

<!-- row: One row of results -->
<!ELEMENT row (v+)>

<!-- v: One "cell" of results. "v" means "value" -->
<!ELEMENT v (#PCDATA)>

<!-- attribute v:url: The url of the value as configured by the <column>'s 
     <url_template> -->
<!ATTLIST v url CDATA #IMPLIED>
]>
Example 8. Example XML Output Format
<?xml version="1.0" encoding="UTF-8"?>
<results>
<meta>
    <filters>
         <filter>
             <source>/student@fname like 'J%'</source>
             <readable>'Student', 'First Name' like 'J%'</readable>
         </filter>
         <filter>
             <source>/student@lname = 'Hernadez'</source>
             <readable>'Student', 'Last Name' = 'Hernandez'</readable>
         </filter>
    </filters>
    <columns>
        <column>
            <id>id</id>
            <displayName>Student ID</displayName>
            <tablePath>/student</tablePath>
        </column>
        <column>
            <id>fname</id>
            <displayName>First Name</displayName>
            <tablePath>/student</tablePath>
        </column>
        <column>
            <id>lname</id>
            <displayName>Last Name</displayName>
            <tablePath>/student</tablePath>
        </column>
    </columns>
    <totalCount>2</totalCount>
</meta>
<data>
    <row>
        <v>177</v>
        <v>Joe</v>
        <v url="/some/url/177">Hernandez</v>
    </row>
    <row>
        <v>213</v>
        <v>Julia</v>
        <v url="/some/url/213">Hernandez</v>
    </row>
</data>
</results>

Error Response

Status Code:  400 and 500

XML responses with error have this DTD structure:

<!DOCTYPE messages [
<!-- messages: The set of error messages -->
<!ELEMENT messages (message+)>

<!-- message: one error message -->
<!ELEMENT message (#PCDATA)>
]>
Example 9. Example XML Error Output Format
<?xml version="1.0" encoding="UTF-8"?>
<messages>
    <message>The filter "/student@id &amp; 173" does not match filter grammar. The "Operator" definition is missing or unparseable.</message>
</messages>

JSON Format

The json format writes JSON to the HTTP response body.

Format Parameter:  json

Response Content-Type:  application/json

OK Response

Status Code:  200

JSON responses without error have this structure:

{
// meta: contains meta information about the results. 
"meta":{
    // filters: (Optional) contains information about the filters applied to this 
    // result set if applicable. 
    "filters": [
          {"source":string    // String representation of the original filter parameter.
           ,"readable":string // Contains the readable filter String with Table and Column 
                              // DisplayName's substituted for id's.
          },...
    ]
    // subqueries: (Optional) contains information about the subqueries applied to this 
    // export result. Only present if a <subquery>'s parent <table> is included 
    //  within the export result and the <subquery> has a description.
    "subqueries": [
          {"tablePath":string    // The table resource path to the <subquery>
           ,"displayName":string // The displayName of the <subquery> parent <table>
           ,"description":string
          },...
    ]
    // columns: meta information about the columns of the result set. The order 
    // of child objects matches the order of array elements in each 
    // "data" array.
    ,"columns": [
          {"id":string           // The id of the <column>
           ,"displayName":string // The displayName of the <column>
           ,"tablePath":string   // The table resource path to the <column>
           }
           ...
    ]
    // The total number of results to be returned based on the filter and columns
    // parameters. This can differ from the length of the data array
    // if the limit and offset parameters are specified. 
    ,"totalCount":integer
}
// data: array of arrays representing the results
// All values are coerced to a JavaScript type appropriate for their database type
// (i.e. String, date, number). <column>s with a <url_template> are represented via
// a JavaScript Object with attributes "url" and "value"
,"data": [
   [value1, value2, {"url":string,"value":string},...]
   ,...
]
}
Example 10. Example JSON Output Format
{
"meta": {
    "filters":[
         {
             "source":"/student@fname like 'J%'"
             ,"readable":"'Student', 'First Name' like 'J%'"
         }
         ,{
             "source":"/student@lname = 'Hernadez'"
             ,"readable":"'Student', 'Last Name' = 'Hernandez'"
         }
    ]
    ,"columns":[
         {
             "id":"id"
             ,"displayName":"Student ID"
             ,"tablePath":"/student"
         }
         ,{
             "id":"fname"
             ,"displayName":"First Name"
             ,"tablePath":"/student"
         }
         ,{
             "id":"lname"
             ,"displayName":"Last Name"
             ,"tablePath":"/student"
         }
    ]
    ,"totalCount":2
}
"data":[
    {
        177
        ,"Joe"
        ,{"url":"/some/url/177","value":"Hernandez"}
    }
    ,{
        213
        ,"Julia"
        ,{"url":"/some/url/213","value":"Hernandez"}
    }
]
}

Error Response

Status Code:  400 and 500

JSON responses with error have this structure:

{
 // messages: array of message Strings.
 "messages":[
     ...
 ]
}
Example 11. Example JSON Error Output Format
{
 "messages":[
      "The filter \"/student@id & 173\" does not match filter grammar. The \"Operator\" definition is missing or unparseable."
  ]
}

HTML Format

The html format writes a complete HTML document to the HTTP response body. This format extends the HTML Table format and decorates the result <table> with HTML <body> and <html> tags to make the result valid HTML. Additionally, the HTML Format titles the HTML with the Pull Report name and includes the Pull Reports™ CSS style sheet and <div> elements for basic formatting.

Format Parameter:  html

Response Content-Type:  text/html

Decorating the returned HTML

See the administration chapter for information on how to decorate (wrap) the exported HTML with additional HTML elements such as a custom header and footer.

OK Response

Status Code:  200

HTML format responses without error are valid HTML5 documents. The HTML result <table> is identical to that returned by the HTML Table format except the addition of a style class attribute. The exact format of the document may vary slightly between Pull Reports™ releases, but a reference example is provided below.

Example 12. Example HTML Output Format
<!DOCTYPE html>
<html>
    <head>
        <title>Student Report</title>
        <link rel='stylesheet' href='/mycontext/assets/pullreports.min.css'></link>
    </head>
    <body>
        <div class="container-fluid">
            <div class="row">
                <div class="col-xs-12">
                    <h1>Student Report</h1>
                    <table class="table table-striped table-bordered">
                        <caption>
                            <aside>From 1 to 2 of 2</aside>
                            <h5 class='filters-title'>Filters</h5>
                            <ul>
                                <li>Table: <em>Student</em>, Column: <em>First Name</em> like 'J%'</li>
                                <li>Table: <em>Student</em>, Column: <em>Last Name</em> = 'Hernandez'</li>
                            </ul>
                            <h5 class='subqueries-title'>Subqueries</h5>
                            <ul>
                                <li>Table: <em>Student</em>: Rows are restricted to those students within your school.</li>
                            </ul>
                        </caption>
                        <thead>
                            <tr>
                                <th data-tablePath="/student" data-columnId="id">Student ID</th>
                                <th data-tablePath="/student" data-columnId="fname">First Name</th>
                                <th data-tablePath="/student" data-columnId="lname">Last Name</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>177</td>
                                <td>Joe</td>
                                <td><a href="/some/url/177">Hernadez</a></td>
                            </tr>
                            <tr>
                                <td>213</td>
                                <td>Julia</td>
                                <td><a href="/some/url/213">Hernadez</a></td>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </body>
</html>

Error Response

Status Code:  400 and 500

HTML format responses with error have the same structure as Error responses from the HTML Table format with the addition of valid HTML decoration.

Example 13. Example HTML Table Error Output Format
<!DOCTYPE html>
<html>
    <head>
        <link rel='stylesheet' href='/[context]/assets/pullreports.min.css'></link>
    </head>
    <body>
        <ul class="error">
            <li>The filter "/student@id &amp; 173" does not match filter grammar. The "Operator" definition is missing or unparseable.</li>
        </ul>
    </body>
</html>

HTML Table Format

The htmltable format writes HTML to the HTTP response body. The returned HTML is internally valid XML but not valid HTML since it is neither encapsulated with <html> nor <body> tags. This allows the returned HTML to be embedded within the DOM of an HTML Document.

Format Parameter:  htmltable

Response Content-Type:  text/html

OK Response

Status Code:  200

HTML Table responses without error have this DTD structure:

<!DOCTYPE table [
<!ELEMENT table (caption?,thead,tbody)>

<!ELEMENT caption (aside,h5,ul)>

<!-- aside: Contains the total row count -->
<!ELEMENT aside (#PCDATA)>

<!-- h5 and ul: optionally rendered if at least one filter or subquery with a description 
     is applied to the export results. In the case of filters, the h5 contains the 'Filters' 
     title, and the ul contains human readable filters (with table and column displayName 
     substituted for id's) in a bulleted list. In the case of subqueries with a description, 
     the h5 contains the 'Subqueries' title, and the ul contains the <subquery> description. -->
<!ELEMENT h5 (#PCDATA)>
<!ATTLIST h5 class CDATA>

<!ELEMENT ul (li)>

<!ELEMENT li (#PCDATA|em|samp)>
<!ELEMENT em (#PCDATA)>
<!ELEMENT samp (#PCDATA)>

<!ELEMENT thead (tr)>

<!ELEMENT tbody (tr)>

<!ELEMENT tr (th|td)>

<!-- th: Contains the <column> displayName -->
<!ELEMENT th (#PCDATA)>

<!-- th attributes: The 'tablePath' and 'columnId' value of each column is added as a data 
     attribute for every column header. This makes it possible to find the column associated
     with a given tablePath and columnId with a selector tool like JQuery. -->
<!ATTLIST th data-tablePath CDATA>
<!ATTLIST th data-columnId CDATA >

<!-- td: Contains the row value -->
<!ELEMENT td (#PCDATA|a)>

<!-- a: <column>'s with a <url_template> have their values surrounded with an HTML anchor
     tag (a) with the filled URL template as the value of the href attribute. -->
<!ELEMENT a (#PCDATA)>
<!ATTLIST a href CDATA>

]>
Example 14. Example HTML Table Output Format
<table>
    <caption>
        <aside>From 1 to 2 of 2</aside>
        <h5 class='filters-title'>Filters</h5>
        <ul>
            <li>Table: <em>Student</em>, Column: <em>First Name</em> <samp>like</samp> 'J%'</li>
            <li>Table: <em>Student</em>, Column: <em>Last Name</em> <samp>=</samp> 'Hernandez'</li>
        </ul>
        <h5 class='subqueries-title'>Subqueries</h5>
        <ul>
            <li>Table: <em>Student</em>: Rows are restricted to those students within your school.</li>
        </ul>
    </caption>
    <thead>
        <tr>
            <th data-tablePath="/student" data-columnId="id">Student ID</th>
            <th data-tablePath="/student" data-columnId="fname">First Name</th>
            <th data-tablePath="/student" data-columnId="lname">Last Name</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>177</td>
            <td>Joe</td>
            <td><a href="/some/url/177">Hernadez</a></td>
        </tr>
        <tr>
            <td>213</td>
            <td>Julia</td>
            <td><a href="/some/url/213">Hernadez</a></td>
        </tr>
    </tbody>
</table>

Error Response

Status Code:  400 and 500

HTML Table responses with error have this structure:

<!DOCTYPE ul [
<!ELEMENT ul(li)>
<!ATTLIST ul class CDATA "error">

<!ELEMENT li(#PCDATA)>
]>
Example 15. Example HTML Table Error Output Format
<ul class="error">
    <li>The filter "/student@id &amp; 173" does not match filter grammar. The "Operator" definition is missing or unparseable.</li>
</ul>

KML Format

The KML format writes KML to the HTTP response body following these guidelines:

Format Parameter:  kml

Response Content-Type:  application/vnd.google-earth.kml+xml

Response Content-Disposition:  The Content-Disposition HTTP header is set to the value of the <report> name attribute with the underscore character (_) substituted for any space, comma, or semicolon character plus the .kml extension.

Geometry configured via <kml> The geometry value of each KML <Placemark> (such as a <Point>, <LineString> , or <Polygon>) is the value of the base table column designated by the <report>'s <kml> geometryColumnPath attribute. The KML export format will only create a <Placemark> for result rows with a non-null value for the column referenced by geometryColumnPath.

If no <kml> geometryColumnPath is designated, the format will return a 400 HTTP response status.

Geometry uniqueness respective to base table columns:  The kml format will create a single KML geometry per unique result row. Uniqueness is determined by the aggregate equality of the base table <column> values. This means that even if the columns parameter creates a join condition such that the base table <column> values are repeated across multiple result rows, only one KML geometry will be present in the export results for each unique base table row.

Value of <Document> <name> For OK responses, the KML <Document> <name> value is:

Export from: [<catalog> name]:[<report> name] [filters applied]

The "filters applied" text is only appended if at least one filter parameter is specified in the Export Report REST API request.

For Error responses, the KML <Document> <name> value is:

Error in Export. See description.

Value of <Document> <description> For OK responses, if at least one filter parameter is specified, the <description> contains a bulleted list of human readable filter definitions. Additionally, the <description> contains a bulleted list of subquery descriptions if at least one <subquery>'s parent <table> is included within the export result and the <subquery> has a non-null description.

For Error responses, the <description> contains a bulleted list of error messages.

Additional columns as <ExtendedData> Each base table column of the export result row is included as a child <Data> element of the <Placemark>'s <ExtendedData> element. Each <Data>'s name attribute is the corresponding <column>'s column resource path. Each <Data>'s child <displayName> and <value> elements contain the <column>'s displayName and result row value respectively.

Any <column>s with a <url_template> have their values surrounded with an HTML anchor tag (a) with the filled URL template as the value of the href attribute.

Customizing <Placemark> <name>s:  By default, the <name> element of each <Placemark> is the name of the <report> plus the word "Geometry". Specify the <report>'s <placemark_name_template> element to customize the <name> element based on values from the result row.

OK Response

Status Code: 200

KML responses without error follow this example:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
  <Document>
    <name>Export from: Class Information:Student Report &lt;i&gt;filters applied&lt;/i&gt;</name>
    <!-- Human readable filter definitions are only included in the <description>
         if at least one filter parameter is specified. Otherwise the <description>
         is empty. -->
    <description><![CDATA[<h5>Filters</h5><ul>
        <li>'Student', 'First Name' like 'J%'</li>
        <li>'Student', 'Last Name' = 'Hernandez'</li>
    </ul>
    <h5>Subqueries</h5>
    <ul>
        <li>'Student': Rows are restricted to those students within your school.</li>
    </ul>]]></description> 
    <Placemark>
      <!-- In this example, the <Placemark> <name> is customized via:
      <placemark_name_template>${@fname} ${@lname} Home</placemark_name_template> -->
      <name>Joe Hernandez Home</name>
      <ExtendedData>
        <Data name="/student@id">
          <displayName>Student ID</displayName>
          <value>177</value>
        </Data>
        <Data name="/student@fname">
          <displayName>First Name</displayName>
          <value>Joe</value>
        </Data>
        <Data name="/student@lname">
          <displayName>Last Name</displayName>
          <value>&lt;a href="/some/url/177"&gt;Hernandez&lt;/a&gt;</value>
        </Data>
      </ExtendedData>
      <!-- The geometry value is the value of the base table column specified by
           <kml geometryColumnPath="..."> -->
      <Point>
        <coordinates>-105.0906227,40.5656519,0</coordinates>
      </Point>
    </Placemark>
    <Placemark>
      <name>Julia Hernandez Home</name>
      <ExtendedData>
        <Data name="/student@id">
          <displayName>Student ID</displayName>
          <value>213</value>
        </Data>
        <Data name="/student@fname">
          <displayName>First Name</displayName>
          <value>Julia</value>
        </Data>
        <Data name="/student@lname">
          <displayName>Last Name</displayName>
          <value>&lt;a href="/some/url/213"&gt;Hernandez&lt;/a&gt;</value>
        </Data>
      </ExtendedData>
      <Point>
        <coordinates>-105.0994311,40.5673064,0</coordinates>
      </Point>
    </Placemark>
  </Document>
  </kml>

Error Response

Status Code: 400 and 500

KML responses with error follow this example:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
  <Document>
    <name>Error in Export. See description.</name>
    <description><![CDATA[
    <ul>
        <li>The filter "/student@id & 173" does not match filter grammar. The "Operator" definition is missing or unparseable.</li>
    </ul>
    ]]></description>
  </Document>
</kml>

GeoJSON Format

The GeoJSON format writes GeoJSON to the HTTP response body following these guidelines:

Format Parameter: geojson

Response Content-Type: application/vnd.geo+json

Geometry configured via <geojson>: The geometry value of each GeoJSON feature (such as a Point, LineString , or Polygon) for each export result row is the value of the base table column designated by the <report>'s <geojson> geometryColumnPath attribute. The GeoJSON export format will only create a feature for result rows with a non-null value for the column referenced by geometryColumnPath.

If no <geojson> geometryColumnPath is designated, the format will return a 400 HTTP response status.

Feature uniqueness respective to base table: The geojson format will create a single GeoJSON feature per unique result row. Uniqueness is determined by the aggregate equality of the base table <column> values. This means that even if the columns parameter creates a join condition such that the base table <column> values are repeated across multiple result rows, only one GeoJSON feature will be present in the GeoJSON results for each unique base table row.

FeatureCollection name property: For OK responses, the GeoJSON FeatureCollection has a name property that is:

Export from: [<catalog> name]:[<report> name]

"name" is a non-standard GeoJSON property.

FeatureCollection filters property: For OK responses, if at least one filter parameter is specified, the GeoJSON FeatureCollection has a filters property that is an array of Objects each with a source and readable property whose values are, respectively, the original filter parameter and human readable filter definition.

"filters" is a non-standard GeoJSON property.

FeatureCollection subqueries property: For OK responses, if at least one <subquery>'s parent <table> is included within the export result and the <subquery> has a description, the GeoJSON FeatureCollection will have a subqueries property that is an array of Objects each with a tablePath, displayName, and description property whose values are, respectively, the <subquery> parent <table> resource path and displayName, and <subquery> description.

"subqueries" is a non-standard GeoJSON property.

FeatureCollection totalCount property: For OK responses, the GeoJSON FeatureCollection has a totalCount property that is the total number of results to be returned based on the filter and columns parameters. The total count value is irrespective of limit or offset parameters and whether or not records have non-null GeoJSON geometry.

"totalCount" is a non-standard GeoJSON property.

Additional columns as feature properties: Each base table column of the export result row is included as a property name/value pair in the properties Map of each feature. The property name is the <column>'s displayName and the value is the <column>'s result row value.

The property value of <column>s with a <url_template> is a JavaScript Object with attributes "url" and "value" corresponding to the filled URL template and result row value respectively.

OK Response

Status Code: 200

GeoJSON responses without error follow this example:

{"type": "FeatureCollection"
    ,"name":"Export from: Class Information:Student Report"
    ,"totalCount":2
    "filters":[
         {
             "source":"/student@fname like 'J%'"
             ,"readable":"'Student', 'First Name' like 'J%'"
         }
         ,{
             "source":"/student@lname = 'Hernadez'"
             ,"readable":"'Student', 'Last Name' = 'Hernandez'"
         }
    ]
    ,"subqueries":[
         {
             "tablePath":"/student"
             ,"displayName":"Student"
             ,"description":"The student table has been restricted to records within the school of the current user."
         }
     }
    ,"features": [
        {
            "type": "Feature"
            ,"geometry":{
               "type": "Point"
               ,"coordinates": [-105.0906227,40.5656519]
            }
            ,"properties":{
               "Student ID":177
               ,"First Name":"Joe"
               ,"Last Name":{"url":"/some/url/177","value":"Hernandez"}
            }
        }
        ,{
            "type": "Feature"
            ,"geometry":{
               "type": "Point"
               ,"coordinates": [-105.0994311,40.5673064]
            }
            ,"properties":{
               "Student ID":213
               ,"First Name":"Julia"
               ,"Last Name":{"url":"/some/url/213","value":"Hernandez"}
            }
        }
    ]
}

Error Response

Status Code: 400 and 500

GeoJSON responses with error follow this example:

{
 // messages: array of message Strings.
 "messages":[
     ...
 ]
}
Example 16. Example GeoJSON Error Output Format
{
 "messages":[
      "The filter \"/student@id & 173\" does not match filter grammar. The \"Operator\" definition is missing or unparseable."
  ]
}

Map Format

The map format writes an HTML document to the HTTP response body with an embedded Leaflet map which displays the GeoJSON results from the GeoJSON format. Like the GeoJSON format, the map format is only available if the report has a valid <geojson> element.

Format Parameter: map

Response Content-Type: text/html

Decorating the returned HTML

See the administration chapter for information on how to decorate (wrap) the exported HTML with additional HTML elements such as a custom header and footer.

Customizing the Leaflet mapper

In order to control the behavior of the map format's embedded Leaflet mapper, decorate the returned HTML and define an global JavaScript variable within the <head> of the DOM named mapConfig. mapConfig must be a JavaScript object with one or more of the properties below. The map format JavaScript will detect this variable and use it during initialization of the Leaflet Mapper.

mapConfig Object Properties
initCallback

A JavaScript function to be invoked after map initialization. The arguments to the function are:

  • map: The Leaflet map.

  • geoJsonLayer: The Leaflet markercluster layer which contains the GeoJSON Layer which in turn contains the GeoJSON result of the Export REST API, geojson format.

geoJsonOptions

A JavaScript object to be passed to the Leaflet GeoJSON layer upon construction. See the Leaflet GeoJSON documentation for a complete list of configuration options.

If not specified, Pull Reports™ will set the following default GeoJSON layer option values:

  • onEachFeature: Set to a function which binds a Leaflet popup to the layer for each feature. The content of the popup is a two column table of GeoJSON feature property names to values. The property names and values are the exported <column>s of the report's base table.

options

A JavaScript object to be passed to the Leaflet map upon construction. See the Leaflet map options documentation for a complete list of configuration options.

If not specified, Pull Reports™ will set the following default map option values:

  • center: Set to the geographic center of the lower 48 United States.

  • layers: Set to an array with a single Open Street Map base layer.

  • zoom: Set to 4

Example 17. Example map format customization

This example customizes the map format Leaflet mapper by adding a Leaflet Layers control to the map with the ability to toggle between two base tile layers and toggle the visibility of the report's GeoJSON layer. The example uses the Servlet include technique to include custom JavaScript within the returned HTML <head> element.

To begin, create a text document called mapConfig.html within the installation WAR's WEB-INF/context directory. This document will define the mapConfig global variable within an HTML <script> element. The example below uses the mapConfig options and initCallback properties to set the default map layers and add a Leaflet Layers control respectively.

<script>
    var baseLayers = [];
    baseLayers['ESRI World Topo'] = 
        L.tileLayer('http://server.arcgisonline.com/ArcGIS/rest/services/World_Topo_Map/MapServer/tile/{z}/{y}/{x}', {
        attribution: 'Tiles &copy; Esri &mdash; Esri, DeLorme, NAVTEQ, TomTom, Intermap, iPC, USGS, FAO, NPS, NRCAN, GeoBase, Kadaster NL, Ordnance Survey, Esri Japan, METI, Esri China (Hong Kong), and the GIS User Community'
        ,maxZoom:18
    });
    baseLayers['Open Street Map'] = 
        L.tileLayer('http://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
        attribution: 'Map data © <a href="http://openstreetmap.org">OpenStreetMap</a> contributors'
        ,maxZoom: 18
    });
    
    function initializeMap(map,geojsonLayer){
        L.control.layers(baseLayers, {'My Report Data':geojsonLayer}).addTo(map);
    }

    var mapConfig = {
        options: {
            layers:[baseLayers['Open Street Map']]
        }
        ,initCallback:initializeMap
    };
</script>

To include this file within the map format HTML <head> element, set the export.report.include.map.head.end configuration property in pullreports.properties to the WAR-relative location of mapConfig.html like so:

export.report.include.map.head.end=/WEB-INF/content/mapConfig.html

Pull Reports™ will then insert the mapConfig.html fragment into every map format response and use the mapConfig variable to customize the Leaflet map.


OK Response

Status Code: 200

Map format responses without error are valid HTML5 documents into which the output of the GeoJSON format is written as a JavaScript variable and displayed within an embedded Leaflet map.

Error Response

Status Code: 400 and 500

Map format responses with error have the same structure as Error responses from the HTML format.

CSV Format

The csv format writes double quoted Comma Separated Values to the HTTP response body.

Format Parameter: csv

Response Content-Type: text/csv

Response Content-Disposition:  The Content-Disposition HTTP header is set to the value of the <report> name attribute with the underscore character (_) substituted for any space, comma, or semicolon character plus the .csv extension. If an error occurs, the Content-Disposition value is Error.csv.

CSV Column Headers:  The first row of the returned CSV contains the displayName values for each <column> included in the export results. <column>s which contain a <url_template> create two columns in the export result. The first column, titled with the column's displayName, contains the column's value. The second, titled with the column's displayName plus the term _url, contains the URL value.

OK Response

Status Code: 200

CSV responses without error follow this example:

"Student ID","First Name","Last Name","Last Name_url"
"177","Joe","Hernandez","/some/url/177"
"213","Julia","Hernandez","/some/url/213"

Error Response

Status Code: 400 and 500

CSV responses with error write the error messages to the HTTP response body as a line break separated list. For example:

Example 18. Example CSV Error Output Format
The filter "/student@id & 173" does not match filter grammar. The "Operator" definition is missing or unparseable.

Errors

The Export Report REST API returns non-200 HTTP status codes for a variety of error conditions. The structure of the response body for non-200 responses varies by format. See the detailed format documentation below for more information.

Error Logging

In addition to returning error messages within the HTTP response, Pull Reports™ logs all errors to the installation WAR's logging implementation. See the Administrative Guide, logging documentation for more information.

Here are different types of non-200 responses and their common causes.

Bad Request

Status Code: 400

Returned for HTTP parameter errors such as an incorrectly formatted filter, sort, or columns parameter.

Forbidden

Status Code: 403

A 403 status code is returned if an <access_control_voter> associated with the <report> or <catalog> fails. A 403 status is also returned if an <access_control_voter> associated with a <relationship> or <column> referenced via the columns, filter, or sort parameter fails.

Internal Server Error

Status Code: 500

Returned if the API cannot complete the response due to a server error such as a communication problem with the underlying database.