Did you know that users who are proficient in SQL have access to an Advanced Tab in the Report Setup window that lets them build reports directly from SQL Statements? This feature lets users prepare SQL to define the report content and then have the results magically display in our Report Preview Window. This provides a great alternative to individuals who are comfortable with SQL, but do not wish to learn a new reporting tool or be confined by the constraints of a defined reporter.
To use this feature, it is recommended that you test your SQL Statement in the Query Analyzer tool until the desired records and fields are returned. Once the desired records are obtained, the statement can be copied onto your clipboard for later retrieval in the Reporter.
Once you have your desired statement, go to the Reporter and locate the report on which the SQL is to be executed. It should be noted that it is advisable to copy an existing report for this purpose as the report you modify will lose its existing field settings. Modify the “Setup” of the report as follows:
Select the Advanced Tab
Select the SQL Structure Sub-Tab
In the From (SQL) Text Box, enter your entire SQL Statement (paste from your clipboard if available). Even though there are text boxes for “Join, Where and Having” clauses, the entire statement should be entered into the From (SQL) Box. As a simple example, the following statement would return a series of asset fields to the Report Preview Window for all assets of the “HVAC” Classification:
- When you are finished placing the content into the “From (SQL)” Box, click the Apply Button.
The Report Preview Window will display showing the fields designated in the SQL Select Statement.
Once this feature has been used to specify report content, all further edits must be made directly to the SQL Query from the Advanced Tab. You will no longer be able to use standard features such as the “Available Fields List” or “Smart Reports” to define the report, as the report presentation will bypass most report setup features. You can, however, use the setup tabs to alter some formatting (report heading, style, font) and to schedule the report. In addition, Report Criteria can be passed from the Report Criteria Window to the SELECT statement in the query using a special code, “[WHERE]“. For more information on using the special WHERE clause or if you would like any additional information on using the Magic SQL Reports feature, please contact Customer Support.
Want to try it out? Copy/paste the below SQL statement into the From SQL in the Advanced Tab of any report, and see what you get:
SELECT WOID, Reason, TargetDate, Status FROM WO WHERE IsOpen=1