Hi
We are using Alteryx (analytical tool) to connect to a copy of the prod DB in datalake.
I want to extract the trial balance report from Alteryx. We are unable to figure out the various columns in the hundreds of tables in the DB. We are not quite sure about the SQL query behind the trial balance report
Could you please provide the SQL query behind the trial balance report ?
Thank you for your help
Regards
CT
Hi CT,
There is not a direct SQL statement. The report is built using x++ coding which collects data in a temporary table which will be used for the trial balance. If you look at the coding, you may try to translate that to an SQL query.
Thank you for your reply Andre
The DBA is trying to create a view in SQL so that I can retrieve the data from Alteryx. It is getting difficult to retrieve the code behind the report. I could not find the code
We got some info here - learn.microsoft.com/.../trial-balance-report-ledgertrialbalance
As Andre mentioned its not direct SQL query. Its combination of tables.
Refer to the form LedgerTrialBalanceListPage >> constructBalances(Method name) >> Inside this method they will be calling the calculateBalance method of LedgerTrailBalanceTmp table. Inside this method LedgerTrialBalanceDp class is called with parameters..
You need to debug the code and find out the tables. One table I found was "DimensionFocusBalance".
Thanks,
Girish S.
Thanks for the reply Girish
There are four tables
DimensionAttributeValueCombination table
DimensionFocusBalance table
GeneralJournalAccountEntry table
LedgerTrialBalanceTmp table
But I don't know what calculations are done in the backend.
Finally I have received the x++ query for some reason the query does not the relevant columns which the team uses
The query does not call any the column given below
Journal number
Voucher
Date
Year closed
Type
Ledger account
Account name
Description
Currency
Amount in transaction currency
Amount
Amount in reporting currency
Posting type
Transaction type
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 Pallavi Phade as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
André Arnaud de Cal... 703 Super User 2025 Season 2
Martin Dráb 582 Most Valuable Professional
CA Neeraj Kumar 542
