Before attempting to design any report query, it is important to understand the structure of the GL Query Builder Design form and how it operates. The GL Query Builder Report Query Designer is divided into five major elements which are briefly described below. Follow the links for detailed information on each of these major design elements.
- Data Groups – The Data Groups is the primary component of any report. This section is where individual fields are selected that will be included in the final report results. As each Data Group is expanded, a list of all fields available in the Data Group is displayed. Fields may be selected from one or multiple data groups.
- Fields to Display – As fields are selected from the Data Groups, they are displayed in this area of the Design form. Fields selected may be moved up or down until the fields are listed in the order in which it is desired they appear on the final report.
- Filters – The Filters section allows the User designing the report query to include or exclude data by using built-in operators.
- Sorting – This section allows the User to select the order in which the data is displayed on the finished report. Sort options are based on one or more of the fields selected from the Data Group.
- Report Query Results – Once a query design is complete the User will click the Refresh Results (green arrow) button located at the lower left of the GLQB Designer form. The results of the requested data will be displayed in this grid area.
When designing report queries it is important to understand how data is linked and stored in the SedonaOffice database.
The Database Explorer is a utility available on the SedonaOffice website which provides a road-map to the design of the SedonaOffice database. This utility illustrates which fields in which tables are linked to each other and provides other valuable information that is extremely useful in understanding the data table structures and relationships. It is highly recommended all Users that will be designing report queries review this utility first. This utility should be considered recommended reading. The Database Explorer may be downloaded from this page of the SedonaOffice website: http://www.sedonaoffice.com/resources/downloads/
Data Groups
The Data Groups displayed in the Report Query Design form allow the creation of reports based on many data elements. The list of Data Groups displayed pertains to data which is related to transactions posted to the GL; the Register, G/L Accounts, Accounting Periods, Reference Information and Branches. If a User takes the time to review the Database Explorer they will find there are fields in a data tables that are not listed in any GLQB Data Group; this is by design. From time to time the GLQB is enhanced to add additional fields; when this occurs mention is made in the Release Notice for the software version where the enhancement was made. Please read each release notice to keep current on any new field additions to the GLQB.
When the tree of a Data Group is expanded, all the fields available to include on a report are listed below the Data Group name. Some of the field names are quite long; a horizontal scroll bar is provided at the bottom of the Data Groups to display more of the field name. Fields to be included on the report are selected by double-clicking on the field name or by dragging the field name into the Fields to Display area of the Report Designer.
In the illustration below the Register Data Group has been expanded.
Fields to Display
As fields are selected from the Data Groups they are listed in the Fields to Display section of the Report Query Designer form. The Register Number field is a field that will appear on all report queries and may not be removed; this is by design since all data fields in the GLQB are somehow related to a G/L Register Number.
The report query results will display and print the data fields in the order in which they are listed in this section. The User may move fields up or down in the fields list by clicking the green up or down arrow located below the Fields to Display area.
If the User needs to remove a field that was previously selected, highlight the field name, right-click and select the Delete option.
Filters
Filters are used to include or exclude records from the report query. Each Filter is based on a Data Field to which a Relation is applied and criteria selected from a drop-down list or typed in by the User.
For example a report query is being designed to create a list of Customer Credit Memos posted during the month of August 2009. A filter may be created which will include only transactions with a register type of CRMEMO during the date range needed and will exclude the debit side of the transaction. Below is how this filter would be created.
Listed below are the Relation types available when creating filters.
This section of the Report Query Designer allows the User to select the order in which the data is displayed on the report. Sort options are based on one or more of the fields selected from the Data Group.
Working with the same example used the Filters section on the previous page, the report query may be sorted on Register Date. The User will drag and drop the Register_Date field from the Data Groups into the Sorting area of the Report Designer form. By right-clicking on an Order By field, the User may select to sort the field in ascending or descending order. The default sort order is ascending.
Once data fields have been selected, filters created and sort options have been selected, the User will be ready to see the report query results. Click the Refresh Results button
located at the lower left of the Report Designer form. The Report Results grid will fill in with the data specified in the report query.Double-clicking on any row in the Query Results will open the entire Journal Entry to view both the debits and credits for the transaction.
