<column>

Abstract

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>.

Usage

Using <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.

Example 1. Via an XML Catalog file
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.6.1" id="person_catalog" name="Person Catalog"
    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="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>
                

Example 2. Via the Catalog Configuration Java API

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 javax.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);
    }
}
                

Children

<description>?
<access_control_voter>?
<output_transform>?
<filter_column_transform>?
<filter_value_transform>?
<url_template>?
( <global_label_value_list_ref> | <label_value_list> )?

Parents

<column_group>
<table>

Attributes

displayName (Optional)

The human-readable name of the column.

Security Warning

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.

export (Default: true)

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.

Note

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.

id

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.

Warning

Since the id becomes part of the Pull Reports REST API parameter structure, changing the value may break existing Pull Reports REST API URLs.

name

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.

paramType (Default: java.lang.String)

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