Writing Advanced Queries with SQL

Advanced reports can be created through manual SQL coding. Virtually any reporting option is available if you know the database schema. Clicking Advanced Query hides the query string list boxes and opens the Report data query field in which you can insert existing code or write new SQL code.

Restriction: The SQL statement select top is not supported. It is not possible to report directly on tables which return user information from Silk Central, for example SCC_Users, SCC_AuditEntries, SCC_UserGroups, and SCC_UserGroupRoles.

One approach is to begin query-string construction using the list boxes as outlined in Creating New Reports. If the report criteria are valid, the equivalent SQL statement will be generated and displayed, and then move to advanced mode for further modifications.

Note: If you switch from advanced mode back to simple mode the changes you made within the code will be lost.

To write an advanced query directly in SQL:

In the menu, click Reports > Details View.
In the Reports tree, select the folder in which you want the new report to display. This determines where the report is stored in the directory tree.
Click on the toolbar. The Create New Report dialog box opens.
Type the name of the new report. This is the name that is displayed in the Reports tree.
Check the Share this report with other users check box if you want to make this report available to other users.
Type a description of the report in the Description field.
Click Advanced Query to open the Report data query field. Insert previously written code or write new code directly in the field. The Insert placeholder list assists you in editing the SQL queries with predefined function placeholders. For details, see SQL Functions for Custom Reports.
Note: If you manually edit SQL code for the query, click Check SQL to confirm your work.
Click Finish to save your settings.