Learn to configure the <column>
element for Pull Reports™ Ad Hoc
report and data service software.
Catalog Configuration Java API analog:
ColumnConfigurationBuilder
The <column>
element represents one relational database column available for reporting. Each <table>
within
a Pull Report must have at least one <column>
. <column>
elements are referenced
within parameters of the Pull Reports REST API by their unique
column resource path.
Control access to a <column>
with a nested <access_control_voter>
.
Add hyperlinks to a <column>
value with a nested <url_template>
.
<column>
s with a <table>
In this example, consider the following, database table whose rows represent individuals in a system.
CREATE TABLE person ( person_id integer primary key, first_name varchar(200) not null, last_name varchar(200) not null, birth_date date, sex char(1) not null );
The following Pull Reports™ configuration makes the table available via the Pull Reports REST API. See the comments
within the XML for specific <column>
configuration guidance.
<?xml version="1.0" encoding="UTF-8"?> <catalog xmlns="http://www.pullreports.com/catalog-1.7.0" id="person_catalog" name="Person Catalog" 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="person" name="Person Report"> <export_config defaultColumns="fname,lname"/> <table id="person" name="person" displayName="Person Info"> <!-- export='false' excludes the column from export but still makes it available for other uses such as filtering --> <column id="id" name="person_id" paramType="java.lang.Integer" export="false"/> <column id="fname" name="first_name" displayName="First Name"/> <!-- The nested <url_template> element exports a hyperlink for every value of the "lname" column following the given template. At runtime, the value of the "id" column will substitute for "${@@id}. --> <column id="lname" name="last_name" displayName="Last Name"> <url_template>/person/${@@id}.html</url_template> </column> <!-- The paramType="java.sql.Date" attribute indicates the column datatype and is required for non-character datatypes. --> <column id="bdate" name="birth_date" paramType="java.sql.Date" displayName="Birth Date"> <!-- Add additional metadata via the <description> element. --> <description>The birthdate of the person as entered via their personnel record.</description> </column> <column id="sex" name="sex" displayName="Sex"/> </table> </report> </catalog>
The following example is an identical Catalog Configuration Java API configuration.
package com.pullreports.examples.column;
import com.pullreports.model.CatalogId;
import com.pullreports.model.ColumnId;
import com.pullreports.model.ParamType;
import com.pullreports.model.ReportId;
import com.pullreports.model.TableId;
import com.pullreports.model.config.CatalogConfiguration;
import com.pullreports.model.config.CatalogConfigurationFactory;
import com.pullreports.model.config.ColumnConfiguration;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.TableConfiguration;
import com.pullreports.model.exportconfig.ExportConfiguration;
import jakarta.servlet.ServletContext;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
public class PersonCatalogConfigurationFactory implements CatalogConfigurationFactory {
@Override
public CatalogConfiguration makeCatalog(ServletContext servletContext) {
ColumnConfiguration idColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("id"),"person_id")
.setParamType(ParamType.INTEGER)
.setExport(false).build();
ColumnConfiguration fnameColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("fname"),"first_name")
.setDisplayName("First Name")
.build();
ColumnConfiguration lnameColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("lname"),"last_name")
.setUrlTemplate("/person/${@@id}.html")
.setDisplayName("Last Name")
.build();
ColumnConfiguration bdateColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("bdate"),"birth_date")
.setDisplayName("Birth Date")
.setParamType(ParamType.DATE)
.setDescription("The birth date of the person as entered via their personnel record.")
.build();
ColumnConfiguration sexColumnConfiguration = new ColumnConfiguration.Builder(
new ColumnId("sex"),"sex")
.setDisplayName("Sex").build();
List<ColumnConfiguration> columnConfigurations = Arrays.asList(
idColumnConfiguration
,fnameColumnConfiguration
,lnameColumnConfiguration
,bdateColumnConfiguration
,sexColumnConfiguration);
TableConfiguration personTableConfiguration = new TableConfiguration.Builder(
new TableId("person"),"Person Info",columnConfigurations)
.setName("person").build();
ReportConfiguration personReportConfiguration = new ReportConfiguration.Builder(
new ReportId("person"),"Person Report",personTableConfiguration)
.setExportConfiguration(
new ExportConfiguration.Builder().setDefaultColumns(Arrays.asList(
fnameColumnConfiguration.getId(),lnameColumnConfiguration.getId()))
.build()).build();
List<ReportConfiguration> reportConfigurations = Collections.singletonList(personReportConfiguration);
return new CatalogConfiguration(new CatalogId("person_catalog"),"Person Catalog",reportConfigurations);
}
}
<description> ? |
<access_control_voter> ? |
<output_transform> ? |
<filter_column_transform> ? |
<filter_value_transform> ? |
<url_template> ? |
( <global_label_value_list_ref> | <label_value_list> )? |
<column_group>
|
<table>
|
The human-readable name of the column.
If displayName
is not defined, the <column>
's name
attribute labels the column within the Export Report REST API. Since the use of the
name
attribute exposes database definition
information which can be used in SQL injection attacks, it is a good practice to define a
displayName
different from the name
.
Set to false
to exclude this column from export results. The column
may still be used in filters
and <url_template>
s and
<placemark_name_template>
s definitions.
Even if export="false"
, the <column>
may be used
as the geometryColumnPath
within the <kml>
and <geojson>
elements. Setting columns which contain raw KML and GeoJSON geometries to export="false"
is a convenient way to remove the often extremely large Geometry character
strings from non-GIS capable export formats.
The unique id of the column within the parent
<table>
or <column_group>
. The id
value is used with the Export Report REST API to reference the
column for such features as filters and sorting.
Since the id
becomes part of the Pull Reports REST API parameter structure,
changing the value may break existing Pull Reports REST API URLs.
The name of the column in the relational database. Do not prepend the table name to the column name. Pull Reports™ will prepend an auto-generated table alias to the column name at query time.
The Java type to which to coerce filter parameter values when setting
JDBC query placeholders in the Export Report REST API.
It is required to set the paramType
value if the
column datatype is not a character type (e.g. varchar, char, text). Failing to do so may result in
a type coercion error in the Export Report REST API.
The paramType
usually follows the database column type
but may be different if a child <filter_column_transform>
or <filter_value_transform>
element
changes the column datatype.
Permitted values are:
java.lang.Boolean
java.lang.Long
java.sql.Date
java.sql.Time
java.sql.Timestamp
java.lang.Double
java.lang.Float
java.lang.Integer
java.lang.String
java.math.BigDecimal
java.math.BigInteger