Payroll cube (PayrollCube) for Microsoft Dynamics AX 2012 R2 and R3
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.
Deployment Configuration keys Tables and views Measures Calculated measures Key performance indicators Security 
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 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.
Configuration keys
The following configuration keys are required to use all features of the Payroll cube:
 Payroll (Payroll)
Tables and views
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
Measures
The Payroll cube includes the following measure groups.
Worker arrears
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. 
Worker earnings statement totals
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. 
Payroll workers
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. 
Pay statements
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. 





Payroll tax transaction history
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. 
Payroll worker enrolled benefits
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. 
Benefit accruals
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). 
Calculated measures
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. 
Key performance indicators
The Payroll cube does not include any key performance indicators (KPIs).
Security
The Payroll cube can be accessed by users assigned to the following Microsoft SQL Server Analysis Services roles.
Human resource assistant
Payroll administrator
Payroll manager
Feedback
Loading feedback...