Retreiving Year-end Trial balance for all of the entities under one enviroment

(1) Share

Report

Posted on by MU-

How can I get figures for Trial balance as of year end using PowerBI desktop.

As of now when going into V2.0 and then selecting "trial balances", it puts out the current balances as of YTD with entries made in the current year. However, what I am looking for is to get an Trial balances in PowerBI desktop that would show balances as of 31/12/2022 for all of the entities at once?

Thank you

All responses (6)

Answers (0)

MU- 5 on at

Like (0)

Report

RE: Retreiving Year-end Trial balance for all of the entities under one enviroment

1) When using the SOAP Url from Business Central i get a table below, should I use Odata link isntead?

2) When using Odata link I get TB displayed as of today, and then i guess i should insert the filter you are referring to right? When using the filter you shared I get an error like this.

Thank you for your help, it would be great if you could guide me further
DAnny3211 9,272 Super User 2024 Season 1 on at

Like (0)

Report

RE: Retreiving Year-end Trial balance for all of the entities under one enviroment

the parameter in red allows you to filter the dates

https://{businesscentralPrefix}/api/v2.0/companies({id})/trialBalances?$orderby number&$filter=dateFilter ge 2019-01-01 and dateFilter le 2019-12-31

look this for filter

https://learn.microsoft.com/en-us/dynamics-nav/using-filter-expressions-in-odata-uris

check my answer if it helped you, thanks

DAniele
MU- 5 on at

Like (0)

Report

RE: Retreiving Year-end Trial balance for all of the entities under one enviroment

Thank you for your help. Just to clarify should I use OData link or Soap URL? Also, it seems like that would display trial balance only for one entity, is there a way to make it so it displays for all?

Once, I get to this point, what should I do so it shows the balances per 31-12-2022?

I appreciate your help and patience
DAnny3211 9,272 Super User 2024 Season 1 on at

Like (1)

Report

RE: Retreiving Year-end Trial balance for all of the entities under one enviroment

I mean like this

DAniele
MU- 5 on at

Like (0)

Report

RE: Retreiving Year-end Trial balance for all of the entities under one enviroment

Apologies, not sure if I follow.

In the powerBI desktop you mean to connect using Trial balance API link?

Initially what I am trying to get is the chart of accounts balances per 31-12-2022.

Thank you for your help
DAnny3211 9,272 Super User 2024 Season 1 on at

Like (1)

Report

RE: Retreiving Year-end Trial balance for all of the entities under one enviroment

hi

you could call up from power BI the url to the trialbalance API like this

GET https://{businesscentralPrefix}/api/v2.0/companies({id})/trialBalances?$orderby number&$filter=dateFilter ge 2019-01-01 and dateFilter le 2019-12-31

applying filters &$filter=dateFilter ge 2019-01-01 and dateFilter le 2019-12-31

check my answer if it helped you, thanks

DAniele