SQL Report

The SQL report option allows you to enter a Standard Query Language (SQL) statement and run a report based on the statement. SQL statements can be used to simulate information categories by directing the system to retrieve specific data from a selected table or tables.

For example, select statements are written in the syntax of SELECT, FROM, WHERE, and GROUP BY. "Select" identifies what fields are to be selected for the report; "From" identifies what tables the fields will come from; "Where" identifies the conditions by which the fields for the report will be selected from the tables; and "Group By" identifies how the fields will be grouped (sorted).

If for budgeting purposes you wanted to select the net of credits minus debits from a particular budget table, the SQL statement you would create might appear as follows:

select glt_cr - glt_dr net from glt_trns_dtl

This statement directs the system to let "net" be the result of the general ledger credit value minus the general ledger debit value and instructs that this data is to be retrieved from the glt_trns_dtl table.

To create a SQL report:

Select File > New from the main menu.
Select Report Design. Click OK.
Select SQL Report and click Next. The SQL window is displayed.
Enter the SQL statement by either typing the entire statement or by using a combination of typing and selecting the SQL statement keys.
Click the Check Syntax button to ensure the statement conforms to SQL syntax requirements. If there are errors in the statement, the system will display these in the Database Error message window. If the syntax of your statement is correct, click Next.
In the Report Grouping window, define the basic sort order for your report. Click Next.
In the Report Details window, the SQL statement will be assigned as the title of the report. You can edit this title as needed. Select whether you want to view the report design or whether you want to run the report to the screen or to a printer.
Click Finish. If you selected to view the report before running it, the Report Design window is displayed.