GL Reports Sample Report

The following report is a sample of how the user can use the features available in the Setup GL Reports utility to specify Settings and options in Rows And Columns to create a clear and well-presented report.

Note: It is possible to reset the values of Sub Total, Total and Grand Total after they have been printed. The user will recognise the importance of resetting these values so that they can be utilised again, within the same report.

SQL Filter

When using the P&L Range Value or Bal Sheet Range Val functions you have the ability to specify a SQL Filter. This is an advanced feature, it is the where clause in a SQL statement. For example (A.REPORTCODE = “WAGES”) would return the value of all accounts where the Report Code on the GLACCS Table is WAGES. It is important to remember that the filter will only operate within the account number range specified in the A/C Range.

Another good example of the use of this feature is where the user needs account numbers that are not sequential in nature. This can be achieved using the SQL Filter using (A.ACCNO IN (100,500,600,700)). This would return the values of the accounts listed in total. Again it is important to remember to have all the account numbers in the SQL Filter covered by the A/C Range selection.

MSSQL requires where a table alias name is used, fields must be prefixed with the alias name.  If no alias name, use the table name (to avoid ambiguity).

The alias names used here are:

    GLSUBACCS S (e.g. S.SUBACCNO)

    GLACCS A    (e.g. A.ACCNO)

Because no alias name is used in the following tables, the full name must prefix fields from them:

    GLTRANS.     (e.g. GLTRANS.SEQNO)

    GLMOVEMENTS. (e.g. GLMOVEMENTS.SEQNO)