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

• Permissions
• Finding the Page
• Running a SQL Query
  • Table Schema
  • SQL Type-Ahead
• Saving a SQL Query
  • Editing an Existing Saved SQL Query
  • Deleting an Existing Saved 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.

---

---

Permissions

One of the following permissions must be enabled to use this feature:

• SqlManagerAdministrator()
• SqlManagerBasic()
• SqlManagerReadOnly()

Read more: Setting Up Users and Roles

---

---

Finding the Page

In Corporate Office, navigate to: Reports > SQL Manager.

https://[CLIENTID].corpadmin.directscale.com/Corporate/Reports/SqlViewer

---

---

Running a SQL Query

1. 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

2. Click the Run button to view the query.

   

   The query results will display under Query Results.

   

---

Table Schema

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.

---

SQL Type-Ahead

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

1. In the Query Box, run a SQL Query.

2. Click the Save button to save the query.

   

   The Save Query pop-up window opens.

   

3. In the pop-up, type a Query Name.

   

4. Click Save.

   

The pop-up closes and the query is saved.

---

Editing an Existing Saved SQL Query

1. In the Save Query pop-up window, you can edit an existing query by clicking the query name in the Query Name list.

2. 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.

3. Click the Replace button to confirm.

   

---

Deleting an Existing Saved SQL Query

1. 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.

2. Click the Delete button to confirm.

   A success status message will appear and the query is deleted.

   

---

---

Exporting a SQL Query

1. 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).