Queries

Overview.

QuickTrav’s Database Query Manager enhances an application’s standard reporting by allowing users to extract customised tabular information out of specific registers or tables within the application’s database.

Users can thus specify what data to scan (primary data range), what information to display (columns), what records to include (filtering), how the data must be grouped (grouping and sorting).

Query results can be browsed interactively (i.e. users can double click on records to open the underlying documents) and can be printed via the QuickTrav Document Report Manager. The results can also be emailed or dumped to file if the user has the necessary security level and if provided for by the relevant application.

A separate query facility is provided for each main data source within the application’s database.

E.g.: In QuickTrav vouchers, separate query options are provided for the voucher register, invoice register and receipts register.

Queries can be built on the fly and discarded afterwards, or the definitions can be saved for repetitive use and may also optionally be shared with other users.

Defining a Query.

Query managers are normally accessed via Enquiries, or :

Note: Some applications provide for a condensed view of the above where the columns on the right are initially hidden and may be accessed via a tab under the progress meter.

Steps required for defining a query

1. Decide what data must be scanned (primary data range)
2. Decide how the results may be grouped (groups)
3. Decide how the summaries may be sorted (summary sorting options)
4. Decide what columns must be displayed and in what order (column selection)
5. Decide what conditions must apply to include the record on the results (column filters)

Defining the Primary Data Range

This is used to limit the query’s scan of the source data to a certain range of records and is essential for speed as, without it, the query would have to scan the entire database every time.

Deciding on which range to use depends entirely on the type of query you wish to build and generally should relate directly to the main purpose of the query.

E.g.: If you wish to query a client’s account for specific dates, you would select the “Date” as the primary range as opposed to the “Period”. By doing this, the query will only scan transactions belonging to the specified dates as opposed to scanning the entire database and checking whether each record matches the specified period or not.

The most common primary data range is a period range that will default to the start and end of the current month.

Defining the Grouping

Grouping is optional and is used when you wish to group data records by a common denominator and extract amount summaries. The number of groups or levels depends on the application but is limited to a maximum of five.

Grouping automatically gives the user various view and print options:

1. Primary groups (top-level summary only)
2. All Groups (if more than one group level specified, all group summaries)
3. Current group (currently highlighted primary group’s detailed records)
4. Full (All Groups summaries and all detailed records)

E.g.: If you wish to extract a report on commission earned by consultant by Client type in QuickTrav, you would simply specify the first group as “Consultant” and then group by “Client Type”. After the query has been built, you would be able to view consultant totals only, consultant totals broken down into client type totals for each consultant or all the detailed records grouped and totalled by consultant and by client type.

Group query results are automatically displayed in alphabetical order of the group value and

thus in some applications, both a code and name field is provided for the same data field to enable the user to select the required display order.

Specifying Advance Summary Sorting Options

Advanced group summary sorting options make further browse and print sorting options available to the user after the query results have been built. Note that these options do not replace the standard options covered above, but rather add to them.

1. Sort Summary By

Summary results, by default, can be sorted by the group code/name. Use this to provide an additional option when printing or browsing to sort the results in order of the selected group total.

1. Sort Order

Specify whether summary totals must be sorted in ascending or descending order.

1. Limit to Top X

Use this to provide an additional option when printing or browsing to show only the top certain number of records based on their totals.

Selecting Columns to be Displayed

1. Selecting the columns to be included in the query results

Columns to be included will have a green dot and those to be excluded will have a red dot .

• 1. Use the “Del” key to toggle, or
  2. Right-click on the column and click the “Include?” menu option, or
  3. Double Click on the column to access its properties and check the “Include?” box.

If you wish to sort group summaries by a column, it must be included in the column display.

1. Changing the order in which the columns will be displayed

Columns will be displayed in the order that they are shown and results will be sorted in the same order (after any grouping)

• 1. Drag and drop the column
  2. Right Click on the column and select “Move to top”, “Move Up”, “Move down” or “Move to Bottom”

1. Selecting Columns to be Totaled

Amount column descriptions will be displayed in green (light green if totalling is on and olive green if totalling is off)

• 1. Right Click on the column and Click the “Total?” menu option, or
  2. Double Click on the column to access its properties and check the “Total?” box.

Setting Column Filters

Column filters allow the user to further filter out unwanted data in the primary data range and are set via the column’s properties (double click on the column or right-click and select “Properties” from the menu)

The following filters are supported

• Containing ($) / Not containing (!$)
• Equal to (=) / Not equal to (!=)
• Range Inclusive (<>) / Exclusive (><)
• Greater than (>) / or equal to (>=)
• Less than (<) / or equal to (<=)

Two further sensitivity options relating to how the query manager compares the column filter values to the actual data are provided depending on the nature of the column and the filter type selected.

Case Sensitivity

If this is active, the query manager will match the filter values to each record using the exact letter casing but if un-checked, the letter casing is totally ignored.

E.g.: Filter Value Inputted by user: Client Name Contains ($) “Donald”

Client Name	Case Sensitive	Non Case Sensitive
McDonald A Mr	Match	Match
Mr. A. Macdonald	No match	Match
S Smith / A Mcdonald	No match	Match

Exact Matching

This only applies to “free format” character input where the equals operator is active and determines how much of the inputted filter value is used when comparing to the data. If this is active and exact match must be made before the record will be included but if un-checked, then only so much of the value that is inputted is compared to the data.

E.g.: Filter Value Inputted by user: Client Name= “Donald”

Client Name	Case Sensitive	Non Case Sensitive
Donald A Mr	Match	Match
Donald son	No match	Match
Donald Party	No match	Match

Building a Query

Once you have defined the primary data range, column grouping and sorting selected the columns to be displayed and specified any additional column filters, you are ready to build the query.

Click the button or the icon to start building the query.

All buttons and controls will be disabled except for the button or the icon which may be used to interrupt the build process at any time.

After the query results have been successfully built, the button or icon and the button or icon will be active and will allow you to interactively browse or print the results.

* After a query has been built all aspects of the query definition are locked (i.e.: they cannot be changed). This allows the user to swap between browsing and printing at will without having to rebuild the query each time.

To start a new query click the icon and select Yes or No to the “Clear Current Query Definition ?” prompt. If you select No, the definition stays exactly the same but you may now make changes.

Viewing Query Results

After a query has been built, there are two view options available:

1. Interactive Browse, or
2. Output via QuickTrav Document Report Manager – Printed Output (Hard Copy or Preview), Emailed Output (HTML File Attachment with Preview) or File Dump.

Depending on the grouping and sorting options specified in the query definition, you will be presented with a number of different view/output options which can be changed as desired without rebuilding the query.

View Options in Interactive Browse

View Options in QuickTrav Document Report Manager (top section)

View Level

The view level refers to the grouping level that you wish to view and determines the amount of detail displayed.

(These options are only available if grouping has been specified)

• • Primary Groups: Displays only primary (first level) group description and totals.
  • All Groups: Displays group descriptions and totals for all groups. (Only available if more than one group specified)
  • Current Group Only: Displays the currently highlighted primary group description and totals plus all subgroup descriptions and totals under that primary group plus all detailed records under each of the lowest subgroups.
  • Full (All levels): Displays everything – i.e. All group descriptions, totals and all detailed records under each of the lowest subgroups.

View Order

The view order refers to the order in which the group summaries are displayed.

(These options are only available if summary sorting options specified)

• • Group Order: Displays in Alphabetical order based on the group description
  • Summary Order: Displays in Numerical order based on the group total.

View Filter

The view filter allows you to view only the Top X as opposed to all records.

(This option is only available if the “Limit to Top X” option specified)

Saving and loading Query Definitions

Saving a Query Definition

Query definitions may be saved to disk for later repetitive use.

Click the disk icon to bring up the Save options.

To save a New Definition

Enter a unique code and the query description and optionally specify that other users may share this definition and if so, whether they may change the definition or not. Click the “Save” button.

To save changes to or to overwrite an existing definition

Double click the query in the “queries on file” list to insert the code and details into the edit form then click “Save” and confirm the overwrite.

To delete a query

Highlight the query to be deleted in the “queries on file” list then click “delete” and confirm the delete.

Loading a Query Definition

Click the icon to bring up the list of saved queries.

Click the relevant tab to view the different lists and then double click on the query definition you wish to open.

• • System Queries: These are query definitions provided as standard with the relevant application and are available to all users
  • My Queries: These are query definitions that were previously saved by you.
  • Shared Queries: These are query definitions saved by other users as shared.

You may also delete your own queries from here.

-o-