Please note: A PDF version of this guide is available for download at the bottom of the article.

This manual will guide you through the use of the System Query Analyzer located in the System Administration section of iRIS™. The System Query Analyzer enables users to execute queries against the system database.

System Query Analyzer

The System Query Analyzer is used to give administrators access to system tables using only SELECT SQL queries. These queries can be saved, executed, downloaded, and used to build a custom report. The queries are typically used to build user-defined custom reports. It is highly recommended that you have some knowledge of SQL before attempting to execute queries using the System Query Analyzer.

To delete a saved query, click the icon in the Remove column. A query can be edited by selecting the Edit/View icon next to the Query Name. Each query can be given a unique name, which is listed under the Query Name column. The last user that executed and/or made changes to a specific query is listed under the Last Modified By column; as well as the date and time information listed under the Last Date Modified column.

Data Diagram

The Data Diagram button will display a portion of the iRIS™ software database structure to the System Administrator to aid in the creation of queries. The screenshot below shows a portion of the database structure that will be displayed when the Data Diagram page is opened.  

You can navigate through the diagram by clicking on the different links at the top of the page.

Add a Query

To add a query, select the Add a Query button, and the SQL Operations page will open.

The Query Name and Query Content fields are both required. Any name can be given to the query, but a unique name is recommended to distinguish its use for later access (i.e. ‘System Departments’ instead of ‘Query 1’).

The query content must be in SQL code; the System Query Analyzer will only allow the execution of SQL SELECT statements. Knowledge of the names of the iRIS™ database tables is required (i.e. PERSONNEL_CONTACT) to be able to utilize this feature. The Query Content input box will expand to accommodate the SQL query. You can click the Minimize button [-] to the bottom right of the Query Content input box to view more of the executed query results.

Be sure to select the Save Query button to save the query information for future use.

Execute

Clicking the Execute button will display the query results at the bottom of the screen (as seen below). After the successful execution of the SELECT SQL statement you may then choose to Download or Build Report. A query is automatically saved once it has been executed. Note that if an existing query is accessed and the Query Content modified, upon execution of the current content the previous Query Content will be overwritten. Below is an example of a successfully executed SELECT SQL query using the System Query Analyzer.

Download

The Download button will allow you to save the results of the successfully executed SELECT SQL query. Click The Download button to save the results of your query as a .xls format file. This may appear differently depending on the browser you are using. The example shown below is using Chrome.  

Once complete, the following popup will display.

Note that the Download feature requires that the local machine be able to open *.XLS files (Using Microsoft Excel) or other compatible programs. An example of how the downloaded query results may display is provided below. Please note that minor column adjustments may be necessary.

Any query generated with the System Query Analyzer will be automatically saved after you click on the Execute or Download buttons. You can also save the query without executing or downloading it by clicking the Save Query button. If an existing query is accessed and the Query Content modified, then upon execution of the current content the previous Query Content will be overwritten.

Build Report / Report Builder

Click on the Build Report button to create a user defined custom report from the successfully executed SELECT SQL query results. Clicking on the Build Report button will automatically take you to the iRIS Report Builder. Additional configuration may be necessary for any user, including a System Administrator, to have full use and function from the iRIS Report Builder. After clicking on the Build Report button, you will be brought to the iRIS Report Builder – Import page. This will allow you to build a user defined custom report. For more information on this tool see the System Administration – Report Builder manual.