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
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