Payroll cube (PayrollCube) 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 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) Note The Payroll positions and Payroll tax transaction history dimensions are associated with this measure only in Microsoft Dynamics AX 2012 R3.
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 carry-forward	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