De Power BI connector bij Exact Online Controller is een van de Power BI connectoren in de markt waarmee u eenvoudig gegevens uit Exact Online kunt importeren als Power BI dataset. Na het importeren kunt u hiermee in een Power BI dashboard alle aanwezig data visualiseren.
In dit artikel leest u hoe u na configuratie van de Exact Online connector op Invantive Cloud (video aanmelden, video database registratie) tips om een bestaand Power BI dashboard om te zetten van de Power BI connector bij de Exact Online Controller naar Invantive Cloud. U kunt natuurlijk ook gebruik maken van de gratis voorbeelden zoals de Power BI template voor een dashboard met Exact Online resultatenrekening.
Werking Power BI connector voor Exact Online Controller
(Werking voor zover bekend)
De Power BI connector bij Exact Online Controller maakt gebruik van een replica van de Exact Online gegevens in een gedeelde SQL Server omgeving op Azure: de zogenaamde “Azure SQL Elastic Pool”. Hier wordt per relatie een database gedefinieerd waarvan middels IP-adres authenticatie en gebruikersnaam/wachtwoord per database rechten verleend worden. De database wordt met tussenvallen of doorlopend bijgewerkt op basis van gegevens uit Exact Online.
De naam van de SQL Server is:
zoals zichtbaar bij de Data source settings:
Dankzij het gebruik van de standaard met Power BI meegeleverde SQL Server driver is geen aparte connector nodig. Hierdoor werkt een samengesteld rapport ook op PowerBI.com.
Omzetten Excel Controller naar Invantive Cloud
Invantive Cloud ondersteunt standaard alle REST (en XML) API’s van Exact Online. Daardoor zijn alle voor het omzetten benodigde gegevens aanwezig.
Voor Invantive Cloud is er een datamodel met tabellen en een grafische weergave.
De administraties kunnen in Power BI Desktop geconfigureerd worden door een filter in te stellen op een van de administratievelden zoals Division, of door de lijst van administraties te laten instellen op de Invantive Cloud database.
Met de onderstaande tabel kunt u de bij ons bekende Power BI connector voor Exact Online Excel Controller tabellen en views koppelen aan de door Invantive gebruikte namen in Invantive Cloud:
|Excel Controller| Invantive Cloud|
|—|—|—|
| Division | SystemDivisions |
| Qsel_Financieel | Onbekend. |
| Qsel_Financieel_Budget | Onbekend. |
| Qsel_Grootboek | Onbekend. |
| Qsel_GrootboekTot | Onbekend. |
Query Qs_Fin
De query Qs_Fin werkt op basis van de SQL Server objecten Qsel_Financieel en Qsel_Financieel_Budget (de afwisseling tussen Nederlandstalige en Engelstalige kolomnamen is nog onduidelijk qua herkomst):
select Datum
, ProjectStartDatum
, ProjectEindDatum
, Created
, VervalDatum
, Modified
, Koers
, ExtraDutyAmountFC
, ExtraDutyPercentage
, BTW_Perc
, Aantal
, Betalingskorting
, Omzet
, Cash
, BedragVV
, BTW_Grondslag
, BTW_Bedrag
, Bedrag
, RelatieLongitude
, RelatieLatitude
, Budget
, RegelType
, Status
, Boekstuknr
, LineNumber
, MutatieType
, Division
, Jaar
, Periode
, Tijdvak
, Factuurnr
, OrderNumber
, Grootboek_TypeCode
, DocumentNumber
, EC_VerdichtingCode
, EC_HoofdverdichtingCode
, EC_Verdichting
, EC_Hoofdverdichting
, Code_Sub_
, Administratie_Code
, Administratienaam
, Administratie
, Administratie_Division
, Regelomschrijving
, Selectie_Jaar
, Selectie_JaarCash
, Selectie_Datum
, Selectie_Periode
, ProjectKlant
, ProjectCode
, ProjectKlassen
, Project
, ProjectManager
, ProjectType
, Dagboekcode
, Dagboek
, MVA_Code
, MVA
, Kostenplaatscode
, Kostenplaats
, KostendragerCode
, Kostendrager
, RelatieklasseCode1
, Relatieklasse1
, RelatieklasseCode2
, Relatieklasse2
, RelatieklasseCode3
, Relatieklasse3
, RelatieklasseCode4
, Relatieklasse4
, RelatieklasseCode5
, Relatieklasse5
, RelatieklasseCode6
, Relatieklasse6
, RelatieklasseCode7
, Relatieklasse7
, RelatieklasseCode8
, Relatieklasse8
, Artikelcode
, ArtikelOmschrijving
, ArtikelOmschrijvingExtra
, Artikelgroepcode
, Artikelgroep
, ArtikelKlasseCode01
, ArtikelKlasseCode02
, ArtikelKlasseCode03
, ArtikelKlasseCode04
, ArtikelKlasseCode05
, ArtikelKlasseCode06
, ArtikelKlasseCode07
, ArtikelKlasseCode08
, ArtikelKlasseCode09
, ArtikelKlasseCode10
, BalanceType
, Grootboekcode
, GrootboekOmschrijving
, Grootboek
, Soort
, Level0_Code
, Level0_Desc
, Level0
, Level1_Code
, Level1_Desc
, Level1
, Level2_Code
, Level2_Desc
, Level2
, Level3_Code
, Level3_Desc
, Level3
, Level4_Code
, Level4_Desc
, Level4
, DocUrl
, BudgetScenarioCode
, BudgetScenarioDescription
, DocumentSubject
, ModifierFullName
, Notes
, SerialNumber
, Abonnement
, TrackingNumber
, Tracking
, BTWCode
, BTW
, BTW_Type
, YourRef
, CreatorFullName
, Valuta
, Grootboek_Type
, Betalingsreferentie
, RelatieCode
, Relatie
, RelatieStatus
, RelatiePlaats
, RelatieLand
, Lokatie
, RelatieManager
from Qsel_Financieel
union all
select Datum
, ProjectStartDatum
, ProjectEindDatum
, Created
, VervalDatum
, Modified
, Koers
, ExtraDutyAmountFC
, ExtraDutyPercentage
, BTW_Perc
, Aantal
, Betalingskorting
, Omzet
, Cash
, BedragVV
, BTW_Grondslag
, BTW_Bedrag
, Bedrag
, RelatieLongitude
, RelatieLatitude
, Budget
, RegelType
, Status
, Boekstuknr
, LineNumber
, MutatieType
, Division
, Jaar
, Periode
, Tijdvak
, Factuurnr
, OrderNumber
, Grootboek_TypeCode
, DocumentNumber
, EC_VerdichtingCode
, EC_HoofdverdichtingCode
, EC_Verdichting
, EC_Hoofdverdichting
, Code_Sub_
, Administratie_Code
, Administratienaam
, Administratie
, Administratie_Division
, Regelomschrijving
, Selectie_Jaar
, Selectie_JaarCash
, Selectie_Datum
, Selectie_Periode
, ProjectKlant
, ProjectCode
, ProjectKlassen
, Project
, ProjectManager
, ProjectType
, Dagboekcode
, Dagboek
, MVA_Code
, MVA
, Kostenplaatscode
, Kostenplaats
, KostendragerCode
, Kostendrager
, RelatieklasseCode1
, Relatieklasse1
, RelatieklasseCode2
, Relatieklasse2
, RelatieklasseCode3
, Relatieklasse3
, RelatieklasseCode4
, Relatieklasse4
, RelatieklasseCode5
, Relatieklasse5
, RelatieklasseCode6
, Relatieklasse6
, RelatieklasseCode7
, Relatieklasse7
, RelatieklasseCode8
, Relatieklasse8
, Artikelcode
, ArtikelOmschrijving
, ArtikelOmschrijvingExtra
, Artikelgroepcode
, Artikelgroep
, ArtikelKlasseCode01
, ArtikelKlasseCode02
, ArtikelKlasseCode03
, ArtikelKlasseCode04
, ArtikelKlasseCode05
, ArtikelKlasseCode06
, ArtikelKlasseCode07
, ArtikelKlasseCode08
, ArtikelKlasseCode09
, ArtikelKlasseCode10
, BalanceType
, Grootboekcode
, GrootboekOmschrijving
, Grootboek
, Soort
, Level0_Code
, Level0_Desc
, Level0
, Level1_Code
, Level1_Desc
, Level1
, Level2_Code
, Level2_Desc
, Level2
, Level3_Code
, Level3_Desc
, Level3
, Level4_Code
, Level4_Desc
, Level4
, DocUrl
, BudgetScenarioCode
, BudgetScenarioDescription
, DocumentSubject
, ModifierFullName
, Notes
, SerialNumber
, Abonnement
, TrackingNumber
, Tracking
, BTWCode
, BTW
, BTW_Type
, YourRef
, CreatorFullName
, Valuta
, Grootboek_Type
, Betalingsreferentie
, RelatieCode
, Relatie
, RelatieStatus
, RelatiePlaats
, RelatieLand
, Lokatie
, RelatieManager
from Qsel_Financieel_Budget
Query Qs_Grootboek
De query Qs_Grootboek werkt op basis van het SQL Server object Qsel_Grootboek:
select *
from Qsel_Grootboek
Query Qs_Division
De query Qs_Division werkt op basis van het SQL Server object Division:
select D.Hid
Administratie_Code
, D.Division
, D.Description Administratienaam
, D.Hid + ' - ' + D.Description
Administratie
, ltrim(str(D.Division)) + ' - ' + D.Description
Administratie_Division
from Division D
De velden van Division zijn in Invantive Cloud te vinden onder SystemDivisions.
Query Qs_Cashflow
De query van Qs_Cashflow is:
select D.Administratie_Code
, D.Division
, D.Administratienaam
, D.Administratie
, D.Jaar
, D.Periode
, D.Tijdvak
, D.Datum
, D.Dagboekcode
, D.Dagboek
, D.Boekstuknr
, D.LineNumber
, D.Bedrag
, D.Cash
, D.Regelomschrijving
, D.MutatieType
, D.Relatie
, D.GLAccount
, gt.Soort
, gt.Grootboek
, gt.Level1
, gt.Level2
, gt.Level3
, gt.Level4
from ( select f.Administratie_Code
, f.Division
, f.Administratienaam
, f.Administratie
, f.Jaar
, f.Periode
, f.Tijdvak
, f.Datum
, f.Dagboekcode
, f.Dagboek
, f.Boekstuknr
, f.LineNumber
, f.Bedrag
, f.Cash
, f.Regelomschrijving
, f.MutatieType
, f.Relatie
, case
when f.Grootboek_TypeCode = 20
then ( select top 1 D.GLAccount
from Qsel_Financieel D
where D.MutatieType In (20, 21)
and D.Grootboek_TypeCode Not In (20, 22, 24)
and D.Division = f.Division
and D.Account = f.Account
order
by abs(f.Bedrag) desc
)
else case
when f.Grootboek_TypeCode = 22
then ( select top 1 D.GLAccount
from Qsel_Financieel D
where D.MutatieType In (30, 31)
and D.Grootboek_TypeCode Not In (20, 22, 24)
and D.Division = f.Division
and D.Account = f.Account
order
by abs(f.Bedrag) desc
)
else f.GLAccount
end
end
GLAccount
from Qsel_Financieel f
where f.MutatieType in (40)
and f.Grootboek_TypeCode not in (10, 12)
) D
join Qsel_GrootboekTot gt
on D.GLAccount = gt.GLAccount
and D.Division = gt.Division
Notitie 1. Hou er rekening mee dat voor grote aantallen administraties en/of grote administraties het gebruik van de *Incremental tabellen de voorkeur heeft.
Notitie 2. Standaard worden alle administraties in Exact Online waar de app rechten op heeft gekregen verwerkt. Dit kan ingesteld worden via app machtigingen (zie Nieuwe Exact Online administratie niet zichtbaar in Invantive Cloud voor Power Query/BI - 2 van forums), maar ook verfijnd via een specifieke lijst of via Power BI Desktop. Voor eenvoudige consolidatie van en benchmarks over administraties heen worden de gegevens ingelezen uit Exact Online administraties onder elkaar gezet, zodat het optellen voor consolidatie geen verdere kennis vereist.