Related article: Enhanced Reporting

Table of content:

A - How to extract a Custom Fee

B - How to include a Custom Field from Guest Pre-arrival Form

C - How are multiple currencies handled

D - Extract all fees into columns
E - How to extra subtotals in revenue report
F - How different commission rates on length of stay are being handled.

G - Clone / Import custom reports

H - How to split long reservations into reports of shorter period

I - Generate report on guests' tags

K - How the amounts are rounded up or down


A - How to extract a Custom Fee

To extract a custom fee, you must know the name of the fee you want to extract. You can run a “Fee” report and note the values in the Fee Name column. You will use the Fee Name to indicate what fee you want to extract.

Let’s say the Custom Fee you want to extract is called “Paris Occupancy Tax”. To do this you would create a Formula with a name like: “PARIS_OCCUPANCY_TAX”. Drag the “Custom Fee” field from the Fees section of the Field Catalog and drop it into the formula. The formula should now look like this:

{CUSTOM_FEE}

Edit the formula, adding a parameter “Paris Occupancy Tax”. It should now look like this:

{CUSTOM_FEE("Paris Occupancy Tax")}

This formula can now be output as a column in your report or can be included in other formulas. Make sure to Save the report before leaving.

The added Custom Fee will then show up in your report:

Pro tip: The fee string can include wild cards.

For example, instead of: {CUSTOM_FEE("Paris Occupancy Tax")} we could specify {CUSTOM_FEE("* Tax")}which would match any fee that ended with the string ” Tax”.

B - How to include a Custom Field from Guest Pre-arrival Form

NOTE: the following has not yet been implemented in the UI

To include a custom field, drag the “Custom Field” from the Guest section of the field catalog and drop it into the report. Next, select the custom field by clicking it in the column header. In the “Field Specification”, Click the Edit button to edit the field. There will be a “Variable” drop down menu that shows all the custom variables defined by this agency. Select the desired custom field, update the column header, and Save.


C - How are multiple currencies handled

In some cases, a report will include multiple currencies, and when both currencies use the same symbol (i.e. “$” for USD and AUD) it can be hard to tell what currency applies. In such cases, include the “Currency Code” field from the field catalog as a column in the report.

Then the currency will be displayed for each line. The currency symbol will also be displayed in the “Totals” footer.

In the Totals footer, there will be one “total” row for each currency used in the report, and the total will be labeled with the currency symbol (if Currency Symbol is a column in the report).


D - Extract all fees into columns

Regarding extracting all fees as columns, using the Fee report and outputting to CSV, you can then import the CSV into a spreadsheet.




And then create a pivot table that will give you what you want: each row will be an order, and there will be a separate column for each custom fee with the sum of the charges of that fee type per order.



E - How to extra subtotals in revenue report

There is not currently a way to extract subtotals per property in Revenue Report, however, you can download the CSV file and create a pivot table in a spreadsheet program like excel (Select Data > Insert & Create Pivot).



F - How different commission rates on length of stay are being handled.

If all reservations under 29 nights would have a 30% commission rate & all reservations 30+ nights would have a rate of 20%. It could be done with a formula like:

(({LEAD_NIGHTS} <= 29) * 30%) + (({LEAD_NIGHTS} > 29) * 20%)

The reason this works is that ({LEAD_NIGHTS} <= 29) will be 0 if false and 1 if true, and similarly ({LEAD_NIGHTS} > 29) will be 0 if false and 1 if true.

Then, you’ll either end up with .30 + 0 or 0 + .20 depending on the number of nights.


Alternatively, a cleaner formula would be max( ({LEAD_NIGHTS} <= 29) * 30%, ({LEAD_NIGHTS} > 29) * 20% ) and that should work as well.

G - Clone / Import custom reports with Report Templates

H - How to split long reservations into reports of shorter period

The fields in the catalog with the asterisks (*) in the headers generally correspond to values that are prorated according to the reporting period. There’s a field in particular called NIGHTS_IN_PERIOD which represents the number of nights for a reservation that falls in the reporting period. It’s basically capturing occupancy, which is used in all of the calculations to determine prorated values

Let's take an example report with period of June 1st - June 30th:

  • For a booking of June 1st - July 15th, NIGHTS_IN_PERIOD = 30

  • For a booking of May 25th - June 10, NIGHTS_IN_PERIOD = 9

  • For a booking of June 15 - August 1st, NIGHTS_IN_PERIOD = 16

Then there are formulas that use NIGHTS_IN_PERIOD to calculate the revenue for a particular period. These can be found in the Revenue report.

I - Generate report on guests' tags

We have the ability to extract tags that are associated with guests. Let’s say you have a guest and you want to know if they are tagged as Agent. You would create a custom formula like this:


{HAS_GUEST_TAG(“Agent”)}

This will return 0 or 1 depending on whether the guest is tagged as Agent. By including that field as a “Total”, you can total up all the agents.

K - How the amounts are rounded up or down in the report

The numbers you see in the reports sometimes are rounded up/down depending on the binary representation. It’s a floating point number which is an approximation, and it will be either a little above or a little below. For example, the number 191.965 you see in the report actually could be 191.965000001 or 191.964999999. In the first case, it will be rounded up to 19.97 where in the second case, it will be rounded down to 191.96.

To let the report show round up or down to exactly the number you want, you can structure the calculations to bias the results in a particular direction. For example, using an agency commission of .15000001 in the management fee calculation will ensure that the dollar (pound) values always round up. Similarly, using 0.8499999 in the Owner Gross Earnings calculation will ensure that those values always round down

====================================

If you come up with some cool formulas, or custom report templates to share, or have any question, suggestion on this article, feel free to let us know via pop-up chat on the page's bottom, or via email to support@hostfully.com.

Did this answer your question?