CCH KPI Monitoring : Query Designing

Record-Level Queries

The first step in any query is to identify its purpose. For example:

“When a ‘critical’ service call is logged, send an email alert to the customer’s
account manager.”

Consider what you’d like the alert message to look like. For example:

Hello, Don Farber:

The following critical support call was just logged for client Acme Incorporated:

Contact:  Tom Jones
Call Received:  09/14/2001  1:35 PM
Type of Problem:  Fatal error message
Status:  Open
Assigned to:  Lisa MacKinnon
Description:  Error 1094 during login.

You should identify the data fields you wish to put into the alert:

Hello, [salesrep_name]:

The following critical support call was just logged for [customer_company]:
Contact:  [customer_name]
Call Received:  [ticket_log_date]  [ticket_log_time]
Type of Problem:  [ticket_type]
Status:  [ticket_status]
Assigned to:  [ticket_rep_name]
Description:  [ticket_description]

By taking these steps, you’ll know what fields the query needs to retrieve data from. And by reviewing the event’s description (“. . . when a ‘critical’ service call is logged”) we can identify the conditions that the query needs to check for:

• Calls with a priority of ‘critical’
• Calls that are received today

In terms of the recipient of an alert, in some cases, recipients are “hard-coded”; e.g., notifying your CEO or CFO. In other cases, you might wish to notify a person who is associated with the record being triggered as in the following example: 

“Send an alert to the customer’s account manager when a ‘critical’ service call is logged.”

In this scenario, the alert recipient will depend on the client for whom the critical call was logged. If you look back on the alert message, you can see that the person who should receive the alert message is “[salesrep_name]”. 

However, “[salesrep_name]” is not an email address; if you look further into the salesrep data, you might find a field called “[salesrep_mail_address]” – and this is the field that contains the address that you wish to send the alert to.

KPI Monitoring events can also generate & deliver forms & documents (such as invoices or statements) as well as reports. 

If an event will generate a report, review the report to see if it uses parameters (selection criteria). If it does, the query must retrieve the data which will get used as (passed into) the parameters. For example, an event might trigger an A/R Aging Report for an overdue client. If your report has a parameter on “client ID”, you need to make sure that your query retrieves the data from the “client ID” field, so that value can be automatically passed into the report’s parameter.

The last preparatory step is to identify whether an event will execute workflow to add or update records in an application database. If so, your query must include the data fields required for use by the workflow update procedures.

Aggregate Queries

Aggregate queries are designed much in the same way as record-level queries except that aggregate queries will use one of the following 5 options:

• Count  (e.g., fewer than 5 assigned leads)
• Summarize  (e.g., less than $10,000 in sales)
• Average  (e.g., average response time is 47 minutes)
• Minimum  (e.g., the first order placed by a client)
• Maximum  (e.g., the most recent delivery)

Aggregate queries should be designed with the same considerations as record-level queries except for the fact that an aggregate query is going to be triggered by a condition related to a “group” of matching records. For example:

“If a client has more than 3 overdue invoices, send an email alert to the client’s account manager, attach an “Open Receivables” report for that client, and schedule a phone call for the account manager to contact the client.” 

And – correspondingly – the alert message might look like this:

The following client has more than 3 overdue invoices:

Client:  Jim Ramey
Number of Overdue Invoices:  5
Total Value of Overdue Invoices:  $27,960.28

Description

The “Query Description” field contains the name of your query. Make your names short, consistent, and descriptive. We recommend starting the description with the item being monitored, followed by the condition. For example:

Jobs; Shipped Today

The “Display Description” will default to the same value as the query’s description.  

Click “Active” to enable a query to be used in an event.

QUERY DESCRIPTION

Tables & Columns

The Columns tab is where you choose what database tables and views (and what columns within those tables/views) a query will retrieve data from. There are four things you need to consider:
What data might you wish to put into an alert message?
Even a query as simple as “jobs shipped today” would probably need to retrieve data from more than just the “job” table. The corresponding client data might be stored in an “account” table, and the client’s account manager’s details might be stored in a “salesrep” table.
Where are the alert addresses of the person(s) you wish to notify stored?
If you wish to notify a person who is “associated” with a triggered record, you need to include the table that contains their delivery address (such as their email address).
Will an event that uses the query execute any workflow actions (e.g., will it need to add or update information in an underlying database)?
Since an event can execute workflow to add or update database records, you need to consider what data the workflow needs access to.
Will an event that uses this query also execute a Crystal or SSRS report?
If an event is going to execute a report, see if the report uses “parameters” – as those parameter values will have to come from the event’s query.
To select columns for a query, you first need to select the table(s) that contain those columns. Go to the list of Available Tables and click on green “Plus” sign to the left of each desired table. (You may also double-click on the desired table name.)
If you accidentally select the wrong table for a query and wish to remove it, go to the list of Selected Tables and click on the red “Minus” sign next to the table you wish to remove.

Choosing the Same Table Multiple Times (Table Alias)

Consider the following scenario:  You want an alert about invoices that are overdue. Each invoice is associated with a customer and a salesrep. A table called “email” stores the email addresses of both the client and of the salesrep.
In order to get both email addresses, KPI Monitoring needs to access the “email” table twice – once for the customer’s address, and once for the salesrep’s address. But – in SQL -- you cannot choose the same table twice in a query, so KPI Monitoring lets you create a “table alias” for just such occasions.
When you select the same table multiple times, you are given the ability to specify an alias name for that table.

Columns

After you have selected the tables for a query, the next step is to select the columns (data fields) that you might wish to use in your event. Consider the following when selecting columns:

1.  What data do you want to put into your alert messages?
2. Is the address of a person you wish to notify contained within a database field?
3. Will your event need to pass data into a report’s selection criteria?
4. Will your event need to use data in an application update (workflow)?
5. Will your event need to “sort” matching records by certain fields?

How to View & Select a Table’s Columns

Under “Selected Tables”, click on the blue right-arrow to the right of the table whose columns you wish to display (or double-click on the desired table). The columns of the selected table will appear.
To add a column to a query, go to the list titled “Columns of Selected Table” and either click on the green “Plus” sign to the left of the column name or double-click on the desired column.

As you select columns for a query, you may need to move to ensuing pages to see all your selected columns – which will look like the following:

COLUMNS SELECTED FOR A QUERY

 

 

Selecting “All” Columns

 

 

To include all columns for a selected table, click on the green +All button.
Although this is an excellent way to see all data from a specific table, this is not suggested for normal query building as it makes it more difficult to see (and select) only those columns needed for an event.

Editing/Removing a Selected Column

To edit a column (to include calculations, change its format, etc.), go to your list of Selected Columns and click on the blue “Edit” button to the left of the selected column’s name.
You will be taken into a special editing window, complete with advanced calculation functions to choose from.
To remove a selected column from a query, click on the red “Minus” sign to the left of the selected column’s name.

Column “Type” 

Record-level and field-level change queries always use a column type of “Normal”. The other column types are detailed under the “Aggregate Queries” section of this chapter.

“Customized Name”

When an event’s alert message is designed, you refer to the query’s columns by using the columns’ “Customized Names.” This is important, as some applications have cryptic names for their columns. For example, an “activity” table may have a column called “ddate” – designed to store an activity’s “due date”.
Using a column’s “Customized Name”, you can create your own “label” for a field, for example “due_date” for the scenario above.
When you select a column for a query, KPI Monitoring will take the column’s name and place it in the “Customized Name” field. You can then modify that value. 
Here are some notes about using customized names:

• Do not include blank spaces. (use “due_date” and not “due date”).
• Keep customized names under 25 characters.
• Do not begin a customized name with SQL reserved words, such as “sum”, “sub”, “add”, “avg”, “min”, “max”, “total”, “trim”, and “format”. 
• Do not have multiple fields with the same customized names; you won’t be able to tell them apart.
• Use only letters and numbers in a customized name; do not use symbols.
• Do not change the default Customized Name of a query’s “unique” column if the query tracks changes to the value of one or more database fields.

A Query’s “Unique” Column

In KPI Monitoring, you can specify whether an event will trigger just once for a record that meets its criteria, or will trigger repeatedly, each time the event runs, for as long as a record continues to meet the event’s criteria. For example:

• Trigger Once:  At 9 AM, activity #123 is overdue and triggers an alert. At 10 AM the activity is still overdue, but since it already triggered, it does not trigger another alert.
• Trigger Repeatedly:  At 9 AM, activity #123 is overdue and triggers an alert. At 10 AM – and every hour thereafter, it triggers and alerts again (until the activity is no longer overdue).

Although the option for one-time versus repeat triggering is specified on the event level, you need to do something on the query level to enable this option. 
To enable records to trigger only once, KPI Monitoring has to “remember” a triggered record. This is done by identifying which column uniquely identifies a triggered record. (This is often referred to as a record’s “key”; in KPI Monitoring this is referred to as a query’s “unique” column.)
If you do not specify a query’s unique column, any event that uses this query will automatically be in “repeat” mode.
If you do specify a query’s unique column, you will have the option (on the event level) to specify one-time or repeat notifications.
To specify a query’s unique column, go to your list of selected columns for a query and put a checkmark in the “Unique” box of the one column that uniquely identifies each record (such as a “client ID” or an “order number”). 

How a Record Becomes “Un-Triggered”

Consider an event that runs daily and is set to “not repeat” its alerts about customers who get placed on credit hold.
On Monday, Acme Corp is placed on credit hold and an alert is sent. 
On Tuesday, Acme Corp is still on hold. The event (set to “not repeat”) runs but does not trigger another alert about Acme.
On Wednesday, Acme is taken off of hold. The event runs, sees that Acme no longer meets the event’s criteria, and “clears” (“un-triggers”) the Acme record. 
On Thursday, Acme is once again placed on credit hold. The event runs, is newly triggered, and another alert is sent.

Multi-Field Unique 

To create a multi-field unique, you’ll need to concatenate (combine) the values from the two (or more) fields.
The “type” of fields you concatenate must be “character” or “string” type fields. To concatenate fields of other types (e.g., numeric or date), you must convert the fields into character format first. (See “Field Editing Notes”.)
To concatenate fields (for SQL Server), follow these steps:

1. Click on the button called “Add Calculated Column”.
2. Choose the first database column that you wish to comprise your concatenated unique.
3. Click in the Calculated Column Workbox and enter a plus sign (+) after the column name you have selected.
4. Choose the second database column that you wish to comprise your concatenated unique. The concatenated column is now complete and looks like the following:  

sysdba.orders.order_number+sysbda.orders.order_lineno

(The concatenation symbol for an Oracle database is two vertical bars (||).)

Order Line Item Unique

In many ERP systems, an order’s details consists of a “header” and “line item details”. Because of this, every order detail line has the same order number associated with it.
In such cases, make sure that the query’s unique consists of both the order number AND the line number. That way, each detail line has its own unique value.

Calculated Fields

Examples of calculated fields are:

• The number of days until an invoice is due
• Unit sales price times quantity ordered
• Percentage of credit limit remaining

Calculated fields may be used in a query as well as in an event’s alert messages, report selection criteria and workflow actions.
To create a calculated field, click on the “Add Calculated Column” button. To add a calculation to a selected column, just click on the blue Edit button that appears to the left of that column.
The Calculated Column Editor provides you with a list of frequently-used arithmetic calculations at the top of the window; you may use any valid arithmetic symbols and functions (such as ‘+’, ‘-‘, ‘/’, and ‘*’) as well as any valid SQL functions that are approved for use with the database and data provider you are using.
The editor also provides you with a list of all columns from your selected tables, as well as KPI Monitoring’s complete list of date substitution variables. The “workbox” at the bottom of this window is where your calculation syntax is formatted.

CALCULATED COLUMN EDITOR

If you select any of the functions from the list of calculations, KPI Monitoring will automatically format that function for you, and all you need to do is select any remaining elements for that function. 

Example #1: a calculated field that computes the gross margin percent of an item.
The gross margin is calculated by determining the difference between an item’s price and cost, multiplying that amount by one hundred, and then dividing that amount by the item’s cost. You would do the following in the Calculated Column editor:

Double-click on the column that contains an item’s price; you’ll see something like:
INVENTORY.ITEMPRICE

In the workbox area, add a minus sign after that field
INVENTORY.ITEMPRICE-

Double-click on the column that contains the item’s cost.
INVENTORY.ITEMPRICE--INVENTORY.ITEMCOST

Type in parentheses around this expression.
(INVENTORY.ITEMPRICE--INVENTORY.ITEMCOST)

Type in *100 at the end of your calculation.
(INVENTORY.ITEMPRICE--INVENTORY.ITEMCOST)*100

Type in an additional set of parentheses around the entire expression.
((INVENTORY.ITEMPRICE--INVENTORY.ITEMCOST)*100)

Type in the division symbol “/” at the end of your calculation
((INVENTORY.ITEMPRICE--INVENTORY.ITEMCOST)*100)/

Double-click on the item’s “price” field again.
((INVENTORY.ITEMPRICE--INVENTORY.ITEMCOST)*100)/ INVENTORY.ITEMCOST

Click on “OK”.

Example #2:  A field that determines the number of days until an invoice is due. 
In Microsoft SQL Server, this calculation uses of a function called “DATEDIFF”, which calculates the number of days between two dates. Thus, you would click on the “Add Calculated Column to Query” button and choose the function “DATEDIFF – Difference in Days between two dates”. The following will appear in the workbox area:

DATEDIFF(DAY,$Column$,$Column$)

Since you want to calculate the number of days between “today” and an invoice’s due date, select the “%Current Date%” variable and follow that by selecting the column that contains an invoice’s due date. Your result will look like this:

DATEDIFF(DAY,'{%Current Date%}',dbo.tarInvoice.DueDate)

Calculated Field Notes

Use a query’s “Preview” to ensure that your calculation syntax is correct.
Be sure to modify the “Customized Name” of a calculated field. 
A single query can have multiple calculated fields within it.
Copy and paste the contents of a calculated field by using CTRL/C to copy the contents and CTRL/V to paste them.
Verify the format in which an application stores its “date” or “date/time” values. Even though the values might be displayed in one format (e.g., mm/dd/yyyy), your app might store them in a different format (e.g., yyyymmdd).

Date-Related Functions

Most databases (and drivers) allow you to calculate the difference between two dates. The syntax of these functions is dependent on the database (and provider) you are using. 
Here are a few examples:

Invoices Due Within ‘x’ Days (MS SQLServer)
datediff(Day,'{%Current Date%}’,ARinvoice.DueDate)

Client Whose Last Order Was More Than ‘x’ Days Ago (MS Access)
datediff('d',"Invoice"."DateCreated",'{%Current Date%}’)

Quotes Due to Expire Within ‘x’ Days (Pervasive SQL)
datediff(Day,oehdr.CancelDate,’{%Current Date%}’)

The number of hours between a date/time field and the current date/time
datediff(hour,table.column,'{%Current Date%}’

Add 3 hours to the current date/time
dateadd(hour,3,'{%Current Date%}’)

Add 3 hours to the value in a database field:
dateadd(hour,3,table.column)

Configuring a Query to Deliver to Multiple Dynamic Addresses

A common requirement in query design is when you wish an event to send alerts to multiple addresses that are drawn from database values. For example, an event monitoring open support tickets might need to notify both the rep whom the ticket is assigned to, and the salesperson who is assigned to that client’s account.
Although an event supports multiple dynamic delivery addresses (such as the “send to”, “cc”, and “bcc” email addresses), only a single query field may be referenced for any one of those options (i.e., you can specify only one query column for the “send to” addresses).
In order specify multiple addresses for one of these options, you need to combine those addresses into a single field such as:

sysdba.ACCOUNT.EMAIL+','+sysdba.USERINFO.EMAIL
(Multiple email addresses are separated with commas.)

Trimming “Padded Spaces” from a Value  

Sometimes column values contain “leading” or “trailing” spaces and you may not want KPI Monitoring to include these spaces when it retrieves the column’s value.
Depending on the database being monitored, you can use the following functions. (The following are for SQLServer databases.)

• rtrim:  Remove all following spaces from the right side of the field value
• ltrim:  Remove all leading spaces from the left side of the field value
• trim:  Remove all surrounding spaces from both sides of the field value 

The format for using these functions is:

trim(table.column)   (SQLServer)

Retrieving ‘x’ Characters of a Value

Sometimes you might not want to retrieve the entire contents of a field; you may (for example) wish to retrieve only the day of a person’s birthday (as opposed to the day, month, and year). 
To do this, you would use the “substring” function as follows:

Substring(table.column,starting position,length)

For example:

Substring(account.name,1,20)       (SQL Server)

This would retrieve the first 20 characters of an account’s name.

Converting a Field’s Data Type

If you need to convert a field from one data type (such as numeric) into another type (such as character) you would use the “convert” function as shown in the following example:

Convert(char(20),account.createdate,0)       (SQLServer)

This converts a date field into a string field.
For an Oracle database, the command is:

TO_CHAR(TABLE.NUMBER,'9999999')

This turns a numeric field into a string.
For a Pervasive SQL database, the command is:
              convert(ARTRAN.OrderNumber,sql_char)

Retrieving the “Top x” Number of Records

Another useful function is the ability to retrieve only the top ‘x’ number of records that meet your criteria. (For example, the top five sales this week.) The syntax for doing so (under SQLServer) is:

Top x table.column

As in:

Top 10 sales.amount       (SQLServer)

Creating a Column that is a Label + Field Value

In some cases, you may wish to create a column that contains both a field “label” as well as the field’s value. This is useful if you have a field whose label you do not wish to appear in an alert message if the value of the field itself is blank (or null).
For example, let’s say that you have an ERP system in which you have four types of line items:  inventory, non-inventory, miscellaneous charges, and comment lines. Only inventory items contain a warehouse code, and for those lines you wish to include a section in your alert message that says

WHSE:  Boston

(where “Boston” is the name of the warehouse from your ERP database)
Since the three other line types do not use a warehouse, you do not want the label “WHSE:” to appear in the alert message for those lines.
The answer is to combine the label (‘WHSE:’) with the value from the warehouse field. Thus your query column would look like the following:

'WHSE:'+'  '+InvDetail.Whse

And – as you’ll learn in the “Event Designing” chapter, you can tell KPI Monitoring not to include (or allocate space to) any field whose value is null.
You can add labels to only character-based fields.  

Creating a Column that Contains a Dynamic Date Value

KPI Monitoring includes many date substitution values, such as “current date”, “last Monday date”, “next Friday date”, and so on. To use one of these fields as a column within a query, surround the variable with single quote symbols as in the following: 

'{%Last Friday Date%}'

Retrieving Only the Month or Day from a Date Field

To retrieve just the month or day from a date field, use the following syntax: 

month(hrpersnl.p_birth)       (SQLServer)
day(hrpersnl.p_birth)            (SQLServer)

Creating a Column that Contains a Hard-Coded Value

To create a column that contains a hard-coded value such as an email address (e.g., smith@KPI Monitoring.com), follow these steps:

1. Add any column to a query.
2. Edit the column you just added and delete the existing column name.
3. Type in a single quote (‘), followed by the hard-coded value, followed by another single quote (as in ‘smith@KPI Monitoring.com’).
4. Go to the “Customized Name” for this column and enter an appropriate name for this field.

Creating a Column Pointing to a File Spec

One of KPI Monitoring’s special functions is to create a column whose contents are a file spec – that is, the column refers to the name and location of one or more files that you wish KPI Monitoring to deliver. Multiple files (referenced within the column’s value) must be separated by commas.
The file spec referred to within this column must use either the actual server, disk and directory spec, or must use UNC format; do not refer to mapped drives.
When you add such a column to a query, the Customized Name of that column must be “attachment_files” (case sensitive).
When an event uses a query that contains such a column, you’ll have the option to instruct KPI Monitoring to deliver the corresponding file(s) via email, webcast, or Copy/FTP. 
In a web service call or VB script, if a response parameter was named “attachment_files”, KPI Monitoring will use that as the name of the file to be attached.

Creating a Column Pointing to a Dynamically-Named File Spec

You can modify the use of the above “attachment_files” option to specify a dynamically-named file by referring to a column name (e.g., “{order_no}”) within the associated file’s name. 
For example, you might have multiple files that store customer order tracking details in your ‘C’ drive. These files are named according to the order they relate to as in:

• Tracking_100.pdf
• Tracking_101.pdf
• Tracking_102.pdf
  (and so on)

(The above 3 files contain tracking details for orders #100, #101, and #102.)
As long as your query retrieves the “order number” value (e.g., in a column named “orders.order_number”), you can have KPI Monitoring to pick up the files associated only with the order records that meet your query’s criteria. In this case, you would modify the name of the selected column to read:

'C:\Tracking_{order_number}.pdf'

You refer to the customized name of the desired column, within curly braces.  KPI Monitoring will automatically substitute in the appropriate order numbers and retrieve the corresponding PDF files. 
Make sure that the customized name for the column that retrieves these files is “attachment_files” (do not include the quotation marks) and be sure that the options to “include files”, include “reports”, and “reports must wait” are ALL checked ‘on’ in any events that use this query.

“If – then – else” Logic

Consider the following logic:

• “if the contact’s email address is blank, use the default account email address”
• “if the contact’s email address is blank, use the salesrep’s email address”
• “if the value of the credit status field is “H”, I want the alert message to read “Hold”

To check the value of a field and then modify that value (or use a different value) is very useful. The SQL syntax for doing so is:
CASE table.column when 'value' then table.column+'value' else table.column END

If, for example, you have a column in a table that has values of I, C or D and you want to display them as IN, CM, and DM in your alert message, you’d create three columns in your query as follows:

• CASE table.column when 'I' then table.column+'N' else table.column END
• CASE table.column when 'C' then table.column+'M' else table.column END
• CASE table.column when 'D' then table.column+'M' else table.column END

The preceding syntax is for SQL Server databases.

Removing Columns from a Query

To remove a column from a query, go to the “Selected Columns” window, click on the red Minus sign to the left of the field you wish to remove.

Return Distinct Rows of Data Only

What if you wished to receive a list of all customers who placed orders last month – but didn’t wish to see any duplicate records (such as if a client purchased multiple times during the month)? This is referred to as “returning distinct records only” (eliminating any duplicate records) and is available via a checkbox in the “Selected Columns” window.
“Return distinct” is based on the columns chosen for a query, so (using the previous example of client orders), if you choose just the client’s name, your results will show one record per client. If, however, you choose (as columns) both the client name and their order number, a client who purchased multiple times during a month would appear (in the query’s result set) multiple times, as each order number is “distinct”.

Sub-Queries (Embedded Selects)

One of the more advanced query design functions is the ability to create a column whose value is the result of another query. This is referred to as “sub-queries” or “embedded selects”.
Embedded selects require advanced SQL syntax knowledge, as each column name would be edited to include a SQL select statement. 

To create, view, or modify embedded selects, use the Calculated Column Editor, as it gives you a full-screen editor in which to create your embedded select statement.
KPI Monitoring has an EventPak that illustrates the use of embedded selects. To receive this Pak, please contact Customer Support using the form on our website.

 Sort Type (Record Sorting Order)

When a query runs, it retrieves matching data records. You can specify the order in which these records will be listed and processed. (And this is the order in which those records will be shown in any corresponding alert messages.) This is the query’s “sort type.”
Any fields that were selected for the query’s “Columns” are available for use in its sorting order. To select your sorting order, go to the “Selected Columns” window, locate your first field for sorting purposes (you can have multiple levels of sorting), click in the “Sort Type” (it will default to “None”), and select either “Ascending” or “Descending”.
The selected column will now appear in the “Sort Order” window. 

The “Sort Order” window will appear like the following:

QUERY “SORTING” (BOTTOM-RIGHT)

The Effect of Sorting on “Grouped” Alert Messages

“Sorting” controls the order in which matching records are displayed in alert messages. This is especially important when you want an event to “group” records together – such as sending each employee an alert that lists all of their overdue activities or sending a customer an alert listing just their orders.

In order to send alerts where similar records are grouped together (i.e., all of an employee’s activities, all of a customer’s orders) you must use Sorting to group those records accordingly within the query’s result set.
(In the above examples, if you do not sort the records, an employee or salesrep might receive multiple alerts, each alert containing one, or just a few of their records, instead of a single alert containing all of their records.)

In the preceding examples, the “overdue activities” query would sort by rep, the “customer order listing” query would sort by customer number (or name).

Links

If you have chosen more than one table, you must tell KPI Monitoring how those tables are linked (or “joined”) together. It is essential that you choose the query’s primary table first in your series of links. 
In the example of a “Jobs; Created Today” query that uses the “jobs” and “organizations” tables, this query’s primary database table is the “jobs” table.
Link your tables by doing the following:
Click on the “Links” tab. You will see the following:

QUERY “LINKS”

To add a new link:

o    Under “Table Name,” select the query’s primary database table.
o    Under “Linked to Table Name,” select (or confirm) a corresponding table.
o    Under “Column Name,” select the “link from” field.
o    Select the “link to” column name. If a column in the “Link to Table” is found with the same name as the “Link from Table,” that column will be loaded into the “Linked to Column Name”.
o    The link type will default to “left outer join”; leave this unless your database schema specifically requires a different type of join.
o    Click on the green Plus sign to add the link.

Once one or more links are added to a query, this tab will look like the following:

SUCCESSFULLY ADDED LINKS

If you have more than two tables that require linking, repeat the above process until all tables are linked. In most cases you will have a number of links that is equal to one fewer than the number of tables the query is using.

Manually Input Table Linking

Depending on the applications, databases, and connection method (.NET Provider, OLE/DB, ODBC) you are using, you may come across an instance where KPI Monitoring’s default linking syntax is not sufficient for the type of “join” you wish to create.
To specify your own table linking syntax, follow these steps:

1. Join the two tables together using the table linking wizard.
2. Click on the SQL tab and copy the “join” syntax (starting after the word “from” and ending before the ‘)’ symbol. Do not include the parentheses that begin and end the join syntax. E.g.,
   dbo.Jobs LEFT OUTER JOIN dbo.Organizations ON dbo.Jobs.jmpCustomerOrganizationID = dbo.Organizations.cmoOrganizationID
3. Click back on the Links tab, click on the checkbox titled “I would like to manually input the table linking.” A blank line will appear.
4. Paste the “join” syntax that you copied from the SQL tab into this line.
5. Edit this syntax to perform the join as you require.

You may also directly edit the field names in the “Tables Linked Together” grid, detailed in the following section.

Modifying Your Links

Do not select “I would like to manually input the table linking”.
Under “Tables Linked Together”, locate the link you wish to modify.
Click your cursor in any of the “column name”, “linked to column”, or “join type” fields.
Manually type in (or select) the desired syntax. Any valid SQL syntax is supported.

Multi-Column Links

To link one table to another using more than one column, follow these steps. 

1. Create a link using the first of the two fields you need to link with.
2. Click in the "Linked to Column" field; an edit-capable window appears.
3. Add a blank space after the column name.
4. Type in "and table1.2nd_link_column = table2.2nd_link_column" 
5. In place of "table1.2nd_link_column", key in the secondary linking column in the first table, and in place of "table2.2nd_link_column", key in the secondary linking column in the second table. Do not include the quotation marks in your syntax.
   
   A MULTI-COLUMN LINK

Filters

The most important part of query design is the “conditions” that will cause the query to trigger an event. These conditions are referred to as “filters” and “sub-filters”.
There are four general concepts of filter design. They are:

• What column values do you need to test
• What “operator” will a filter use
• What will the column value be compared against
• Should a filter be parameter driven

The following sections address each of these concepts.

Available Column Values

Filters can check the value of any column within any tables or views within a database. They can identify new records, records that meet specific criteria or thresholds, and can also check to see if the value of a field has changed.

Operators

A filter can use the following operators (along with “is” or “is not”):

• Equal to (priority is equal to “high”)
• Between (use “greater than or equal to” and “less than or equal to” instead)
• Greater than (amount is greater than 100)
• Greater than or equal to
• Less than (amount is less than 100)
• Less than or equal to
• Like (contains the string of letters “brit”)
• Null
• One of (priority is one of “Critical”, “High”, or “Medium”)
• Starting with (starts with the letter “brit”)

A filter can also use one other operator:
Value has changed since the last time KPI Monitoring checked it

Compare Values

A filter can do much more than compare the value in one field against a constant value (e.g., test for a priority of ‘high’). Here are some examples of other tests that can be executed by a filter:

• Test a field against another field (sale price is less than the cost of goods)
• Test a calculated field against a constant value (Sales amount * sales probability is greater than 10,000)
• Test one calculated field against another calculated field (percentage of damaged items > percentage of undamaged items)
• Test a numeric field against a range  (sales probability is greater than 60% and less than 90%)
• Test a date field against a date variable (forecast close date is equal to “today”)
• Test a calculated date value against a threshold (due date is within 7 days)
• Test a calculated date field against a variable (expire date is this month)

Parameter-Driven Filters

Consider this scenario:
Your sales manager wishes to be alerted about pending sales greater than $25k. Your CFO wants to know about sales greater than $50k. And your CEO wants to know about sales greater than $100k.
Do you need three different queries? The answer is no.
You can design one query called Sales > ‘x’ Dollars.
This is a “parameter-driven” filter. Three different events can each use this same query but specify a different triggering threshold (sales amount) on the event level.

Filter Components

When you click on the “Filters” tab you will see the following:

QUERY “FILTERS”

When creating a filter, you can select a specific column (from the list of “available columns to filter”) whose value you wish to test, and/or you can select one or more columns (from the list of “if column values have changed”) should you wish to monitor a column for a change to its value. (See “Value Change Filters”).
To select an available column to filter, double-click on the desired column. To create a filter based on a calculated column, click on the “Add Calculated Column” button. To edit a selected column (such as to add a calculation to it), click on the blue “Edit” button; to remove a previously selected column, click on the red Minus sign.

Comparing a Column against a Constant Value

Example:  “ship via = FedEx”

COMPARING A COLUMN VALUE TO A CONSTANT VALUE

You can manually enter the compare value, or you can choose it from a drop-down list. KPI Monitoring is case-sensitive. To select the value from a list, follow these steps:

1. Click on the green “…” button in the “Compare Value”field.
2. Click on the list button under the heading “Type in a Value or Choose from dropdown List”. This will display a list of possible entries for this field.
3. Choose the value that you wish to compare the column to and click on “OK”.

   The dropdown list shows values that have appeared in this field in the underlying database. It does not display all possible values that could appear there.

Comparing a Column against another Database Field

Example:  “shipped date is later than the required date”

COMPARING ONE COLUM’S VALUE AGAINST ANOTHER COLUMN’S VALUE

To do this:
Click in the “Compare Value” field and delete the ‘?’ that appears there.
Type in the full name (“table.column”) of the field you wish to compare the selected column to (e.g., “dbo"."Orders"."RequiredDate”).
Go to the “Type” field and select “Literal” (so that KPI Monitoring recognizes the ‘compare value’ as a database column name).

Comparing a Column against a Date or Numeric Range

Example:  “order total is greater than $99 and less than $999”

COMPARING A FIELD AGAINST A DATE OR NUMERIC RANGE

A common request is to see if records fall within a date or numeric range. Although you can use the “between” function, use of this operator is not suggested, as this operator tends to execute less efficiently than using a combination of “greater than or equal to” and “less than or equal to”. Additionally, not all database query methods support “between”, and this operator cannot be used in parameter-driven filters.
To create a range filter, follow these steps:

1. Select (as filter #1) the field that you wish to perform a “range” test on.
2. Choose the operator “greater than or equal to”
3. In “Compare Value”, remove the ‘?’ symbol and key in a “beginning” range (such as a “start date” or lowest allowable threshold).
4. Make sure the “And/Or” option reads “And”.
5. Add a second filter on the same field as filter #1.
6. Choose the operator “less than or equal to”
7. In “Compare Value”, remove the ‘?’, key in an “ending” range (such as an “end date” or highest allowable threshold).

Comparing a Column against a Date Substitution Variable

Example:  “order date = today”

COMPARING A FIELD AGAINST A DATE SUBSTITUTION VARIABLE

Many queries test the value of a date field against a value such as “today”, “tomorrow”, “last week”, or “next month”. KPI Monitoring provides a wide variety of “date substitution variables” that can be used as the “Compare Value”.
To use a date substitution variable as a compare value, follow these steps:
Click on the “…” button in the “Compare Value” field. In the resulting window, look for the list of “Variables to use in calculation”. 
There is a wide variety of variables to choose from. You will see multiple formats for the same variable. The choices without any formatting suffix will use your system’s regional settings for its default date format.
Double-click on the value that you wish to compare the column to and click on “OK”.
Be careful when using the “current date” variable if you are comparing it against a field that contains both the date and time. A field that contains today’s date and time will always be greater than (and not “equal to”) the “current date” variable.
You can address this either by configuring your filter see if the date/time field is greater than or equal to today’s date, or by configuring your filter to see if the date/time field is greater than “{%Current Date%} 00:01” and less than “{%Current Date%} 23:59”.

Identifying “New” Records

You can configure a filter to retrieve records with a create date of “today”, and since KPI Monitoring remembers what records have already been triggered, it will not notify you repeatedly about the same records.
But KPI Monitoring has a special date variable that makes this process more efficient – it’s a variable called “Last Date/Time Event Checked”. This variable allows a query to retrieve records that have been added to your database only since the last time KPI Monitoring ran the event.
This is particularly useful if your database is quite large or you are running an event very frequently (e.g., every hour or less).

Pervasive & Providex Date Formatting

PSQL and Providex databases typically store their dates in "YYYY-MM-DD" format. Thus to compare a date value from one of these databases to a date variable, you would typically use a variable which shows the matching format, such as:

{%Current Date YYYY-MM-DD%}

Parameter-Driven Filters

Requirement:  “3 events; for invoices coming due in 7, 14, and 30 days”
Sometimes it’s more efficient to create queries whose triggering conditions are specified on the event level as opposed to (or in combination with) a query’s filters. For example:
Invoices due for payment in the next 7, 14, and 30 days
In this scenario, you could create 3 queries (one query for each triggering threshold), or you could create 1 query – where the “number of days” is a threshold (“parameter”) specified on the event level. A “parameter-driven” query enables you to use the same query in multiple events, each event having a unique triggering parameter (such as ‘7’, ‘14’, or ‘30’ days).
A parameter-driven filter is represented by the ‘?’ symbol in the Compare Value field of a query – the ‘?’ indicating that the compare value will be specified on the event level. 
Events using parameter-driven filters will require that a compare value be specified at the time that the event is configured. Once the event is configured, the parameter will have a specific value which is used every time that the event is executed.
The operators “one of”, “between”, and “null” may not be used as parameter-driven filters.
To set up a filter to be parameter-driven, follow these steps:

1. Select the column to be filtered and its operator.
2. Make sure that a ‘?’ appears in the “Compare Value” field.
3. In the “Columns selected for filtering” window, scroll the window to the right until you see the field called “Optional Prompt.” The optional prompt is the text that will appear (on the event level) when this query is selected for use with an event.   (The “optional prompt” appears within an event using a parameter-driven query filter. The “prompt” refers to the text that will inform a person that a triggering value is required for this filter, such as “How many days until an invoice is due?”)

For a parameter-driven filter, delete the default optional prompt text and key in your own text, such as:
o    “Select a Priority:”
o    “Status is:”
o    “Date Range Begins” or “Date Range Ends”. (This requires two parameter-driven filters on the same ‘date’ field.) Your filters will look like this: 

MAKING A FILTER “PARAMETER-DRIVEN”

Checking a Column Against a “Changed” Value

Example:  “credit status has changed” 
KPI Monitoring can tell you when the value in a data field (column) has changed. When a “data change” event runs for the first time, KPI Monitoring stores the value of the field(s) being monitored for a change. 
The next time (and every subsequent time) the event runs, it compares the “current value” of the field(s) being monitored to the “old value” from the previous run. If the two values are different, the event is triggered and KPI Monitoring replaces the “old value” with the “new current value”. And the cycle begins again.
Note the following about “change-based” queries:
They may check for changes to fields in the query’s primary table only.
They must have a unique column, this column cannot be a calculated or edited column, and its “Customized Name” must not be changed from its default value.
If the database you’re monitoring supports “auditing”, it’s more efficient to build queries to monitor that database’s audit table rather than using changed-based queries.
If you have multiple events checking for a change in the same field, these events must be scheduled to run at the same time (e.g., daily at 9:00 AM).
You can check to see if a field value has changed, or if it has changed to a specific value.
If a query monitors multiple “change” fields, KPI Monitoring assumes an “OR” relationship between them.
If a query includes “change-based” criteria and standard filter criteria, KPI Monitoring assumes an “AND” relationship between them.
Any events using a change-based query must have “Repeat Notification for Triggered Items”) checked on. 
When you Preview a query, the “change” logic is ignored. 
Change-based queries are possible only for tables that have a single-column unique. If a table uses a multi-column unique, please create a view for that table that has a single-column unique value.

Selecting a Column for Value-Change Tracking

To create a filter that checks to see if a value has changed, go to the Filters window titled “If Column Values Have Changed” and you’ll see the columns from the query’s primary database table. To select a column for change-tracking, select the column from the left and click on the right-arrow button to move it to the right.
You may add additional filters, including a filter on the current value of your “change field” to check for records whose content has changed to a specific value.

Storing & Reporting On “Changed” Values

To store the history of when a “changed value” event has been triggered, it is suggested that you use the “Create File” action to create an audit trail. This lets you store the details of what records were changed, when they were changed, and what the old and new values for the change are. 

Filter Operator:  “One Of”

The “one of” operator lets you test a column for any one of multiple values such as:
Send an alert for any calls that have a priority of ‘A’ or ‘B, or ‘C’.
To use the “one of” operator:

1. Select “one of” from the operator list.
2. Click in the “Compare Value” field and remove the ‘?’. 
3. Click on the “…” button in the “Compare Value” field.
4. In the “Type in a Value” field, either key in the individual compare values (pressing ‘Enter’ after each entry), or choose the compare values from the drop-down list.
5. Click on “OK” when complete

Filter Operator:  “Between”

The “between” operator is a slow-executing one, and as such we suggest that you use the combination of the “greater than or equal to” and “less than or equal to” operators in place of the “between” operator wherever possible.
The “between” operator may not be parameter-driven.

Operator:  “Starting With”

The “starting with” operator lets you locate any database records that have a field whose value starts with a certain string of letters, numbers, and/or symbols. To use the “starting with” operator, follow these steps:

1. Select “starting with” from the operator list.
2. Edit the “Compare Value” field and type in the first few characters of the string that you wish to use for matching purposes.
3. The “starting with” operator may be parameter-driven.

Operator:  “Like”

“Like” is similar to “starting with” except that it lets you locate records that have a field whose value contains a certain string of letters, numbers, and/or symbols.
 “Starting with ‘brit’” will retrieve “Britain” and “Britannia” but not “New Britain”.
“Like ‘brit’” will retrieve all three values.

To use the “like” operator, follow these steps:

1. Select “like” from the operator list.
2. In the “Compare Value” field, enclose the text you wish to monitor for within percent signs, as in “%brit%”.

 The “like” operator may be parameter-driven.

Operator:  “Null”

You might wish to check to see if a field’s value is blank; but a “blank” value can be either blank or null. To test a field for a blank value, leave the compare value field blank. To test a field for a null value, follow these steps:

1. Select “null” from the operator list.
2. Remove any value in the compare value field.

If you are not sure how an application identifies an “empty” field, you might want to create a query with two filters – one that tests for blank, and one that tests for “null”. Be sure to use the “or” condition between them.

Using Calculated Fields in Filters

Please refer to the earlier section of this chapter titled “Calculated Fields” – and be sure that the query filter’s “Type” field (“data type”) refers to the correct type of data being returned by your calculated field.

And/Or (“Boolean”) Logic

Consider the following scenario:
Notify me about new orders for greater than $99 and . . . . . . that have freight charges less than $5,
or: . . . that have freight charges more than $50
This type of “and/or” (“boolean”) logic requires the ability to group filters together, using parenthetical expressions. Each filter that you create automatically has an “and/or” associated with it – and those words apply to the current filter AND the filter that follows it. If there is no following filter, the “and/or” has no effect.
So – to create the preceding scenario:
Create the three filters the query needs:

1. Orders greater than $99; (“and”)
2. Freight charges less than $5; (“or”)
3. Freight charges more than $50; (“and/or” does not apply)

Edit the “Column Name” of filter #2 and place a left parentheses in front of the name.
Edit the “Compare Value of filter #3 and place a right parentheses after the compare value.

Your Boolean filters will look like the following:

BOOLEAN (“AND/OR”) LOGIN IN FILTERS

Sub-Filters

Sub-filters are not used with record-level or data-change queries; they are used only with aggregate queries and will be discussed in this chapter’s section on aggregate queries.

Query SQL

The “SQL” tab lets you review the SQL syntax that a query is executing. There is rarely (if ever) the need to make direct changes to a query’s SQL. Once you start manually editing query SQL, all subsequent changes to the query must be done through the SQL tab. 

If you wish to manually edit the SQL, place a checkmark in “I would like to manually edit the SQL for this query” box. It is essential that you select all needed columns for a query and identify the query’s unique column before you manually edit a query’s SQL.

QUERY “SQL” SYNTAX

Once you manually edit the SQL syntax, any modifications that you make to any of the other query definitions tabs will not be retained. 
If you edit a query’s SQL and then de-select the manual editing option, the query will re-constitute the SQL syntax based on the content from the other query tabs.

Using a Stored Procedure in a Query

To use a stored procedure instead of a query, follow these steps:

1. Select the option “I would like to manually edit the SQL for this query” (see preceding section).
2. Type in the procedure name, as in “dbo.myprocedurename”
3. To pass parameters into your procedure, use the following syntax:
   “dbo.myprocedurename param1,param2,param3”
4. (Parameters that are string values must be enclosed by single quotes.)

As stored procedures are custom elements in KPI Monitoring, our Technical Support Services are not able to assist you in debugging these procedures.

Preview

The Preview tab lets you verify that your query is running as you wish it to.

When you click on “Preview”, KPI Monitoring will return any records that meet your query’s criteria. If the preview tab is blank (containing no records), it means that no records met your criteria. When Preview mode returns matching records, it will appear similar to the following: 

QUERY “PREVIEW”

If you receive an error message when previewing, it means that KPI Monitoring was not able to successfully execute your query. The most common reasons for this are:

• The database connection (e.g., .NET Provider, ODBC, OLE/DB) for this application was not correctly configured
• Multiple tables for this query were not correctly linked
• Your query includes calculated fields that contain invalid SQL syntax
• You have specified an invalid customized name for a query column
• You have specified an invalid filter 
• You have edited the underlying SQL and introduced a syntax error
• You have referenced a SQL function not supported by your database connection

If your query includes parameter-driven filters, you will be prompted to enter corresponding values; if the filter requires date values, you will be able to choose from KPI Monitoring’s date substitution variables.

Aggregate “Grouping” Results

One of the benefits of aggregate queries is that they can “group” their results based on any fields in a database. Consider the following three examples and the results from corresponding aggregate queries:
KPI Monitoring can “count” the number of records that meet certain criteria. For example, an aggregate query that counts the number of orders today could yield the following result:

17

An aggregate query that counts the number of orders per region today could yield the following result:

6      Northwest
3      Southwest
8      Eastern

And an aggregate query that counts the number of orders per salesrep within each region today could yield the following:

3      Northwest       Karen Chavez
2      Northwest       Dave Bruce
1      Northwest       Sue Murphy

Aggregate Query Configuration

Please read through the earlier (record-level) query design section of this chapter before proceeding with the following sections. The instructions on aggregate query configuration assume that you are familiar with record-level query design and so the following sections will focus only on those areas of aggregate query design that are different from record-level queries.

Aggregate Query Columns

An aggregate query’s columns are selected based on the following three criteria:
The field that the aggregate function will be performed on  
The “group by” field(s)
Other fields to appear in the alert message, contain a recipient’s address, or will be used in an event’s actions

Aggregate Function:  Summarize

• The “summarize” function lets you total the amounts in a numeric field, for example:
• The sum total of dollars of sales received today
• The sum total of minutes spent on high priority support calls
• The sum total of units of a particular item sold this month

Aggregate Function:  Average

• The “average” function lets you derive the numeric average for a field, such as:
• The average sale price this week
• The average length of a support call in minutes
• The average percent of damaged received goods

Aggregate Function:  Count

• The “count” function lets you count the number of records that meet certain conditions, such as:
• The count of overdue invoices per client
• The count of activities scheduled per rep today
• The count of current backorders for a specific stock item

Aggregate Functions:  Maximum & Minimum

• The “maximum” and “minimum” functions let you identify the maximum or minimum values for a field. This function is for such things as:
• The maximum sale closed last week
• The minimum order quantity this week
• The most recent contact date for a client

Aggregate Grouping

When working with aggregate queries, you need to consider not only what you want to perform the aggregate upon (e.g., total sales) but whether you wish to “group” the results of that aggregate (e.g., sales total by salesrep).
The ability to create aggregates that group their results is one of the most powerful aspects of aggregate queries. Consider how you can take an ordinary aggregate query such as “total sales” and create the following by adding one or more “group by” fields:

• Total sales by salesrep
• Total sales by region
• Total sales by product line
• Total sales by region and (within region) by product line
• Total sales by regional sales manager

All aggregate functions support grouping, but grouping is most commonly used in “summarize”, “average”, and “count” aggregate queries.

How to Configure an Aggregate Query

To select columns for a “summarize”, “average”, or “min/max” query, follow these steps:

1. Identify the aggregate condition you wish to perform (e.g., you wish to “total” the sales amounts).
2. Select the table that contains the field you wish to perform the aggregate function on (e.g., the sales amount is stored in the “orders” table).
3. Select the column you wish to perform the aggregate function on (e.g., the “total” column from the “orders” table). The aggregate column is usually the first column selected in an aggregate query; the exception to this is aggregate queries that retrieve data for charts. Those queries need to have their “group by” value selected first, and the aggregate value selected second.
4. Under “Selected Columns”, click next to the word “Normal” in the Column Type field to display the list of aggregate functions and select the appropriate function (e.g., “Summarize”).
5. Change the customized name of this column to reflect that this field contains an aggregate total (e.g., “sales_total”).
   (Do not put the aggregate function name at the start of a customized name  (e.g., “total_sales”) as some databases do not allow this.)
6. Go back to the list of available columns and select your “group by” field(s) (e.g., select the “customer” column to total the sales by customer).

Your query will look similar to the following:

AGGREGATE “SUMMARIZE” FUNCTION: TOTAL SALES PER CUSTOMER

The “Count” Function

To create an aggregate “count” query, do the following:

1. On the Columns tab, choose the column that’s the primary key (or “unique”) for the table whose records you wish to count
2. Click next to the word “Normal” in the Column Type field and select “Count.”
3. Change the “Customized Name” as appropriate
4. Select your “group by” field(s)

Your result will look similar to the following:

AGGREGATE “COUNT” FUNCTION: NUMBER OF LATE DELIVERIES PER CUSTOMER

Additional Column Selections

Here are a few tips for choosing additional columns in your aggregate query:
Make your “group by” field as informative as possible. For example, to display total sales per salesrep, do not make your “group by” field the salesrep’s ID, but rather the salesrep’s full name. 
To send the results of a query to the individuals that are identified within the query, include the column that contains the delivery address that the alert will be sent to (e.g., include the salesrep’s email address as a query column).
A single query can have multiple aggregate fields. E.g., a query could count the number of jobs last month and show the total cost of those jobs by using both the “count” and “summarize” functions.

 The “Unique” Field

The “Unique” field does not apply to aggregate queries; you may skip specifying a unique column for an aggregate query.

Aggregates and Calculated Fields

Calculated fields may be used with aggregate queries in the same manner that they are used with record-level queries. For example, you could create a column that uses a calculation to identify the weighted value of sales opportunities and then use the “summarize” function to total that value.

Sort Type / Sorting Order

(See “Sorting”, earlier.)

Filters

Aggregate queries use filters to determine which records are to be included in the aggregate calculation. For example, a query that checks to see if there are more than 6 open, high-priority support calls uses filters to limit the potential records to only those calls that are open and have a high priority.
In this regard, filters for aggregate queries are handled in the same manner as filters for record-level queries.
Aggregate queries cannot use the “If Column Values Have Changed” function.

Sub-Filters

Sub-filters are used only with aggregate queries; it is the sub-filter that tests the result of the aggregate calculation to see if it meets certain criteria. 
For example, in an aggregate query that “counts” the number of open, high priority support calls to see if there are more than 6, the sub-filter is what checks to see if the number of calls exceeds 6. (And it’s the query’s standard filters that tell KPI Monitoring to look only at calls that are “open” and “high priority”.)
The following queries have their sub-filters highlighted:

• Total sales of less than $10,000 per region
• Average discount percent of more than 15%
• More than 10 open support calls per agent

Sub-filters configured as follows:
Click on the “Sub-Filters” tab.

• Column Name.  From the list of “Available Columns to Filter” select the aggregate column you created earlier.
• “Is” or “Is Not”.  Defaults to “is”; click and choose “is not” to change.
• Operator.  See “Operator Notes” earlier for details.
• Compare Value.  Specify a constant value, a database field name, a date substitution value, or the question mark. (See earlier in chapter for details.)
• Type.  The type of data the aggregate column contains. Leave with its default unless comparing against another database field or calculated value (use ‘literal’).
• “And / Or”.  Since a query can include multiple sub-filters, this gives you the ability to specify “and/or” logic in the sequence of your sub-filters.
• Optional Prompt.  Used only with a sub-filter that is parameter driven (has a ‘?’ in the Compare Value field). See the section titled “Parameter-Driven Filters” for details.)

 
AGGREGATE QUERY SUB-FILTER

SQL Tab (for Aggregate Queries)

See “Query SQL”, earlier.

Preview (for Aggregate Queries)

Previewing an aggregate query is the same manner as for a record-level query:

AGGREGATE QUERY PREVIEW MODE

How Queries are Linked

If you determine that you need two (or more) queries to identify whether a business condition exists, the first thing to decide is whether the queries will have some data in common with each other. Consider the example given previously:
Check to see if a client has purchased less than $5,000 over the last month, and has called into support more than 5 times.
This scenario requires one aggregate query to “sum” a client’s sales, and a second aggregate query to “count” the client’s number of support calls.
What makes this scenario unique is that both queries need to retrieve their results for the same clients – and the “client” is referred to as the queries’ “linking field”. Here’s how to “link” these queries:

1. Identify the linking field (“client ID”).
2. Decide which query will go first.
3. Design query #1 (e.g., “Clients Purchasing < $5k”)
4. Go to the “Columns” of query #1 and be sure to include the linking field as one of the columns for this query.
5. Modify the Customized Name of the linking field to something memorable (e.g., “client_link”).

Design the “Link to” Query

The second query should identify that it is a “link to” query, so give it a description such as “Clients with > 5 Support Tickets (Link To)”. Design this query as per its requirements and then (to create the link with query #1) follow these steps:

1. Go to the “Filters” of query #2.
2. Add a filter on the linking field (e.g., the “Client ID” field).
3. Select an operator of “is equal to”.
4. In the Compare Value field, key in (within curly braces) the customized name of the linking field from query #1 {client_link}.

By designing your “link to” query in this manner, it will automatically retrieve records for the same client as query #1. The following is an example of this:

“LINK TO” (COMPARE VALUE) LINKING SYNTAX

Data Manipulation between Linked Queries

One of the special benefits of linking queries is that it enables you to use data from query #1 in query #2. Consider the following example:

When you link queries together, data from the first query can be used in the second query’s “Columns”, “Filters”, and “SubFilters”. Like the linking field, all fields are referred to using their customized name within curly braces, as shown below (note format of column name selected for filtering):

DATA MANIPULATION BETWEEN LINKED QUERIES