GET Export Report

Abstract

The GET Export Report API is the core endpoint of the report and data service API. It exports a query defined by the columns and filter parameters to the content-type of the format parameter.


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 ReportsReport 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 included 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.6.1" id="class" name="Class Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.1
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.1.xsd">
    <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 , jsontree , csv , html , htmltable , htmltree , kml , geojson , or map. 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 present. If the sort parameter is present but empty (e.g. sort=), the results will be unsorted regardless of the presence of the defaultSort attribute.

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

Response structure varies by format parameter:

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.