Generating payroll reports

Article
11/29/2021

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

Microsoft Dynamics AX provides a set of standard payroll reports to assist you with payroll processing and government reporting. If you have installed Microsoft Dynamics AX 2012 R3 or cumulative update 7 or later for AX 2012 R2, a Payroll data cube is also available. Use the standard reports to create pay statements and W-2 forms to issue to your workers, to validate payroll taxes and benefit amounts, and to complete federal and state regulatory reports. Use the Payroll cube to create custom reports that answer a variety of questions that you might have about your payroll data.

This topic describes functionality that is available only if the Payroll - USA configuration key is selected.

Note

This topic includes information about features that were added or changed for cumulative update 7 or later for Microsoft Dynamics AX 2012 R2. This information also applies to AX 2012 R3.

Standard Payroll reports

The following table summarizes when and why you would use each report. For more information about how to use reports, see Microsoft Dynamics AX reports.

Report	Each pay period	Quarterly	Annually	As required
Pay statements	After you generate the payroll payment journal, use this report to print pay statements to issue to workers.			Use this report to reprint pay statements for workers.
Benefit register	Use this report to validate the benefit amounts that were calculated during payroll processing.			Use this report to validate the benefit amounts that were calculated during payroll processing.
Worker payment register	To comply with auditing best practices, use this payroll report each pay period to validate data and to sign off on pay runs.
Tax register	Use this report to validate the tax amounts that were calculated during payroll processing.	Use this report to validate the tax amounts that were calculated during payroll processing.	Use this report to validate the tax amounts that were calculated during payroll processing.	Use this report to validate the tax amounts that were calculated during payroll processing.
State quarterly wage and tax preparation		Use the information in this report when you prepare the quarterly wage and tax forms for state unemployment taxes. Note This report is available only if you have installed Microsoft Dynamics AX 2012 R3 or cumulative update 7 or later for AX 2012 R2.
Form 941 preparation		Use the information in this form when you prepare the quarterly report of payroll taxes for the IRS. Note This report is available only if you have installed Microsoft Dynamics AX 2012 R3 or cumulative update 7 or later for AX 2012 R2.
Form 940 preparation			Use the information in this report when you prepare the annual federal unemployment (FUTA) tax return. Note This report is available only if you have installed Microsoft Dynamics AX 2012 R3 or cumulative update 7 or later for AX 2012 R2.
Form W-2 reconciliation			Use this report to balance Form W-2s and to run validation before you issue Form W-2s to workers.	Use this report to balance Form W-2s and to run validation before you issue Form W-2s to workers.
Form W-2			Use this report to create Form W-2s to issue to workers.	Use this report to create Form W-2s to issue to workers.
Electronic Form W-2			Use this report to file Form W-2s with the Social Security Administration.

Custom Payroll reports in Microsoft Dynamics AX 2012 R3 or cumulative update 7 or later for AX 2012 R2

The following tables provide examples of common questions you might ask, and the measures and dimensions in the data cube for Payroll that you can use to answer those questions.

After the data cube has been deployed and processed at least one time, you can use Microsoft Excel, Microsoft SQL Server Analysis Services, or other tools to create custom reports based on the data in the cube. As you become more familiar with the data cube, you’ll be able to find answers to more and more of your questions.

If you have Power View configured, you can also access the data cube from the Workers list page. To do this, click the Analyze data button on the Payroll tab in the Action Pane. Power View provides quick and easy visualization of your data. When you use Power View, you can access the same measures and dimensions you would access by using Excel or SQL Server Analysis Services. After you select the dimensions and measures, you can quickly visualize raw data, adjust it to make multiple charts that correspond to each other, and save the charts to a library. You can access that library in a list format or place any chart from the library into any Role Center.

Tip

The data in the custom reports will be accurate as of the last time the cube was processed. To keep the data current, the cube should be processed frequently, for example, each night.

For more information about how to create custom reports from the data in a cube, see Create a report by using Power View to connect to a cube, Create a report by using SQL Server Report Builder to connect to a cube, or Create a report by using the Excel data connection wizard to connect to a cube.

Pay and earnings

Questions to answer	Measures and attributes to use
What was the gross amount of each pay statement in a particular pay period?	The Pay statement - sum of gross amount measure in the Pay statements measure group The Pay statement number dimension attribute under the Pay statements dimension A filter for the pay period using the Pay period end date dimension attribute under the Pay period dimension Tip To see the combined gross pay for all pay statements in all pay periods, delete the Pay statement number dimension attribute and clear the filter.
What was a worker’s gross pay and net pay for a particular pay statement?	The following measures in the Pay statements measure group: Pay statement - sum of gross amount Pay statement - sum of net amount The Pay statement number dimension attribute under the Pay statements dimension The Worker.Worker - Name dimension attribute under the Worker dimension –or– A filter for the worker’s name in the Worker.Worker - Name dimension attribute under the Worker dimension
What was the average gross pay per pay statement for each worker last year?	The Pay statement - average of gross amount measure in the Pay statements measure group The Worker.Worker - Name dimension attribute under the Worker dimension A filter for the last calendar year using the Accounting date.Year dimension attribute under the Accounting date dimension
Which workers receive physical checks instead of electronic payments?	The Number of workers paid by check measure in the Payroll workers measure group The Worker.Worker - Name dimension attribute under the Worker dimension (Optional) The Company dimension attribute under the Company dimension Tip A 1 for the worker means that the worker is paid by check. A 0 means that the worker is paid electronically. If you want to know how many workers are paid by check, and not which ones, omit the Worker.Worker - Name dimension attribute.
What is the amount of earnings per pay statement?	The Pay statement lines - sum of earning lines amounts measure in the Pay statements measure group The Pay statement number dimension attribute under the Pay statements dimension
How many earnings statements from a particular pay period included lines that were entered manually?	The Earning statements count measure in the Worker earnings statement totals measure group The Pay period end date dimension attribute under the Pay period dimension A filter for the Source dimension attribute under the Earnings statement dimension where the attribute value is {User entry}
What is the average amount of earnings statement lines for a particular worker?	The Earnings statement lines - average of line amounts measure in the Worker earnings statement totals measure group A filter for the Worker.Worker - Name dimension attribute under the Worker dimension where the attribute value is the worker’s name

Taxes and benefits

Questions to answer	Measures and attributes to use
How much did we pay in state unemployment insurance last year?	The Pay statement lines - sum of tax line amounts measure in the Pay statements measure group A filter for the Tax code type dimension attribute under the Tax code dimension where the attribute value is {State unemployment insurance} A filter for the Accounting date.Year dimension attribute under the Accounting date dimension where the attribute value is the last calendar year
How many employees have waived healthcare coverage?	The Number of benefit enrollments measure in the Payroll worker enrolled benefits measure group A filter for the Benefit type dimension attribute under the Benefit type dimension where the attribute value is {Medical} A filter for the Benefit option dimension attribute under the Benefit option dimension where the attribute value is {Waive}
Which benefits are most used by employees?	The Number of benefit enrollments measure in the Payroll worker enrolled benefits measure group The Payroll benefit plan dimension attribute under the Payroll benefit plan dimension
How many workers have tax levies and garnishments?	The Number of benefit enrollments measure in the Payroll worker enrolled benefits measure group A filter for the Benefit type dimension attribute under the Benefit type dimension where the attribute value is {Garnishment, Tax levy} Tip The total shows the number of enrollments in tax levies and garnishments, not the number of workers. To determine the number of workers, you can add the Worker.Worker - Name dimension attribute under the Worker dimension, which will allow you to find workers with multiple enrollments.
How much has the company contributed for healthcare benefits?	The Sum of contribution amount measure in the Payroll worker enrolled benefits measure group A filter for the Benefit type dimension attribute under the Benefit type dimension where the attribute value is {Medical} Tip To see the amount that has been contributed for a specific plan, delete the Benefit type filter and add a filter using the Payroll benefit plan dimension attribute under the Payroll benefit plan dimension.
How much has been deducted from worker pay for healthcare benefits?	The Sum of deduction amount measure in the Payroll worker enrolled benefits measure group A filter for the Benefit type dimension attribute under the Benefit type dimension where the attribute value is {Medical} (Optional) A filter for the Worker.Worker - Name dimension attribute under the Worker dimension where the attribute value is the worker’s name Tip To see the amount that has been deducted for a specific plan, delete the Benefit type filter and add a filter using the Payroll benefit plan dimension attribute under the Payroll benefit plan dimension.
What is the total cost of benefits by worker?	The following measures in the Payroll worker enrolled benefits measure group: Sum of contribution amount Sum of deduction amount Sum of default deduction amount Sum of default contribution amount The Worker.Worker - Name dimension attribute under the Worker dimension

PTO and other benefit accrual plans

Questions to answer	Measures and attributes to use
How much sick time has been used at one company compared to another company?	The Total used measure in the Benefit accruals measure group (Optional) The Company dimension attribute under the Company dimension A filter for the sick-time plan using the Benefit accrual plan dimension attribute under the Benefit accrual plan dimension
How many hours of PTO were carried over from last year to this year?	The Total carry-forward measure in the Benefit accruals measure group A filter for the PTO plan using the Benefit accrual plan dimension attribute under the Benefit accrual plan dimension
How many hours did a worker have accrued in the benefit accrual plan for sick time as of a particular pay statement?	The Pay statement accrual balances - sum of accrued hours measure in the Pay statements measure group The Pay statement number dimension attribute under the Pay statements dimension (Optional) A filter for a specific worker name using the Worker.Worker - Name dimension attribute under the Worker dimension

Tips

Some dimensions, such as those related to benefit deductions and contributions, do not contain date attributes. For these dimensions, you can’t answer questions about what a measure was during a particular time period, or how it changed over time.
When you work with the Positions dimension, you see attributes called ValidFrom and ValidTo. Use these dimension attributes when you analyze date-effective information about positions and workers.

To see which details are date effective, click the Maintain versions button in the Position form to open the date manager form. All these field values have an effective date and an expiration date, which are reflected in the cube as ValidFrom and ValidTo.
Under the Payroll employment dimension, you can use a filter with the Is position worker assignment active dimension attribute set to Yes to help you view date-effective data. This attribute filters out data that is out of date. For example, if you use the Number of employments measure in the Payroll workers together with the company, department, and worker dimensions, you might see a count of 11 instead of 7 because a worker might have worked in one of the positions in a specific department in the past. The past position adds to the count unless you set Is position worker assignment active to Yes.
A worker can have more than one position assignment and can have an employment in more than one company at the same time. As a result, the Number of employments measure and the Number of position assignments measure in the Payroll workers measure group give different results when they are used with the Company dimension. The Company dimension attribute represents the legal entity. When you use it with the Number of employments measure, it shows the number of past, present, and future employments in the legal entity. When you use it with the Number of position assignments measure, it shows the total number of past, present, and future position assignments that have been assigned to workers in a company.
Under the Worker dimension, there are two dimension attributes for the names of workers, Worker.Worker and Worker.Worker - Name. The Worker.Worker attribute shows duplicates, and the Worker.Worker - Name attribute shows distinct values. When you create a report by using measures and dimensions, it doesn’t matter which one that you use; both attributes produce the same results.
As you work with dimensions, sometimes you see an IsNotApplicable dimension attribute. This attribute is added to every dimension because one row always is retained for records that are not applicable. If a measure contains any values that are not in the dimension, the measure is designated as IsNotApplicable.

Share via