Setup Onyx Report for export

Setup and Configuring Onyx Nightly Export Report

• In roomMaster click on Reports and find the section called Custom Reports
• Right click on Custom Reports and choose Add Report
• Give the report a title and description. These can be the same, but name it something similar with Onyx in the title
• Make sure in the Select Report Type you choose Embedded SQL Report and then click on the SQL Statement button
• Copy the following SQL into the SQL Statement window:

SELECT TRAVHEAD.TRAVELCODE AS 'AGENCY NUMBER',
       TRAVHEAD.NAME AS 'AGENCY TRADING NM',
       TRAVHEAD.STREET AS 'AGENCY STRADDR2',
       TRAVHEAD.CITY AS 'AGENCY CITY',
       TRAVHEAD.ZIPCODE AS 'AGENCY POSTCD',
       TRAVHEAD.STATE AS 'AGENCY STATECD',
       TRAVHEAD.COUNTRY AS 'AGENCY CTRYCD',
       TRAVHEAD.PHONE AS 'AGENCY TELEPH',
       TRAVHEAD.FAX AS 'AGENCY TELEFX',
       TRAVHEAD.EMAIL AS 'AGENCY EMAIL',
       TRAVDETL.NUMBER AS 'HOTEL RES NO',
       HISTHD.FIRSTNAME AS 'GUEST FIRST NM',
       HISTHD.LASTNAME AS 'GUEST LAST NM',
       HISTHD.CHECKIN AS 'ARRIVAL DT',
       HISTHD.CHECKOUT AS 'DEPARTURE DT',
       HISTHD.NIGHTS AS 'ROOM NTS',
       TRAVDETL.COMMDUE AS 'COMMISSION AMOUNT',
       TRAVHEAD.BILLAMOUNT AS 'COMMISSION PCT',
       HISTHD.RATE_REQ AS 'RATE CODE TX',
       TRAVDETL.COMMDUE - TRAVDETL.COMMPAID AS 'COMMISSION AMT',
       CASE TRAVHEAD.AGENTSTATUS
 WHEN 0 THEN 'USD'
 WHEN 1 THEN 'USD' ELSE '' END AS 'CURRENCY'
 
FROM HISTHD
JOIN TRAVHEAD ON HISTHD.TRAVELID = TRAVHEAD.TRAVELID
JOIN TRAVDETL ON HISTHD.NUMBER = TRAVDETL.NUMBER
WHERE (TRAVHEAD.TRAVELCODE <> ' ' AND TRAVHEAD.TRAVELCODE IS not NULL) AND  TRAVHEAD.NAME NOT LIKE '%Expedia%' AND  TRAVHEAD.NAME NOT LIKE '%Booking.com%' AND  TRAVHEAD.NAME NOT LIKE '%creative lodging solutions%' AND  TRAVHEAD.NAME NOT LIKE '%hotels.com%' and TRAVHEAD.NAME NOT LIKE '%travelocity%' AND TRAVDETL.COMMDUE - TRAVDETL.COMMPAID > 0
ORDER BY TRAVHEAD.TRAVELCODE

 

• Put a check in the "Allow export of data" box at the bottom of the SQL statement window. Click "Run Query" choose a date. On the report that comes up, select the "Export" button at the top of this window.
   
• On the General tab select the three dots in the Export File Parameters section and it should default to the C:\roomMaster directory. Name the file 'Onyx' and click Save.
• In the section named "Action After Exporting" select the email using internal mail (SMTP) and paste the Onyx mailbox in the Email Address field.
• Select the Options tab and click on the "Create Export Specification" button. It should open to the C:\roomMaster directory. At this time, the .qwe file needs to be saved in this location. Name the file 'Onyx' and click Save. You should get the message that the Export Specification was successfully created, click OK. Click OK again. 
• Back in the SQL Statement window copy and paste the following SQL code to the bottom of your SQL query:

  EXPORT USING 'Onyx.qwe' 

11. You should now have the following complete SQL query in this window, confirm it is exactly the same and then click the Green Check mark to save.

SELECT TRAVHEAD.TRAVELCODE AS 'AGENCY NUMBER',
       TRAVHEAD.NAME AS 'AGENCY TRADING NM',
       TRAVHEAD.STREET AS 'AGENCY STRADDR2',
       TRAVHEAD.CITY AS 'AGENCY CITY',
       TRAVHEAD.ZIPCODE AS 'AGENCY POSTCD',
       TRAVHEAD.STATE AS 'AGENCY STATECD',
       TRAVHEAD.COUNTRY AS 'AGENCY CTRYCD',
       TRAVHEAD.PHONE AS 'AGENCY TELEPH',
       TRAVHEAD.FAX AS 'AGENCY TELEFX',
       TRAVHEAD.EMAIL AS 'AGENCY EMAIL',
       TRAVDETL.NUMBER AS 'HOTEL RES NO',
       HISTHD.FIRSTNAME AS 'GUEST FIRST NM',
       HISTHD.LASTNAME AS 'GUEST LAST NM',
       HISTHD.CHECKIN AS 'ARRIVAL DT',
       HISTHD.CHECKOUT AS 'DEPARTURE DT',
       HISTHD.NIGHTS AS 'ROOM NTS',
       TRAVDETL.COMMDUE AS 'COMMISSION AMOUNT',
       TRAVHEAD.BILLAMOUNT AS 'COMMISSION PCT',
       HISTHD.RATE_REQ AS 'RATE CODE TX',
       TRAVDETL.COMMDUE - TRAVDETL.COMMPAID AS 'COMMISSION AMT',
       CASE TRAVHEAD.AGENTSTATUS
 WHEN 0 THEN 'USD'
 WHEN 1 THEN 'USD' ELSE '' END AS 'CURRENCY'
 
FROM HISTHD
JOIN TRAVHEAD ON HISTHD.TRAVELID = TRAVHEAD.TRAVELID
JOIN TRAVDETL ON HISTHD.NUMBER = TRAVDETL.NUMBER
WHERE (TRAVHEAD.TRAVELCODE <> ' ' AND TRAVHEAD.TRAVELCODE IS not NULL) AND  TRAVHEAD.NAME NOT LIKE '%Expedia%' AND  TRAVHEAD.NAME NOT LIKE '%Booking.com%' AND  TRAVHEAD.NAME NOT LIKE '%creative lodging solutions%' AND  TRAVHEAD.NAME NOT LIKE '%hotels.com%' and TRAVHEAD.NAME NOT LIKE '%travelocity%' AND TRAVDETL.COMMDUE - TRAVDETL.COMMPAID > 0
ORDER BY TRAVHEAD.TRAVELCODE

EXPORT USING 'Onyx.qwe'

• After clicking the green check mark to save the SQL query, you will be back on the report properties screen. In the "Run Report" field select the dropdown and choose "After Dayend Close" and then click OK. 

That is all there is to the steps of setting up the export report to have it email to the Onyx mailbox. Then when the property runs their night audit, it will prompt the night auditor to select the date  and it will run and export to a file to be emailed automatically.