Pace Reports with Formula Fields

A pace report allows you to see where your organization is at compared to the same months from prior years. This article describes how to create a pace report for Blocked Room Nights on the Event Opportunities screen. However, you can use the same method to create pace reports for events or service orders.

Pace reporting involves formula fields (and summary fields if you have the Dashboard Reports or Executive Dashboards module). The key to leveraging the use of formula fields and/or summary fields is understanding where the information you want to report on resides in the software along with what other information you need to know.

When performing pace reporting, you need to know when "sales" occurred in order to compare them to sales current year to date. One of the simplest ways to do this is by categorizing sales into the month in which they occurred. For example, if an event opportunity became definite in July 2016, then the room nights should count for July. This is easy to know because the date the event opportunity went definite is tracked with the Definite Date field on the event opportunity. However, when performing a pace report, the monthly numbers need to be cumulative or YTD (include sales from not only July, but also anything that happened in that year from January to June) so you can see if you are pacing ahead or behind prior years and when you fell behind or went ahead of that pace.

For the example below, assume that there were 1000 Blocked Room Nights which turned Definite each month from January 2015 – September 2066, except in April 2016 when 2000 room nights turned Definite. A grid report of those room nights by month shows the following:

By contrast, a pace report includes January results in February, January and February results in March, and so on. The same data would be illustrated like this:

Notice in September 2016, you are ahead of the 2015 pace because of the 2000 nights in April; however, if you don’t close any business between now and the end of the year, you fall behind the 2015 pace in November.

Create the Pace Report

When creating the pace report, the key is having columns for each month that only count Room Nights where the event opportunity turned definite in that month or a prior month. To do this, you create a formula field for each month of the year in the Event Opportunities screen. The formula looks like this: 

=IF(MONTH([UB Field - Date Sale Occurred])<=[Number of Month],[UBField Containing Total Value],"")

To create a column on the Event Opportunities screen that includes Room Nights that turned Definite, use the following:

[UB Field - Date Sale Occurred] – Booked Date field since it is most widely used.

[Number of Month] – Number of the month fir the column. January = 1, Feb = 2, etc.

[UBField Containing Total Value] – Field containing the room nights total you want, such as Blocked Room Nights.　

Now that you know the formula, you can create formula fields for each month.

1. Click the Event Opportunities link from the Main Menu. The Event Opportunities page opens.
2. Right-click on the column headings.
3. Select Show Columns. The Column Settings window displays.
4. Click the Add Field drop-down.
5. Select Add Formula Field. The Add Formula Custom Field window displays.
6.  Enter "01-Jan" in the Label field.
7. In the Formula editor, enter the following formula:

   =IF(MONTH([UB Field - Date Sale Occurred])<=[Number of Month],[UBField Containing Total Value],"") 

   • Use the Insert Field () option to insert database fields into the formula.
   • Use the number of the month for the field you are creating – Jan = 1, Feb = 2, etc.
   • If using Booked Date and Blocked Room Nights, the formula field looks like this:
8. Select Numeric from the Data Type drop-down.
9. Select No Decimals from the Format drop-down.
10. Click OK. 

Repeat the above steps to create columns for each month of the year. If using the Copy Custom Field option, only change the field label and the number of the month for the field (02-Feb should use 2 instead of 1 for the number of the month in the formula, 03-Mar should use 3 for the number of the month, and so on).

After you create all 12 formula fields, the Column Settings screen looks similar to this:

Now select the Sort, Group and Total tab and select your desired grouping and totaling. See below for an example:

Click OK. The Event Opportunities screen displays the values within the formula field columns as determined by the formula you entered.

Once you confirm the filters are correct for the screen, make sure you save the view as a new view. You now have your pace report!