Bank statement

Objectives

You want to generate a bank statement with the following features:

• Display an intermediate balance on each page.
• The number of lines that fit on each page may vary depending on the length of the transaction text.

Solution

Here’s how you can achieve this using Carbone:

Structuring data by page

1. Based on your pagination settings, estimate:

   • the number of characters that fit on a line (e.g., 45),
   • the number of lines that fit on a page (e.g., 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)} where:

   • The length of the transaction len is divided div by the number of characters per line (45), and rounded up ceil to the nearest whole number,
   • :add(1) accounts for the line spacing in the table.
   • The cumulative sum cumSum is stored 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)} where:

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

   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), set positive amounts to 0 using :ifGTE(0):show(0) before aggSum: {d.group[i].data[].amount:ifGTE(0):show(0):aggSum:formatC}

• For credits (positive amounts), set negative amounts to 0 using :ifLT(0):show(0) before aggSum: {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 / Pagination

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

more info