Visma.net journal balances not retrievable

[idz]

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 Visma.net swagger, I do get the data I need when providing the period.

Error on VismaNet.GeneralLedgerBalanceV2.GeneralLedgerBalancesV2@vnt:

itgenoda061
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.

SQL

Query used:

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

Message:

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

[forums]

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:

What are table functions and table function parameters?

Go to Dutch version In this topic you will learn what table functions and table function parameters are and how they are applied within Invantive SQL. In the SQL grammar you can find more information about the concepts behind Invantive SQL. Invantive SQL has three types of tables: tables: a list of rows with data that can be retrieved by providing the name views: a list of rows with data that can be accessed by providing the name Table functions: a list of rows with data that is retrievable …

For Visma.net 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 Visma.net SQL-driver from Invantive for use with Power BI.

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

[forums]

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

[forums]

This question was automatically closed after at least 2 weeks of inactivity after a possible solution was provided. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.