Before attempting to design any report query, it is important to understand the structure of the CQB Design form and how it operates. The CQB 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 are 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 button located at the lower left of the CQB 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. For example, if a User selected fields from the Invoice Data Group and fields from the Equipment Data Group, the report would make no sense in that Invoices have no direct relationship to a System Equipment List.
The Database Explorer is a utility available on the SedonaOffice website that 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 who 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/Downloads.html.
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 Customers. There are no Data Groups available for creating reports for Jobs at this time. If a User takes the time to review the Database Explorer, they will find there are fields in a data table that are not listed in any CQB Data Group; this is by design. From time to time the CQB 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 CQB.
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 names. Fields to be included in 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 Customer Data Group has been expanded; only a partial list of the available field names is displayed. Use the vertical scroll bar to move up or down to display more data fields.
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 Customer 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 CQB are somehow related to a customer.
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 customers that are actively being billed for recurring services. This list will be used to send out a letter notifying the customer the company is now accepting automated credit card payments. A filter may be created that will include only customers where the customer status is equal to AR (Active Recurring). Below is how this filter would be created.
Filters
Since this report query result is going to be a mailing list, and a customer may have multiple Bill To addresses, the report query should only include the Primary Bill To Address, otherwise the customer could receive the same letter at multiple addresses. An additional filter can be added to only include customers that are not canceled.
Listed below are the Relation types available when creating filters.
= Equal to
<> Not Equal To
> Greater Than (used with numeric or date type fields)
< Less Than (used with numeric or date type fields)
>= Greater Than or Equal To (used with numeric or date type fields)
<= Less Than or Equal To (used with numeric or date type fields)
Is Null The field is empty
Is Not Null The field contains any value
Starts With The value entered is found at the beginning of the field
Ends With The value entered is found at the end of the field
Contains The value entered is found anywhere in the data
Between The value is a range - selecting this operator will prompt for a beginning range value and an ending range value; this is typically used for filtering on a specific date range.
Sorting
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 in the Filters section on the previous page, the report query may be sorted by zip code so that the letters printed out may be bundled in this order to receive a lower postage rate. Drag and drop the Bill_Postal_Code field 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 order.
Report Query Results
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 (green arrow) 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.
Across the bottom of the CQB Report Design form are a series of function buttons. Click on the link below for the definition of each Function Button.
Customer Query Builder Function Buttons
