Opera OSR: Opera Report Builder

Step 1

Login to Opera > Click on Configuration button.

Step 2

How do you start a simple report? In the menu, select Setup > Report Setup > Reports > New

All existing reports in Opera PMS display. Click on New to create your report and ensure Simple Report is ticked.

Step 3

Give your report a name

• Report Name: Type your report name here
• Simple Report: As soon as you tick here, the File Name field populates auto.
• Report Group: Select a group. You can select any, however for simplifying the search and keeping it organized, select a relevant group here.
• Keep everything else default and click on Customize.

Customize report output

You get the below tabs:

• Import: Instead of creating a new report, you can use this to import the report setup from a previously saved/exported file. These files have a .OSR extension. And, you use the same tab to export your report setup.
• General: Use this tab to set the report title, layout, and font size. Importantly, under Views, you tick which data set to use in the report. In our example case, we need guest information from Profiles and booking details from Reservations.
• Columns: Here, you select the columns for your report.
• Group by: If you want to group your data, such as the report to display all Deluxe rooms together, Standard rooms together, etc., and print a summary for each group.
• Order by: Order by column(s).
• Filters: You set your report filters and whether they should prompt user input.
• Preview: Finally, preview the report.

Step 4

General tab: Select report views

The report title is the report name from the previous step that comes by default which you can change. Keep the default setting for column labels. Your report deals with guest information and booking information, so tick Reservations and Profiles views here.

Step 5

Columns tab: Add report columns

On the left, you get the list of all available columns based on the views you selected in the previous step. Select the columns from the Available Columns list on the left and double-click or use the arrow button in the middle to add to Report Columns.

• Search columns by their names using the Like and Not Like fields. This is a wildcard search field, so you can type “name” and it displays all columns having “name” in them such as “first name”, “last name”, “name id”, “company name”.
• Use the Line No. button to add a sequence column.
• Use the Space button to add an empty space between columns.

Step 6

Change column settings

Click on any column under the Report Column (ones you have added) to set column-specific characteristics.

• Change the Column Label, Alignment, and Font size, if you need to.
• Set the column height and especially width according to the data it’s going to output.
• Finally, under Summary, you can add a calculated total (count, max, and min. If the column is number, sum and average also appear). This prints at the end of the report or each group.

Step 7

Group by tab: Add grouping (if needed)

Select your grouping here by finding the column under Available Columns and adding it under Group by.

• You can add multiple Group by columns to subgroup results. You don’t have to have the group by column under Report Columns.

Step 8

Order by tab: Add report sorting order

Same as you added the Group by, select your sort order here by finding the column under Available Columns and adding it under Order by. You can add multiple Order by columns here.

Step 9

Filters tab: Report criteria

This tab makes the Opera Simple Report writer powerful. Select a column from Available Columns and add it under Report Filter.

While you’re adding the column, the report builder shows various possible filters that you can apply to the column depending on the column’s data type such as text, number, date, etc. The filter column doesn’t have to be in the report.

You need to filter data based on:

1. The guests must be in-house.
2. Say, you want the user to select the date range for departure. So that the user can get a report of all guests checking out today and tomorrow or in the future.

Set the condition to filter checked-in guests: Add the ‘Reservation Status’ column. Drop down Default Value and select: CHECKED IN, DUE OUT, PENDING CHECK OUT, WALKIN. And, OK, save.

Set a departure date filter that prompts user entry: Add the ‘Departure Date’ column.

Since it’s a date-type column, there are different possible filter types.

As you want the user to enter from/to date ranges, select ‘Between’. Tick on the Parameter so that it prompts user entry for the filter. Notice Business Date is ticked to say From date has the default value of Opera business date.

Step 9

Preview tab: Preview your final output.

Opera Simple Report tips for advanced users

You can do more with OSR such as adding a column that is not listed under Available Columns, adding a calculated column, or you can also format an existing column.

You achieve this by adding Oracle SQL statements and PL/SQL functions under the Expression button explained under Step 6.

When you click on the button it looks like the below:

When you select ‘Full Name’ column and click on the Expression:

When you select ‘Room No.’ column and click the Expression:

• Notice that ‘display name’ comes from the Profile view you select in the beginning, and it has an alias b. Similarly, ‘ROOM’ column comes from the Reservation view with an alias a.
• If you want the guest name to display as ‘Alan (Room: 1010)’, you would replace either column expression to (b.display_name ||’ (Roon: ‘||a.ROOM||’)’) – Notice all expressions should go within a bracket.
• Say you want to display the first 2 digits of the room numbers and call it Floor, you can enter (substr(a.ROOM,1,2))
• You can replace these functions under an existing column or even better add a Space column and add the function.
• To add sysdate, you can add, (select sysdate from dual)
• Every guest has a unique profile ID, “b.NAME_ID” and a unique reservation ID, “a.RESV_NAME_ID”.
• For instance, I want to know if the guest has requested turndown service in his reservation, and I don’t find this column in the report writer. Knowing which table holds this field, I can write the below query under Expression: (select turndown_yn from reservation_name where resv_name_id = a.RESV_NAME_ID)

Above are a few examples. However, the point is to show the possibilities so that you can explore more.

For a comprehensive user-guide from Oracle, here is the link