Overview
Pivot table reports in Aspire let you compare and analyze data in ways that standard reports and search lists don’t support. You can filter, group, and summarize information to get a custom view of your business data.
There are four pivot table reports available in the Reports module:
Hours Pivot Table — View hours each employee has logged.
Purchases Pivot Table — View how inventory and purchases are allocated to work tickets.
Work Tickets Pivot Table — View all work tickets with their budgeted and actual totals.
Sales Pivot Table — View a full summary of all sales opportunities.
This article walks you through building an Hours Pivot Table report for payroll reconciliation. You can apply the same steps to build any of the four pivot table reports. Keep reading or watch the video below to learn more!
Video
Things to know
Pivot table data refreshes overnight. Changes you make in Aspire appear in standard reports right away, but don’t appear in pivot tables until after the overnight refresh.
You only need to build a pivot table report once. After you save it, you can reuse it from the Favorites tab. Saved reports keep all your filters, so you do not have to rebuild them each pay period.
Use specific date ranges in your filter fields. Because pivot table data goes back five years, filtering by date prevents you from sorting through unrelated records.
Share saved reports with team members who handle the same reconciliation tasks. This keeps everyone working from the same data.
Requirements
Aspire only pulls approved and exported time into pivot reports. Approve all employee hours in Weekly Time Review before you begin.
To access Pivot Reports in the Reporting module, you need View Pivot Table Reports added to your user role. You need any drill down pivot report permission, depending on the report you need to work with. Read about the drill down permissions in our article section on Pivot Reports.
Steps
Open the Hours Pivot Table
Select the Reports module in the side menu.
Select the Standard Reports tab.
Scroll down to the Administration section.
Select Hours Pivot Table.
The pivot table screen opens with four header areas. Each area controls a different part of your report:
Filter Fields — Limits the data displayed in the report. Works like filters on other list screens in Aspire.
Data — Controls the value columns on the right side of the report. For example, regular hours, OT hours, and labor cost.
Row Fields — Categorizes information in rows on the left side of the report. For example, employee name and pay code.
Column Fields — Organizes how row and data field information is grouped across columns.
Set up filter fields
Select and hold or right-click in the Filter Fields header area. Select Show Field List. A pop-out window opens with all available fields. Drag the pop-out window to the side of the screen so it stays visible.
From the field list, drag the following fields into the Filter Fields header area:
Work Date Year
Work Date Month
Work Date
Status
Pay Schedule
Apply filter values
After you add the filter fields, select specific values for each one to display the data you need.
Select the Work Date Year dropdown. Clear the Show All checkbox, select the year for the pay period you are reconciling, and select OK.
Select the Work Date Month dropdown. Clear the Show All checkbox, select the month for the pay period, and select OK.
Select the Work Date dropdown. Clear the checkboxes for any dates that fall outside your pay period, and select OK.
Select the Status dropdown. Select Exported so the report only shows hours that were exported during Weekly Time Review, and select OK.
Select the Pay Schedule dropdown. Select the pay schedule you want to include (for example, Weekly 40), and select OK.
Below is an example of the dropdown menu in action where you can make selections based on the filters you have applied to your report:
Add data fields
From the field list, drag the following fields into the Data header area:
Regular Hours
OT Hours
OT Premium
Total Labor Cost No Burden
Add row fields
From the field list, drag the following fields into the Row Fields header area:
Employee, Last First
Service Name
Base Rate
Pay Code
Tip
If a default Employee field is already in the Row Fields area, select and hold or right-click it and select Hide This Field to remove it. The Employee, Last First field you added replaces it.
Sort report data (optional)
To change how data is sorted in the report, select and hold or right-click any column header in the Column Fields area and choose your preferred sort order.
Save the report
After you finish building the report, save it so you can reuse it later.
Select the Save icon in the upper-right corner of the screen.
Select Save As.
Enter a name for the report. Example: "Hours Pivot Report for Payroll Reconciliation"
(Optional) Choose to share the report with specific users or user roles within your company.
(Optional) Add a description. This description is visible to anyone with access to the report.
Select the Save icon in the lower-right corner of the window.
The saved report is added to the Favorites tab in the Reports module. Open it from there the next time you need to reconcile payroll.
Export the report to Excel or PDF
Select the Save icon in the upper-right corner of the screen.
Select Export to Excel.
The report downloads as an Excel spreadsheet. You can export any pivot table report this way to continue working with the data outside of Aspire.
Expected results
After you complete these steps, your Hours Pivot Table report displays a filtered view of employee hours for the selected pay period. You can see regular hours, overtime hours, overtime premiums, and labor costs organized by employee, service, base rate, and pay code.
The saved report appears in the Favorites tab in the Reports module and is ready to use for future pay periods. You will still need to update the fields for Work Date Month and Work Date to reflect the correct pay period hours.