Custom-Reports

From Beds24 Wiki
Jump to navigation Jump to search
Custom Reports
This page is about the menu REPORTS > CUSTOM REPORTS

1 Summary

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

Custom reports can be exported to Excel or .csv and send automatically via Email using the "Routines" function and you can also add them to your Dashboard.

We provide also a wide range of preset standard reports which you can generate in REPORTS > STANDARD REPORTS

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".

2.1 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.

2.2 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.

2.3 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.

2.4 Examples

2.4.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)

2.4.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.

2.4.2 Specific Custom Reports

2.4.2.1 Specific Custom Reports using Sort By

2.4.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)

2.4.3 Graphs

2.4.3.1 Pace report current month

Report Title = Pace Report

Check In From = Today

Check In To = Next 365 days

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 = Bar Graph

Sort By = Booked Days in Advance

Drag and Drop the columns to "Show Columns":

Quantity

2.4.3.2 Room distribution current month


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

Drag and Drop the columns to "Show Columns": Quantity

2.4.4 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)


2.4.4.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


2.4.4.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


2.4.4.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


2.4.4.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

2.4.4.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'

2.4.5 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

2.4.6 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.


2.4.7 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.

2.4.8 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".

2.4.9 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".

2.4.10 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".

2.4.11 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".

2.5 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.

2.6 Available Columns

Available columns for custom reporting

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 =

Drag and Drop the columns to "Show Columns":

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