Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / Refreshable GL Trial B...
Finance forum
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.

Categories:
Financial Management
  • 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 14,978 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.

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 9th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Community Spotlight for September 2024

Kudos to Mohana Yadav!

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,263 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,112 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans