Skip to main content

Embedded SQL Report

roomMaster allows you to write simple and advanced SQL statements to form run-time reports which can be placed on the Reports menu for any user to simply execute. This option is designed for intermediate and advanced computer users to create simple or complex queries, save it, and then attach it to the Reports menu so any user can simply execute it and get the information they need.

From the Reports menu, click on Custom Reports in the Category tree and then press the Properties button. Next, press the Insert button to begin a new custom report. Add a Report Title and Description. Next, press the SQL Statement button to begin to build your SQL statement.

Click here for basic/beginners information about "SQL".

Security with SQL Reports

All user profiles marked as "Manager" will have access to create and maintain SQL reports. If you wish for other users to be able to create and maintain these reports, you must grant them access through their Menu Class / Menu Profile. This is done by selecting Setup > Menu Profiles. Find the Menu Class you wish to grant access and select it. Be sure you are aware of all users who you are granting access to as allowing access to this function allows the user to write a report against any data in your system including accounting information. If you don't want the user to have access to your accounting information, and you grant them access to Interactive SQL, they could simply write a report over the accounting files to see this information, so be careful. However, anyone who does not have access to the Interactive SQL menu option (to create and maintain SQL reports) will still be allowed to simply execute these reports off the Reports Menu.

Interactive versus Embedded SQL Report

The difference between an Interactive SQL Report and an Embedded SQL Report is that for an Interactive SQL Report, the statement and report is executed directly from the Tools menu in roomMaster, and is created at that time. Only users with access to Interactive SQL can write and maintain reports. To allow other users to execute these reports, create them as Embedded SQL Reports and place them under the Custom Reports category on the Reports menu.

Limitations of Embedded and Interactive SQL

  • A maximum of 33 columns can be selected. Additional columns will be truncated.

  • Each column when displaying in the data grid preview can contain up to 128 characters. Once printed, files with more than 128 characters will print as notes fields and have a limit of 5,000 characters. (Notes fields are truncated in the display grid at 128 characters but fully printed when printing on a report)

  • The maximum length of a final SQL statement is 20480 characters. (20K)

  • The maximum length of a saved Embedded SQL is 10240 characters (10K)

  • All SQL statements must conform to MariaDB SQL standards with additional InnQuest functions and InnQuest Extension functions.

  • Be aware the many built-in reports use more than a single SQL statement to gather the data and additional code is used to display the data.

SQL Syntax and Tips - See Also