Summary
Exact Online provides balances and budgets also through the API. This note explains how you can use Invantive SQL to easily compare actuals versus using a full outer join. Also, accumulated numbers can easily be reported across multiple companies using a query.
Exact Online Balance and Budget
In the accounting package Exact Online, you can manage your actuals and budgets. Exact Online offers one accounting approach for actuals, but using reporting schemes you can create custom groups of accounts which allows you to reorder, regroup and filter actuals. Multiple budgets per fiscal year can be registered using scenarios.
You can verify the progress of your business according to the plan by combining figures from budgets and balance of actuals.
Recommended APIs and Performance
The simple approach to determine the balance is to retrieve all general ledger transactions and sum these to determine the balance by any available dimension. Such an approach scales poorly since the opening balance is composed of all transactions in all preceding fiscal years. As many other accounting, Exact Online real-time maintains a list of summed actuals for a specific combination of dimensions such as company, period, general ledger account and the analytical dimensions cost center and cost unit. These pre-calculated totals do not include dimensions such as Item, Project or Asset, so in general they are less suitable for operational reporting depending on your set up.
It is highly recommended to use the pre-calculated totals instead of summing the transactions. It is not uncommon for large companies that the totals can be retrieved in seconds through the APIs, whereas the transactions take hours.
Exact Online offers two APIs for actual balances, which scale well in number of transactions: the Invantive SQL table BalanceLinesPerPeriod in the ExactOnlineXML catalog and ReportingBalance in the ExactOnlineREST catalog. Even with the improved performance of the pre-calculated totals, the use of BalanceLinesPerPeriod is recommended over ReportingBalance. ReportingBalance is significantly notoriously slower due to a number of design choices. However, ReportingBalance offers some additional information such as the number of transactions that constitute the total. This number can be valuable for analytical purposes and query optimization.
The use of BalanceLinesPerPeriod is recommended over ReportingBalance
For budgets, the recommended API is Budgets. The related XML API works similarly and performs identical.
Actuals versus Budgets
Using an Invantive SQL query you can easily compare actuals versus budget across multiple companies.
First let’s select all authorized Exact Online companies to perform the analysis upon:
--
-- Select all Exact Online companies.
--
use all
Note that an actual amount might be present on a general ledger account for which budget has been established. And vice versa: a budget might be present for a general ledger account for which no transactions have been registered. The full outer join covers both scenarios; a full outer join returns a row for every combination where either the left side or right side of the join has a row.
The data selection becomes:
select ...
from exactonlinexml..balancelinesperperiod ble
full
outer
join exactonlinerest..budgets bud
on bud.division = to_number(ble.division_code)
and bud.glaccountcode = ble.periods_year_years_balance_code_attr
and bud.reportingyear = ble.periods_year_reportingyear_attr
and bud.reportingperiod = ble.reportingperiod_attr
--
-- Optional budget scenario code filter using Invantive Control syntax.
--
and bud.budgetscenariocode = coalesce($p{p_budgetscenario}, bud.budgetscenariocode)
The values returned need to take into account that values from both sides of the join are present or only of one side. Therefore it is necessary to use a SQL function like coalesce: coalesce evaluates to the first not null value in the list given. The order in which the columns are listed is not relevant in general.
The resulting Invantive SQL query which reports actuals versus budgets across multiple companies is:
select coalesce(to_number(ble.division_code), bud.division)
division
, coalesce(bud.glaccountcode, ble.periods_year_years_balance_code_attr)
glaccountcode
, coalesce(bud.reportingyear, ble.periods_year_reportingyear_attr)
reportingyear
, coalesce(bud.reportingperiod, ble.reportingperiod_attr)
reportingperiod
, bud.amountdc
budgetamountdc
label 'Budget (EUR)'
, ble.balance
actualsamountdc
label 'Actuals (EUR)'
from exactonlinexml..balancelinesperperiod ble
full
outer
join exactonlinerest..budgets bud
on bud.division = to_number(ble.division_code)
and bud.glaccountcode = ble.periods_year_years_balance_code_attr
and bud.reportingyear = ble.periods_year_reportingyear_attr
and bud.reportingperiod = ble.reportingperiod_attr
--
-- Optional budget scenario code.
--
and bud.budgetscenariocode = coalesce($p{p_budgetscenario}, bud.budgetscenariocode)
Note that the query assumes that all companies have the same structure for periods, chart of accounts and division currency.
Cumulative Actuals versus Budgets
The query shown above only reports the actuals and budgets from a specific period. They are neither accumulated across the periods nor related to the opening balance. For many applications it is more useful to study cumulative totals since deficits can be compensated by surpluses in other periods.
Despite no Exact Online APIs exist that accumulate the amounts across all preceding periods in a fiscal year, Invantive SQL enables you to derive these totals easily. The essence is to replace BalanceLinesPerPeriod by a cumulative variant and dito for Budgets. After that, you just add to opening balance to the totals. The opening balance includes all previous fiscal years.
A cumulative variant of BalanceLinesPerPeriod can be written as:
select ble.division_code
, ble.periods_year_years_balance_code_attr
, ble.periods_year_reportingyear_attr
, ble.reportingperiod_attr
, ble.periods_year_open
, sum(bleprev.balance) balance
from exactonlinexml..balancelinesperperiod ble
join exactonlinexml..balancelinesperperiod bleprev
on bleprev.division_code = ble.division_code
and bleprev.periods_year_years_balance_code_attr = ble.periods_year_years_balance_code_attr
and bleprev.periods_year_reportingyear_attr = ble.periods_year_reportingyear_attr
--
-- Include also all preceding periods.
--
and bleprev.reportingperiod_attr <= ble.reportingperiod_attr
group
by ble.division_code
, ble.periods_year_years_balance_code_attr
, ble.periods_year_reportingyear_attr
, ble.reportingperiod_attr
, ble.periods_year_open
Compared to just retrieving the actuals from the table, the query above joins the table with itself on all uniquely identifying columns (the primary or business key). However, using the following join condition it select all preceding periods in the same fiscal year:
and bleprev.reportingperiod_attr <= ble.reportingperiod_attr
For example, with the following amounts per period for a specific combination of company, general ledger account and fiscal year:
| Period | Amount |
|---|---|
| 1 | 25 |
| 2 | 13 |
| 3 | 8 |
| 4 | 37 |
and period 3, the join condition ensures that bleprev returns the amounts 25, 13 and 8, which is exactly the needed total.
The full cumulative actuals versus budgets query for all selected Exact Online companies is:
select coalesce(to_number(ble.division_code), bud.division)
division
, coalesce(bud.glaccountcode, ble.periods_year_years_balance_code_attr)
glaccountcode
, coalesce(bud.reportingyear, ble.periods_year_reportingyear_attr)
reportingyear
, coalesce(bud.reportingperiod, ble.reportingperiod_attr)
reportingperiod
, bud.amountdc
budgetamountdc
label 'Budget (EUR)'
, ble.balance
+ ble.periods_year_open
actualsamountdc
label 'Actuals including Opening Balance (EUR)'
from ( select ble.division_code
, ble.periods_year_years_balance_code_attr
, ble.periods_year_reportingyear_attr
, ble.reportingperiod_attr
, ble.periods_year_open
, sum(bleprev.balance) balance
from exactonlinexml..balancelinesperperiod ble
join exactonlinexml..balancelinesperperiod bleprev
on bleprev.division_code = ble.division_code
and bleprev.periods_year_years_balance_code_attr = ble.periods_year_years_balance_code_attr
and bleprev.periods_year_reportingyear_attr = ble.periods_year_reportingyear_attr
--
-- Include also all preceding periods.
--
and bleprev.reportingperiod_attr <= ble.reportingperiod_attr
group
by ble.division_code
, ble.periods_year_years_balance_code_attr
, ble.periods_year_reportingyear_attr
, ble.reportingperiod_attr
, ble.periods_year_open
) ble
full
outer
join ( select bud.division
, bud.glaccountcode
, bud.reportingyear
, bud.reportingperiod
, sum(budprev.amountdc) amountdc
from exactonlinerest..budgets bud
join exactonlinerest..budgets budprev
on budprev.division = bud.division
and budprev.glaccountcode = bud.glaccountcode
and budprev.reportingyear = bud.reportingyear
and budprev.budgetscenariocode = bud.budgetscenariocode
--
-- Include also all preceding periods.
--
and budprev.reportingperiod <= bud.reportingperiod
where bud.budgetscenariocode = coalesce($p{p_budgetscenario}, bud.budgetscenariocode)
group
by bud.division
, bud.glaccountcode
, bud.reportingyear
, bud.reportingperiod
)
bud
on bud.division = to_number(ble.division_code)
and bud.glaccountcode = ble.periods_year_years_balance_code_attr
and bud.reportingyear = ble.periods_year_reportingyear_attr
and bud.reportingperiod = ble.reportingperiod_attr
Invantive SQL
The Invantive SQL used in this note with Exact Online is a Dutch innovation provided by Invantive. Invantive SQL is supported across a wide range of Invantive products and covers over 50 platforms (cloud and on-premise). For Exact Online, software can be downloaded from https://exact-online-apps-by-invantive.com. The Invantive Query Tool is a simple tool to start using Invantive SQL. The grammar is given on documentation.invantive.com.