Payroll cube (PayrollCube) 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 Payroll cube for Microsoft Dynamics AX is used to analyze data about earnings statements, pay statements, workers, positions, arrears, tax history, benefits, benefit accruals, and reversals. Analysis of payroll data is required for organizations to effectively manage payrolls and make decisions based on accurate information. This article provides details about the cube.
Note 

This cube is available with cumulative update 7 for Microsoft Dynamics AX 2012 R2 and Microsoft Dynamics AX 2012 R3. 
The Payroll 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 Payroll cube uses data from the following tables and views:

HcmBenefitOption table

HcmBenefitType table

HcmPositionWorkerAssignment table

HcmWorker table

PayrollEarningStatement table

PayrollEarningStatementLine table

PayrollPayCycle table

PayrollPayPeriod table

PayrollPayStatement table

PayrollPayStatementAccrualBalance table

PayrollPayStatementLine table

PayrollTaxCode table

PRLDeductionArrear table

PrlUSTaxTransactionHistory table

HcmBenefitCube view

HcmBenefitPlanCube view

HcmDepartmentCube view

HcmJobCube view

HcmUnionAgreementCube view

PayrollAccrualTransactionsView view

PayrollArrearsCube view

PayrollBenefitAccrualCube view

PayrollEarningCodeCube view

PayrollEarningStatementCube view

PayrollEmploymentCube view

PayrollPayStatementCube view

PayrollPositionDetailCube view

PayrollTaxRegionCube view

PayrollTaxTransHistoryCube view

PayrollWorkerEnrolledBenefitsCube view
The Payroll cube includes the following measure groups.
This measure group is based on the PayrollArrearsCube view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Worker arrears count  Not applicable  Count  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  Company Worker arrears Worker arrears table Payroll pay statement Worker slicer Worker Payroll pay statement (payroll pay statement  pay statement number) Date (transaction date arrear created) Date (transaction date arrear recovered) Date (pay period arrear created  start date) Date (pay period arrear created  end date) Date (pay period arrear recovered  start date) Date (pay period arrear recovered  end date) Payroll benefit plan Benefit option 
Sum of arrears  recovery in transaction currency  PayrollArrearsCube.DeductionArrearAmountAC  Sum  The sum of the amount in the Transaction Currency field on the Arrearage Recovery and Adjustments grid of the Worker Arrears form.  
Sum of arrear  original amount  PayrollArrearsCube.DeductionArrearSumAC  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section. 
This measure group is based on the PayrollEarningStatementCube view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Worker earnings statement totals count  Not applicable  Count  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  Company Jobs Union agreement Earning code Earnings statement Payroll positions Tax region Payroll earning statement Earning statement detail Pay cycle Pay period Worker slicer Worker Date (earnings date) Date (pay period end date) Date (pay period start date) Benefit (worker compensation benefit) Benefit (worker general liability insurance benefit) 
Workers total earning amount  PayrollEarningStatementCube.AccountingCurrencyAmount  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Workers total earning quantity  PayrollEarningStatementCube.Quantity  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Sum of earning rate  PayrollEarningStatementCube.EarningRate  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Earning statements count  PayrollEarningStatementCube.EarningStatement  Distinct count  The number of earning statements created in the system. 
This measure group is based on the PayrollEmploymentCube view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Payroll workers count  Not applicable  Count  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  Company Position worker assignments Jobs Departments Union agreement Payroll employment Payroll positions Worker slicer Worker Date (employment start date) Date (employment end date) 
IsEmployeeActive  PayrollEmploymentCube.IsEmployeeActive  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section. 
This measure group is based on the PayrollPayStatementCube view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Pay statements Count  Not applicable  Count  The number of pay statements in the system.  Company Jobs Union agreement Benefit accrual plan Earning code Pay statements Payroll positions Tax region Pay cycle Pay period Payroll pay statement Payment benefit accrual balance Pay statement lines Tax code Worker slicer Worker Date (earnings date) Date (pay period end date) Date (pay period start date) Payroll pay statement (payroll pay statement number 1) Benefit (worker compensation benefit) Benefit (worker general liability insurance benefit) Date (accounting date) Date (payment date) Payroll benefit plan Benefit option 
Amount in transaction currency  PayrollPayStatementCube.AccountingCurrencyAmount  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Pay statement counts  PayrollPayStatementCube.PayStatement  Distinct count  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Gross pay  PayrollPayStatementCube.GrossAccountingCurrencyAmount  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Net pay  PayrollPayStatementCube.NetAccountingCurrencyAmount  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Pay statements amount in transaction currency  PayrollPayStatementCube.BaseTimeEarningBaseAmount  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Quantity  PayrollPayStatementCube.BaseTimeEarningBaseHours  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Pay statements amount in transaction currency 0  PayrollPayStatementCube.PremiumEarningBaseAmount  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Pay statements quantity  PayrollPayStatementCube.PremiumEarningBaseHours  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Rate  PayrollPayStatementCube.EarningRate  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Pay statements quantity 0  PayrollPayStatementCube.Quantity  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Benefit accrual plan  PayrollPayStatementCube.Accrual  Count  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Amount accrued  PayrollPayStatementCube.AccruedHours  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Plan year accrued  PayrollPayStatementCube.TotalAccruedHours  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Available  PayrollPayStatementCube.TotalAvailableHours  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Plan year used  PayrollPayStatementCube.TotalUsedHours  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Amount used  PayrollPayStatementCube.UsedHours  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
This measure group is based on the PayrollTaxTransHistoryCube view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions  

Payroll tax transaction history Count  Not applicable  Count  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  Company Jobs Union agreement Tax transaction date Payroll positions Payroll tax transaction history Prl US tax transaction history Tax code Worker Date (tax transaction date)
 
Calculated tax amount  PayrollTaxTransHistoryCube.CalculatedTaxAmount  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Excess wages  PayrollTaxTransHistoryCube.ExcessWages  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Gross subject wages  PayrollTaxTransHistoryCube.GrossSubjectWages  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Gross wages  PayrollTaxTransHistoryCube.GrossWages  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Hours  PayrollTaxTransHistoryCube.Hours  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Tax rate  PayrollTaxTransHistoryCube.Rate  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Subject wages  PayrollTaxTransHistoryCube.SubjectWages  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Tax line amount  PayrollTaxTransHistoryCube.TaxLineAmount  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section. 
This measure group is based on the PayrollWorkerEnrolledBenefitsCube view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Payroll worker enrolled benefits Count  Not applicable  Count  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  Company Benefit type Jobs Union agreement Payroll positions Payroll worker enrolled benefits Worker slicer Worker Payroll benefit plan Benefit option 
Contribution  PayrollWorkerEnrolledBenefitsCube.ContributionAmountCur  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Deduction  PayrollWorkerEnrolledBenefitsCube.DeductionAmountCur  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Amount  PayrollWorkerEnrolledBenefitsCube.DefaultContributionAccountingCurrencyAmt  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Payroll worker enrolled benefits amount  PayrollWorkerEnrolledBenefitsCube.DefaultDeductionAccountingCurrencyAmt  Sum  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  
Number of benefit enrollments  PayrollWorkerEnrolledBenefitsCube.HCMWorkerEnrolledBenefitRecID  Distinct count  Count or number of benefit enrollments. 
This measure group is based on the PayrollAccrualTransactionsView view and includes the following measures.
Measure  Measure field name  Aggregation  Description  Associated dimensions 

Benefit accruals Count  Not applicable  Count  This measure is used in the calculated measure and is hidden from end users. See the Calculated measures section.  Company Benefit accrual plan Earning code Payroll accrual transaction view Worker 
Total accrued  PayrollAccrualTransactionsView.Accrued  Sum  The sum of benefit accrual plans that have been associated with workers.  
Total carryforward  PayrollAccrualTransactionsView.CarryOver  Sum  The balance that was carried forward from the previous plan year, including any manual adjustments.  
Total used  PayrollAccrualTransactionsView.Used  Sum  The total amount that has been used during the current plan year, including any manual adjustments.  
Total pending usage  PayrollAccrualTransactionsView.PendingUsage  Sum  The amount that has been released in earnings statement lines but that has not been submitted for payment.  
Total available hours  PayrollAccrualTransactionsView.TotalAvailableHours  Sum  The number of balance accruals that is available for workers to use. Available = Carry forward + Plan year accrued  Plan year used  Pending usage (Hours entered in earning statement line that are released). 
The Payroll cube contains the following calculated measures.
Calculated measure  Aggregation  Associated measure group  Description 

Pay statement  average of net amount  Average  Pay statements  The average of net amounts on the pay statements, so it is the sum of net pay statements divided by the total number of pay statements. 
Pay statement  average of gross amount  Average  Pay statements  The average of gross amounts on the pay statements, so it is the sum of gross pay statements divided by the total number of pay statements. 
Pay statement  sum of gross amount  Sum  Pay statements  The sum of gross amounts on the pay statements, so it is the sum of gross pay statements divided by the total number of pay statements. 
Pay statement  sum of net amount  Sum  Pay statements  The sum of net amounts on the pay statements, so it is the sum of net pay statements divided by the total number of pay statements. 
Pay statement benefit lines  average of base earning amounts  Average  Pay statements  The average of the amount from the base time portion of the applicable earnings. 
Pay statement benefit lines  average of base earning hours  Average  Pay statements  The average of the hours from the base time portion of the applicable earnings. 
Pay statement benefit lines  average of premium earning amounts  Average  Pay statements  The average of the amount from the premium time portion of the applicable earnings. 
Pay statement benefit lines  average of premium earning hours  Average  Pay statements  The average of the hours from the premium time portion of the applicable earnings. 
Pay statement earning lines  average of earning rates  Average  Pay statements  The average of the rate specified on the earning lines of the pay statement on the Earnings tab. 
Pay statement earning lines  average of quantities  Average  Pay statements  The average of the quantity specified on the earning lines of the pay statement on the Earnings tab. 
Pay statement accrual balances  average of accrued hours  Average  Pay statements  The average of the amount accrued on a pay statement’s Benefit Accrual Calculations tab. 
Pay statement accrual balances  average of plan year accrued hours  Average  Pay statements  The average of the plan year accrued on a pay statement’s Benefit Accrual Calculations tab. 
Pay statement accrual balances  average of available hours  Average  Pay statements  The average of the available hours on a pay statement’s Benefit Accrual Calculations tab. 
Pay statement accrual balances  average of plan year used hours  Average  Pay statements  The average of the plan year used on a pay statement’s Benefit Accrual Calculations tab. 
Pay statement accrual balances  average of number of hours used  Average  Pay statements  The average of the amount used on a pay statement’s Benefit Accrual Calculations tab. 
Pay statement benefit lines  sum of base earning amounts  Sum  Pay statements  The sum of the amount from the base time portion of the applicable earnings. 
Pay statement benefit lines  sum of base earning hours  Sum  Pay statements  The sum of the hours from the base time portion of the applicable earning. 
Pay statement benefit lines  sum of premium earning amounts  Sum  Pay statements  The sum of the amount from the premium time portion of the applicable earnings. 
Pay statement benefit lines  sum of premium earning hours  Sum  Pay statements  The sum of the hours from the premium time portion of the applicable earnings. 
Pay statement earning lines  sum of quantities  Sum  Pay statements  The sum of the quantity specified on the earning lines of the pay statement on the Earnings tab. 
Pay statement accrual balances  sum of accrued hours  Sum  Pay statements  The sum of the amount accrued on a pay statement’s Benefit Accrual Calculations tab. 
Pay statement accrual balances  sum of plan year accrued hours  Sum  Pay statements  The sum of the plan year accrued on a pay statement’s Benefit Accrual Calculations tab. 
Pay statement accrual balances  sum of available hours  Sum  Pay statements  The sum of the available hours on a pay statement’s Benefit Accrual Calculations tab. 
Pay statement accrual balances  sum of plan year used hours  Sum  Pay statements  The sum of the plan year used on a pay statement’s Benefit Accrual Calculations tab. 
Pay statement accrual balances  sum of number of hours used  Sum  Pay statements  This calculated measure is hidden to end users. It is used in other calculations. 
Pay statement lines count  Count  Pay statements  This calculated measure is hidden to end users. It is used in other calculations. 
Pay statement earning lines count  Count  Pay statements  This calculated measure is hidden to end users. It is used in other calculations. 
Pay statement benefit lines count  Count  Pay statements  This calculated measure is hidden to end users. It is used in other calculations. 
Earnings statement lines  average of earning rates  Average  Worker earnings statement totals  The average of the Rate field for the earning statement line. 
Earnings statement lines  average of earning quantities  Average  Worker earnings statement totals  The average of the Quantity field for the earning statement line. 
Earnings statement lines  average of line amounts  Average  Worker earnings statement totals  The average of the Amount field for the earning statement line. 
Earnings statement lines  sum of line amounts  Sum  Worker earnings statement totals  The sum of the Amount field for the earning statement line. 
Earnings statement lines  sum of quantities  Sum  Worker earnings statement totals  The sum of the Quantity field for the earning statement line. 
Pay statement tax lines count  Count  Pay statements  This calculated measure is hidden to end users. It is used in other calculations. 
Pay statement lines  sum of earning lines amounts  Sum  Pay statements  The sum of the amount specified on the earning line of the pay statement’s Earnings tab. 
Pay statement lines  sum of benefit lines amounts  Sum  Pay statements  The sum of the Deduction Amount + Contribution Amount on the Benefit Calculations tab of a pay statement. It looks best if sliced with the Payroll Benefit Plan dimension. 
Pay statement lines  sum of tax lines amounts  Sum  Pay statements  The sum of the Deduction Amount + Contribution Amount specified on the Tax Calculations tab of a pay statement. 
Pay statement lines  average of earning lines amounts  Average  Pay statements  The average of the amount specified on the earning line of the pay statement’s Earnings tab. 
Pay statement lines  average of benefit lines amounts  Average  Pay statements  The average of the Deduction Amount + Contribution Amount on the Benefit Calculations tab of a pay statement. It looks best if sliced with the Payroll Benefit Plan dimension. 
Pay statement lines  average of tax lines amounts  Average  Pay statements  The average of the Deduction Amount + Contribution Amount on the Tax Calculations tab of a pay statement. It looks best if sliced with the Tax Code dimension. 
Pay statement  sum of benefit accrual lines count  Count  Pay statements  This calculated measure is hidden to end users. It is used in other calculations. 
Sum of calculated tax amount  Sum  Payroll tax transaction history  The sum of the amount of tax calculated for the payment. 
Sum of excess wages  Sum  Payroll tax transaction history  The sum of the portion of the current wages that exceed the unemployment wage base. 
Sum of gross subject wages  Sum  Payroll tax transaction history  The sum of the amount of gross wages subject to the tax. 
Sum of gross wages  Sum  Payroll tax transaction history  The sum of the amount of gross wages used to calculate the tax. 
Sum of hours  Sum  Payroll tax transaction history  The sum of the hours from all earning codes used to calculate the gross wages. 
Sum of subject wages  Sum  Payroll tax transaction history  The sum of the amount of wages subject to the tax. 
Average of subject wages  Average  Payroll tax transaction history  The average of the amount of wages subject to the tax. 
Average of calculated tax amount  Average  Payroll tax transaction history  The average of the amount of tax calculated for the payment. 
Average of excess wages  Average  Payroll tax transaction history  The average of the portion of the current wages that exceed the unemployment wage base. 
Average of gross subject wages  Average  Payroll tax transaction history  The average of the amount of gross wages subject to the tax. 
Average of gross wages  Average  Payroll tax transaction history  The average of the amount of gross wages used to calculate the tax. 
Average of hours  Average  Payroll tax transaction history  The average of the hours from all earning codes used to calculate the gross wages. 
Prl tax transactions count  Count  Payroll tax transaction history  This calculated measure is hidden to end users. It is used in other calculations. 
Average of tax rate  Average  Payroll tax transaction history  The average of the tax rate. 
Sum of actual combined tax  Sum  Payroll tax transaction history  The sum of the actual tax amount for the combined tax transaction. 
Average of actual combined tax  Average  Payroll tax transaction history  The average of actual tax amount for the combined tax transaction. 
Sum of arrears  original amount  Sum  Worker arrears  The sum of the original amount on the Worker Arrears form. 
Sum of arrears  amount remaining  Sum  Worker arrears  The sum of the amount remaining on the Worker Arrears form. 
Number of workers  Count  Payroll workers  The number or count of workers. 
Workers terminated count  Count  Payroll workers  The number of employees that are being terminated or employment end date is less than today. 
Number of position assignments  Count  Payroll workers  The number of positions assigned to a worker. 
Sum of contribution amount  Sum  Payroll worker enrolled benefits  The sum of the amount of the employer contribution for the selected plan. 
Sum of deduction amount  Sum  Payroll worker enrolled benefits  The sum of the amount of the worker deduction for the selected plan. 
Sum of default contribution amount  Sum  Payroll worker enrolled benefits  The sum of the default amount contributed each pay period until the contribution limit is reached. 
Sum of default deduction amount  Sum  Payroll worker enrolled benefits  The sum of the default amount deducted each pay period until the deduction limit is reached. 
Number of employments  Count  Payroll workers  The number of employees. 
Pay statement count  Count  Pay statements  The number of pay statements in the system. 
Earnings statement lines count  Count  Worker earnings statement totals  The number of lines on the earning statement. 
Number of workers paid by electronically  Count  Payroll workers  The number or count of employees paid electronically. 
Number of workers paid by check  Count  Payroll workers  The number or count of employees paid by check. 
Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).