With Invantive Cloud you can in one action download the data of all companies in - for example - an Exact Online or Twinfield subscription into Power BI. The data of all companies will be listed below each other in Power BI and the company of origin is shown by the value of a field such as “Division” (Exact Online) or for example “COMPANY_CODE” (Twinfield).
The selection of companies is currently done via a setting on an Invantive Cloud SQL database; the “Startup SQL” field captures the selected companies. The initial setting after database creation is to select all companies:
Behind the word
use is the selection.
all means that all available companies are selected for the data container with the specified alias.
The selection after
use is usually a comma-separated list of Exact Online division codes or Twinfield companies. The selected companies are combined with the permissions granted from the cloud accounting package (setting this up is illustrated later in the text).
An example of a comma-separated list of companies is:
use 123456@eol, 234567@eol, 43545@eol
More complex selections can also be made, such as “all non-archived companies” or “all companies of the Haarlem branch”. The syntax for this is further explained in Selecting partitions/administrations via the use SQL statement.
The easiest way to query the available companies is to choose the “SQL Editor” button in the database screen:
The list of companies appears in the upper right corner of the SQL Editor. The value in the round brackets (shown in red) is the code to be used in the
use statement, such as
102673 in this example:
Unfortunately, the database does not yet have a graphical tool like the company selection in Invantive Control for Excel or Invantive Query Tool; however, we are striving to make this possible in a similar way:
An idea has been registered for this.
Setting permissions correctly on companies in the accounting software has many nuances and depends on the chosen package. In case of problems, contact your application administrator in the first instance. Starting point for more information on this are:
- What is "division scoping"?
- Nieuwe Exact Online administratie niet zichtbaar in Invantive Cloud voor Power Query/BI - 2 van forums (Dutch)
By default, Invantive Cloud places a
use statement in the Startup SQL in the form:
when the first added data container on a database supports partitions, such as Twinfield. This lacks the alias; Invantive’s UniversalSQL can determine the alias of the data container when there is only one data container.
If you add a second data container, the applicable data container alias can no longer be determined automatically. You must then add the alias explicitly, such as:
or - when both data containers support companies/partitions:
with in this example
eol depicting the most frequently used alias for Exact Online, and
tfd that for Twinfield.
You can also set the list of partitions/administrations separately for each data container using:
use BEDRIJF1@tfd, 123456@eol, 456789@eol