Notifications
Announcements
No record found.
I need to see the balances by Account using a SQL query against our database for All Accounts.
*This post is locked for comments
This will be incredibly slow and hard on your SQL Server if you have a large number of records in your ledger. There are faster but more complex ways to arrive at the same answer, depending on your needs. The current balance in any account is the sum of all operating (not opening or closing) transactions in that account.
SELECT m.[PARTITION], m.MAINACCOUNTID, SUM(a.ACCOUNTINGCURRENCYAMOUNT) AS ACCOUNTINGCURRENCYAMOUNT FROM GENERALJOURNALACCOUNTENTRY a JOIN GENERALJOURNALENTRY e ON e.[PARTITION] = a.[PARTITION] AND e.RECID = a.GENERALJOURNALENTRY JOIN MAINACCOUNT m ON m.[PARTITION] = a.[PARTITION] AND m.RECID = a.MAINACCOUNT JOIN FISCALCALENDARPERIOD p ON p.[PARTITION] = e.[PARTITION] AND p.RECID = e.FISCALCALENDARPERIOD WHERE p.[TYPE] = 1 -- 0=Opening,1=Operating,2=Closing GROUP BY m.[PARTITION], m.MAINACCOUNTID ORDER BY m.[PARTITION], m.MAINACCOUNTID
The problem with using DimensionFocusBalance, while it does have far fewer records than GeneralJournalAccountEntry records and is thus much faster, is that not all vouchers are guaranteed to be absorbed into DimensionFocusBalance.
When a user opens the Trial balance list page or runs the Trial balance report in AX, the Dimension set is incrementally updated to flush the DimensionFocusUnprocessedTransactions. Without processing those unprocessed vouchers, the results will not reflect the current balance.
Your code does not do that processing, and certainly SQL code cannot do it either.
SQL can achieve an accurate balance by using a union on DimensionFocusBalance and DimensionFocusUnprocessedTransactions, without having to actually absorb them into the Dimension set. Hence, the more complex solution I alluded to.
Is there a way where I can select different month's out of the year...for Instance I want to look at August Trial Balance?
The balance for any main account at any point in time is the sum of all transactions on/before that point in time. In other words, if you add up all transactions on/before a given date, you will have the ending balance as of that date. Thus, add a date filter to your SQL.
So I would I use the AccountingDate in the SQL expression?
I would like to see this expression work with Accounting Date and Showing all AR Balances, would that be a specific Main Account?
Can I run this as a New Query in Morphx or can it be ran in SQL Management Studio?
If Morphx, where in the query can I add this code? Under Methods?
Usually when someone talks about A/R balances, they mean each customer's balance, and that's not drawn from ledger or main accounts. You will certainly have one or more main accounts that represent the total balance of A/R trade. But, those are 2 different things.
In your original post, you said "all accounts" but you really weren't clear what you wanted.
Shouldn't you get a lot more specific?
Yes I agree, the direction changed from All Accounts to specific accounts based on the requester since posting. I'm assuming the total balance of AR Trade...but it was requested the following accounts:
• A/R
• Net Revenue
• A/P
• COGS
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.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Martin Dráb 4 Most Valuable Professional
Priya_K 4
MyDynamicsNAV 2
