Project accounting cube (ProjCube) for Microsoft Dynamics AX 2012 R2 and R3
Important
This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.
Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2
The Project accounting cube for Microsoft Dynamics AX is used to report on project profitability, revenue costs, committed costs, employee utilization, and cash flow for one or multiple projects in a company. This article provides details about the cube.
.png "Reference")
Deployment Configuration keys Tables and views Measures Calculated measures Key performance indicators Security |
.png "Resources")
Analytics in Microsoft Dynamics AX Cube and KPI reference for Microsoft Dynamics AX 2012 R2 and R3 Cube and KPI reference for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack |
Deployment
The Project accounting cube is included in the Dynamics AX project. For information about how deploy the Dynamics AX project—and the cubes that it contains—see Deploy the default cubes.
Configuration keys
The following configuration keys are required to use all features of the Project accounting cube:
Project I (ProjBasic)
Project II (ProjAdvanced)
Tables and views
The Project accounting cube uses data from the following tables and views:
CustGroup table
ForecastModel table
ProjCategory table
ProjFundingSource table
ProjGroup table
ProjInvoiceTable table
CostControlTransCommittedCostCube view
InventTableExpanded view
ProjActivityCompletedCostView view
ProjBIEmplTrans view
ProjBIForecastEmpl view
ProjProjectEstimatesView view
ProjProjectPlannedCostView view
ProjProjectPlannedEffortView view
ProjTransBudgetCube view
ProjTransPostingCube view
smmActivitiesCube view
Note
The ProjActivityCompletedCostView, ProjProjectEstimatesView, ProjProjectPlannedCostView, and ProjProjectPlannedEffortView views are used with this cube only in Microsoft Dynamics AX 2012 R3.
Measures
The Project accounting cube includes the following measure groups.
Actuals
This measure group is based on the ProjTransPostingCube view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
||
|---|---|---|---|---|---|---|
| Actuals count |
Not applicable |
Count |
The number of project actual transactions. |
Company Customer Released products Activity Project funding source Project category Project groups Project contracts Project Activity Completed Cost View Project Estimates View Project Planned Costs View Project Planned Effort view Projects Worker Actuals Date (ledger date) Date (project transaction date) Date (exchange rate date)
|
||
| Amount |
ProjTransPostingCube.AmountMST |
Sum |
The sum of the value, in the accounting currency, of project transactions. |
|||
| Actual accrued loss |
ProjTransPostingCube.ActualAccruedLoss |
Sum |
The sum of the value, in the accounting currency, of project accrued loss transactions. |
|||
| Actual accrued revenue |
ProjTransPostingCube.ActualAccruedRevenue |
Sum |
The sum of the value, in the accounting currency, of project accrued revenue transactions. |
|||
| Actual cash inflow |
ProjTransPostingCube.ActualCashInflow |
Sum |
The sum of the value, in the accounting currency, of project cash inflow transactions. |
|||
| Actual cash outflow |
ProjTransPostingCube.ActualCashOutflow |
Sum |
The sum of the value, in the accounting currency, of project cash outflow transactions. |
|||
| Actual consumption |
ProjTransPostingCube.ActualConsumption |
Sum |
The sum of the value, in the accounting currency, of project consumption transactions. |
|||
| Actual consumption cost expense |
ProjTransPostingCube.ActualConsumptionCostExpense |
Sum |
The sum of the value, in the accounting currency, of project expense cost consumption transactions. |
|||
| Actual consumption cost hours |
ProjTransPostingCube.ActualConsumptionCostHours |
Sum |
The sum of the value, in the accounting currency, of project hour cost consumption transactions. |
|||
| Actual consumption cost item |
ProjTransPostingCube.ActualConsumptionCostItem |
Sum |
The sum of the value, in the accounting currency, of project item cost consumption transactions. |
|||
| Actual cost |
ProjTransPostingCube.ActualCost |
Sum |
The sum of the value, in the accounting currency, of project actual cost transactions. |
|||
| Actual cost committed |
ProjTransPostingCube.ActualCostCommitted |
Sum |
The sum of the value, in the accounting currency, of project committed cost transactions. |
|||
| Actual cost expense |
ProjTransPostingCube.ActualCostExpense |
Sum |
The sum of the value, in the accounting currency, of project expense cost transactions. |
|||
| Actual cost hour |
ProjTransPostingCube.ActualCostHour |
Sum |
The sum of the value, in the accounting currency, of project hours cost transactions. |
|||
| Actual cost item |
ProjTransPostingCube.ActualCostItem |
Sum |
The sum of the value, in the accounting currency, of project item costs transactions. |
|||
| Actual gross margin |
ProjTransPostingCube.ActualGrossMargin |
Sum |
The sum of the value, in the accounting currency, of project revenue less project cost transactions. |
|||
| Actual gross WIP cost |
ProjTransPostingCube.ActualGrossWIPCost |
Sum |
The sum of the value, in the accounting currency, of project work in progress revenue and project work in progress cost transactions. |
|||
| Actual hours |
ProjTransPostingCube.ActualHours |
Sum |
The sum of the hours logged against projects. |
|||
| Actual invoiced on account |
ProjTransPostingCube.ActualInvoicedOnAccount |
Sum |
The sum of the value, in the accounting currency, of project invoiced on account revenue transactions. |
|||
| Actual invoiced revenue |
ProjTransPostingCube.ActualInvoicedRevenue |
Sum |
The sum of the value, in the accounting currency, of project invoiced revenue transactions. |
|||
| Actual net cash flow |
ProjTransPostingCube.ActualNetCashflow |
Sum |
The sum of the value, in the accounting currency, of project cash inflow less project cash outflow transactions. |
|||
| Actual net WIP |
ProjTransPostingCube.ActualNetWIP |
Sum |
The sum of the value, in the accounting currency, of gross project work in progress revenue, less project work in progress on-account transactions. |
|||
| Actual payroll allocation |
ProjTransPostingCube.ActualPayrollAllocation |
Sum |
The sum of the value, in the accounting currency, of project payroll allocation transactions. |
|||
| Actual revenue |
ProjTransPostingCube.ActualRevenue |
Sum |
The sum of the value, in the accounting currency, of project revenue transactions. |
|||
| Actual WIP cost |
ProjTransPostingCube.ActualWIPCost |
Sum |
The sum of the value, in the accounting currency, of project work in progress cost transactions. |
|||
| Actual WIP invoiced on account |
ProjTransPostingCube.ActualWIPInvoicedOnAccount |
Sum |
The sum of the value, in the accounting currency, of project invoiced work in progress on account transactions. |
|||
| Actual WIP sales |
ProjTransPostingCube.ActualWIPSales |
Sum |
The sum of the value, in the accounting currency, of project work in progress sales transactions. |
.gif "Note")
Note Committed costs
This measure group is based on the CostControlTransCommittedCostCube view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
||
|---|---|---|---|---|---|---|
| Committed costs count |
Not applicable |
Count |
The number of committed cost transactions. |
Currency Company Customer Activity Vendor Project category Project contracts Project Activity Completed Cost View Project Estimates View Project Planned Costs View Project Planned Effort view Projects Worker Committed costs Date (committed date) Date (exchange rate date)
|
||
| Committed cost |
CostControlTransCommittedCostCube.CommittedCost |
Sum |
The total value of committed cost transactions, in the accounting currency. |
|||
| Committed amount |
CostControlTransCommittedCostCube.AmountMST |
Sum |
The total value of committed cost transactions, in the accounting currency. |
|||
| Committed cost updates quantity |
CostControlTransCommittedCostCube.Qty |
Sum |
The sum of the quantity for committed item cost transactions. |
|||
| Committed hour cost |
CostControlTransCommittedCostCube.CommittedHourCost |
Sum |
The sum of committed hour costs, in the accounting currency. |
|||
| Committed item cost |
CostControlTransCommittedCostCube.CommittedItemCost |
Sum |
The sum of committed item costs, in the accounting currency. |
Actual hour utilization
This measure group is based on the ProjBIEmplTrans view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
||
|---|---|---|---|---|---|---|
| Actual hour utilization count |
Not applicable |
Count |
The number of actual hour transactions. |
Currency Company Customer Activity Project category Project contracts Project Activity Completed Cost View Project Estimates View Project Planned Costs View Project Planned Effort view Projects Worker Date (ledger date) Date (project transaction date)
|
||
| Actual efficiency burden hours |
ProjBIEmplTrans.ActualBurdenEfficiencyRate |
Sum |
The number of burden hours using the efficiency calculation method. |
|||
| Actual efficiency utilized hours |
ProjBIEmplTrans.ActualUtilizationEfficiencyRate |
Sum |
The number of utilized hours logged using the efficiency calculation method. |
|||
| Actual billable utilized hours |
ProjBIEmplTrans.ActualUtilizationBillableRate |
Sum |
The number of utilized hours logged using the billable calculation method. |
|||
| Actual billable burden hours |
ProjBIEmplTrans.ActualBurdenBillableRate |
Sum |
The number of burden hours logged using the billable calculation method. |
|||
| Actual efficiency overtime hours |
ProjBIEmplTrans.ActualOvertimeHoursEfficiencyRate |
Sum |
The number of overtime hours logged using the efficiency calculation method. |
|||
| Actual billable overtime hours |
ProjBIEmplTrans.ActualOvertimeHoursBillableRate |
Sum |
The number of overtime hours logged using the billable calculation method. |
|||
| Hours quantity |
ProjBIEmplTrans.Qty |
Sum |
The total number of hours logged. |
Forecast hour utilization
This measure group is based on the ProjBIForecastEmpl view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
||
|---|---|---|---|---|---|---|
| Forecast hour utilization count |
Not applicable |
Count |
The number of forecast hour transactions. |
Currency Company Customer Activity Forecast models Project category Project contracts Project Activity Completed Cost View Project Estimates View Project Planned Costs View Project Planned Effort view Projects Worker Date (ledger date) Date (project transaction date)
|
||
| Hour forecast quantity |
ProjBIForecastEmpl.Qty |
Sum |
The total forecast hours. |
|||
| Budget efficiency utilized hours |
ProjBIForecastEmpl.BudgetUtilizationEfficiencyRate |
Sum |
The number of utilized hours forecast using the efficiency calculation method. |
|||
| Budget efficiency burden hours |
ProjBIForecastEmpl.BudgetBurdenEfficiencyRate |
Sum |
The number of burden hours forecast using the efficiency calculation method. |
|||
| Hour forecast cost price |
ProjBIForecastEmpl.CostPrice |
Sum |
The cost price per hour for forecasted hours. |
|||
| Budget billable utilized hours |
ProjBIForecastEmpl.BudgetUtilizationBillableRate |
Sum |
The number of utilized hours forecast using the billable calculation method. |
|||
| Budget billable burden hours |
ProjBIForecastEmpl.BudgetBurdenBillableRate |
Sum |
The number of burden hours forecast using the billable calculation method. |
|||
| Budget billable overtime hours |
ProjBIForecastEmpl.BudgetOvertimeHoursBillableRate |
Sum |
The number of overtime hours forecast using the billable calculation method. |
|||
| Budget efficiency overtime hours |
ProjBIForecastEmpl.BudgetOvertimeHoursEfficiencyRate |
Sum |
The number of overtime hours forecast using the efficiency calculation method. |
Forecasts
This measure group is based on the ProjTransBudgetCube view and includes the following measures.
| Measures |
Measure field name |
Aggregation |
Description |
Associated dimensions |
||
|---|---|---|---|---|---|---|
| Forecasts count |
Not applicable |
Count |
The number of forecast transactions. |
Company Customer Released products Activity Forecast models Project funding source Project category Project groups Project contracts Project Activity Completed Cost View Project Estimates View Project Planned Costs View Project Planned Effort view Projects Worker Forecasts Date (ledger date) Date (project transaction date) Date (exchange rate date)
|
||
| Budget updates quantity |
ProjTransBudgetCube.Qty |
Sum |
The sum of quantities for forecast item transactions. |
|||
| Budget invoiced revenue |
ProjTransBudgetCube.BudgetInvoiceRevenue |
Sum |
The sum of the value, in the accounting currency, of forecast invoiced revenue transactions. |
|||
| Budget updates amount |
ProjTransBudgetCube.AmountMST |
Sum |
The sum of the value, in the accounting currency, of forecast transactions. |
|||
| Budget accrued revenue |
ProjTransBudgetCube.BudgetAccruedRevenue |
Sum |
The sum of the value, in the accounting currency, of forecast accrued revenue transactions. |
|||
| Budget revenue |
ProjTransBudgetCube.BudgetRevenue |
Sum |
The sum of the value, in the accounting currency, of forecast revenue transactions. |
|||
| Budget cost hour |
ProjTransBudgetCube.BudgetCostHour |
Sum |
The sum of the value, in the accounting currency, of forecast hour cost transactions. |
|||
| Budget cost expense |
ProjTransBudgetCube.BudgetCostExpense |
Sum |
The sum of the value, in the accounting currency, of forecast expense cost transactions. |
|||
| Budget cost item |
ProjTransBudgetCube.BudgetCostItem |
Sum |
The sum of the value, in the accounting currency, of forecast item cost transactions. |
|||
| Budget cost |
ProjTransBudgetCube.BudgetCost |
Sum |
The sum of the value, in the accounting currency, of forecast cost transactions. |
|||
| Budget WIP cost |
ProjTransBudgetCube.BudgetWIPCost |
Sum |
The sum of the value, in the accounting currency, of forecast work in progress cost transactions. |
|||
| Budget WIP sales |
ProjTransBudgetCube.BudgetWIPSales |
Sum |
The sum of the value, in the accounting currency, of forecast work in progress sales transactions. |
|||
| Budget hours |
ProjTransBudgetCube.BudgetHours |
Sum |
The sum of the value, in the accounting currency, of forecast hour transactions. |
|||
| Budget consumption cost hours |
ProjTransBudgetCube.BudgetConsumptionCostHours |
Sum |
The sum of the value, in the accounting currency, of forecast hour consumption cost transactions. |
|||
| Budget consumption cost expense |
ProjTransBudgetCube.BudgetConsumptionCostExpense |
Sum |
The sum of the value, in the accounting currency, of forecast expense consumption cost transactions. |
|||
| Budget consumption cost item |
ProjTransBudgetCube.BudgetConsumptionCostItem |
Sum |
The sum of the value, in the accounting currency, of forecast item consumption cost transactions. |
|||
| Budget consumption |
ProjTransBudgetCube.BudgetConsumption |
Sum |
The sum of the value, in the accounting currency, of forecast consumption cost transactions. |
|||
| Budget payroll allocation |
ProjTransBudgetCube.BudgetPayrollAllocation |
Sum |
The sum of the value, in the accounting currency, of forecast payroll allocation transactions. |
|||
| Budget invoiced on account |
ProjTransBudgetCube.BudgetInvoicedOnAccount |
Sum |
The sum of the value, in the accounting currency, of forecast on account transactions. |
|||
| Total budget cost |
ProjTransBudgetCube.TotalBudgetCost |
Sum |
The sum of the value, in the accounting currency, of forecast cost transactions. |
|||
| Remaining budget cost |
ProjTransBudgetCube.RemainingBudgetCost |
Sum |
The sum of the value, in the accounting currency, of remaining forecast cost transactions. |
|||
| Original budget cost |
ProjTransBudgetCube.OriginalBudgetCost |
Sum |
The sum of the value, in the accounting currency, of forecast original budget transactions. |
|||
| Budget gross WIP cost |
ProjTransBudgetCube.BudgetGrossWIPCost |
Sum |
The sum of the value, in the accounting currency, of forecast work in progress cost transactions. |
|||
| Budget gross margin |
ProjTransBudgetCube.BudgetGrossMargin |
Sum |
The sum of the value, in the accounting currency, of forecast revenue less forecast cost transactions. |
|||
| Budget net WIP |
ProjTransBudgetCube.BudgetNetWIP |
Sum |
The sum of the value, in the accounting currency, of forecast work in progress revenue less forecast work in progress cost transactions. |
|||
| Remaining budget deviation |
ProjTransBudgetCube.RemainingBudgetDeviation |
Sum |
The sum of the value, in the accounting currency, of actual to budget deviation. |
|||
| Budget cash inflow |
ProjTransBudgetCube.BudgetCashInflow |
Sum |
The sum of the value, in the accounting currency, of forecast cash inflows. |
|||
| Budget cash outflow |
ProjTransBudgetCube.BudgetCashOutflow |
Sum |
The sum of the value, in the accounting currency, of cash outflows. |
|||
| Budget net cash flow |
ProjTransBudgetCube.BudgetNetCashflow |
Sum |
The sum of the value, in the accounting currency, of forecast cash inflow less forecast cash outflow. |
|||
| Budget WIP invoiced on account |
ProjTransBudgetCube.BudgetWIPInvoicedOnAccount |
Sum |
The sum of the value, in the accounting currency, of forecast work in progress on account transactions. |
Exchange rates by day
This measure group is based on the BIExchangeRateView view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Exchange rate |
BIExchangeRateView.CrossRate |
Max |
The exchange rate. |
Currency Date (exchange rate date) Analysis currency |
Total estimated costs
This measure group is based on the ProjActivityCompletedCostView view and includes the following measures.
Note
This measure group is available only in Microsoft Dynamics AX 2012 R3.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Total estimated costs count |
Not applicable |
Count |
The number of estimated costs. |
Company Activity Project activity completed cost view |
Completed activity planned cost |
ProjActivityCompletedCostView.TotalCostPrice |
Sum |
The total planned costs for activities that are marked as complete. |
Project estimates view
This measure group is based on the ProjProjectEstimatesView view and includes the following measures.
Note
This measure group is available only in Microsoft Dynamics AX 2012 R3.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Project estimates view count |
Not applicable |
Count |
The number of project estimates. |
Company Project estimates view Projects |
Estimate at complete |
ProjProjectEstimatesView.CostAtComplete |
Sum |
The effort in hours that is estimated to be incurred at completion, taking into consideration any changes or delays. |
|
Effort at complete |
ProjProjectEstimatesView.EffortAtComplete |
Sum |
The total effort in hours that will be incurred at completion. |
Project planned costs view
This measure group is based on the ProjProjectPlannedCostView view and includes the following measures.
Note
This measure group is available only in Microsoft Dynamics AX 2012 R3.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Project planned costs view count |
Not applicable |
Count |
The number of project planned costs. |
Company Project planned costs view Projects Date (Scheduled end date) |
Planned cost |
ProjProjectPlannedCostView.SumOfTotalCostPrice |
Sum |
The initial planned cost for the various activities and tasks in the project. |
Project planned effort view
This measure group is based on the ProjProjectPlannedEffortView view and includes the following measures.
Note
This measure group is available only in Microsoft Dynamics AX 2012 R3.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Project planned effort view count |
Not applicable |
Count |
The number of project planned efforts. |
Company Project planned effort view Projects Date (Scheduled end date) |
Planned effort |
ProjProjectPlannedEffortView.Effort |
Sum |
The initial planned effort in hours for the various activities and tasks in the project. |
Calculated measures
The Project accounting cube contains the following calculated measures.
Calculated measure |
Aggregation |
Associated measure group |
Description |
|---|---|---|---|
Invoiced revenue deviation |
Sum |
Actuals |
The difference between forecast and actual invoiced revenue. |
Accrued revenue deviation |
Sum |
Actuals |
The difference between forecast and actual accrued revenue. |
Revenue deviation |
Sum |
Actuals |
The difference between forecast and actual revenue. |
Cost deviation |
Sum |
Actuals |
The difference between forecast and actual costs. |
Cost hour deviation |
Sum |
Actuals |
The difference between forecast and actual hours costs. |
Cost expense deviation |
Sum |
Actuals |
The difference between forecast and actual expense costs. |
Cost item deviation |
Sum |
Actuals |
The difference between forecast and actual item costs. |
Gross margin deviation |
Sum |
Actuals |
The difference between forecast and actual gross margin. |
WIP cost deviation |
Sum |
Actuals |
The difference between forecast and actual work in progress costs. |
WIP sales deviation |
Sum |
Actuals |
The difference between forecast and actual sales work in progress. |
Gross WIP cost deviation |
Sum |
Actuals |
The difference between forecast and actual gross work in progress costs. |
WIP invoiced on account deviation |
Sum |
Actuals |
The difference between forecast and actual on-account work in progress. |
Net WIP deviation |
Sum |
Actuals |
The difference between forecast and actual net work in progress. |
Hours deviation |
Sum |
Actuals |
The difference between forecast and actual hour quantities. |
Consumption deviation |
Sum |
Actuals |
The difference between forecast and actual consumption costs. |
Consumption cost hours deviation |
Sum |
Actuals |
The difference between forecast and actual hour consumption costs. |
Consumption cost expense deviation |
Sum |
Actuals |
The difference between forecast and actual expense consumption costs. |
Consumption cost item deviation |
Sum |
Actuals |
The difference between forecast and actual item consumption costs. |
Payroll allocation deviation |
Sum |
Actuals |
The difference between forecast and actual payroll allocation. |
Total cost total budget |
Sum |
Forecasts |
The difference between forecast and actual costs. |
Deviation total budget |
Sum |
Forecasts |
The difference between forecast and actual revenue. |
Remaining budget cost1 |
Sum |
Forecasts |
The remaining forecast costs. |
Cash inflow deviation |
Sum |
Actuals |
The difference between forecast and actual cash inflows. |
Cash outflow deviation |
Sum |
Actuals |
The difference between forecast and actual cash outflows. |
Net cash flow deviation |
Sum |
Actuals |
The difference between forecast and actual net cash flow. |
Actual efficiency hours ratio |
Sum |
Actual hour utilization |
The ratio between actual hours and burden hours using the efficiency calculation method. |
Actual billable hours ratio |
Sum |
Actual hour utilization |
The ratio between actual hours and burden hours using the billable calculation method. |
Budget efficiency hours ratio |
Sum |
Forecasts |
The ratio between forecast hours and burden hours using the efficiency calculation method. |
Budget billable hours ratio |
Sum |
Forecasts |
The ratio between forecast hours and burden hours using the billable calculation method. |
Key performance indicators
The following sections describe the key performance indicators (KPIs) in the Project accounting cube.
KPI calculations
The following table lists the KPIs that are associated with the Project accounting cube. You can use the information in the following table to help verify the information in your KPIs.
The KPIs in the Project accounting cube depend on ledger posting types. Ledger posting types specify the ledger accounts to which project transactions are posted.
KPI |
Associated measure group |
Calculation |
|---|---|---|
Revenue deviation |
Actuals |
The difference between actual and forecast revenue. |
Gross margin deviation |
Actuals |
The difference between actual and forecast gross margin. |
Net WIP deviation |
Actuals |
The difference between actual and forecast net work in progress. |
Cash inflow devation |
Actuals |
The difference between actual and forecast cash inflows. |
Cash outflow deviation |
Actuals |
The difference between actual and forecast cash outflows. |
Net cashflow deviation |
Actuals |
The difference between actual and forecast cash inflows less cash outflows. |
Role Centers
The following table lists the Role Centers and web parts that display the measures and KPIs associated with the Project accounting cube.
Role Center |
Web parts and KPIs |
|---|---|
Project manager |
Actual vs. forecast:
Business overview:
|
Security
The Project accounting cube can be accessed by users assigned to the following Microsoft SQL Server Analysis Services roles.
Chief executive officer
Chief financial officer
Project manager
Project supervisor