10
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
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 |
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.
Regards
CT
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.
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
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
284,877
Super User
#2
Martin Dráb
225,425
Super User
#3
nmaenpaa
101,146
Share
Report
All responses (
Answers (