This manual will guide you through the use of the System Query Analyzer menu item under the System Administration menu group on the homepage of your iRIS software.  The System Query Analyzer will allow you to write queries against the iRIS software database.

System Query Analyzer

The System Query Analyzer is used to allow System Administrators access to system tables using only SELECT SQL queries. They can be saved, executed, downloaded, and used to build a custom report. These 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 Modified by column; as well as the date and time information listed under the Date Modified column.

Data Diagram

The Data Diagram button will present a portion of the iRIS software database structure to the System Administrator to aid in the creation and use of the System Query Analyzer. The screenshot below shows a portion of the iRIS software 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, the System Administrator – 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 any information that would require 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 that 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. A new page will be displayed and after a few moments, another window will also display (see below).

The Download function generates a Query.XLS file that will prompt you to either open in the browser or save to the local machine. 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 using the System Query Analyzer. Clicking on the Build Report button will automatically take you to the iRIS Report Builder. Additional user 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 iRIS Report Builder Manual.