Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Refreshable GL Trial B...
Finance | Project Operations, Human Resources, ...
Suggested answer

Refreshable GL Trial Balance in Excel

(0) ShareShare
ReportReport
Posted on by Mark E 6,405

Have a client that is currently manually exporting GL Trial Balance data from D365 F&O (Cloud) into Excel, then creating some Financial Reports.  They want to be able to automate the Excel exporting via refreshable link using OData (or some other method).  Eventually they want to create a Financial reporting dashboard in Power BI.  I can see the Query Statement in the Form Information/Administration page, but not sure if I can use this query in Excel.

SELECT FIRSTFAST FORUPDATE AmountCredit, AmountCreditSim_IT, AmountDebit, AmountDebitSim_IT, ClosingAdjustments, ClosingTransactions, DimensionValues[1], EndingBalance, LedgerDimension, OpeningBalance, PrimaryFocus, PrimaryFocusDescription, ReportingAmountCredit, ReportingAmountDebit, ReportingClosingAdjustments, ReportingClosingTransactions, ReportingEndingBalance, ReportingOpeningBalance, DimensionValues[2], DimensionValues[3], DimensionValues[4], DimensionValues[5], DimensionValues[6], DimensionValues[7], DimensionValues[8], DimensionValues[9], DimensionValues[10], DimensionValues[11] FROM LedgerTrialBalanceTmp(LedgerTrialBalanceTmp)

Any help would be appreciated.

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Refreshable GL Trial Balance in Excel

    You can have a look at the General Ledger activities for the raw accounting data but you might need more entities to identify the ledger accounts, findims, periods, etc.

  • Mark E Profile Picture
    Mark E 6,405 on at
    RE: Refreshable GL Trial Balance in Excel

    If anyone can help point me in the direction of the correct datasets for the D365 GL Trial Balance using PowerBI I would appreciate it.  I can connect to the D365 Online Service and pull up the available datasets in the Navigator pane, but I'm not yet familiar enough with the datasets to know which one(s) to pull for the GL data.

    Thanks.

  • Mark E Profile Picture
    Mark E 6,405 on at
    RE: Refreshable GL Trial Balance in Excel

    Thanks to you both.  Relatively new to D365, so might need more guidance.  I have linked PowerBI in the D365 workspaces.  Not sure where to find the GL Trial Balance information from here.  If you know of any video tutorials that will help me, would appreciate it.

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Refreshable GL Trial Balance in Excel

    Hi Mark E,

    The potential problem that I see with this OData Excel approach is the time it takes to extract and update the data. Especially if you have a lot of transactions posted, you daily refresh in Excel might take long.

    Also, don't forget that Excel has a limit or something around 1 mio rows. What will you do if you have more than 1 mio postings?

    I would rather go with the recommendation of Saurabh and use PowerBI or alternatively the standard financial reports, which can be auto-generated based on a pre-defined time schedule.

    Best regards,

    Ludwig

  • Suggested answer
    saurabh bharti Profile Picture
    saurabh bharti 15,029 Super User 2024 Season 2 on at
    RE: Refreshable GL Trial Balance in Excel

    Why don't you use Power BI embedded in D365 which is based on different entity store (System admin > Setup > Entity store)? 

    This will have Trial balance data. Now you can utilize this for exporting trial balance.

    https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/embed-power-bi-workspaces

    You can even edit existing dashboards:

    https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/customize-analytical-workspace#:~:text=On%20the%20Action%20Pane%2C%20on,to%20customize%20the%20report%20canvas.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,642 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,371 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans