Table of Contents:
Why Use the Work Ticket Pivot Report?
The Work Ticket Pivot Report, available through the Reports screen, can be used to forecast revenue or hours for the following twelve months based on potential and actual renewals.
How Does It Work?
Aspire summarizes data for the pivot reports every night. When summarizing information for the Work Ticket Pivot Report, Aspire generates pseudo work ticket records for the next twelve months representing renewals for opportunities that you have not yet renewed. Understanding this behavior will help you create forecasts using this report.
When generating pseudo work tickets, Aspire considers the following:
Pseudo-work tickets are not generated for opportunities that have been canceled or lost.
Pseudo-work tickets are not generated if you have already won the renewal contract for the upcoming year.
The work ticket status for the pseudo work tickets is set to “Pending Renewal”.
The work ticket number assigned to these work tickets matches the work ticket number on the existing ticket for the current year.
Forecasting is only provided for the next twelve months.
During the first half of a given year, Aspire generates pseudo tickets for any appropriate contracts whose end date was in the prior year or in the current year.
After July 1, Aspire generates pseudo tickets for any appropriate contract whose end date is in the current year dropping off those opportunities in the prior year that you will likely not renew.
Navigating to the Work Tickets Pivot Report
To navigate to the Work Ticket Pivot Report, click on the Reports Module (1). Scroll towards the bottom of the page and click the Work Tickets Pivot Table (2).
Creating Your Forecasted Work Ticket Pivot Report
From the default view, right click Service Type and then click Hide this Field
On the Data Headers line, right click and then click Show Field List
Click and drag Work Ticket Status Name to the place of where Service Type was
We'll now remove Hours Actual and Hours Budget Variance. To do this, hover over Data Headers, right click Hours Actual, and then click Hide This Field. Repeat this step with Hours Budget Variance.
Once again, on the Data Headers line, right click and then click Show Field List
Click and drag both Scheduled Date Year and Scheduled Date Month above the table as shown below:
Click on the downward arrow on the Scheduled Date Year field. Uncheck any past years including current. Uncheck any future years you do now want to see. Click OK when done.
To view the Scheduled Date Months, click on the plus sign next to the Scheduled Date Year.
To view a break down of Work Ticket Status, including Pending Renewal, click the plus sign next to the division.
When everything looks correct based on what you want to see, click the Save icon in the top right and then Save As.
Type a name that represents your forecasting report and share it with the needed users and roles. Make sure to add a description as well. Finally, click save.
You should have a report that looks similar to the below:
The report is being generated in July, but the generated pseudo work ticket records fill in a reasonable forecast for later months as existing opportunities would otherwise be dropping off based on their end date.
This is shown by expanding the pivot table to display the Work Ticket Status Names to show the values represented by the Pending Renewal pseudo work tickets.