Using the advanced pagination facility

At this stage, the pagination at the footer of the report grid is a simple previous and next link which is not really up to the standards expected of a modern user interface. This is because we haven’t structured our report in a way that lets Indicia run a COUNT version of the query to find the total records, therefore it does not know how many pages there are. The best way to rectify this is to replace the list of fields in the query with a tag which Indicia can replace with the field list, then provide the SQL for each field in the column definition. The tag needed is called #columns# and the attribute required to specify the SQL is simply called sql, meaning that our query must be changed to:

select #columns#
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>now()-'1 month'::interval

An example of a column definition using the sql attribute is:

<column name="id" visible="false" sql="o.id"/>

Indicia will simply join all the column SQL with commas and replace the #columns# tag with the result when running the report query. When Indicia needs the total record count in order to build the paginator, it replaces the #columns# tag with count(*). We also need to know how to handle the special Date column, since this is a construct of the start, end and type fields of the vague date. The best approach to this is to declare columns for each of the 3 vague date fields and set visible to false, then to declare another column named date which allows us to control the position of the date column in the report output. Hence the report should now look like the following:

<?xml version="1.0" encoding="UTF-8"?>
<report title="Tutorial"
    description="Display some records for the report writing tutorial">
  <query>
    select #columns#
    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" sql="o.id" visible="false" datatype="integer"/>
    <column name="public_entered_sref" sql="snf.public_entered_sref" display="Grid Ref"/>
    <column name="preferred_taxon" sql="cttl.preferred_taxon" display="Species"/>
    <column name="default_common_name" sql="cttl.default_common_name" display="Common Name"/>
    <column name="date_start" sql="o.date_start" visible="false" />
    <column name="date_end" sql="o.date_end" visible="false" />
    <column name="date_type" sql="o.date_type" visible="false" />
    <column name="date" display="Date" />
  </columns>
</report>

Update your tutorial report file and save it, then reload the report in the warehouse. You should notice the the report grid now has a much more useful paginator including a total record count at the bottom.