Project accounting cube (ProjCube) for Microsoft Dynamics AX 2012 R2 and R3 [AX 2012]

Updated: July 22, 2014

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.

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.

The following configuration keys are required to use all features of the Project accounting cube:

  • Project I (ProjBasic)

  • Project II (ProjAdvanced)

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

NoteNote

The ProjActivityCompletedCostView, ProjProjectEstimatesView, ProjProjectPlannedCostView, and ProjProjectPlannedEffortView views are used with this cube only in Microsoft Dynamics AX 2012 R3.

The Project accounting cube includes the following measure groups.

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)

NoteNote

The Project groups, Project Activity Completed Cost View, Project Estimates View, Project Planned Costs View, and Project Planned Effort view dimensions are associated with this measure only in Microsoft Dynamics AX 2012 R3.

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.

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)

NoteNote

The Project Activity Completed Cost View, Project Estimates View, Project Planned Costs View, and Project Planned Effort view dimensions are associated with this measure only in Microsoft Dynamics AX 2012 R3.

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.

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)

NoteNote

The Project Activity Completed Cost View, Project Estimates View, Project Planned Costs View, and Project Planned Effort view dimensions are associated with this measure only in Microsoft Dynamics AX 2012 R3.

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.

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)

NoteNote

The Project Activity Completed Cost View, Project Estimates View, Project Planned Costs View, and Project Planned Effort view dimensions are associated with this measure only in Microsoft Dynamics AX 2012 R3.

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.

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)

NoteNote

The Project groups, Project Activity Completed Cost View, Project Estimates View, Project Planned Costs View, and Project Planned Effort view dimensions are associated with this measure only in Microsoft Dynamics AX 2012 R3.

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.

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

This measure group is based on the ProjActivityCompletedCostView view and includes the following measures.

NoteNote

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.

This measure group is based on the ProjProjectEstimatesView view and includes the following measures.

NoteNote

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.

This measure group is based on the ProjProjectPlannedCostView view and includes the following measures.

NoteNote

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.

This measure group is based on the ProjProjectPlannedEffortView view and includes the following measures.

NoteNote

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.

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.

The following sections describe the key performance indicators (KPIs) in the Project accounting cube.

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.

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:

  • Actual vs. forecast hours

  • Actual vs. forecast revenue

  • Actual vs. forecast costs

Business overview:

  • Revenue deviation

  • Gross margin deviation

  • Net WIP deviation

  • Cash inflow deviation

  • Cash outflow deviation

  • Net cash flow deviation

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


Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).

Community Additions

ADD
Show: