Summary
Exact Online offers RGS support through the user interface and APIs. Using Invantive SQL this note describes how to create financial reports using RGS codes.
RGS or Referentie Grootboekschema
An optional standardized General Ledger scheme is available within the Netherlands named ‘RGS’ or ‘Referentie Grootboekschema’. Although the creation process was complicated, it now aids business users and accountants in assessing the statements about businesses contained in a General Ledger. It is especially excels when the same information is to be processed by multiple parties and/or compared across multiple companies.
The scheme has a number of versions which are incrementally developed by a community. The reference site has them available.
The implementation approach and adoption speed of individual accounting packages for RGS varies and is sadly enough left to the software vendor market. Also, the inclusion of RGS codes in XML Audit Files version 3.2 and newer is optional. An overview is available at rgsready.nl.
Adoption of RGS especially in small accounting shops is lagging behind as well as support in many accounting software solutions.
RGS is also meant to facilitate information exchange between different software solutions. It enables data-analytics, dashboard or tax computation solutions to use the RGS code to automatically classify account balances in the correct category. This also allows for quicker adoption of other software solutions which in turn may increase speed of innovation in the economy.
Therefore, the promises of economic gains of RGS have not yet been fulfilled and . Also RGS can help in a plain level playing field by improved consistent tax collections. I hope that the Ministry of Finance someday makes use of RGS non-optional for accountants and requires software vendors to support it.
To reduce the amount of interpretation and labor involved in assigning GL Accounts to RGS codes, Analys.io has made available software to automatically add RGS codes based upon heuristics and a database of previous mappings.
RGS on Exact Online
Exact Online has released in 2016 support for RGS based upon the existing Reporting Schemes. For instance, the picture below shows the mapping of a GL Account 0770 towards RGS:
The RGS Reporting Scheme is standardized; it can not be changed. However, customized Reporting Schemes are available too. In this case, all transactions on the GL Account 0770 can be mapped into a reporting code for use in Belgium.
The use of non-standard Reporting Schemes and/or RGS is only available to accountancy subscriptions. Sadly enough this hinders fulfilling the economic gains even more, since cloud collaboration between entrepreneur and accountant is made impossible for this area. The same holds for using multiple customized Reporting Schemes, which is great for reporting according to multiple GAAPs and handling off-balance. Especially when combined with a custom reporting package like Invantive Control the use of additional Reporting Schemes allows cross-company and cross-GAAP reporting and consolidation.
Hopefully Exact makes available Customized and Standardized Reporting Schemes to entrepreneurs in a future review of their subscription’s contents.
RGS APIs
After some delay, RGS is now available through a number of APIs. These APIs are available through Invantive SQL. The Exact Online driver of Invantive also adds various reference tables for RGS like ‘Rubrieken’.
However note that several APIs still only support the Customized Reporting Schemes. Also note that I yet have to see real-life practical cases of the use of the fifth level of RGS. I hope that instead of level 5 dimensions such as cost center, project or asset on transactions will be used.
The following queries help you to find APIs which cover RGS and return the necessary information and create reports using Invantive SQL. Of course, you can also use Microsoft SQL Server, PostgreSQL, Oracle or MySQL for reporting when combined with Invantive Data Replicator. Data Replicator is recommended for performance reasons when the number of companies exceeds 1.000.
To run these queries, you can use any Invantive SQL-based product such as Invantive Query Tool (free version limited to 1.000 rows) or Invantive Control for Excel. These are available for download on download.invantive.com.
Intended Result
The following picture shows you a sample of a RGS-based financial report across multiple Exact Online companies you can achieve with the queries below:
Actual Queries on RGS
After installation and starting the Query Tool from the start menu or completely re-starting Excel, first log on using an accountancy-based subscription:
The queries can be executed for the initial Exact Online company, which defaults to the last used company in the website of Exact. You can also select multiple companies to use. The company numbers can be extracted from a dropdown in the menu, using the number between ‘(’ and ‘)’:
Optional Preparation
To optionally change the companies all future queries will run across to 102673 (‘Zorgdienst…’), 868041 (‘Commercieel bedrijf…’) and 868056 (‘Voorbeeld projectondernemingen’) when you execute the following statement. This step is optional, but when you execute it first replace the numbers by your intended division numbers:
use 102673,868041,868056
It is essential that you choose the right country, since Exact Online has different environments per country. You can execute the following optional step as an Application Control to check that you are using the environment of the Netherlands.
--
-- Optional step.
--
select DivisionCustomerName
, EnvironmentTopLevelDomain
, case
when EnvironmentTopLevelDomain != 'nl'
then raise_error('sample1', 'Please use the Dutch environment.', 'Log on to Exact Online NL.')
else 'OK'
end
status
from me
The outcome is similar to:
Query GL Account
The following query will help you retrieve the description of all GL Accounts with code ‘0770’:
--
-- Access GL Account information, in this case for 0770.
--
-- The division in the first column is the unique number of the
-- Exact Online company within the country-specific environment
-- of Exact Online. In this case the Netherlands.
--
select division
, divisionlabel
, code
, description
from exactonlinerest..glaccounts
where code = '0770'
The output is similar to:
Query RGS Classification of GL Accounts
The two following queries will both return the RGS classification of all GL Accounts in the companies chosen.
Exact Online offers several APIs: XML and REST. The XML APIs have aged well, especially for accounting, and the REST APIs cover more ERP functionality. The XML APIs get some maintenance, but the major enhancements are made in the REST APIs. In general you will need to use both XML and REST for meaningful reports for accounting.
--
-- This table is based on a XML API and shows the relationship
-- between a GL Account and a RGS version.
--
select division_code
, glclassificationlinks_glaccount_code_attr
, glclassificationlinks_glaccount_description
, glaccountscheme_code_attr
, glclassification_code_attr
, glclassification_description
from glaccountclassifications
where glaccountscheme_code_attr = 'RGS versie 1.1'
order
by division_code
, glclassificationlinks_glaccount_code_attr
The same output can be achieved using:
--
-- This query returns the same results as the previous one,
-- but now using a table based on the REST API.
--
select Division
, GLAccountCode
, GLAccountDescription
, ClassificationCode
, ClassificationDescription
from GLAccountClassificationMappings
where GLSchemeCode = 'RGS versie 1.1'
order
by Division
, GLAccountCode
The output is similar to the following picture. Note that the account 0770 has a RGS mapping in only two companies, both to the RGS code ‘BEivOreOvw’.
Query RGS Tree Structure
Knowing the RGS code a GL Account in an Exact Online company is mapped to, is only part of the story. You also want to follow the path from the RGS tree entry level all the way to the top.
The following query displays the relationship between the RGS codes. The column ‘Parent’ gives you a unique text that should occur in the column ‘ID’ with the parent of a RGS code. Note that the DivisionNotNull column is used to circumvent a bug in Exact with retrieval of the Division column.
select DivisionNotNull
, Code
, Description
, Id
, Parent
, PeriodType
from GLClassifications
where TaxonomyNamespaceDescription = 'Referentie GrootboekSchema versie 1.1'
order
by Code
The output will be similar to the following, where the parent ID of code ‘Beff - EFFECTEN’ is ‘3201…’ matches the value at for ‘01 - Balans’.
Balance with RGS
The following query produces the intended balance sheet with additional RGS columns (in this case the RGS code plus the optional parent RGS code) as shown at the top of this post:
select ble.division_code
, ble.reportingyear_attr
, ble.open
, ble.close
, ble.years_balance_code_attr
, ble.years_balance_description
, gcg.ClassificationCode label 'RGS Code'
, gcg.ClassificationDescription label 'RGS Description'
, gcn1.Code label 'Parent RGS Code'
, gcn1.Description label 'Parent RGS Description'
from balancelines ble
--
-- Combine the GL Account with the optional mapping to RGS.
--
left
outer
join GLAccountClassificationMappings gcg
on gcg.division = ble.division_code
and gcg.GLAccountCode = ble.years_balance_code_attr
and gcg.GLSchemeCode = 'RGS versie 1.1'
--
-- Find the starting entry in the RGS relationship table.
--
left
outer
join GLClassifications gcn0
on gcn0.DivisionNotNull = gcg.division
and gcn0.Code = gcg.ClassificationCode
and gcn0.TaxonomyNamespaceDescription = 'Referentie GrootboekSchema versie 1.1'
--
-- Navigate one level up in the RGS relationship table to get the RGS parent code.
--
left
outer
join GLClassifications gcn1
on gcn1.DivisionNotNull = gcn0.DivisionNotNull
and gcn1.Id = gcn0.Parent
where ble.reportingyear_attr = 2018
order
by ble.division_code
, ble.years_balance_code_attr
RGS Reference Data
The RGS reference data are also available in various reference tables, which are listed here for reference only.
select code
, description
from RGS11Codes
order
by code
select *
from RGS11CodeRelations
order
by code
select *
from RGS11Hoofdrubrieken
order
by code
select *
from RGS11Mutaties
order
by code
select *
from RGS11Rekeningen
order
by code
select *
from RGS11Rubrieken
order
by code
