Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / To create report on pr...
Finance | Project Operations, Human Resources, ...
Answered

To create report on project id but invoice amount is getting repeated on all project id .

(0) ShareShare
ReportReport
Posted on by Dineshkarlekar 1,457
I was trying to create the report where i get the invoice amount related to project id from project invoice proposals but as i know one contract id can have many project so i am checking the query which get proper invoice amount , actually the sql query i designed works properly when a contract id have single project but it repeats the same invoice amount if there are more than one project even if there is no transaction on project proposal journal .how can i get the correct value can someone help me on this. am  i missing any join in my query my code is below. 
 
 
  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,711 Super User 2024 Season 2 on at
    To create report on project id but invoice amount is getting repeated on all project id .
    Hi Dinesh,
     
    Here are the tables you would need to include in your query to get the details: ProjInvoiceCost, ProjInvoiceEmpl, ProjInvoiceItem, ProjInvoiceOnAcc, and ProjInvoiceRevenue. You can also use a view that will already get all detail in a union query per invoice: ProjInvoiceTransUnion. There might be more union views available. You can check in the AOT if there might be one having invoiced amounts per project instead of per invoice. 
     
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,457 on at
    To create report on project id but invoice amount is getting repeated on all project id .
    thanks for reply , 
    To get details per project, how can i calculate the invoiced amounts on individual project invoice detail lines(hour, item, fee, on-account). which table should i use can you sned me the sql query plz . so i can use it in my report .
  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,711 Super User 2024 Season 2 on at
    To create report on project id but invoice amount is getting repeated on all project id .
    Hi Dinesh,
     
    The project proposals and invoices don't have a reference to a project. Your current statement is calculating the total of invoices from the project contracts. To get details per project, you would need to calculate the invoiced amounts on individual project invoice detail lines(hour, item, fee, on-account). 
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,457 on at
    To create report on project id but invoice amount is getting repeated on all project id .
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,457 on at
    To create report on project id but invoice amount is getting repeated on all project id .
     Select PROJTABLE.PROJID,DTPROJECTVALUE,PROJPROPOSALJOUR.LINEPROPERTY,PROJINVOICETABLE.PROJINVOICEPROJID ,sum(PROJPROPOSALJOUR.INVOICEAMOUNT) as amount  from PROJTABLE
join PROJINVOICETABLE on PROJINVOICETABLE.PROJINVOICEPROJID = PROJTABLE.PROJINVOICEPROJID
join PROJPROPOSALJOUR  on PROJPROPOSALJOUR.PROJINVOICEPROJID = PROJTABLE.PROJINVOICEPROJID  
join PROJINVOICEJOUR on PROJPROPOSALJOUR.PROPOSALID = PROJINVOICEJOUR.PROPOSALID
AND PROJPROPOSALJOUR.LINEPROPERTY = '3'
group by PROJTABLE.PROJID,DTPROJECTVALUE,PROJPROPOSALJOUR.LINEPROPERTY,PROJINVOICETABLE.PROJINVOICEPROJID 
order by PROJINVOICETABLE.PROJINVOICEPROJID

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

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,711 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,458 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans