Custom-Reports: Difference between revisions

Jump to navigation Jump to search
← Older edit
Lesley (talk | contribs)
3,945 edits
Lesley (talk | contribs)
3,945 edits
 
(41 intermediate revisions by 4 users not shown)
Line 1: Line 1:
 
[[Category:Use_Cases]]
[[Category:Daily_Use]]
[[Category:Daily_Use]]
[[Category:Reports]]
[[Category:Reports]]
Line 5: Line 5:
  This page is about the menu REPORTS > CUSTOM REPORTS
  This page is about the menu REPORTS > CUSTOM REPORTS
__TOC__
__TOC__
== General set up ==
=Summary=
<div class="summarybox">
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
</div>
 
= Set up =
1. Create a custom report
1. Create a custom report
*Enter a "Report Title" which is the name under which the report will be stored.
*Enter a "Report Title" which is the name under which the report will be stored.
*Adjust the other criteria
*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.
*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
2. Generate a preset custom report
Line 15: Line 25:
*If required adjust the criteria e.g. dates
*If required adjust the criteria e.g. dates
*If you want to use a custom report on the dashboard set it "Layout" = tabular.
*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.
If you use info codes in booking you can use these  to show or hide selected bookings.
Line 32: Line 45:
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.  
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 colum which can have a "Total" can show as graph.
*Each column that can have a "Total" can show as a graph.
*The monthly bar graph requires dated to be selected in "Sort by".
*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 colunm "Quantity" or a charges/payments/price column.  A pie chart will be dawn by what is set to "Sort by" by number of bookings or value.
*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.


== Template variables in Custom Reports ==
== Template variables in Custom Reports ==
Line 46: Line 59:


== Examples==
== Examples==
===To-Do Liste showing all bookings with substatus "Action Required"===
===To-Do List showing all bookings with substatus "Action Required"===
Go to REPORTS - > CUSTOM REPORTS  and create a new custom report with these settings:
Go to REPORTS - > CUSTOM REPORTS  and create a new custom report with these settings:


Line 57: Line 70:
Layout = Tabular
Layout = Tabular


Ziehen Sie die Spalten mit den Informationen, die Sie sehen möchten, von "Verfügbare Spalten" zu "Spalten anzeigen" z.B.:
Drag and Drop the columns for the information you want to see from the “Available Columns” to the “Show Columns” For Example:


Substatus (required)
Substatus (required)
Line 71: Line 84:


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.
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.
=== Specific Custom Reports ===
=== Specific Custom Reports ===
==== Specific Custom Reports using Sort By====
==== Specific Custom Reports using Sort By====


==== Referrer report / Booking source ====
==== Referrer report / Booking source ====
Go to REPORTS - > CUSTOM REPORTS  and create a new custom report with these settings:
Go to REPORTS > CUSTOM REPORTS  and create a new custom report with these settings:


Report Title = Referrer Report
Report Title = Referrer Report
Line 96: Line 110:
Room (optional)
Room (optional)


Unit (optional)
=== Graphs ===
 
==== Pace report current month ====
<div class="col-6">
<div style="padding: 0 10px 0 10px;">
 
[[Image:Pace.png|400px|link=]]
[[Media:Pace.png|view large]]
 
</div>
</div>
 
<div class="col-6">
<div style="padding: 0 10px 0 10px;">
 
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


Full Name (optional)
Sub Status = All


First Night (optional)
Booking Source = All


Check Out (optional)
Layout = Bar Graph
 
Sort By = Booked Days in Advance
 
Drag and Drop the columns to "Show Columns":
 
Quantity
 
</div>
</div>
<div style="clear: both;"></div>
 
==== Room distribution current month ====
<div class="col-6">
<div style="padding: 0 10px 0 10px;">
 
[[Image:Roomdistribution.png|400px|link=]]
[[Media:Roomdistribution.png|view large]]
 
</div>
</div>
 
 
<div class="col-6">
<div style="padding: 0 10px 0 10px;">
 
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
 
</div>
</div>
<div style="clear: both;"></div>


=== Specific Custom Reports using Info Codes ===
=== Specific Custom Reports using Info Codes ===
Line 110: Line 208:




To create Info Codes go to {{#fas:cog}} (SETTINGS) >ACCOUNT - PREFERENCES  “Booking Info Code Presets” and  create the info codes you need.  
To create Info Codes go to {{#fas:cog}} (SETTINGS) >ACCOUNT - PREFERENCES  “Booking Info Code Values” and  create the info codes you need.  


For example:
For example:
Line 122: Line 220:
AirportTransfer (for example for airport transfer)
AirportTransfer (for example for airport transfer)


<div class="mw-collapsible mw-collapsed">
<div  class="headline-collapse" >
===== Daily breakfast report =====
</div>
<div class="mw-collapsible-content">


===== Daily breakfast report =====
1.  Create your info codes as explained above.
1.  Create your info codes as explained above.


Line 185: Line 287:
Booking Info Specified Columns  
Booking Info Specified Columns  


</div>
</div>


===== Daily Half Board report =====  
<div class="mw-collapsible mw-collapsed">
<div  class="headline-collapse" >
 
===== Daily Half Board report =====
</div>
<div class="mw-collapsible-content">


1.  Create your info codes as explained above.
1.  Create your info codes as explained above.
Line 250: Line 359:
Booking Info Specified Columns  
Booking Info Specified Columns  


</div>
</div>
<div class="mw-collapsible mw-collapsed">
<div  class="headline-collapse" >


=====Daily Full Board report =====  
=====Daily Full Board report =====  
</div>
<div class="mw-collapsible-content">


1.  Create your info codes as explained above.
1.  Create your info codes as explained above.
Line 315: Line 432:
Booking Info Specified Columns
Booking Info Specified Columns


</div>
</div>
<div class="mw-collapsible mw-collapsed">
<div  class="headline-collapse" >


=====Airport Transfer Report Daily basis=====  
=====Airport Transfer Report Daily basis=====  
</div>
<div class="mw-collapsible-content">


1.  Create your info codes as explained above.
1.  Create your info codes as explained above.
Line 382: Line 507:


Booking Info Specified Columns
Booking Info Specified Columns
</div>
</div>
<div class="mw-collapsible mw-collapsed">
<div  class="headline-collapse" >


=====  List room charges and extras individually tallied or in groups =====
=====  List room charges and extras individually tallied or in groups =====
</div>
<div class="mw-collapsible-content">


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


<span style=color:#fe746c;“ >{{#fas:exclamation-triangle}}  </span>  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'
<span style=color:#fe746c;“ >{{#fas:exclamation-triangle}}  </span>  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'
</div>
</div>


===Report for tax advisor===
===Report for tax advisor===
Line 457: Line 594:


===Commission report for owners===
===Commission report for owners===
Go to SETTINGS-PROPERTIES-ROOMS-SETUP and use template variables in "Room Template" to calculate the commission.  
Go to {{#fas:cog}} (SETTINGS) > PROPERTIES >ROOMS > SETUP and use template variables in "Room Template" to calculate the commission.  


Examples:
Examples:
Line 478: Line 615:
Drag the columns with the information you want to see from "Available Columns" to "Show Columns".
Drag the columns with the information you want to see from "Available Columns" to "Show Columns".


== Send reports  via Email EMail==
=== 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".
 
=== 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".
 
=== 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".
 
 
 
=== Create a report that lists the group booking id only ===
 
In Template variable field enter [BOOKID] equal [GROUPID]
 
=== Create a Customer Statement with calculated values using booking info codes ===
 
This process uses several auto actions to create Booking Info codes with values in every booking.
 
You can follow the same principle for the columns/values you need for your customer reports.
 
Once you set up the same type of Auto Actions, you can create a custom report for your customer.
 
You can also use the calculated values in another calculation, as seen in the Auto Action screenshots below.
 
 
So if you select the Booking info codes in 'Any booking info code' all bookings/values for the report period will show in the report.
 
Custom Report :- https://screencast-o-matic.com/i/c31ilBV3q9l
 
 
 
Then the report :- https://screencast-o-matic.com/i/c31ilkV3q9A - use Layout = Tabular.
 
 
Create an auto action for each value/column you wish to calculate and show in the report.
 
The booking info codes will be shown in alphabetical order, so you will need to use 1. 2. etc to manage the order/display of the columns in your report.
 
We advise you to use 1 'word' as the booking info code, this ensure there is no confusion in the calculations.
 
Auto Actions, in the Booking Info tab, ensure that you set Process Template Variables = '''When created''' otherwise it will try to recalculate when the report is run and could give incorrect values.
 
 
'''1.BOOKPRICE''' - Booking price  -Sum of the Invoice Charges -:- https://screencast-o-matic.com/i/c31ilgV3q9q
 
the value in the Booking Info Text box should be [INVOICECHARGESNUM]
 
'''2.EXTCOMM''' - External Commission  - Commission value sent by the channel (Commission field) :- https://screencast-o-matic.com/i/c31ilEV3qRq
 
the value in the Booking Info Text box should be [COMMISSONNUM]
 
'''3.COMMAMMOUNT''' - Commission Amount - Base price (first row in the Charges & Payments tab minus the value in the Commission field :- https://screencast-o-matic.com/i/c31il5V3q9R
 
the full value in the Booking Info Text box should be [BASEPRICENUM][-][COMMISSIONNUM]
 
'''4.OURCOMMISSION''' - Our Commission  - 20% of the base price - commission (20% of the Commissionable amount  :- https://screencast-o-matic.com/i/c31ilmV3qRA
 
the full value in the Booking Info Text box, with 2 decimal places  [BASEPRICENUM][*.2]0.20[-]COMMISSIONNUM][*.2]0.20
 
'''5.VATCOMMISSION''' - Vat Commission - VAT on the Commission values :- https://screencast-o-matic.com/i/c31ilJV3qRd
 
the full value in the Booking Info Text box should be
 
[BOOKINGINFOCODETEXT:4.OURCOMMISSION][*]0.20[+][BOOKINGINFOCODETEXT:2.EXTCOMM][*]0.20
 
'''6.TOTALCHAR''' - Total Charges - Total PM Charges :- https://screencast-o-matic.com/i/c31ildV3qSn
 
the full value in the Booking Info Text box should be
 
[BOOKINGINFOCODETEXT:2.EXTCOMM][+][BOOKINGINFOCODETEXT:4.OURCOMMISSION][+][BOOKINGINFOCODETEXT:5.VATCOMMISSION]
 
'''7.OWNERPAYOUT''' - Owner Payout -  Booking price - Total PM charges :- https://screencast-o-matic.com/i/c31ilKV3qSb
 
the full value in the Booking Info Text box should be
 
[BOOKINGINFOCODETEXT:1.BOOKPRICE][-][BOOKINGINFOCODETEXT:4.TOTALCHAR]
 
== 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.
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.


Line 514: Line 765:
   
   
| Booking Info Specified Columns
| Booking Info Specified Columns
| If a booking info code has been specified in the report, then this will display the 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)
|-  
|-  
   
   
Line 1,144: Line 1,403:
| Property id of booking
| Property id of booking
|-
|-
<div style="display:none">
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":
Retrieved from "https://wiki.beds24.com/index.php/Custom-Reports"