Notifications
Announcements
No record found.
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.
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 660 Most Valuable Professional
André Arnaud de Cal... 549 Super User 2025 Season 2
Sohaib Cheema 307 User Group Leader
