Using the SQL Manager to View Data

Admin: Corporate Admin
Page: Reports > SQL Manager
URL: [CLIENTID].corpadmin.directscale.com/Corporate/Reports/SqlViewer
Permission: SqlManagerAdministrator(), SqlManagerBasic(), or SqlManagerReadOnly()

In order to utilize the SQL Manager effectively, you must possess a basic understanding of SQL (Structured Query Language). To learn more about SQL, visit the SQL Tutorial on W3 Schools.

Suppose you need to view or export data. In that case, you can use the SQL Manager to query and export data directly from a read-only copy of the Database

Keep in mind, the SQL Manager is for viewing data only. If you need to modify database data, use the Data Editor.

Enabling Permissions

The SQL Manager requires one of the following permissions to be enabled for your Corporate Admin Role:

Permission Run Save Export Delete

SqlManagerAdministrator

SqlManagerBasic

❌*

SqlManagerReadOnly

* You can delete your queries but not other users'.

Learn more about permissions in Assigning Employee Corporate Admin Permission Roles.

Supported Syntax

In addition to vanilla SQL, the SQL Manager also supports Microsoft’s Transact-SQL (T-SQL) syntax to perform additional capabilities such as declaring variables.

Enter a New SQL Query

The Enter Query tab allows you to run and save a new query. To run a query, enter your SQL statement in the query box and click Run. The query results will display under Query Results. The query will return up to 300 results.

Query Results

Table Schema

If you need to view all the available tables and the types of data they contain:

  1. Select Table Schema. The Table Schema pop-up window will appear.
    Table Schema
  2. Utilize the search bar to locate a table name or scroll through the Table Name List.
  3. Select a table name to view the table's schema.
    Table Schema pop-up window
  4. The view-only search results will now display below the Query Box.

Example table schema

Client Schema ([client])

Database schemas are logical collections of data objects. The Database has the following schemas:

To specify the schema in your query, precede the table name with the schema in brackets [].

Examples:

SELECT * FROM [dbo].[CRM_Distributors]
SELECT * FROM [client].[MyCustomTable]

SQL Type-Ahead

While writing your query statement, if you need help with the syntax, press Ctrl Space on your keyboard to enable SQL Type-Ahead.

SQL Type-Ahead displays a menu of keywords, tables, or column options available to you at that point in the statement.

Press Ctrl Space on your keyboard to enable the SQL Type Ahead feature.

To see a table’s fields in the type-ahead, you must first give the table an alias.

Alias example

Example of setting an alias:

SELECT d.BackofficeID FROM CRM_Distributors d

Running Only Selected Lines

Want to run individual lines or only part of a longer query?

  1. Use your mouse to highlight specific lines.
    Highlighted lines
  2. Click Run.

The results will only display the data queries from the highlighted lines, an excellent tool for troubleshooting.

Expanding the Query Box

If you need to change the size of the Query Box, you can click and drag the right-hand corner.

Enlarge the Query Box

Saved Queries

The Saved Queries tab shows a list of all the queries that have been saved. The list includes the Saved Query Name, Created By (the creator who first created the query), and the date Last Modified columns.

Saved Queries Tab

Running a Saved Query

To run a saved query, click on the query name shown within the Saved Query Name list.

Query List

You will now be directed to the Enter Query tab. The saved query will automatically populate within the query box.

From here, you can adjust the query as needed. Click the Run button to see the results below.

Enter Query Tab

Saving a Query

To save a query:

  1. Enter a query in the Enter Query Box.
  2. Click the Save button.
  3. The Save Query pop-up window will appear.
  4. Enter a new Query Name.
      Warning: If you are editing a saved query, the Query Name field will be populated with the current query name. If you save a query with the name of an existing query, it will replace the previous query with the new query.
  5. Click Save.
Save Query Box

Deleting a Saved Query

To delete a saved query, go to the Saved Queries tab and click on the delete button next to the saved query you want to delete.

Delete Saved Query

Exporting a SQL Query

After running a query, you can export the results:

  1. Click the Export button.
    Export button

A CSV file downloads. You can open this file in any spreadsheet program (such as Microsoft Excel or Google Sheets).

Was this article helpful?
0 out of 0 found this helpful
Previous Next

Comments

0 comments

Please sign in to leave a comment.