Share
ReportI need to see the balances by Account using a SQL query against our database for All Accounts.
*This post is locked for comments
You’re offline. This is a read only version of the page.
I need to see the balances by Account using a SQL query against our database for All Accounts.
*This post is locked for comments
The GeneralJournalEntry and GeneralJournalAccountEntry tables are your ledger.
The Trial balances uses a summary of those tables, called DimensionFocusBalance, which is the other place you can easily and quickly query your ledger history, though it can lag behind GeneralJournalEntry and GeneralJournalAccountEntry, so it depends on how current you need your data to be.
The Trial balance can disagree with your actual ledger in some cases. If this happens, rebuild your Financial dimension sets to clear it up.
LedgerJournalTable and LedgerJournalTrans are not your whole ledger, unless all you do is post journals in your system. If you transact in any other way (sales orders, inventory), it will not be complete.
Using LedgerJournal tables was the only way I can see the amounts for add up correctly for Trial Balance. Using the GeneralJournal the numbers were off and did not show correctly. Is there a form or report in AX>GL that will show me the numbers from General Journal?
I'm not sure why you switched to LedgerJournalTable and LedgerJournalTrans. Those are the ledger journals that when posted become records in GeneralJournalEntry and GeneralJournalAccountEntry. But, not all ledger activity comes from ledger journals. Plus, ledger journals sometimes have just an account and sometimes also have an offset account, which you did not consider. Those are at least two reasons why your query does not tie to the actual trial balance.
Yes, Type = 1 (no quotation marks needed for an integer value) will get you Operating type fiscal periods only.
To use SUM, you will have to add a GROUP BY clause with all of the fields you do not want to sum. For example, you would SUM the amounts (debit and credit), and then group by all the other fields not being summed.
Ok, I have put a SQL statement togehter that ties in (somewhat) the data to the Trial Balance.
I'm using account 132004 with a Debit of $46,739.42
and then Running this sql:
SELECT
LJT.JournalNum, LJT.Name, LJT.DataAreaID, LJT.JournalType, LJT.Posted, LJTR.LineNum, LJTR.Txt, LJTR.CurrencyCode, LJTR.AmountCURDebit, LJTR.AmountCURCredit
,LJTR.Voucher, LJTR.TransDate, FCP.Name, DVC.DisplayValue, DVC.MainAccount FROM
Weetabix.LedgerJournalTable LJT
INNER JOIN Weetabix.LedgerJournalTrans LJTR
ON LJT.JournalNum = LJTR.JournalNum
AND LJT.DATAAREAID = LJTR.DATAAREAID
INNER JOIN Weetabix.DimensionAttributeValueCombo DVC
ON LJTR.LedgerDimension = DVC.RecID
INNER JOIN Weetabix.MainAccount MA
ON DVC.MainAccount = MA.RecID
INNER JOIN WEETABIX.FISCALCALENDARPERIOD FCP
ON LJTR.TransDate >= FCP.STARTDATE AND LJTR.TransDate <= FCP.ENDDATE
WHERE MA.MainAccountID = '132004' AND FCP.Type = '1'
My question is, How can I get the SUM of the amount, and Reconcile the data shown in AX? And am I selecting the right Fiscal Period using "Operating"?
So still main accounts, just fewer of them.
Add a WHERE clause on MainAccountId (on table MainAccount) as well as AccountingDate (on GeneralJournalEntry).
The SQL I provided runs in SQL Sever Management Studio.
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
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?
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?
I would like to see this expression work with Accounting Date and Showing all AR Balances, would that be a specific Main Account?
So I would I use the AccountingDate in the SQL expression?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (