2

To solve consolidation problems with another package, we download all General Ledger transaction lines into an Excel sheet using Invantive Control. In Exact Online we have on each company a classification 2 which contains the code of the client, such as '123400'. There are multiple companies per client.

From another spreadsheet we reference those GL transaction lines using a query such as:

SELECT `'Exact transacties$'`.Administratie, `'Exact transacties$'`.Jaar2, `'Exact transacties$'`.Periode, `'Exact transacties$'`.Grootboeknr, `'Exact transacties$'`.Grootboekomschr, `'Exact transacties$'`.Classificatie_code, `'Exact transacties$'`.Zoek_code, `'Exact transacties$'`.Dagboek, `'Exact transacties$'`.Valuta, `'Exact transacties$'`.Bedrag
FROM `'Exact transacties$'` `'Exact transacties$'`
ORDER BY `'Exact transacties$'`.Administratie, `'Exact transacties$'`.Jaar2, `'Exact transacties$'`.Grootboeknr

The data source is:

DSN=Excel Files;DBQ=PATH\Databestand EOL (bestandsnaam niet wijzigen).xlsm;DefaultDir=PATH;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

However, the consolidation per period across all companies of a client takes 30 minutes per client. Most time is spent thousands of times executing the query on the Exact Online spreadsheet through this formula present in approximately 10 companies x 14 periods x 600 categories = 84.000 cells:

=ALS(W$511<=Huidige_periode;V595+(-SOMMEN.ALS(Tabel_Query_van_Excel_Files_1[Bedrag];Tabel_Query_van_Excel_Files_1[Administratie];Cliëntnr_EOL1;Tabel_Query_van_Excel_Files_1[Jaar2];HuidigBoekjaar;Tabel_Query_van_Excel_Files_1[Periode];W$511;Tabel_Query_van_Excel_Files_1[Classificatie_code];$B595&"*";Tabel_Query_van_Excel_Files_1[Dagboek];"<900"));0)

How can I improve the performance to something less than one minute?

R. van Deursen
  • 23

1 Answers1

1

Although technically and functionally correct, the approach you sketch is a bit slow and heavy on the APIs and your CPU. It is better to only retrieve the summed balances from Exact Online and let them do the heavy lifting.

Example:

Choose the right companies to request data on:

use
select DIVISIONCLASSES_DIVISIONCLASSNAME_DIVISIONCLASSNAMES_ADMINISTRATION_CODE_ATTR
from   AllAdministrationClassifications 
where  DIVISIONCLASSES_DIVISIONCLASSNAME_NUMBER_ATTR = 2 
and    CODE_ATTR = '123123' -- PUT HERE YOUR CLIENT CODE

The use statement otherwise than SQL Server allows you to specify multiple partitions (which are companies on Exact Online).

And then fetch the results per GL Account, period and division:

select BALANCE
,      PERIODS_YEAR_YEARS_BALANCE_CODE_ATTR
,      DIVISION_HID
,      PERIODS_YEAR_REPORTINGYEAR_ATTR
,      REPORTINGPERIOD_ATTR
from   BalanceLinesPerPeriod

In my setup it takes like 10 seconds to retrieve the consolidated amounts across 10 large companies.

There are some table variants available like with cost unit / cost center and year opening / closing balances.

Guido Leenders
  • 780