Announcements
Dear Guys,
What are the SQL tables to calculate actuals / budget?
I'm writing a SSRS report that needs to group both.
regards,
Hi Ahmed,
There are quite some tables involved. You can look at standard reports in the application to learn which queries need to be created supported by some x++ logic or not. There are some transaction tables, but Microsoft created a lot of views supporting easier access and flattening the data model. I don't know all objects names by head.
Anyway, is the requirement that specific that configuration of the Financial reporter is not giving the expected results for your requirement?
Thanks André for the reply
I tried to reverse engineer some of the budget screens, and built some queries.
This is one for the actuals, for example:
SELECT T1.FOCUSLEDGERDIMENSION AS f1, T2.ACCOUNTINGCURRENCYAMOUNT AS f2, 1 AS RECVERSION, 5637144576 AS PARTITION FROM DIMENSIONFOCUSLEDGERDIMENSIONREFERENCE T1 CROSS JOIN GENERALJOURNALACCOUNTENTRY T2 CROSS JOIN GENERALJOURNALENTRY T3 CROSS JOIN DIMENSIONATTRIBUTEVALUECOMBINATION T4 CROSS JOIN MAINACCOUNT T5 WHERE ((T1.PARTITION = 5637144576) AND ((T1.FOCUSDIMENSIONHIERARCHY = 5637144589) AND (((T3.ACCOUNTINGDATE >= {ts '2023-01-01 00:00:00.000'}) AND (T3.ACCOUNTINGDATE <= {ts '2023-01-31 00:00:00.000'})) AND (T5.TYPE = 2)))) AND ((T2.PARTITION = 5637144576) AND (T1.ACCOUNTENTRYLEDGERDIMENSION = T2.LEDGERDIMENSION)) AND ((T3.PARTITION = 5637144576) AND (((T3.POSTINGLAYER = 0) AND (T3.LEDGER = 5637145326)) AND (T2.GENERALJOURNALENTRY = T3.RECID))) AND ((T4.PARTITION = 5637144576) AND (T2.LEDGERDIMENSION = T4.RECID)) AND ((T5.PARTITION = 5637144576) AND ((T5.LEDGERCHARTOFACCOUNTS = 5637144576) AND (T4.MAINACCOUNT = T5.RECID))) AND EXISTS(SELECT 'x' FROM FISCALCALENDARPERIOD T6 WHERE ((T6.PARTITION = 5637144576) AND (NOT ((T6.TYPE = 2)) AND (T3.FISCALCALENDARPERIOD = T6.RECID)))) ORDER BY T1.FOCUSDIMENSIONHIERARCHY, T1.ACCOUNTENTRYLEDGERDIMENSION
now my next hurdle is:
how to implement this query in X , especially the cross joins.
Any ideas guys?
thanks in advance
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.
Jump in, show your community spirit, and win prizes!
Expanding mentorship, skilling, and AI innovation
These are the community rock stars!
Stay up to date on forum activity by subscribing.
André Arnaud de Cal... 628 Super User 2026 Season 1
Giorgio Bonacorsi 612
Abhilash Warrier 589 Super User 2026 Season 1
