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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Linking Project transa...
Microsoft Dynamics AX (Archived)

Linking Project transactions to General Ledger transactions

(1) ShareShare
ReportReport
Posted on by Community Member

We are using Dynamics AX 2012 R2 and are currently trying to reconcile numbers between the Project Module and General Ledger. Is there any way to link Project transactions (PROJTRANSPOSTING) to General Ledger transactions (GENERALJOURNALACCOUNTENTRY)? Help would be greatly appreciated, thanks!

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Linking Project transactions to General Ledger transactions

    Hi,

    In the GL Report section there are a couple of reconciation reports for Project-GL transactions available. Have you tried using those reports?

    Ludwig

  • Community Member Profile Picture
    Community Member on at
    RE: Linking Project transactions to General Ledger transactions

    Hi Ludwig,

    thank you for your comment. No I have not tried using those reports. How do I access them?

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Linking Project transactions to General Ledger transactions

    Hi,

    GL/Reports/Project and then /reconciliation or /transaction.

    If you are using a financial dimension for your projects you can alternatively also use the Trial Balance form in GL. In addition to the project financial dimension required you would need to setup a financial simension set that includes your main accounts and project financial dimensions under GL/Setup/financial dimensions/financial dimension sets.

    Hope this helps.

    Best regards,

    Ludwig

  • Community Member Profile Picture
    Community Member on at
    RE: Linking Project transactions to General Ledger transactions

    Thank you kindly once again for addressing my question Ludwig, but I am afraid that those out of box front-end reports won't be of much help with our reconciliation. Our GL data is setup in AX to be summarized and there are a large number of projects that need to be reconciled, a process that is cumbersome through the front-end reports.

    Ideally, as a BI/Reporting analyst, I would like to be able to use SQL to link projects to GL transactions and present it via a SSRS report...etc

    Would you know if it is possible to do this through SQL? I am interested in the tables and their associated links. Help as always is greatly appreciated!

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Linking Project transactions to General Ledger transactions

    Hi,

    The project module tables have a lot of relations to other AX module tables. What might be a good starting point though is the standard project cube that includes a number of views that you can access from the AOT. Probably take a look at the ProjTransPostingCube & ProjectTransPosting views available in the AOT to identify the tables and fields that you need for the reconciliation job.  

    Another alternative are the ProjectReconLedger classes. Probably take a look at those classes to identify how and from where they retrieve the data.

    Hope this helps at least to get started.

    All the best,

    Ludwig

  • David Massey Profile Picture
    David Massey on at
    RE: Linking Project transactions to General Ledger transactions

    There is no way to link all project transactions to the GL postings.  As you indicated postings from project to the GL can be summarized. The project transactions will indicate the voucher used to post the GL amounts.  A another issue which can affect trying to reconcile the amounts is that not all postings to a GL account are process through the project module. Depending on the business documents used, the source document framework will help in the scenario of summarized posting.  Unfortunately, not all of project business documents use the source document framework.

  • Community Member Profile Picture
    Community Member on at
    RE: Linking Project transactions to General Ledger transactions

    Hello M84AB1,

    I just came across yyour query from June 2016, I am having the exact same issue and cannot see any link between PROJTRANSPOSTING and GENERALJOURNALACCOUNTENTRY - I wondered if you manage to solve this ?

    Regards

    Bobby B

  • Community Member Profile Picture
    Community Member on at
    RE: Linking Project transactions to General Ledger transactions

    Hi Bobby,

    I have somewhat managed (although not 100% perfect) to link Project to GL. It works ok and I use it primarily to compare the overall (grand total) for each project for what is in the Project module versus what has landed in the GL. Have a try and let us know how you go. Hope it helps and of course you can bring in whatever columns you want from the below tables.

    SELECT
    T1.PROJID,
    T1.TRANSID,
    T1.AMOUNTMST,
    T3.MAINACCOUNTID
    FROM dbo.PROJTRANSPOSTING T1
    LEFT JOIN dbo.DIMENSIONATTRIBUTEVALUECOMBINATION T2
    ON T1.LEDGERDIMENSION = T2.RECID
    LEFT JOIN dbo.MAINACCOUNT T3
    ON T2.MAINACCOUNT = T3.RECID
    LEFT JOIN dbo.PROJREVENUETRANS T4
    ON T1.TransID = T4.TRANSID

  • Community Member Profile Picture
    Community Member on at
    RE: Linking Project transactions to General Ledger transactions

    Hello, 

    I currently have a similar issue with projects. 

    I'm trying to pull out revenue from a GL account and filter a value based on how much of the revenue was posted to the project. 

    Going to 'transaction origin' in AX in the GL you can see the costs which have been posted to 'module' project. This is a temporary table, however, and i can't find any way to access this to filter information. 

    Ideally i want to see a value of transactions in GL X which have been coded to project Y - surely this can't be as difficult as it seems. I can't pull this information from the projects module as this is for looking at deferred revenue. The revenue only hits the project as one sum, the deferrals affect the GL - not the project. 

  • David Massey Profile Picture
    David Massey on at
    RE: Linking Project transactions to General Ledger transactions

    Unless you are using Project as one of the financial dimensions, you cannot get this information from the general ledger.  Project transactions may be summarized when posting to the general ledger. ProjTransPosting contains the detailed posting for project.

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…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#2
Community Member Profile Picture

Community Member 2

#2
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans