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.
Using 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.
Look up available companies
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:
Graphical Selection.
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.
Permissions Cloud Accounting Package.
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)
itgendid190 message: specify a data container alias when selecting partitions
By default, Invantive Cloud places a use statement in the Startup SQL in the form:
use all
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:
use all@eol
or - when both data containers support companies/partitions:
use all@eol,all@tfd
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