We had a user who was keen to have a trial balance report in Microsoft Excel, rather than having to access D365 F&O directly. They also preferred not to use Power BIPower BI is a cloud-based suite of business intelligence and analytics tools that lets anyone connect to, visualise, and analyse data with greater speed, efficiency, and understanding. for this particular case.
Firstly, a quick reminder of what a trial balance is. A trial balance is essentially a financial report that summarises the debit and credit balances of all ledgers. Typically prepared by a company at the end of each reporting period, the main objective of a trial balance is to confirm that the total of all the debit entries in the company’s ledgers is equal to the total of all the credit entries.
Here’s a screenshot of a sample trial balance report in D365 F&O:
Now, due to the complexity of the system, there is no single table for trial balance available in D365 F&O, as you might find in a lower-tier, single ledger accounting system. It’s also not possible to use the Microsoft Dynamics Office Add-in – there’s no friendly “Open in Excel” option, available throughout much of the rest of the system.
It seemed like a good opportunity to revisit ODataOpen Data Protocol is an open protocol that allows the creation and consumption of queryable and interoperable Web service APIs in a standard way.!
We first needed to connect to our D365 F&O environment to Excel. The screenshot below show the steps needed:
The URL is simply the address of the environment (copied from the browser bar), with “/data/” appended1.
It’s necessary to sign-in with your Microsoft account – the same account as you use to access D365 F&O. This makes sure you can only access data permitted by your assigned security role(s).
Once you’ve connected, Excel will load all available tables from the D365 F&O environment.
The primary information for creating a trial balance is found in GeneralLedgerActivities, but it’s also helpful to get MainAccounts and FiscalCalendars, in order to filter by legal entity and display by period.
Excel includes the same Power Query Editor as Power BIPower BI is a cloud-based suite of business intelligence and analytics tools that lets anyone connect to, visualise, and analyse data with greater speed, efficiency, and understanding., so if you’re familiar with one, you won’t struggle in the other, when it comes to building your trial balance report. It does require a couple of joins, grouping, and sorting, but the output is quite pleasing to the eye – for an accountant!
For reference, here’s the query behind the scenes:
let
Source = Table.NestedJoin(GeneralLedgerActivities, {"LedgerGregorianDateId"}, FiscalCalendars, {"LedgerGregorianDateId"}, "FiscalCalendars", JoinKind.Inner),
#"Merged Queries" = Table.NestedJoin(Source, {"MainAccountRecId"}, MainAccounts, {"MainAccountRecId"}, "MainAccounts", JoinKind.Inner),
#"Expanded FiscalCalendars" = Table.ExpandTableColumn(#"Merged Queries", "FiscalCalendars", {"FriendlyPeriod", "PeriodOffset"}, {"FiscalCalendars.FriendlyPeriod", "FiscalCalendars.PeriodOffset"}),
#"Expanded MainAccounts" = Table.ExpandTableColumn(#"Expanded FiscalCalendars", "MainAccounts", {"MainAccountId", "Name"}, {"MainAccounts.MainAccountId", "MainAccounts.Name"}),
#"Grouped Rows" = Table.Group(#"Expanded MainAccounts", {"MainAccounts.MainAccountId", "MainAccounts.Name", "FiscalCalendars.PeriodOffset", "FiscalCalendars.FriendlyPeriod"}, {{"Sum.ReportingCurrencyAmount", each List.Sum([ReportingCurrencyAmount]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows", {"FiscalCalendars.PeriodOffset"}),
#"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[FiscalCalendars.FriendlyPeriod]), "FiscalCalendars.FriendlyPeriod", "Sum.ReportingCurrencyAmount"),
#"TrialBalance" = Table.RemoveColumns(#"Pivoted Column",{"FiscalCalendars.PeriodOffset"})
in
#"TrialBalance"Do let us know what you think in the comments. What would you add or change about the query? Would you approach the challenge another way?
- (Added 15/01/24): To speed up loading of your data, try including the table name and filter criteria in your source, e.g. https://************.sandbox.operations.dynamics.com/data/GeneralLedgerActivities?$filter=YearOffset eq 0 and Ledger eq ########## ↩︎