Custom-Reports

Custom Reports

This page is about the menu REPORTS > CUSTOM REPORTS

1 General

You can create your own custom reports based on the criteria you want to extract.

Check In From

Check In To

Check Out From

Check Out To

Booking Date From

Booking Date To

For the other settings please use the context help for each setting.

2 Set up

1. Create a custom report

• Enter a "Report Title" which is the name under which the report will be stored.
• Adjust the other criteria
• Drag the items you want to the left side and the one you do not want to the right then SAVE. If will now be always available.
• The setting for "Layout" defines how the bookings which are included in the report will be displayed.

2. Generate a preset custom report

• Use the selector to choose a report
• If required adjust the criteria e.g. dates
• If you want to use a custom report on the dashboard set it "Layout" = tabular.

Dates can be restricted by check-in, check-out and booking date. If multiple restrictions are applied they all apply to filter the shown bookings. The relative month restrictions like "this month", "last month" etc restrict from the first day of the month to the last day of the month respectively when used as FROM and TO restrictions.

If you use info codes in booking you can use these to show or hide selected bookings.

For example, if you use the code "CHECKEDIN" when guests check in and "CHECKEDOUT" when guests check out, you could create a custom reports to show custom reports by showing booking which include the info code "CHECKEDIN" and excluding those that have the info code "CHECKEDOUT".

The "Room Template" columns show the content added in SETTINGS-PROPERTIES-ROOMS-SETUP under "Room Template". These fields can contain template variables and also math variables which allows you to use these colums for custom calculations.

There can be problems exporting data to Excel or CSV in some browsers including Internet Explorer/Edge, it works well with Chrome and Firefox. Problems also occur with Chrome and MAC.

If you want to generate a report by invoice number you need to set "Status" = "Invoice number assigned".

3 Graphs and Charts

Under "Layout" you can choose to show a graph or chart. If you want to use this option what is set to "Sort by" defines what is shown horizontally.

• Each column that can have a "Total" can show as a graph.
• The monthly bar graph requires a date to be selected in "Sort by". This will be used to assign the bookings to the bars.
• For a pie chart you only need the column "Quantity" or a charges/payments/price column. A pie chart will be drawn by what is set to "Sort by" by a number of bookings or value.

4 Template variables in Custom Reports

The columns "Property Template" and "Room Template" will show the content you have entered in SETTINGS->PROPERTIES->DESCRIPTION / SETTINGS->PROPERTIES->ROOMS->SETUP. These fields support template variables. If you enter template variables there the report will show the corresponding value from the booking. You can also use math variables to calculate values from the booking.

5 Dashboard Reports

To show custom reports on the Dashboard, build the report in REPORTS->CUSTOM REPORTS, update the report name and "Layout" = Tabular.

Then go to Dashboard and in Components add the Custom report number to your Dashboard.

6 Examples

6.1 To-Do List showing all bookings with substatus "Action Required"

Go to REPORTS - > CUSTOM REPORTS and create a new custom report with these settings:

Report Title = = To-Do-List

Check-in / Check-out = choose the time span the report should reflect

Substatus = Action Required

Layout = Tabular

Drag and Drop the columns for the information you want to see from the “Available Columns” to the “Show Columns” For Example:

Substatus (required)

Booking Ref (optional)

6.1.1 Create a report in showing a column which is calculated from a value in the booking

1. In this example we will calculate 15% from the lodging price. Do do this go to (SETTINGS) > PROPERTIES >ROOMS > SETUP and add [INVOICEUPSELLNUM0][*]0.15 as "Room Template 1"

2. Create a custom report:

Drag the columns with the information you want to see from "Available Columns" to "Show Columns" make sure you choose the "Room Template 1" column.

6.2 Specific Custom Reports

6.2.2 Referrer report / Booking source

Go to REPORTS > CUSTOM REPORTS and create a new custom report with these settings:

Report Title = Referrer Report

Layout = Tabular

Include Totals = Sort By (or Sort By and Report if you want totals across the whole report)

Sort by = Referrer

Drag and Drop the columns for the information you want to see from the “Available Columns” to the “Show Columns” for example:

Referrer (required)

Quantity (required)

Booking Ref (optional)

Room (optional)

6.2.3 Room distribution current month

view large

Report Title = Room Distribution

Check In From = Any Date

Check In To = Any Date

Check Out From = Any Date

Check Out To = Any Date

Booking Date From = This month

Booking Date To = This month

Status = Not cancelled

Sub Status = All

Booking Source = All

Layout = Pie Chart

Sort By = Room

Go to REPORTS > CUSTOM REPORTS and create a new custom report with these settings:

Report Title =

Check In From =

Check In To =

Check Out From =

Check Out To =

Booking Date From =

Booking Date To =

Status =

Sub Status =

Booking Source =

Booking Source =

Booking Source =

Booking Source =

Invoice Balance =

Pending Payments =

Has upsell item =

Invoicee =

Any Booking Info Code =

All Booking Info Codes =

Exclude Booking Info Code =

Exclude Room Id's =

Booking Field Contains =

Booking Field Contains =

Booking Field Does Not Contain =

Booking Field Does Not Contain =

Template Variable =

Layout =

Sort By =

Secondary Sort =

Include Totals =

Show max =

Date Format =

6.3 Specific Custom Reports using Info Codes

You can create specific reports showing only bookings which meet certain criteria.

These bookings are identified using Booking Info Codes which are automatically set by Auto Actions.

To create Info Codes go to (SETTINGS) >ACCOUNT - PREFERENCES “Booking Info Code Presets” and create the info codes you need.

For example:

F&B-BB (for example for breakfast)

F&B-HB (for example for half board)

F&B-FB (for example for full board)

AirportTransfer (for example for airport transfer)

6.3.1 Daily breakfast report

1. Create your info codes as explained above.

2. Set up Auto Actions to automatically assign the Info Codes.

Go to (SETTINGS) > GUEST MANAGEMENT >AUTO ACTIONS and create an Auto Action or each of your meal options.

Trigger tab:

Trigger Event = After Booking

Trigger Time = Immediate

Booking Source = Booking.com (for example)

Booking Field Contains: meal_plan=Breakfast (or Extra Breakfast - use the text the channel uses)

Booking info tab

Booking Info Code : F&B-BB

Booking Info Text : [NUMPEOPLE1]

3. Create Custom Report, Go to REPORTS - > CUSTOM REPORTS and create a new custom report with these settings:

Report Title = Breakfast

Check In From = Any Date

Check In To = Today

Check Out From = Today

Check Out To = Any Date

Any Booking Info Code =F&B-BB

Layout = Tabular

Drag and Drop the columns for the information you want to see from the “Available Columns” to the “Show Columns”

Booking Ref

Property

Room

Unit

Full Name

First Night

Check Out

Booking Info Specified Columns

6.3.2 Daily Half Board report

1. Create your info codes as explained above.

2. Set up Auto Actions to automatically assign the Info Codes.

Go to (SETTINGS) > GUEST MANAGEMENT >AUTO ACTIONS and create an Auto Action or each of your meal options.

Trigger tab:

Trigger Event = After Booking

Trigger Time = Immediate

Booking Source = Booking.com (for example)

Booking Field Contains: meal_plan=Half Board (or use the rate code for half board - use the text the channel uses)

Booking info tab

Booking Info Code : F&B-HB

Booking Info Text : [NUMPEOPLE1]

3. Create Custom Report, Go to REPORTS - > CUSTOM REPORTS and create a new custom report with these settings:

Report Title = Half Board

Check In From = Any Date

Check In To = Today

Check Out From = Today

Check Out To = Any Date

Any Booking Info Code =F&B-HB

Layout = Tabular

Drag and Drop the columns for the information you want to see from the “Available Columns” to the “Show Columns”

Booking Ref

Property

Room

Unit

Full Name

First Night

Check Out

Booking Info Specified Columns

6.3.3 Daily Full Board report

1. Create your info codes as explained above.

2. Set up Auto Actions to automatically assign the Info Codes.

Go to SETTINGS -> GUEST MANAGEMENT ->AUTO ACTIONS and create an Auto Action or each of your meal options.

Trigger tab:

Trigger Event = After Booking

Trigger Time = Immediate

Booking Source = Booking.com (for example)

Booking Field Contains: meal_plan=Full Board (or use the rate code for Full board - use the text the channel uses)

Booking info tab

Booking Info Code : F&B-FB

Booking Info Text : [NUMPEOPLE1]

3. Create Custom Report, Go to REPORTS - > CUSTOM REPORTS and create a new custom report with these settings:

Report Title = Full Board

Check In From = Any Date

Check In To = Today

Check Out From = Today

Check Out To = Any Date

Any Booking Info Code =F&B-FB

Layout = Tabular

Drag and Drop the columns for the information you want to see from the “Available Columns” to the “Show Columns”

Booking Ref

Property

Room

Unit

Full Name

First Night

Check Out

Booking Info Specified Columns

6.3.4 Airport Transfer Report Daily basis

1. Create your info codes as explained above.

2. Set up Auto Actions to automatically assign the Info Codes.

Go to SETTINGS -> GUEST MANAGEMENT ->AUTO ACTIONS

Trigger tab:

Trigger Event = After Booking

Trigger Time = Immediate

Booking Source = Booking.com (for example)

Booking Field Contains: Guest Comment = airport shuttle service ( or any other valid trigger for the airport shuttle)

Booking info tab :

Booking Info Code : AirportTransfer

Booking Info Text : [NUMPEOPLE1]

3. Create Custom Report, (please note you might need to adjust the Check In From, Check In To, Check Out From, Check Out To" to see in advanced the airport transfer requests).

Go to REPORTS - > CUSTOM REPORTS and create a new custom report with these settings:

Report Title = Airport Transfer Report

Check In From = Any Date

Check In To = Today

Check Out From = Today

Check Out To = Any Date

Any Booking Info Code =AirportTransfer

Layout = Tabular

Drag and Drop the columns for the information you want to see from the “Available Columns” to the “Show Columns”

Booking Ref

Property

Room

Unit

Full Name

First Night

Check Out

Booking Info Specified Columns

6.3.5 List room charges and extras individually tallied or in groups

1.) Create meaningful identifiers for your charges groups. For example:

Room charges, upsell item cleaning = Room

upsell item breakfast = Breakfast

upsell items cats, dogs = Pets.

2.) Insert these identifiers into the status fields for invoice items or upsell items

The status fields are not directly accessible. You have to append the content to the description field with a pipe (|).

Invoice items:

Settings > Guest Management > Invoicing > Invoice Item Room Description: Your description|Your identifier

Upsell items:

Settings > Booking Engine > Upsell Items > Description: Your description|Your identifier

By adding the same identifier to multiple upsell items or invoice items you pool them into a group

3.) Create a custom report. Use the fields that you need and make sure to use the field 'Invoice Status Values'

• Layout = 'Tabular'

• Include Totals = 'Report'

This creates a column for each of your charges groups with the identifier as the column header and the total amounts at the bottom.

Bookings that were made before you created the identifiers won't be included in this report unless you manually add the identifier in the booking popup under 'Charges and Payments'

6.4 Report for tax advisor

1.) If you require sums for different payment methods, first set up status descriptions for your payment methods under (SETTINGS) > GUEST MANAGEMENT > INVOICES "Payment Status".

2.) Create a custom report:

• Status = Invoice Number Assigned

then click on SAVE

• Invoice date from / to = the period for which you want to create a report.
• Layout = Tabular 2
• Sort = invoice number

Drag the columns with the information you want to see from "Available Columns" to "Show Columns".

Invoice Number

Invoice Date

Invoice Item Description

Invoice Item Price

Invoice Item Vat

Invoice Item Vat %

Invoice Status Values (if you require sums for different payment methods)

Invoice Payments Value

Invoice VAT by Percantage

6.5 Report on pending payments

Create a custom report:

• Pending Payments = choose which types of bookings you want to include in the report
• Layout = Tabular
• Sort = check-in

Drag the columns with the information you want to see from "Available Columns" to "Show Columns". Make sure you choose the "Pending Payments" columns you want to display.

6.6 Commission report for owners

Go to (SETTINGS) > PROPERTIES >ROOMS > SETUP and use template variables in "Room Template" to calculate the commission.

Examples:

• If the commission you charge is 10% of the booking value including all extras use [INVOICECHARGES:10%]
• If the commission you charge is 10% of cost of the lodging use [INVOICEUPSELLNUM0][*]0.1

Create a custom report:

• Layout = Tabular
• Include Totals= Property

Drag the columns with the information you want to see from "Available Columns" to "Show Columns". Make sure you choose the "Room Template" columns you are using to calculate the commission.

6.7 Report on use of voucher codes

Create a custom report:

• Booking Field Contains
  • Choose "Rate Description"
  • Enter the name of the voucher code
• Layout = Tabular

Drag the columns with the information you want to see from "Available Columns" to "Show Columns".

6.8 Report of bookings cancelled in the last day (Since yesterday)

Create a custom report:

• Check In From = Yesterday
• Status = Cancelled

• Template Variable = [MODIFYDATE:{%e %B, %Y}] equal [CURRENTDATE:-1day{%e %B, %Y}]
• Layout = Sortable Table

Drag the columns with the information you want to see from "Available Columns" to "Show Columns".

6.9 Report of bookings cancelled between 2 dates

For this report we are looking for all bookings with the Cancelled date between 1st-31st October 2021. Ensure the date format entered into the Template variable is YYYY-MM-DD

Create a custom report:

• Check In From = Any Date
• Check In To = Any Date
• Check Out From = Any Date
• Check Out To = Any Date
• Booking Date From = Any Date
• Booking Date To = Any Date
• Status = Cancelled

• Template Variable = [IFBETWEEN:[CANCELLEDTIME:{%Y-%m-%d}]:2021-10-01:2021-10-31:yes||]
• select 'equal' from the drop down list
• enter yes in the next box

• Layout = Sortable Table

Drag the columns with the information you want to see from "Available Columns" to "Show Columns".

6.10 Report of bookings with Custom Question ticked/not ticked

Can be used for any of the Custom Questions set in Properties > Booking Questions > Custom Questions with Type = Tick Box.

Create a custom report:

• Check In From = select appropriate dates for your report
• Status = All not cancelled

• Template Variable = [GUESTCUSTOMQ1] equal ticked (or) not ticked
• Layout = Sortable Table

Drag the columns with the information you want to see from "Available Columns" to "Show Columns".

7 Send reports via EMail

If you want to send reports via EMail go to SETTINGS->ACCOUNT->ROUTINES. For instructions please use the context help (? icons) and click on HELP (top right) of this page.

8 Available Columns

Available columns for custom reporting

Availabile Columns	Description
Booking Ref	Booking reference number
Group Ref	Booking reference of the Master booking
Internal Notes	Internal Notes (Info tab)
Booking Info All Columns	All booking info codes in the booking
Booking Info Codes	Booking info codes
Booking Info Texts	Booking info text
Booking Info Specified Columns	If a booking info code has been specified in the report, then this will display the specified columns
Booking Info Specified Columns Code	If a booking info code has been specified in the report, then this will display the specified code in columns
Booking Info Specified Columns Total	If a booking info code has been specified in the report, then this will sum up and show the total value of all the values from the info items (expecting the text to be numbers)
Summary Tab	Description
Status	Booking status
Sub status	Booking Sub Status
Property	Property name
Room	Room name
Unit	Unit name
First Night	First night booked
Check Out	Check-out day
Last Night	Last night booked
Nights	Number of nights
Title	Guest title
First Name	Guest First name
Last Name	Guest last name
Full Name	Guest full name
Guest Comments	Guest Comments
Notes	Notes (Summary tab)
Arrival Time	Guest arrival time
Adults	Number of Adults
Children	Number of Children
Guests	Total number of guests
Telephone	Guest telephone number
Mobile	Guest mobile number
Fax	Guest fax number
Company	Guest Company details
Address	Guest Address
City	Guest City
State	Guest State
Postcode	Guest Postcode
Country (selector)	Guest Country from the selector
Country (text)	Guest Country text, manually entered
Detail Tab	Description
Message	Guest Message
Custom Question 1-10	Answer to Custom question 1-10
Reference	content of the booking reference field
Quantity	Number of rooms booked
Flag	Latest flag set in the booking
Status Code	Status Code can be used for your own purposes and for triggering auto actions.
Original Referrer	Original referrer
Channel Ref	Channel booking reference
Referrer	Referrer
Booking Date	booking date
Booking IP	Ip address of the computer used to make the booking
API Message	Channel booking information
Mail & Action Tab	Description
Email	Guest email address
Language	Guest language
Charges & Payments Tab	Description
Charge Item Description	Charge Item Description
Charge Item Qty	Charge Item Qty
Charge Item Price	Charge Item Price
Charge Item Net	Charge Item Net
Charge Item Total	Charge Item Total
Charge Item Status	Charge Item Status
Charge Item Vat %	Charge Item Vat %
Charge Item Vat	Charge Item Vat
Charge Item Date	Charge Item Date
Charge Item Invoicee Name	Charge Item Invoicee Name
Charge Item Invoicee Code	Charge Item Invoicee Code
Payment Item Description	Payment Item Description
Payment Item Amount	Payment Item Amount
Payment Item Status	Payment Item Status
Payment Item Date	Payment Item Date
Payment Item Invoicee Name	Payment Item Invoicee Name
Payment Item Invoicee Code	Payment Item Invoicee Code
Pending Payment Item Description	Pending Payment Item Description
Pending Payment Item Amount	Pending Payment Item Amount
Pending Payment Item Status	Pending Payment Item Status
Pending Payment Item Due	Pending Payment Item Due
Pending Payment Item Invoicee Name	Pending Payment Item Invoicee Name
Pending Payment Item Invoicee Code	Pending Payment Item Invoicee Code
Pending Payment Item Rule	Pending Payment Item Rule
Invoicee	Invoicee code assigned to booking
Offer	Offer number booked
Price	Indicative price information at time of booking, not recalculated
Tax	Indicative tax information at time of booking, not recalculated
Deposit	Indicative Deposit information at time of booking, not recalculated
Commission	Indicative Commission information at time of booking, not recalculated
Discount Voucher Code	discount voucher code entered by the guest at the time of booking
Rate Description	description of the rate that was quoted at time of booking
Invoice Tab	Description
Invoice Charges Value	Invoice Charges Value
Invoice Balance Value	Invoice Balance Value
Invoice Payments Value	Invoice Payments Value
Invoice Vat Value	Invoice Vat Value
Invoice Vat by Percentage	Invoice Vat by Percentage
Invoice Description Values	Invoice Description Values
Invoice Status Values	Invoice Status Values
Invoice Item Description	Invoice Item Description
Invoice Item Qty	Invoice Item Qty
Invoice Item Price	Invoice Item Price
Invoice Item Net	Invoice Item Net
Invoice Item Total	Invoice Item Total
Invoice Item Status	Invoice Item Status
Invoice Item Vat %	Invoice Item Vat %
Invoice Item Vat	Invoice Item Vat
Invoice Item Date	Invoice Item Date
Invoice Item Invoicee Name	Invoice Item Invoicee Name
Invoice Item Invoicee Code	Invoice Item Invoicee Code
Invoice Item Booking Ref	Invoice Item Booking Ref
Invoice Number	Invoice Number
Invoice Date	Invoice Date
First Invoice Item Charge Value	First Invoice Item Charge Value
Second Invoice Item Charge Value	Second Invoice Item Charge Value
Third Invoice Item Charge Value	Third Invoice Item Charge Value
Fourth Invoice Item Charge Value	Fourth Invoice Item Charge Value
Fifth Invoice Item Charge Value	Fifth Invoice Item Charge Value
Sixth Invoice Item Charge Value	Sixth Invoice Item Charge Value
Extra Invoice Item 1-20 Value	Extra Invoice Item 1-20 Value
Extra Invoice Item 1-20 Qty	Extra Invoice Item 1-20 Qty
Extra Invoice Item 1-20 VAT	Extra Invoice Item 1-20 VAT
Extra Invoice Manual Value	Extra Invoice Manual Value
Extra Invoice Manual Qty	Extra Invoice Manual Qty
Extra Invoice Manual VAT	Extra Invoice Manual VAT
Print Invoice	Icon to print Invoice
Assign Invoice Number	Icon to Assign Invoice number
Invoicee Code	Invoicee Code - Guest Management > Invoicee
Invoicee Name	Invoicee Name assigned to the booking
Invoicee Currency	Invoicee Currency - Guest Management > Invoicee
Invoicee Template 1-8	Invoicee Template 1-8 - Guest Management > Invoicee
Base Value	Lodging Price (Accommodation price)
Base Qty	Lodging quantity (Accommodation)
Upsell 1-20 Value	Upsell items Value added to the booking
Upsell 1-20 Qty	Upsell items Qty added to the booking
Upsell 1-20 VAT	Upsell items VAT added to the booking
Calculated Fields	Description
Price less Commission	Calculation - Price field - Commission field
Price less Tax	Calculation - Price field - Tax field
Price less Commission and Tax	Calculation - Commission field - Tax field
Cancel Date	Cancelled date
Modified Date	Modified date
Booked Days In Advance	Number of days between booking and check-in
Price per Day	Average price, booking price / number of nights
People Nights	Number of people x number of nights
Adult Nights	Number of adults x number of nights
Child Nights	Number of children x number of nights
Bookings in Group	Number of bookings in the group
General Fields	Description
Guests Ids	Guest id from Guest database
Guests Names	Guest Names (Group booking guest names)
Guests First Names	Guest First Names (Group booking guest names)
Guests Last Names	Guest Last Names (Group booking guest names)
Group Notes	Notes for group bookings
Account	Account id of the booked property
Random 1	a 1 digit random number seeded from the booking number
Random 2	a 2 digit random number seeded from the booking number
Random 3	a 3 digit random number seeded from the booking number
Random 4	a 4 digit random number seeded from the booking number
Random 5	a 5 digit random number seeded from the booking number
Random 6	a 6 digit random number seeded from the booking number
Random 7	a 7 digit random number seeded from the booking number
Random 8	a 8 digit random number seeded from the booking number
Bedding	Displays bedding info codes
Currency	Property Currency
Room Template 1-8	Properties > Setup > Rooms > Room Template 1-8
Property Template 1-8	Properties > Description > Property Template 1-8
Room Id	Room id assigned for the booking
Property Id	Property id of booking