A query written in Structured Query Langauage (SQL) is the most common technique for extracting information from a database. However, this language is awkward for Users with no SQL programming experience.

The Query Builder is a tool for building database queries without any knowledge of SQL programming.  The builder presents database information in simple-to-use screens where the User selects their requirement.  A preview option is included in the builder so that at any stage of the development, the configuration can be verified.

There are two broad categories of queries supported by the Query Builder:
Standard, a  query that returns data directly from selected columns
Cross Tab, a query that returns data by cross tabulating selected columns.

Both categories of queries provide a rich set of features for the User. In addition to the database columns, the User can create expressions, functions and summaries and use them as columns in the query.  To ensure that only the information of interest is retrieved from the database, a comprehensive filtering option is provided.

Configuring a standard query with summary information

Previewing the query results from a standard query

How do you select which category of query to use?  The answer is personal preference.  In the above display, a standard query is constructed to retrieve summary information on four tags over three days.  When the results are previewed, we find that we get six rows of data for each day; a reflection on how the data is stored in the database.  

Suppose we needed all the values for a particular day on a single row, how could this be accomplished?  The answer is to build the same query as a cross tab.

Configuring a cross tab query with summary information

Previewing the query results from a cross tab query

In addition to retrieving data, most Users would also like calculated values such as averages.  However, performing calculations using SQL is not always straightforward.  This is especially true if the User requires information that contains 'running values', intermediate sub calculations and grand calculations.  The Query builder has been designed to tackle complex problems of this nature.

Sub calculations add richness to the report

Preview of daily summary calculations

Once a query has been defined, it can be added to a report using either the Connection Configurator or the Layout Expert.

© 2006 SyTech, Inc. All Rights Reserved.