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.

Reference

Deployment

Configuration keys

Tables and views

Measures

Calculated measures

Key performance indicators

Security

Resources

Analytics in Microsoft Dynamics AX

Cube and KPI reference for Microsoft Dynamics AX 2012 R2 and R3

Cube and KPI reference for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack

Deployment

The 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)

NoteNote

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