The SQL Manager is used to improve the standard reporting that exists in Corporate Office. To ensure that the live database performance will not be impacted, all queries run through the SQL Manager will run off a replicated database (the same that’s used for fail-overs).
What We’ll Cover
- Finding the Page
- Running a SQL Query
- Saving a SQL Query
- Exporting a SQL Query
SQL statements to manipulate the data or structure are not supported. The Data Editor is used for updating data in the database.
One of the following permissions must be enabled to use this feature:
Read more: Setting Up Users and Roles
Finding the Page
In Corporate Office, navigate to: Reports > SQL Manager.
Running a SQL Query
In the SQL Query box, type your SQL statement.
For examples of SQL queries, see: SQL Query Library
You can click the Table Schema link to view a list of all available tables you can click and use in queries.
Tip: Click in the Query Box and press CTRL+SPACE on your keyboard to enable SQL Type-Ahead
Click the Run button to view the query.
The query results will display under Query Results.
View a list of all available tables you can click and use in queries. The query will return up to 300 results.
Use the search bar to find a table name, or scroll through the Table Name List.
Click a table name to view the table. The view-only results show below the Query Box and can’t be saved.
Click in the Query Box and press CTRL+SPACE on your keyboard to enable SQL Type-Ahead. This is a Code Mirror implementation that suggests SQL syntax and tables as you type to aid in writing queries.
Saving a SQL Query
In the Query Box, run a SQL Query.
Click the Save button to save the query.
The Save Query pop-up window opens.
In the pop-up, type a Query Name.
The pop-up closes and the query is saved.
Editing an Existing Saved SQL Query
In the Save Query pop-up window, you can edit an existing query by clicking the query name in the Query Name list.
The Query Box will populate with the saved SQL statement. Make any necessary edits and save the query in the method detailed above.
Note: Typing a new name will save a new query under that name. Leave the name the same to save the edits to the existing query.
The pop-up will update, asking if you want to overwrite the existing query.
Click the Replace button to confirm.
Deleting an Existing Saved SQL Query
You can delete a saved query by clicking the Trash icon to the right of the query name in the Saved Queries pop-up window.
A Delete Query confirmation pop-up opens.
Click the Delete button to confirm.
A success status message will appear and the query is deleted.
Exporting a SQL Query
After running a query, you can export the results by clicking the Export button.
This prompts a .CSV file to download. You can open this file in any spreadsheet program (such as Microsoft Excel or Google Sheets).