journal balances not retrievable

I am currently messing around with setting up a balance sheet per period without having to import all historic journal transaction lines. For this, I need a balance per account per end of period.

However, I do not see the view JournalTransactionsByPeriodOrDateV2(which was previously available I believe). Also the default one requires a periodId which I cannot seem to incorporate when linking to Power BI. When testing in the swagger, I do get the data I need when providing the period.

Available Views

Error on VismaNet.GeneralLedgerBalanceV2.GeneralLedgerBalancesV2@vnt:

At least one of the parameters should be provided: PeriodId, LastModifiedDateTime

Could you instruct me, if possible, how to retrieve journal balances per period to Power BI? It also does not seem to work in the SQL editor, but even if I were to find out how in SQL, I could not replicate it via Power BI.


Query used:

select *
from   Vismanet.GeneralLedgerBalanceV2.GeneralLedgerBalancesV2
where  periodId = 202101
limit  1000


Onbekende ID ‘periodId’.
Consider one of the following: …

periodId is not a column, but a parameter and therefore GeneralLedgerBalancesV2 is a so-called "table function. Table functions can not be addressed from Power BI, since to our knowledge it’s OData-driver does not support these.

For more information on using table functions in general see:

For balances, you might want to try whether the following query provides usable results. If so, please update this topic for possible including of a new view for this purpose in the SQL-driver from Invantive for use with Power BI.

select *
from   FinancialPeriods fpd
join   GeneralLedgerBalancesV2(fpd.company_code, fpd.Period)
limit  10

A new view GeneralLedgerBalancesAll is available starting for the Balances per company in the release 22.0.455 of Invantive SQL.