web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / General transaction in...
Microsoft Dynamics AX (Archived)

General transaction into excel

(0) ShareShare
ReportReport
Posted on by Santosh 766

Hi Guys,

I would like all general transaction data into excel which should have all relevant field like Journal Name, Batch Number, description, Date, voucher number, Account type, Account, Debit, Credit, offset account type and offset account.

If anybody know in SQL table  or view it stores data please share with me. Or let me know the table list, I will create view and get data into excel.

I have check table LedgerJournalTable and LedgerJournalTrans but does not have account and offset account details. It has some keys at LedgerJournalTrans table.

This is required for finance team so that they can prepare their fancy reports using excel formulas.

We have AX 2012 R3 running on SQL 2014.

Thanks in advance.

Regards,

Santosh

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at

    Hello Santosh,

    From what you write I assume that you main issue is the account - offset account issue that some of the accountants love. Is that right?

    The view that you try to generate can easily get into a nightmare as AX supports n:m account-Offset account relationships.

    What might help though is the account Statement Report / GL Statement by Dimension Report which has a standard parameter "Offset account". If there are not too many offset accounts involved you might get some good information out of that Report. So probably start taking a look at this standard reports and how it is created to create your own extended view based on that.

    Hope this helps,

    Ludwig

  • Santosh Profile Picture
    Santosh 766 on at

    Hi Ludwig, thanks for your prompt feedback. You are right the main requirement is account and offset account for all transactions. And this is what I need to reporting purpose in excel.

    let me check the account Statement Report / GL Statement by Dimension report.

    I was thinking if I get the data directly into excel then it will be easy for me to do financial report.

    Thanks,

    Santosh

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Hi Santosh,

           

    The thing is that you need to pull the Offset account right?

    1.no need to create a view for this, you just create a form with LedgerJournalTable and LedgerJournalTrans you will get the data what you required, from that form export the data to excel.

    2. If you want bank details you can add BankAccountTable.

     creating a view is not required for your requirement. 

    Thanks,

  • Santosh Profile Picture
    Santosh 766 on at

    Hi Suresh,

    Thanks for your help. Actually I am not technical person so not able to understand how to create the form and how to attach table. Will it show data as soon as we attach the required table to form?

    For me query is easy option as If I know table and linking field I can easily create data. Also I can pull data into excel by linking SQL views.

    Thanks for help,

    Santosh

  • Santosh Profile Picture
    Santosh 766 on at

    Hi AX tech Guys..

    Any luck on this query.

    Thanks

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at

    Hello Santosh,

    What might help is the following. Can you open GL-common-main account and select an account that has some transactions recorded? Then open the account statement from the action bar and run the report with the "Offset account" parameter checked. Is this something that you want to have?

    If so, then you might want to have a look at the LedgerTransStatement classes behind that report and how they retrieve the data.

    In addition, the LedgerTransStatement query might help that you might use for extracting those data.

    Yet, I believe it might not be that easy to get exactly what you want and a lot of testing and verification of the data is required.

    Hope this helps,

    Ludwig

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.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans