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?
André Arnaud de Cal...
291,979
Super User 2025 Season 1
Martin Dráb
230,848
Most Valuable Professional
nmaenpaa
101,156