It can be seen that within the enQuiry grid there are a number of functions which allow more sophisticated handling and tailoring of enQuiry functions when using the builder utility.
The titles of these are seen below in the following screen shot:
The above options will be described below.
Output
This function simply permits output of the chosen field depending on the check box. If the check box is ticked, the field will be output but if it is not to be included the check box for "output" should be left unchecked.
Expression
The expression field provides a pull-down list of all the fields available from the chosen data categories down the left hand side of the display, such as "Contact Details" and "Address Details".
The only fields available in the pull down list are those chosen in the queries corresponding to the chosen areas of interest, such as all the fields in "Contact Details" and "Address Details", if they are the only ones chosen.
An example of the pull-down list available is illustrated below:
This allows you to change the fields required so the query can be modified if so desired.
Aggregate
The aggregate field permits a set of standard SQL functions to be utilised in order to perform various actions on the data, such as obtaining the maximum or minimum values of the data. Other examples include calculation of an average, count number of items and sum of all values in the field. Any field can have an aggregate function performed on it. This is often used in conjunction with the "Grouping" field as items can be grouped according to the aggregate function used.
An example of the list of aggregate functions provided can be seen in the following screenshot:
Alias
In SQL, an alias is simply another name for a field to use in the query, e.g.
SELECT [Contact Details].[Entity Code], [Contact Details].[First Name] As name
FROM enSelect.QueryBuilder.[Contact Details] [Contact Details]
The "As name" phrase refers to the field using the identifier "name" instead of the default field name (First Name). Aliases are used when some fields are used in an expression e.g.
SELECT [Account].[Balance] + [Account].[Offset] as TotalBal, or simply to use as a different reference name for a report or simply to output the field under a different name e.g. SELECT [Account].[Balance] As bal.
If an alias is used the alias name is used rather than the original field name or expression in the output. An example of an alias (Business_or_Employee_Name) in use and the resulting output is shown below:
Sort Type
The sort type in any query for a field can only be of two types, these are ascending or descending order. The pull-down list allows you to choose which of these types you wish to sort the field by i.e. "Ascending" or "Descending".
Sort Order
The sort order dictates in what order the sorted fields have priory. 1 is the highest priority followed by the other ascending numbers.
Grouping and Filtering
In addition to simply outputting a set of required fields, data can be grouped to provide information based on a group of items which are similar. For example, you might want to output information relating to items by country, or by city.
The screenshot below illustrates a typical output of first names grouped by an address type. Each address type is listed before the "First name" of the entity and grouped together. As you can see each address type is grouped together and all the names belonging to each group are shown in the right hand column:
In order to group data in enQuiry it is first necessary to decide what data you would like to group the information by. The best way to explain this is by an example. The 2 fields in the control bar we are concerned with in order to obtain a grouping of data are the "Grouping" option and the "Criteria for" columns. These functions work hand in hand to provide a way of grouping data.
Let us take the previous example, as illustrated in the screenshot. In both cases the data has been grouped, first by "Address Type", and then by "First Name". Note that in both cases the pull-down list in the "Criteria for" column is set to "Groups". The other option in the list is "For values". The difference relates to the way the next column on the right, "Criteria" is used to filter data. If the "Criteria for" field is set to "For groups" then any criteria entered into the "Criteria" field is relevant for the group of items only as a whole, while the "For values" option means that any filtering expression in the "Criteria" field will relate to values of the individual field, and not the grouping.
The flow diagram below illustrates the process to make it a little clearer:
To summarise, whatever is entered into the "Criteria for" pull down list determines how the "Criteria" field is interpreted, when a filter is entered into that field.
For examples of grouping and filtering see the "Example Queries" section.
Criteria
As stated in the previous section, the criteria field is used to filter information about the data that is to be returned. For example, you may only wish to list information from "France".
Here are example outputs for a query designed to return a "Full Name" and country before and after the "Criteria" field has been utilitised to filter the output:
Before:
After:
As can be seen the "Criteria" field has been used to filter the output to return only full name details for entities in "France".
It may be the case that you need to filter by more than value in the criteria field, and this is where the "Or..." fields come into play.
Or...
The purpose of the "Or..." columns is to add more filtering capabilities to an existing query. For instance, if, as in the previous example you had chosen "France" as the criteria in the criteria field and you would like to add more countries to the query you can add some to the list. Therefore if you now wished to add "jersey" to "France" t he list of countries you can place the word "Jersey" in one of the "Or..."columns so that country is also included along with France.
Here is an example of how this would look for our previous query but including Jersey in the first "Or..." column:
As can be seen the query now returns information for both the "France" and "Jersey" countries.
The remaining "Or..." fields are place holders for additional criteria. Note that any field can be filtered by more than one "Or..." criteria and "Criteria" field resulting in a result set that is filtered according to your requirements.