Voor het rapporteren van een wekelijks dashboard zou ik graag grootboeksaldo’s uit de database willen halen per wekelijkse periode, of tussen twee datums.
De huidige formules onder ‘Balansen’ in de module ‘Bouwblokken’ zijn zeer geschikt voor de maandelijkse rapportages. Echter, ik zou dat graag op wekelijkse basis kunnen doen.
Een formule waarbij ik een datum van- tot en met zou in kunnen vullen zou de oplossing zijn.
Wie kan mij helpen?
De formules onder “Balansen” / “Saldi” (laatste versies) zijn uitsluitend te gebruiken per financiële periode vanwege performance. Exact Online houdt namelijk doorlopend de saldi bij voor een combinatie van dimensies:
Deze (sterk ingedikte) informatie is technisch terug te vinden in tabellen zoals BalanceLinesPerPeriod of ReportingBalance.
Een overzicht van deze formules is te vinden op De meest populaire Exact Online Excel add-in formules.
Om de saldi van mutaties op te vragen voor alles wat buiten de genoemde dimensies ligt, zoals bijvoorbeeld datum vanaf/datum tm, is het nodig om de relevante boekingen in die periode op te halen en op te tellen. Dat kan op verschillende manieren.
Wat handig is, hangt af van de beschikbare vaardigheden, het aantal administraties en de omvang van de individuele administraties. Een administratie met elke dag 50.000 boekingen (die zijn er) vereist een andere aanpak dan een administratie met in totaal in 3 jaar 50.000 boekingen. Ter illustratie: het gemiddeld aantal boekingen per Exact Online administratie is 15.000.
Voorkennis: Excel, Exact Online, werken met formules
Het Exact Online saldo over een periode tussen twee willekeurig datums kan real-time met een Excel formule opgevraagd worden met de Excel add-in. De Excel formule voor dit Exact saldo is I_EOL_TXN_AMOUNT_DC. Hierin kunnen ook meerdere grootboeknummers opgegeven worden zoals “8*,65*” voor alle grootboekrekeningen die beginnen met een ‘8’ of ‘65’.
Voor meer uitleg zie Nieuwe Excel consolidatiemogelijkheden voor Exact Online bedrijven en grootboekrekeningen.
Voorkennis: Excel, Exact Online, werken met draaitabellen
Voor omgevingen met niet te gek veel boekingen (paar honderdduizend hoogstens) kan een download gemaakt worden vanuit de Bouwblokken → Exact Online → Boekstukregels. Via een paar stappen worden alle boekingen opgehaald en geplakt in het Excel-sheet.
Omdat de resultaten ook een naam hebben (zie “Name Manager” in het lint) kun je hier vlot een draaitabel van maken.
Voorkennis: Excel, Exact Online, SQL, modelbouw
Invantive Control kent ook blokken. Hiermee kun je complexe calculaties bouwen, maar ook flexibele downloads. De basis is altijd een SQL-query binnen de modeleditor van de Excel add-in, in dit geval bijvoorbeeld:
select *
from TransactionLinesIncremental
where Date >= $X{Sheet1!A1}
and Date <= $X{Sheet1!A2}
Merk op dat ook de SQL-statements rekening houden met de ingestelde administratie. Als er meerdere administraties gekozen zijn, dan wordt alle Exact Online data uit die administraties tegelijk opgehaald.
De begin- en einddatum worden uit de cellen A1 en A2 gehaald.
Er is een 30-tal voorbeeldrapporten beschikbaar waar ook een dergelijk rapport tussen zit. Indien gewenst sturen we die rapporten graag op. Laat hiervoor een antwoord achter.
Het gebruik van Excel-blokken is redelijk complex, maar ook enterprise-niveau. De cashflowprognose voor Exact Online maakt hier bijvoorbeeld gebruik van.
Een voorbeeld voor het maken staat in de video:
Voorkennis: Excel, Exact Online
In elk abonnement waar Invantive Control for Excel zit, zit ook Get My Report. In Get My Report zit het rapport Boekingsregels waarmee alle boekingen opgehaald kunnen worden. Alle individuele boekingsregels zitten in de resulterende Excel-sheet. Omdat de resultaten ook een naam hebben (zie “Name Manager” in het lint) kun je hier vlot een draaitabel van maken. Video’s over het gebruik van draaitabellen zijn op YouTube vlot te vinden.
Voor meer uitleg zie Transaction Lines - Get My Report.
Voorkennis: Excel, Exact Online, SQL
Vanuit Invantive Control kan ook Query Tool gestart worden. Hiermee kun je queries maken zoals:
select *
from TransactionLinesIncremental
where Date >= to_char('20200101', 'YYYYMMDD')
and Date <= to_char('20201231', 'YYYYMMDD')
local export results as "c:\temp\dump.xlsx" format xlsx include headers
De query kan gedraaid worden via “Execute All” of F5. Ook queries met honderden stappen en complexe bedrijfslogica zoals facturatie en synchronisatie kunnen zo draaien.
Voorkennis: Excel, Exact Online, Power Query
In elk Invantive abonnement met Invantive Control zit ook de Power Query/Power BI connector op Invantive Cloud. Hiermee kun je snel en vlot de data downloaden in Power Query (Power Pivot).
Maak hiervoor een Exact Online database aan zoals beschreven op https://cloud.invantive.com/nl/exactonline/power-query-connector/setup (of bekijk de video’s). Download daarna de tabel TransactionLinesIncremental. Gebruik indien nodig een filterstap in Power Query.
Voorkennis: Excel, Exact Online
In elk Invantive abonnement met Invantive Control zit ook de Power Query/Power BI connector op Invantive Cloud. Hiermee kun je snel en vlot de data downloaden op https://access-odata.com
Maak hiervoor een Exact Online database aan zoals beschreven op https://cloud.invantive.com/nl/exactonline/power-query-connector/setup (of bekijk de video’s).
Ga naar https://access-odata.com
Meld je aan.
Selecteer en download de tabel TransactionLinesIncremental.
Ook hier kun je werken met draaitabellen. Video’s over het gebruik van draaitabellen zijn op YouTube vlot te vinden.
Voorkennis: Excel, Exact Online, SQL, PSQL
Programmeurs kunnen ook zelf precies de structuur sturen van de Invantive SQL-queries op de boekingen. Op bijvoorbeeld Invantive Cloud en andere Invantive SQL-producten kunnen Excel-werkboeken via programmacode samengesteld en gevuld worden. Dit is vooral handig als het gaat om complexe rapportages die herleidbaar en betrouwbaar telkens opnieuw moeten draaien.
Voor meer uitleg zie Excel download uit Exact Online in eigen formaat met een cloud app.
Deze vraag is automatisch gesloten na tenminste 2 weken inactiviteit nadat een mogelijk passend antwoord is gegeven. Het laatste gegeven antwoord is gemarkeerd als oplossing.
Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.