Back to all How-tos

Data-sum it up!

The Dynamic Table wizard has some basic options to create a running total and transport line, but the 2020.1 version of OL Connect introduced some special HTML attributes that offer more possibilities. The data-sum attribute will do the totals for the specified data field as part of the pagination process, and the data-format attribute allows to format data values. This how-to shows some basic samples on how to create running totals and transport lines with these attributes.

Running totals

Creating running totals is easy once you get the hang of it. Consider the following Dynamic Table design.

The table contains a <thead>, <tbody> and <tfoot> section. The Source view in the Designer reveals the HTML structure below.

<table id="table" class="table--minimalist" data-detail="" data-expander="2019">
 <thead>
  <tr>
   <th>Number</th>
   <th>Description</th>
   <th>Unit Price</th>
   <th>Quantity</th>
   <th>Total</th>
  </tr>
 </thead>
 <tbody>
  <tr data-repeat="detail">
   <td data-field="Number">@Number@</td>
   <td data-field="Description">@Description@</td>
   <td data-field="UnitPrice">@UnitPrice@</td>
   <td data-field="Quantity">@Quantity@</td>
   <td data-field="Total">@Total@</td>
  </tr>
 </tbody>
 <tfoot>
  <tr>
   <td colspan=3></td>
   <td data-sum="detail.Quantity">@QtySum@</td>
   <td data-sum="detail.Total">@TotalSum@</td>
  </tr>
 </tfoot>
</table>

Two cells in the element have the data-sum attribute. The value consists of the fully qualified path to a field in a detail table using the dot notation. For example: “detail.Total” links to the Total field in the detail detail table. In this case there are two table cells that add up a value.

The merged result shows the running total for both fields.

Merged result of Dynamic Table with data-sum

These data- attributes can be set via the user interface while working in the Design view. Selecting a cell or row in any part of the dynamic table automatically shows the respective options in the Attributes pane. The following image shows the options for the selected table cell in the footer of the table.

Sum and Format on Attributes pane

Transport lines

Creating transport lines for these two cells is just as simple as creating the running total: simply copy the respective row to the <thead> element. Without making any modifications you’ll notice that the transport line is shown on the first page. As the values start with 0 or 0.00 this doesn’t make sense. Adding the data-show-row attribute with the after-break value to the transport line omits the row on the first page.

<thead>
 <tr>
  <th>Number</th>
  <th>Description</th>
  <th>UnitPrice</th>
  <th>Quantity</th>
  <th>Total</th>
 </tr>
 <tr data-show-row="after-break">
  <td colspan="3"></td>
  <td data-sum="detail.Quantity" style="text-align: right;">@QtySum@</td>
  <td data-sum="detail.Total" style="text-align: right;">@TotalSum@</td>
 </tr>
</thead>

The merged result:

Merged result with transport line

Adding data-show-row="before-break" to the <tr> in the <tfoot> omits that row from the last page. Note that the data-show-row options can be set via the Attributes pane. To reveal these options simply select a <tr> element via the breadcrumbs or on the Outline pane.

Formatting values

By default the values are formatted following their data type, e.g. currency values are formatted as such. With the introduction of various new data- attributes a new option to format values was also added. Formatting values is achieved with the data-format attribute. In case you want to omit the currency symbol simply add data-format="currency-nosymbol". Other value formats are: uppercase, lowercase, date-short, date-medium, date-long and patterns like pattern(‘#,##0.00;(#,##0.00)’). Check the online documentation for the full details.

<tbody>
    <tr data-repeat="detail">
        <td data-field="Number">@Number@</td>
        <td data-field="Description" data-format="uppercase">@Description@</td>
        <td data-field="UnitPrice">@UnitPrice@</td>
        <td data-field="Quantity">@Quantity@</td>
        <td data-format="currency-nosymbol" data-field="Total">@Total@</td>
    </tr>
</tbody>
<tfoot>
    <tr data-show-row="before-break">
        <td colspan="3"></td>
        <td data-sum="detail.Quantity">@QtySum@</td>
        <td data-sum="detail.Total" data-format="currency-nosymbol">@TotalSum@</td>
    </tr>
</tfoot

Merged result with data-format

The observing reader may have noticed the Format option in the screenshot of the Attributes pane under Running totals. This means that this option can also be set via the user interface for elements with the data-field or data-sum attribute.

Adding up nested detail data

Yes, the data-sum attribute also works with sub-detail tables! This is demonstrated in the following HTML snippet and screenshot.

<tr class="InstrumentClass" data-repeat="InstrumentClass">
 <td></td>
 <td data-field="description" data-format="lowercase">@description@</td>
 <td></td>
 <td></td>
</tr>
<tr class="Sector" data-repeat="InstrumentClass.Sector">
 <td></td>
 <td data-field="description" data-format="uppercase">@description@</td>
 <td></td>
 <td></td>
</tr>
<tr class="Holding" data-repeat="InstrumentClass.Sector.Holding">
 <td data-field="code">@code@</td>
 <td data-field="r1r2">@r1r2@</td>
 <td data-field="holding">@holding@</td>
 <td data-field="value" data-format="currency-nosymbol">@value@</td>
</tr>
<tr style="page-break-before: avoid" data-repeat="InstrumentClass.Sector">
 <td></td>
 <td></td>
 <td style="font-weight: bold">Total</td>
 <td data-sum="InstrumentClass.Sector.Holding.value" style="font-weight: bold" data-format="currency-nosymbol"> @value@ </td>
</tr>

Running total for nested detail data values

Leave a Reply

Your email address will not be published. Required fields are marked *