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.
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.
/[context]/pullreports/catalog/[catalog id]/report/[report id]/export
/[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.
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.
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 Reports™ Report Creator to create a valid API request and then customize the request further based on this documentation.
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.
columns
parameter usageThe 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 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.
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 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
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!
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
.
distinct
parameter usageOne 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
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.
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.
filter
parameter usageThe 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.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 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_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.
The following table lists the available filter operators.
Operator | Description |
---|---|
= , != , <> |
Equality / Inequality. Example: |
< , > , <= , >= |
Comparison. Example: |
between X and Y |
Range Comparison. Example: |
in (...) |
Equal to at least one of. Example: |
not in (...) |
Unequal to all of. Example: |
like , not like |
String pattern search. Example: |
is null , is not null |
Null value. Example: |
Values within filter
s parameters are of one of the following types:
Type | Description |
---|---|
Numeric |
Any integer or floating point number. For example:
Commas (,) within numeric values are not supported. |
String | Any single quoted value. For example:
Escape single quotes (') within a String with a second a single quote.
For example: See Filter Value Coercion below for how Strings may be coerced info Dates. |
Boolean | true or false . Because Boolean values are case
insensitive,
TRUE or FALSE are also valid. |
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.
Value Type | <column> paramType | Coercion |
---|---|---|
Numeric | java.lang.String | Number 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: | |
java.lang.Boolean | If the value equals 1, coerce to true .
If the value equals 0, coerce to false . | |
String | java.lang.Long, java.lang.Integer, java.lang.Float, or java.lang.Double | The String will be parsed to a Number |
java.sql.Date | The String will be parsed to a The value must match the | |
java.sql.Time | The String will be parsed to a
If the character length is 8 characters, the String will be parsed with the
If the character length is 12 characters, the String will be parsed with the
| |
java.sql.Timestamp | The String will be parsed to a
If the character length is 10 characters, the String will be parsed with the
If the character length is 19 characters, the String will be parsed with the
If the character length is 23 characters, the String will be parsed with the
| |
java.lang.Boolean | If the String equals '1' or case-insensitive 'true', then the value will be coerced to true .
Otherwise, the value will be coerced to false . | |
Boolean | java.lang.Long, java.lang.Integer, java.lang.Float, or java.lang.Double | If 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.String | If value equals true , then the value will be coerced to
'true' . If value equals
false , then the value will be coerced to 'false' . |
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.
format
parameter usageThe following format
parameter exports the results in the
map
format:
format=map
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.
limit
parameter usageThe following limit
parameter limits the export results to 500 records:
limit=500
The offset
parameter is a non-negative integer indicating the offset of the export results.
Some database vendors do not support offset
without also specifying the limit
parameter.
In this case, a 500
HTTP response status is returned.
offset
parameter usageThe following offset
parameter excludes the first 50 records from the export result:
offset=50
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.
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
sort
parameter usageThe 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 structure varies by format
parameter:
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.
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.
Status Code: 400
Returned for HTTP parameter errors such as an incorrectly formatted filter
, sort
, or
columns
parameter.
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.
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.