Bank statement

Objectives

Generate a bank statement with the following features:

• Handle a variable number of entries per page
• Display an intermediate balance on each page.

Solution

Since all entries are stored at the same level in the JSON array, and the goal is to display a variable number per page, the solution is to first compute the page of each entry , restructure the JSON with appropriate Carbone tags in the template and display them properly.

Here’s how to create a template for this using Carbone:

Structuring data by page

1. Based on your layout settings, estimate:

   • the number of characters that fit on a line (for example, 45),
   • the number of lines that fit on a page (for example, 35)

2. Compute the number of lines that each transaction occupies and store it using {d.operations[].transaction:len:div(45):ceil:add(1):cumSum:set(.nbLine)} . This expressions works as follows:

   • considers the length of the transaction using the :len formatter
   • divides this lenght by the number of characters per line (45) using the :div formatter
   • rounds the result up to the next whole number using the :ceil formatter,
   • adds 1 to account for the line spacing in the table using :add formatter
   • calcultates the cumulative total using :cumSum and stores the result in nbLine using the :set formatter.

   At this stage, each object has a new property named nbLine, which indicates the number of lines it occupies in the report's table.

3. Compute the objects to be printed on a same page using {d.operations[].nbLine:div(35):ceil:set(.numPage)}. This expressions works as follows:

   • the previously computed number of lines nbLine is divided by the maximum number of lines (35) that can fit on a page using :div formatter, and rounded up to the nearest whole number with :ceil formatter
   • the result is stored in numPage using the :set formatter.

   At this stage, each object has a new property named numPage, which indicates the page number on which it will be printed.

4. Restructure the datas into a new array d.group by grouping objects that share the same numPage, using the following formula: {d.operations[]:set(d.group[id=.numPage].data[])}

5. Use this new structure in the template to print the data: {d.group[i].data[i].transaction}.

JSON Before

{
  "operations": [
    {
      "transaction": "School fees payment - Sunshine High School for March tuition",
      "amount": -300
    },
    {...},
    {
      "transaction": "Doctor's consultation fee - CARDIO HOSPITAL - AVENUE DES CHAMPS ELYSEES - 85000 PARIS - SS284591478D21784512",
      "amount": -80
    },
    {...}
  ]
}

JSON After

{
  "operations": [
    ...
  ],
  "group":[
    {
      "data": [
      {
        "transaction": "School fees payment - Sunshine High School for March tuition",
        "amount": -300,
        "nbLine": 3,
        "numPage": 1
      },
      {...}
      ]
    },
    {
      "data": [
      {
        "transaction": "Doctor's consultation fee - CARDIO HOSPITAL - AVENUE DES CHAMPS ELYSEES - 85000 PARIS - SS284591478D21784512",
        "amount": -80,
        "nbLine": 41,
        "numPage": 2
      },
      {...}
      ]
    }
  ]
}

Subtotal computation

To print the subtotal per page, use the :aggSum formatter.

In our example, since there is a column for debits and a column for credits, the subtotals are computed as follows:

• For debits (negative amounts),

  • consider positive amounts to 0 with :ifGTE(0) and :show(0) formatters,
  • then compute :aggSum and apply currency formatting with :formatC

  {d.group[i].data[].amount:ifGTE(0):show(0):aggSum:formatC}

• For credits (positive amounts),

  • consider negative amounts to 0 with :ifLT(0) and :show(0) formatters,
  • then commpute :aggSum and apply currency formatting with :formatC

  {d.group[i].data[].amount:ifLT(0):show(0):aggSum:formatC}

Intermediate balance computation

To print the intermediate balance, combine the aggSum formatter with the cumSum formatter, and :add it to the open_bal stored in the JSON: {d.group[i].data[].amount:aggSum:cumSum:add(...open_bal):formatC}

And there you go!

Extra tip / Layout

To ensure consistent rendering, insert a page break at the beginning of each new page. To achieve this, start the loop with a neutral tag (that prints nothing) as {d.group[i]} before the table and configure a page break in the pagination options of the paragraph when the line is highlighted.

Alternatives