Magic SQL Reports


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

Advertisements

No comments yet... Be the first to leave a reply!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: