Data Query

Page approved.




WARNING icon
Data Query is an Advanced User feature.

The Data Query Report window, which may also be referred to as "Report Generator", allows users to create and run ad-hoc queries against the Khaos Control database. It is opened by either:

This Khaos Control Wiki page briefly covers the features, functions and tools that the user can query the database with, including storing data queries for later use. Data Query is very much an advanced user feature and knowledge of SQL is necessary to successfully query the data held.

Notes:

  1. The dates returned after executing a query will be displayed based on the exact dates provided in the query.
  2. Editing Data Query reports will require the appropriate user permission set in [ System Data | Others | Users ] .

The Data Query Report screen

The Data Query screen is divided into five main areas:

Reports area

The Reports area comprises action buttons above a reports pane
The Reports area comprises action buttons above a reports pane.

Action Buttons

  • BtnOpenX.jpg: used to populate the SQL Script Grid with the saved query. The user must focus on a query before pressing the button else nothing will be displayed.
  • BtnSaveX.jpg: this button only works if there is a query in the adjacent grid to save. A Save Report popup appears enabling the user to enter a name. The user can also overwrite a previously saved report by using the dropdown box and selecting the query. Reports can be moved into a specific section which can be chosen from the dropdown box.
    Note: To create a new section click on the BtnDataQueryNew.jpg button in the popup.
  • BtnDelItemX.jpg: deletes the report the user is currently focused on in the Report Pane.
    Note: The system will prompt the user before deleting the report.
  • BtnEditX.jpg: allows the user to edit the name of the report they are currently focused on in the Report Pane.
  • BtnReportsGreenPlusX.jpg: expands the list of reports in the Reports pane.
  • BtnReportsGreenMinusX.jpg: collapses the list of reports in the Reports pane.
  • Data Query Report Scheduler button: this opens the Email Trigger dialog enabling the user to automate the running of the SQL reports and email internally or externally or upload reports to a specific FTP folder.

Reports Pane

The reports pane is used to access reports that have been saved for reuse. Within the reports pane, sections can be created and used to group and organise reports. For example:

Address
Address - List Addresses with Courier Note
Address - List Deactivated Addresses
Customer
Customer - Company Terms NOT matching Company Class Terms
Customer - Count Customers created on each day
Customer - List companies having document-related email addresses
Customer - List inactive contacts
EPOS
EPOS - Daily Sales Report
Sales Orders
Sales Orders - Countries with sales order restrictions
Sales Orders - Summary by User
Stock
Stock - List initial stock levels and values
Stock - Count Stock Items in each Category
Stock - List stock having duplicate Other Ref
Stock - Quarantined Items
Stock - Stock Items where buy_price or sell_price is zero

List Pane

  • List Pane: The List Pane at the bottom left hand corner of the window displays all the List Manager results. Double clicking on the list results will populate the SQL Script Grid with a query. This can then be executed and the results used for a mail merge, including some financial information. The results can then be additionally queried if required.

Scripting area

The top part of this area has a scripting pane, where users can:

  1. Create a new query by dragging one or more rows from the field grid into the scripting pane; or
  2. Directly enter a SQL query into the scripting pane (see below for examples); or
  3. Load a previously saved query (from the Reports Pane) into the scripting pane.

The name of the currently loaded query is displayed at the top of the Data Query window prefixed with 'Reports: '.

Once executed successfully, the results will appear in the lower results grid, as shown in the following screenshot:

The Data Query window's scripting area, showing a query, together with part of the results grid.
  • the red X icon: removes any text from the scripting pane.
  • the "Execute" button: attempts to run the contents of the scripting pane as a data query.
    Note: the keyboard shortcut is F5.
  • Free Form SQL checkbox:
    UNTICKED: the system will NOT check the query's syntax.
    TICKED: (default) the system WILL check the query's syntax when the Execute button is pressed.
  • Hide ID Fields checkbox:
    UNTICKED: ID fields will be visible in the list of fields.
    TICKED: (default) ID fields will be hidden from the list of fields.

Results

The Results area displays the total number of rows that have been returned in the grid. The following buttons allow you to export the results:

  • The "Print Grid" button: opens the grid menu's "Print Grid" dialog box.
  • The "Export to CSV" button: opens the grid menu's "Send to Notepad" enabling the user to export the grid contents as a .csv file.

Some results, those with a green background, allow the user to Goto or double-click and drill down into the record. The following fields work in this way:

SORDER_ID
COMPANY_ID
INVOICE_ID
RETURN_ID / CRETURN_ID
STOCK_ID
PORDER_ID
SUPPLIER_ID
DELNOTE_ID
INVOICEP_ID
SRETURN_ID
KEYCODE_ID

Tables and Fields

Tables

The Tables grid lists the database tables that may be queried. The user can display tables by their initial character (A-Z) or all of them alphabetically (All Tables).

It is not possible, for reasons of commercial confidentiality, technical differences between platforms, and because the Khaos Control database is continually evolving as new features are added, to provide users with a copy of the database schema.

Fields

The Field grid displays the names of columns contained in the Table currently in focus. These columns can be added to a query by dragging and dropping them onto the scripting pane. If a join is required the system displays the 'Join Tables' dialog, allowing the user to specify the type of join (i.e. inner join or left join) and the field(s) to be used for the join.

Unticking the Hide ID Fields checkbox includes the hidden identity fields (commonly used to create links between tables) when listing a table's fields.

  • The filter condition popup allows the user to query by date and condition for example "<", "<=", ">", etc. By default it is equals "=".
    BtnDataQuerySelect.jpg: if a Fields field is dragged into the SQL grid, then this information will be returned on execution.
    Note: this depends on subsequent filtering.
  • BtnDataQueryOrder.jpg: enables the query can be sorted alpha numerically in a ascending order on execution.
    Note: the user must have selected the field before ordering it.
  • BtnDataQueryWhere.jpg: if a Fields field is dragged into the SQL grid, then the query can be sorted by logic using a filter condition.
    Note: The user must have selected the field before ordering it. The filter condition popup allows the user to query by date and condition. For example: less than "<", less than or equals "<=", greater than ">", etc. The default condition is equality "=". The LIKE field is used with wildcards so a string can be queried. This search is not as rigid as an 'equals to' search as it allows a range of results to be searched for anything that is defined within the wildcards. For example; %hotmail% would return any results where the queried field contained 'hotmail', this could be useful in this example as it would return email address like 'example@hotmail.co.uk' and 'example@hotmail.com', etc.

Sample Data Query Scripts

See Also

Visit KhaosWikiX at http://wiki.keystonesupport.net/wiki_kcx/ to see the following:

Contact the Khaos Team