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