Often you will need to get a rough indication of the size of an Exact Online company in terms of transactions and revenues. It is easier to optimize your queries when combining this information with the typology of the company as expressed by the SBI code.

This article introduces a fast approach to assessing the size of an Exact Online company.

Approach

The Exact Online APIs offers the table ReportingBalance, which contains transaction lines counts as well as amounts, aggregated across a number of dimensions such as General Ledger account and financial period. Even for large companies it is reasonably fast as long as no excessive use is made of Cost Unit / Cost Center combinations.

Periods

The first step is to create a list of financial periods per Exact Online company selected for the last 12 calendar months, irrespective of the financial calendar:

use all

create or replace table periods12@inmemorystorage
as
select fpdprev.division
,      fpdprev.FinYear
,      fpdprev.FinPeriod
,      to_char(fpdprev.division) || '-' || to_char(fpdprev.finyear) || '-' || to_char(fpdprev.FinPeriod) periodlabel
from   FinancialPeriods fpdnow
join   FinancialPeriods fpdprev
on     fpdprev.division = fpdnow.division
and    fpdprev.startdate between add_months(fpdnow.startdate, -11) and fpdnow.startdate
where  trunc(sysdateutc) between fpdnow.startdate and fpdnow.enddate
SQL

Statistics

Using this list of periods in the last 12 calendar months, we create an overview of the revenues and number of transaction lines using:

select Division
,      sum(-amount) revenue_year
,      sum(count)   transaction_lines_year
from   ReportingBalance
--
-- ReportingYear is server-side filtered, so limit the volume as soon as possible.
--
where  ReportingYear in (select finyear from periods12@InMemoryStorage)
and    to_char(division) || '-' || to_char(reportingyear) || '-' || to_char(reportingperiod) in (select periodlabel from periods12@inmemorystorage)
--
-- Reduce data volume further by only considering P&L
-- and sales transactions.
--
and    BalanceType = 'W'
and    type in (20,21)
group
by     Division
SQL