Examples
Examples
This page give detailed examples on how to build a blueiron formatter.
Multi-break with sums
This example shows how to create a formatter with multiple breaks with sums.
The goal of this example is to display an accounting journal for real estate. The journal entries should be grouped by appartments, year and entry type. Each entry should display the debit, credit, the running sum for the entry type and the running sum for the account.
Initial data
The dataset that we will use in this example is the following:
| Appartment number (NOIMME) | Label (LIBELL) | Accounting year (NOANNE) | Entry type (TYPENC) | Entry date (DATECR) | Amount (MONTAN) | Credit / debit (CODECR) |
|---|---|---|---|---|---|---|
| 001 | jan 2017 | 2017 | 01 - Rent | 31/01/2017 | 1200 | 1 |
| 001 | fev 2017 | 2017 | 01 - Rent | 28/02/2017 | 1200 | 1 |
| 001 | mar 2017 | 2017 | 01 - Rent | 31/03/2017 | 1200 | 1 |
| 001 | apr 2017 | 2017 | 01 - Rent | 30/04/2017 | 1200 | 1 |
| 001 | mai 2017 | 2017 | 01 - Rent | 31/05/2017 | 1200 | 1 |
| 001 | jun 2017 | 2017 | 01 - Rent | 30/06/2017 | 1200 | 1 |
| 001 | jul 2017 | 2017 | 01 - Rent | 31/07/2017 | 1200 | 1 |
| 001 | aug 2017 | 2017 | 01 - Rent | 31/08/2017 | 1200 | 1 |
| 001 | sep 2017 | 2017 | 01 - Rent | 30/09/2017 | 1200 | 1 |
| 001 | oct 2017 | 2017 | 01 - Rent | 31/10/2017 | 1200 | 1 |
| 001 | nov 2017 | 2017 | 01 - Rent | 30/11/2017 | 1200 | 1 |
| 001 | dev 2017 | 2017 | 01 - Rent | 31/12/2017 | 1200 | 1 |
| 001 | jan 2018 | 2018 | 01 - Rent | 31/01/2018 | 1200 | 1 |
| 001 | fev 2018 | 2018 | 01 - Rent | 28/02/2018 | 1200 | 1 |
| 001 | mar 2018 | 2018 | 01 - Rent | 31/03/2018 | 1200 | 1 |
| 001 | apr 2018 | 2018 | 01 - Rent | 30/04/2018 | 1200 | 1 |
| 001 | mai 2018 | 2018 | 01 - Rent | 31/05/2018 | 1200 | 1 |
| 001 | jun 2018 | 2018 | 01 - Rent | 30/06/2018 | 1200 | 1 |
| 001 | jul 2018 | 2018 | 01 - Rent | 31/07/2018 | 1200 | 1 |
| 001 | aug 2018 | 2018 | 01 - Rent | 31/08/2018 | 1200 | 1 |
| 001 | sep 2018 | 2018 | 01 - Rent | 30/09/2018 | 1200 | 1 |
| 001 | oct 2018 | 2018 | 01 - Rent | 31/10/2018 | 1200 | 1 |
| 001 | nov 2018 | 2018 | 01 - Rent | 30/11/2018 | 1200 | 1 |
| 001 | dec 2018 | 2018 | 01 - Rent | 31/12/2018 | 1200 | 1 |
| 001 | elec 2017 | 2017 | 02 - Electricty fees | 30/06/2017 | 400 | 0 |
| 001 | elec 2018 | 2018 | 02 - Electricty fees | 30/06/2018 | 500 | 0 |
| 002 | jan 2017 | 2017 | 01 - Rent | 31/01/2017 | 1500 | 1 |
| 002 | fev 2017 | 2017 | 01 - Rent | 28/02/2017 | 1500 | 1 |
| 002 | mar 2017 | 2017 | 01 - Rent | 31/03/2017 | 1500 | 1 |
| 002 | apr 2017 | 2017 | 01 - Rent | 30/04/2017 | 1500 | 1 |
| 002 | mai 2017 | 2017 | 01 - Rent | 31/05/2017 | 1500 | 1 |
| 002 | jun 2017 | 2017 | 01 - Rent | 30/06/2017 | 1500 | 1 |
| 002 | jul 2017 | 2017 | 01 - Rent | 31/07/2017 | 1500 | 1 |
| 002 | aug 2017 | 2017 | 01 - Rent | 31/08/2017 | 1500 | 1 |
| 002 | sep 2017 | 2017 | 01 - Rent | 30/09/2017 | 1500 | 1 |
| 002 | oct 2017 | 2017 | 01 - Rent | 31/10/2017 | 1500 | 1 |
| 002 | nov 2017 | 2017 | 01 - Rent | 30/11/2017 | 1500 | 1 |
| 002 | dec 2017 | 2017 | 01 - Rent | 31/12/2017 | 1500 | 1 |
| 002 | jan 2018 | 2018 | 01 - Rent | 31/01/2018 | 1500 | 1 |
| 002 | fev 2018 | 2018 | 01 - Rent | 28/02/2018 | 1500 | 1 |
| 002 | mar 2018 | 2018 | 01 - Rent | 31/03/2018 | 1500 | 1 |
| 002 | apr 2018 | 2018 | 01 - Rent | 30/04/2018 | 1500 | 1 |
| 002 | mai 2018 | 2018 | 01 - Rent | 31/05/2018 | 1500 | 1 |
| 002 | jun 2018 | 2018 | 01 - Rent | 30/06/2018 | 1500 | 1 |
| 002 | jul 2018 | 2018 | 01 - Rent | 31/07/2018 | 1500 | 1 |
| 002 | aug 2018 | 2018 | 01 - Rent | 31/08/2018 | 1500 | 1 |
| 002 | sep 2018 | 2018 | 01 - Rent | 30/09/2018 | 1500 | 1 |
| 002 | oct 2018 | 2018 | 01 - Rent | 31/10/2018 | 1500 | 1 |
| 002 | nov 2018 | 2018 | 01 - Rent | 30/11/2018 | 1500 | 1 |
| 002 | dec 2018 | 2018 | 01 - Rent | 31/12/2018 | 1500 | 1 |
| 002 | elec 2017 | 2017 | 02 - Electricty fees | 30/06/2017 | 500 | 0 |
| 002 | elec 2018 | 2018 | 02 - Electricty fees | 30/06/2018 | 600 | 0 |
Step 1 - add a grand total
The first step is to define a break that will display the sum of debits and credits and have a title.
Since the columns debit and credit don’t exist in the original dataset, we need to create them and add them using project.
If CODECR is equal to 0, put the amount (MONTAN) in the debit column and 0 in the credit column.
If CODECR is equal to 1, put amount (MONTAN) in the credit column and 0 in the debit column.
<GridAdvancedFormater ID="REGEI_1" type="blueiron">
<columns>
<column id="DEBIT" format="Financial" label="FR={Débit} DE={Soll}" />
<column id="CREDIT" format="Financial" label="FR={Crédit} DE={Haben}" />
</columns>
<blueiron output="final">
<step id="final" source="REGEI_11">
<sort>
<column id="NOIMME" />
<column id="NOANNE" />
<column id="TYPENC" />
<column id="DATECR" />
</sort>
<project insert="after">
<expr id="DEBIT">CODECR == 0? MONTAN: 0</expr>
<expr id="CREDIT">CODECR == 1? MONTAN: 0</expr>
</project>
<break>
<title>
<value id="LIBELL">Total</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</title>
<total>
<value id="LIBELL">Total</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</total>
<computations>
<expr id="SUM_DEBIT">previousValue + DEBIT</expr>
<expr id="SUM_CREDIT">previousValue + CREDIT</expr>
</computations>
</break>
<project>
<column id="NOIMME" />
<column id="DATECR" />
<column id="NOANNE" />
<column id="TYPENC" />
<column id="LIBELL" />
<column id="DEBIT" />
<column id="CREDIT" />
</project>
</step>
</blueiron>
</GridAdvancedFormater>
Step 2 - group by appartment
In this step, we will add a new break inside the first one that will group the entries by appartment and display the total.
Since this break will do the same cumputations as the first break, we will use inheritsComputations="true".
In order to group by appartment, we need to add a condition in the break on the NOIMME column.
<GridAdvancedFormater ID="REGEI_1" type="blueiron">
<columns>
<column id="DEBIT" format="Financial" label="FR={Débit} DE={Soll}" />
<column id="CREDIT" format="Financial" label="FR={Crédit} DE={Haben}" />
</columns>
<blueiron output="final">
<step id="final" source="REGEI_11">
<sort>
<column id="NOIMME" />
<column id="NOANNE" />
<column id="TYPENC" />
<column id="DATECR" />
</sort>
<project insert="after">
<expr id="DEBIT">CODECR == 0? MONTAN: 0</expr>
<expr id="CREDIT">CODECR == 1? MONTAN: 0</expr>
</project>
<break>
<title>
<value id="LIBELL">Total</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</title>
<total>
<value id="LIBELL">Total</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</total>
<computations>
<expr id="SUM_DEBIT">previousValue + DEBIT</expr>
<expr id="SUM_CREDIT">previousValue + CREDIT</expr>
</computations>
<break inheritsComputations="true">
<condition type="column" id="NOIMME" />
<title>
<value id="LIBELL">${NOIMME}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</title>
<total>
<value id="LIBELL">Total ${NOIMME}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</total>
</break>
</break>
<project>
<column id="NOIMME" />
<column id="DATECR" />
<column id="NOANNE" />
<column id="TYPENC" />
<column id="LIBELL" />
<column id="DEBIT" />
<column id="CREDIT" />
</project>
</step>
</blueiron>
</GridAdvancedFormater>
Step 3 - group by year
In this step, we add a new break inside the second one that will group the entries by year (NOANNE) and display the total.
Once again, we use inheritsComputations="true" and have a condition on the column NOANNE
<GridAdvancedFormater ID="REGEI_1" type="blueiron">
<columns>
<column id="DEBIT" format="Financial" label="FR={Débit} DE={Soll}" />
<column id="CREDIT" format="Financial" label="FR={Crédit} DE={Haben}" />
</columns>
<blueiron output="final">
<step id="final" source="REGEI_11">
<sort>
<column id="NOIMME" />
<column id="NOANNE" />
<column id="TYPENC" />
<column id="DATECR" />
</sort>
<project insert="after">
<expr id="DEBIT">CODECR == 0? MONTAN: 0</expr>
<expr id="CREDIT">CODECR == 1? MONTAN: 0</expr>
</project>
<break>
<title>
<value id="LIBELL">Total</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</title>
<total>
<value id="LIBELL">Total</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</total>
<computations>
<expr id="SUM_DEBIT">previousValue + DEBIT</expr>
<expr id="SUM_CREDIT">previousValue + CREDIT</expr>
</computations>
<break inheritsComputations="true">
<condition type="column" id="NOIMME" />
<title>
<value id="LIBELL">${NOIMME}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</title>
<total>
<value id="LIBELL">Total ${NOIMME}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</total>
<break inheritsComputations="true">
<condition type="column" id="NOANNE" />
<title>
<value id="LIBELL">${NOIMME}-${NOANNE}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</title>
<total>
<value id="LIBELL">Total ${NOIMME}-${NOANNE}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</total>
</break>
</break>
<project>
<column id="NOIMME" />
<column id="DATECR" />
<column id="NOANNE" />
<column id="TYPENC" />
<column id="LIBELL" />
<column id="DEBIT" />
<column id="CREDIT" />
</project>
</step>
</blueiron>
</GridAdvancedFormater>
Step 4 - group by entry type
The last group step is to create a break on the entry type (TYPENC) and display the total.
<GridAdvancedFormater ID="REGEI_1" type="blueiron">
<columns>
<column id="DEBIT" format="Financial" label="FR={Débit} DE={Soll}" />
<column id="CREDIT" format="Financial" label="FR={Crédit} DE={Haben}" />
</columns>
<blueiron output="final">
<step id="final" source="REGEI_11">
<sort>
<column id="NOIMME" />
<column id="NOANNE" />
<column id="TYPENC" />
<column id="DATECR" />
</sort>
<project insert="after">
<expr id="DEBIT">CODECR == 0? MONTAN: 0</expr>
<expr id="CREDIT">CODECR == 1? MONTAN: 0</expr>
</project>
<break>
<title>
<value id="LIBELL">Total</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</title>
<total>
<value id="LIBELL">Total</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</total>
<computations>
<expr id="SUM_DEBIT">previousValue + DEBIT</expr>
<expr id="SUM_CREDIT">previousValue + CREDIT</expr>
</computations>
<break inheritsComputations="true">
<condition type="column" id="NOIMME" />
<title>
<value id="LIBELL">${NOIMME}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</title>
<total>
<value id="LIBELL">Total ${NOIMME}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</total>
<break inheritsComputations="true">
<condition type="column" id="NOANNE" />
<title>
<value id="LIBELL">${NOIMME}-${NOANNE}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</title>
<total>
<value id="LIBELL">Total ${NOIMME}-${NOANNE}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</total>
<break inheritsComputations="true" data="#{REGEI_DETAILS == true}">
<condition type="column" id="TYPENC" />
<title>
<value id="LIBELL">${TYPENC}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</title>
<total>
<value id="LIBELL">Total ${TYPENC}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
</total>
</break>
</break>
</break>
<project>
<column id="NOIMME" />
<column id="DATECR" />
<column id="NOANNE" />
<column id="TYPENC" />
<column id="LIBELL" />
<column id="DEBIT" />
<column id="CREDIT" />
</project>
</step>
</blueiron>
</GridAdvancedFormater>
At this point, the result should look like this :
| Appartment number (NOIMME) | Entry date (DATECR) | Accounting year (NOANNE) | Entry type (TYPENC) | Label (LIBELL) | DEBIT | CREDIT |
|---|---|---|---|---|---|---|
| Total | 2000 | 64800 | ||||
| 001 | 900 | 28800 | ||||
| 001-2017 | 400 | 14400 | ||||
| 01 - Rent | 0 | 14400 | ||||
| 001 | 31/01/2017 | 2017 | 01 - Rent | jan 2017 | 0 | 1200 |
| 001 | 28/02/2017 | 2017 | 01 - Rent | fev 2017 | 0 | 1200 |
| 001 | 31/03/2017 | 2017 | 01 - Rent | mar 2017 | 0 | 1200 |
| 001 | 30/04/2017 | 2017 | 01 - Rent | apr 2017 | 0 | 1200 |
| 001 | 31/05/2017 | 2017 | 01 - Rent | mai 2017 | 0 | 1200 |
| 001 | 30/06/2017 | 2017 | 01 - Rent | jun 2017 | 0 | 1200 |
| 001 | 31/07/2017 | 2017 | 01 - Rent | jul 2017 | 0 | 1200 |
| 001 | 31/08/2017 | 2017 | 01 - Rent | aug 2017 | 0 | 1200 |
| 001 | 30/09/2017 | 2017 | 01 - Rent | sep 2017 | 0 | 1200 |
| 001 | 31/10/2017 | 2017 | 01 - Rent | oct 2017 | 0 | 1200 |
| 001 | 30/11/2017 | 2017 | 01 - Rent | nov 2017 | 0 | 1200 |
| 001 | 31/12/2017 | 2017 | 01 - Rent | dev 2017 | 0 | 1200 |
| Total 01 - Rent | 0 | 14400 | ||||
| 02 - Electricty fees | 400 | 0 | ||||
| 001 | 30/06/2017 | 2017 | 02 - Electricty fees | elec 2017 | 400 | 0 |
| Total 02 - Electricty fees | 400 | 0 | ||||
| Total 001-2017 | 400 | 14400 | ||||
| 001-2018 | 500 | 14400 | ||||
| 01 - Rent | 0 | 14400 | ||||
| 001 | 31/01/2018 | 2018 | 01 - Rent | jan 2018 | 0 | 1200 |
| 001 | 28/02/2018 | 2018 | 01 - Rent | fev 2018 | 0 | 1200 |
| 001 | 31/03/2018 | 2018 | 01 - Rent | mar 2018 | 0 | 1200 |
| 001 | 30/04/2018 | 2018 | 01 - Rent | apr 2018 | 0 | 1200 |
| 001 | 31/05/2018 | 2018 | 01 - Rent | mai 2018 | 0 | 1200 |
| 001 | 30/06/2018 | 2018 | 01 - Rent | jun 2018 | 0 | 1200 |
| 001 | 31/07/2018 | 2018 | 01 - Rent | jul 2018 | 0 | 1200 |
| 001 | 31/08/2018 | 2018 | 01 - Rent | aug 2018 | 0 | 1200 |
| 001 | 30/09/2018 | 2018 | 01 - Rent | sep 2018 | 0 | 1200 |
| 001 | 31/10/2018 | 2018 | 01 - Rent | oct 2018 | 0 | 1200 |
| 001 | 30/11/2018 | 2018 | 01 - Rent | nov 2018 | 0 | 1200 |
| 001 | 31/12/2018 | 2018 | 01 - Rent | dec 2018 | 0 | 1200 |
| Total 01 - Rent | 0 | 14400 | ||||
| 02 - Electricty fees | 500 | 0 | ||||
| 001 | 30/06/2018 | 2018 | 02 - Electricty fees | elec 2018 | 500 | 0 |
| Total 02 - Electricty fees | 500 | 0 | ||||
| Total 001-2018 | 500 | 14400 | ||||
| Total 001 | 900 | 28800 | ||||
| 002 | 1100 | 36000 | ||||
| 002-2017 | 500 | 18000 | ||||
| 01 - Rent | 0 | 18000 | ||||
| 002 | 31/01/2017 | 2017 | 01 - Rent | jan 2017 | 0 | 1500 |
| 002 | 28/02/2017 | 2017 | 01 - Rent | fev 2017 | 0 | 1500 |
| 002 | 31/03/2017 | 2017 | 01 - Rent | mar 2017 | 0 | 1500 |
| 002 | 30/04/2017 | 2017 | 01 - Rent | apr 2017 | 0 | 1500 |
| 002 | 31/05/2017 | 2017 | 01 - Rent | mai 2017 | 0 | 1500 |
| 002 | 30/06/2017 | 2017 | 01 - Rent | jun 2017 | 0 | 1500 |
| 002 | 31/07/2017 | 2017 | 01 - Rent | jul 2017 | 0 | 1500 |
| 002 | 31/08/2017 | 2017 | 01 - Rent | aug 2017 | 0 | 1500 |
| 002 | 30/09/2017 | 2017 | 01 - Rent | sep 2017 | 0 | 1500 |
| 002 | 31/10/2017 | 2017 | 01 - Rent | oct 2017 | 0 | 1500 |
| 002 | 30/11/2017 | 2017 | 01 - Rent | nov 2017 | 0 | 1500 |
| 002 | 31/12/2017 | 2017 | 01 - Rent | dec 2017 | 0 | 1500 |
| Total 01 - Rent | 0 | 18000 | ||||
| 02 - Electricty fees | 500 | 0 | ||||
| 002 | 30/06/2017 | 2017 | 02 - Electricty fees | elec 2017 | 500 | 0 |
| Total 02 - Electricty fees | 500 | 0 | ||||
| Total 002-2017 | 500 | 18000 | ||||
| 002-2018 | 600 | 18000 | ||||
| 01 - Rent | 0 | 18000 | ||||
| 002 | 31/01/2018 | 2018 | 01 - Rent | jan 2018 | 0 | 1500 |
| 002 | 28/02/2018 | 2018 | 01 - Rent | fev 2018 | 0 | 1500 |
| 002 | 31/03/2018 | 2018 | 01 - Rent | mar 2018 | 0 | 1500 |
| 002 | 30/04/2018 | 2018 | 01 - Rent | apr 2018 | 0 | 1500 |
| 002 | 31/05/2018 | 2018 | 01 - Rent | mai 2018 | 0 | 1500 |
| 002 | 30/06/2018 | 2018 | 01 - Rent | jun 2018 | 0 | 1500 |
| 002 | 31/07/2018 | 2018 | 01 - Rent | jul 2018 | 0 | 1500 |
| 002 | 31/08/2018 | 2018 | 01 - Rent | aug 2018 | 0 | 1500 |
| 002 | 30/09/2018 | 2018 | 01 - Rent | sep 2018 | 0 | 1500 |
| 002 | 31/10/2018 | 2018 | 01 - Rent | oct 2018 | 0 | 1500 |
| 002 | 30/11/2018 | 2018 | 01 - Rent | nov 2018 | 0 | 1500 |
| 002 | 31/12/2018 | 2018 | 01 - Rent | dec 2018 | 0 | 1500 |
| Total 01 - Rent | 0 | 18000 | ||||
| 02 - Electricty fees | 600 | 0 | ||||
| 002 | 30/06/2018 | 2018 | 02 - Electricty fees | elec 2018 | 600 | 0 |
| Total 02 - Electricty fees | 600 | 0 | ||||
| Total 002-2018 | 600 | 18000 | ||||
| Total 002 | 1100 | 36000 | ||||
| Total | 2000 | 64800 |
Step 5 - Add a running solde and an overall sum
In this step, we add two new columns: one for the solde of the current break (SOLDE), one for the overall sum (CUMUL).
In the project tag, we add 2 new expr for SOLDE and CUMUL.
The computation of the CUMUL is simply the previous CUMUL value to which we add or substract the MONTAN depending on the credit/debit type.
For the SOLDE, we need to check the value of the TYPENC.
If the TYPENC is the same as the one from the previous line, we add or substract the MONTAN to the previous SOLDE value.
If the TYPENC is not the same, we need to reset the SOLDE.
In the break computation, we define 2 variables that will be used in the total and title of the breaks:
- SUM_SOLDE which is the value of the SOLDE column
- SUM_CUMUL which is the value of the CUMUL column
Unfortunately, since the value of SOLDE is reset when we change the entry type, the values we obtain is the break by year or appartment or in the grand total is wrong. This is why we need to define a new computation for the SOLDE. That is why we define a new computation RUNNING_SOLDE = previous RUNNING_SOLDE + CREDIT - DEBIT
<GridAdvancedFormater ID="REGEI_1" type="blueiron">
<columns>
<column id="DEBIT" format="Financial" label="FR={Débit} DE={Soll}" />
<column id="CREDIT" format="Financial" label="FR={Crédit} DE={Haben}" />
<column id="SOLDE" format="Financial" label="FR={Solde} DE={Saldo}" />
<column id="CUMUL" format="Financial" label="FR={Cumul} DE={Kumul}" />
</columns>
<blueiron output="final">
<step id="final" source="REGEI_11">
<sort>
<column id="NOIMME" />
<column id="NOANNE" />
<column id="TYPENC" />
<column id="DATECR" />
</sort>
<project insert="after">
<expr id="DEBIT">CODECR == 0? MONTAN: 0</expr>
<expr id="CREDIT">CODECR == 1? MONTAN: 0</expr>
<expr id="SOLDE">
context.PREVIOUS_TYPENC = context.CURRENT_TYPENC;
context.CURRENT_TYPENC = TYPENC;
initial = previousValue;
if(context.PREVIOUS_TYPENC != TYPENC) {
initial = 0;
}
amountToAdd = (CODECR == 1? 1 : -1) * MONTAN;
initial + amountToAdd;
</expr>
<expr id="CUMUL">previousValue + (CODECR == 1? 1 : -1) * MONTAN</expr>
</project>
<break>
<title>
<value id="LIBELL">Total</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</title>
<total>
<value id="LIBELL">Total</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</total>
<computations>
<expr id="SUM_DEBIT">previousValue + DEBIT</expr>
<expr id="SUM_CREDIT">previousValue + CREDIT</expr>
<expr id="SUM_SOLDE">SOLDE</expr>
<expr id="SUM_CUMUL">CUMUL</expr>
<expr id="RUNNING_SOLDE">previousValue - DEBIT + CREDIT</expr>
</computations>
<break inheritsComputations="true">
<condition type="column" id="NOIMME" />
<title>
<value id="LIBELL">${NOIMME}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</title>
<total>
<value id="LIBELL">Total ${NOIMME}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</total>
<break inheritsComputations="true">
<condition type="column" id="NOANNE" />
<title>
<value id="LIBELL">${NOIMME}-${NOANNE}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</title>
<total>
<value id="LIBELL">Total ${NOIMME}-${NOANNE}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</total>
<break inheritsComputations="true" data="#{REGEI_DETAILS == true}">
<condition type="column" id="TYPENC" />
<title>
<value id="LIBELL">${TYPENC}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="SUM_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</title>
<total>
<value id="LIBELL">Total ${TYPENC}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="SUM_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</total>
</break>
</break>
</break>
<project>
<column id="NOIMME" />
<column id="DATECR" />
<column id="NOANNE" />
<column id="TYPENC" />
<column id="LIBELL" />
<column id="DEBIT" />
<column id="CREDIT" />
<column id="SOLDE" />
<column id="CUMUL" />
</project>
</step>
</blueiron>
</GridAdvancedFormater>
Step 6 - Conditional colors and title display
In this final step, we add conditional color on the SOLDE column and a conditional display on the break titles.
The conditional color is added by adding an handle on the formatter.
For the title, we enable one of the titles based on the value of a boolean field DETAILS which is contained in the header.
The goal is to have the option to display or not the details of the entries.
We also use data="false" on the last break in order to hide the entries.
<GridAdvancedFormater ID="REGEI_1" type="blueiron">
<columns>
<column id="DEBIT" format="Financial" label="FR={Débit} DE={Soll}" />
<column id="CREDIT" format="Financial" label="FR={Crédit} DE={Haben}" />
<column id="SOLDE" format="Financial" label="FR={Solde} DE={Saldo}" />
<column id="CUMUL" format="Financial" label="FR={Cumul} DE={Kumul}" />
</columns>
<blueiron output="final">
<step id="final" source="REGEI_11">
<sort>
<column id="NOIMME" />
<column id="NOANNE" />
<column id="TYPENC" />
<column id="DATECR" />
</sort>
<project insert="after">
<expr id="DEBIT">CODECR == 0? MONTAN: 0</expr>
<expr id="CREDIT">CODECR == 1? MONTAN: 0</expr>
<expr id="SOLDE">
context.PREVIOUS_TYPENC = context.CURRENT_TYPENC;
context.CURRENT_TYPENC = TYPENC;
initial = previousValue;
if(context.PREVIOUS_TYPENC != TYPENC) {
initial = 0;
}
amountToAdd = (CODECR == 1? 1 : -1) * MONTAN;
initial + amountToAdd;
</expr>
<expr id="CUMUL">previousValue + (CODECR == 1? 1 : -1) * MONTAN</expr>
</project>
<break>
<title enabled="#{DETAILS != true}">
<value id="LIBELL">Total</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</title>
<total>
<value id="LIBELL">Total</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</total>
<computations>
<expr id="SUM_DEBIT">previousValue + DEBIT</expr>
<expr id="SUM_CREDIT">previousValue + CREDIT</expr>
<expr id="SUM_SOLDE">SOLDE</expr>
<expr id="SUM_CUMUL">CUMUL</expr>
<expr id="RUNNING_SOLDE">previousValue - DEBIT + CREDIT</expr>
</computations>
<break inheritsComputations="true">
<condition type="column" id="NOIMME" />
<title enabled="#{DETAILS == true}">
<value id="LIBELL">${NOIMME}-${DLABEL}</value>
</title>
<title enabled="#{DETAILS != true}">
<value id="LIBELL">${NOIMME}-${DLABEL}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</title>
<total>
<value id="LIBELL">Total ${NOIMME}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</total>
<break inheritsComputations="true">
<condition type="column" id="NOANNE" />
<title enabled="#{DETAILS == true}">
<value id="LIBELL">${NOIMME}-${NOANNE}</value>
</title>
<title enabled="#{DETAILS != true}">
<value id="LIBELL">${NOIMME}-${NOANNE}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</title>
<total>
<value id="LIBELL">Total ${NOIMME}-${NOANNE}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="RUNNING_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</total>
<break inheritsComputations="true" data="#{DETAILS == true}">
<condition type="column" id="TYPENC" />
<title enabled="#{DETAILS == true}">
<value id="LIBELL">${TYPENC}</value>
</title>
<title enabled="#{DETAILS != true}">
<value id="LIBELL">${TYPENC}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="SUM_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</title>
<total>
<value id="LIBELL">Total ${TYPENC}</value>
<column id="DEBIT" source="SUM_DEBIT" />
<column id="CREDIT" source="SUM_CREDIT" />
<column id="SOLDE" source="SUM_SOLDE" />
<column id="CUMUL" source="SUM_CUMUL" />
</total>
</break>
</break>
</break>
<project>
<column id="NOIMME" />
<column id="DATECR" />
<column id="NOANNE" />
<column id="TYPENC" />
<column id="LIBELL" />
<column id="DEBIT" />
<column id="CREDIT" />
<column id="SOLDE" />
<column id="CUMUL" />
</project>
</step>
</blueiron>
<handles removeZeroValues="true" maxLevelRemoveZeroValues="3">
<handle column="SOLDE"><![CDATA[if(value!=null && value<0) { cell.addStyle("TXT_A00"); } else { cell.addStyle("TXT_00A"); }]]></handle>
</handles>
</GridAdvancedFormater>