I'm trying to recreate the Projects (PM301000) Balances tab in a Generic Inquiry, Pivot Table, or Dashboard. We have a customer who uses them extensively and would like a more centralized way of spot checking the project budget vs project commitments. The primary issue I am running into is the majority of tables related to the Projects module appear to be virtual tables and thus cannot be referenced. For example if I take [PMBudget.CuryActualAmount] - [PMBudget.CommittedInvoiceAmount] it does not actually math out properly. So I am trying to understand if there is a best practices approach to re-creating virtual tables or if the answer just becomes BI tools? Below I have searched each of the significant tabs in the Projects screen and their primary table. Any advice on angle of attack for this problem would be greatly appreciated.
My current theory is that I can re-tabulate the line items of each of the tabs to basically re-create the Balances Tab but I have yet to figure out how to do that. Initial attempt to linking PMProject to the relevant table and then grouping the relevant Amounts field by ContractCD or ContractID or ProjectID.
Projects Header
PMProject
Revenue Budget
PMRevenueBudget
CuryAmount
Cost Budget
PMCostBudget
CuryActualAmount
Balances
PMProjectBalanceRecord
CuryActualAmount
Commitments
POOrder
CuryOrderTotal
Invoices
ARInvoice
CuryOrigDocAmt