SQL Wizard - Creating Custom Report

1) In roomMaster, go to Reports > Right Click on "Custom Reports" > Add a report

2) In the following window, type the report name then click on "SQL Statement"

3) Click on "SQL Wizard":

a-Tables: Here you'll be able to choose the tables that you need the fields from.

b- Group By: To use functions like SUM, MAX, COUNT, etc... 

c- Formula: This will allow you to check for the column's format and rename the column. 

d- Reset: Delete all the chosen entries. 

e- Fix All titles: this will automatically re-name all the chosen columns/fields. . 

-Where Clause, the following step, will allow you to choose the condition for the report (example: make the report run within a specific date range), It is used to extract only those records that fulfill a specified condition. :

If you're using more than one condition in the report, you can choose "AND" or "OR"

 

• The AND operator displays a record if all the conditions separated by AND are TRUE.
• The OR operator displays a record if any of the conditions separated by OR is TRUE.

-Order By, will allow you to sort the outcome of the report in ascending/descending order:

The most used tables: 

1) RESERVE: Contains the details of all the reservations (Future and Past reservations) 

Note: The RESERVE table holds the historical reservations for up to 1 year only.

2) FOLIOHD: Contains the details of the in-house folios

FOLIOTRN: Contains the details of the charges/payment done for the inhouse folios. 

3) HISTHD/HISTRN: same as FOLIOHD?FOLIOTRN but they retrieve the historical folio details. 

4) VIPFILE: This contains the details of the guest profiles. 

Also, once you check the tables, you'll see the description for each table which is straightforward.