Controlling the column output

Columns are defined by creating an element in the XML document called <columns> at the same level as the <query> element. This contains optional definitions of each column in the report in <column> sub-elements with the attributes of each column definition defining which database field it links to as well as the output behaviour. Therefore the structure might look like:

<?xml version="1.0" encoding="UTF-8"?>
<report title="Tutorial"
    description="Display some records for the report writing tutorial">
  <query>
  ...
  </query>
  <columns>
    <column attr="value" />
    <column attr="value" />
    <column attr="value" />
  </columns>
</report>

The first thing we are going to do with the columns is to add a new column for the ID of the record, but to then hide it from the default grid output leaving the id available for code to use but not for the user to see. For example we might want to add a link to the rows in the table which is to a page which accept’s the ID as a URL parameter and displays some information about that record. First, edit the SQL to include the column called o.id at the start of the list of columns (using the o prefix to ensure that the id column is accessed from the correct table). Then, create a <columns> section in your report file after the closing </query> with a single <column> as follows:

<columns>
  <column name="id" visible="false" />
</columns>

Save your report file and reload the report in the warehouse. You shouldn’t notice any difference at this stage because the id field has been set to visible="false" but this field is available for use in the various configuration and templating options available in the report grid.

Now, add 3 new columns to your report for the other 3 standard columns (excluding date which already has a proper caption), using the caption attribute of the column definition to set their captions to Grid ref, Species and Common name respectively. Your report file should look like this:

<?xml version="1.0" encoding="UTF-8"?>
<report title="Tutorial"
      description="Display some records for the report writing tutorial">
  <query>
    select o.id, snf.public_entered_sref, cttl.preferred_taxon, cttl.default_common_name,
          o.date_start, o.date_end, o.date_type
    from cache_occurrences_functional o
    join cache_samples_nonfunctional snf on snf.id=o.sample_Id
    join cache_taxa_taxon_lists cttl on cttl.id=o.taxa_taxon_list_id
    where o.created_on&gt;now()-'1 month'::interval
  </query>
  <columns>
    <column name="id" visible="false" />
    <column name="public_entered_sref" display="Grid ref" />
    <column name="preferred_taxon" display="Species" />
    <column name="default_common_name" display="Common name" />
  </columns>
</report>

Reload the report to check that the column titles have been updated.