web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to get Summary Tri...
Microsoft Dynamics AX (Archived)

How to get Summary Trial Balance Report from SQL Query from AX 2012 object data model?

(0) ShareShare
ReportReport
Posted on by Karthik Desappan 5,680

Hi all,

I need to write a SQL query to get summary trial balance report data from SQL object data model from AX 2012.

If anyone aware of this please let me the same, how to write a SQL query for this.

I tried but some confusion when writing SQL for getting trial balance related date fetching..

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: How to get Summary Trial Balance Report from SQL Query from AX 2012 object data model?

    Hello Karthik AD,

    Have you checked the datasource of the LedgerTrialBalance SSRS Report and how it retrieves the data? Maybe that is a good starting point for your query?!

    Best regards,

    Ludwig

  • Karthik Desappan Profile Picture
    Karthik Desappan 5,680 on at
    RE: How to get Summary Trial Balance Report from SQL Query from AX 2012 object data model?

    HI Ludwig,

    Thanks for ur reply..

    I have tried from the query what you replied but some financial dimension value table confused to me when writing the sql query..If u have any other idea please let me know the same...

    Write a query with parameter as only from and to date and run this query for yearly once..

  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: How to get Summary Trial Balance Report from SQL Query from AX 2012 object data model?

    There are two different sets of tables you can pull this data from.  Either the GeneralJournalEntry and GeneralJournalAccountEntry tables, which are your actual ledger, but often contain MANY transactions, or the DimensionFocusBalance table, which contains the trial balance summary data, and is often much, much smaller in terms of data and complexity.  The problem with DimensionFocusBalance is that it's not always current in SQL.  When you open Trial balance in AX, it flushes the DimensionFocusUnprocessedTransactions table (which contains a list of vouchers that have not yet been integrated into the DimensionFocusBalance table), and gets your Trial balance up to date before presenting any data.  From SQL, you won't be able to trigger that process, but alternately you can integrate these unprocessed transactions yourself in a Union statement, so both approaches can arrive at the same answer.  It's a question of the amount of data.

    Also, if you don't have a Dimension set that contains all of the financial dimensions you want, then Trial balance simply won't work unless you create a new Dimension set that does.  The straight ledger approach always works, but can have a serious impact on your SQL server if you have lots of data (more raw data on storage than RAM on your SQL Server, for example).

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: How to get Summary Trial Balance Report from SQL Query from AX 2012 object data model?

    In both cases, you can join to the DimensionAttrributeLevelValueView view to fetch a financial dimension for a ledger record.  The join clause should include Partition = Partition and ValueCombinationRecId = LedgerDimension and DimensionAttribute = @YourDimensionAttributeRecId.  In other words, first you must acquire the RecId from a DimensionAttribute record of interest, and use that to fetch the DisplayValue from the joined DimensionAttributeLevelValueView record.

    If you want multiple financial dimensions, you must left join that view multiple times.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#2
Community Member Profile Picture

Community Member 2

#2
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans