Dear Guys,
What are the SQL tables to calculate actuals / budget?
I'm writing a SSRS report that needs to group both.
regards,
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
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?
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 Abhilash Warrier as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
CA Neeraj Kumar 2,206
André Arnaud de Cal... 847 Super User 2025 Season 2
Sohaib Cheema 596 User Group Leader
