Queries and Filters
Manager Contents
User Guide Contents
Contents

 Introduction
Query Menu
Selecting Output Fields (Columns)
Output Ordering
Entering Parameters
Query Charts and Indicators
Ownership Settings
Optional Links in Results
Query Options
Pagination Options
Save Options
Exporting Query Results

Introduction

Queries and Filters have similar functionality. The key difference between them is where they are located in CATSWeb.

Filters are available at the top of each task list (Personal Tasks, Department Tasks, etc.) and enable customized lists to be created. Each filter can have its own set of fields (columns), sort order and record selection criteria. CATSWeb provides pre-configured filters as a starting point (e.g., "Personal Tasks") but you or your system administrators may modify their configuration to better suit your particular needs. Or, you can create your own Personal filters. CATSWeb remembers the last filter you used on each page, and utilizes it the next time that page is visited.

Queries are similar to filters, but are accessed via the Queries button on the Home page. While Filters provide customized task lists, Queries are used to ask arbitrary questions about the data in the system, typically for analysis or problem solving purposes.

Back to Top

Queries Menu

The Queries Menu page allows you to create, modify and run queries (including SQL Queries), Advanced Searches, and Dashboards.

Back to Top


Selecting Output Fields (Columns)

 A typical row in a Query or Filter form is shown below:

Record Status Disp Request
CA Request
CA Assigned		 Assigned To

The checkbox to the left of the field caption is used to include the field as a column in the query output. To the right of the field caption is an area to enter parameters which limit what records the query returns. In general, the more parameters entered, the fewer records returned.

The field inclusion checkbox to the left of the caption is completely independent of the parameter area to the right. A field can be included in the query output without specifying any parameters for it. Or, a parameter can be entered for a field even though it is not included in the query output.

Back to Top

Output Ordering

 Query results can be sorted by selecting up to four Order By fields from the four drop-down lists in this section. Each selection includes an option for sorting in either ascending or descending order. Memo fields are not included in the drop-down lists since database systems do not support sorting on this type of field.

Back to Top

Entering Parameters

Entering data in a parameter field will restrict the query results to records that match that data. If more than one parameter field is entered, the records must match all the entries (the fields are combined with a logical AND) unless you also check the Use Or Between Parameters query option. Multiple entries can be made within each field by separating the entries with commas. In this case, the entries within the field are combined with a logical OR.

Keep in mind that if you leave a parameter field blank, it does nothing to limit the records returned. For example, in the case of the Record Status field shown above, leaving all the boxes unchecked tells the query to select records of any status. The same would be true if all the boxes were checked.

Two special and mandatory list fields may be present on the query form: Form Name (or Category) and Select From.... Form Name is used to select the form that defines the captions used on the query form and in the query output. Selecting a particular form does not limit the records returned. The Select From... entries serve as "preselectors", and can limit the set of records accessible to the query. Unless your administrator has defined additional preselectors (by creating View Data Links), there may only be one entry available, such as "All Issues".

Wildcards and Inequalities

Wildcard characters may be used in query fields that expect text. The asterisk character is a text wildcard that replaces 1 or more characters. For example, entering jo* in the Entered By field will locate all records entered by John, Josephine, and Jorge. Entering *ry would locate records entered by Barry and Terry. Inequality symbols may be used in query fields that expect numeric values. Entering <> 0 would locate records with non-zero values, and >15 will locate values greater than 15.

Date Ranges

Date fields can be specified as a single date or a range of dates. Date ranges are specified with a hyphen between them, such as 5/29/98-6/15/98. As with all query fields, date fields can accept multiple values separated by commas. For example, this entry returns records from the first weeks of May and June: 5/1/98-5/7/98, 6/1/98-6/7/98. The {TODAY} token can be used to specify the current date. For example, entering 1/1/99 - {TODAY} will select all records from 1/1/99 to the present.

The {TODAY} token can also be used to enter a relative date range by adding or subtracting time intervals (typically days) from the current date to form either end of the date range. The following examples illustrate this:

Date Expression Meaning
{Today - 30} - {Today} Select any dates from the last 30 days.
{Today - 10} - {Today + 10} Select any dates that are within 10 days of today.
9/15/2000 - {TODAY - 4} Select dates from 9/15/2000 until 4 days ago.
{Today - 30d} - {Today} Select any dates from the last 30 days. Note that this is the same as the first example above, except that the "d" code for days has been included. This is not necessary, since days are the default time interval.
{Today - 3m} - {Today} Select dates from the last 3 months. Here, the "m" character is necessary to specify the interval as months instead of the default of days. Alternatively, "{Today - 90}" would give a similar result, depending on the position of the current date in the calendar (using a month interval allows CATSWeb to use the correct amount of days from each month in the interval, such as 30, 31, 29, etc.)

Note that the entries are not case sensitive. Embedded spaces are also ignored.

Text Tokens (variables)

Text tokens (variables) enable you to develop and save "universal" queries that enable all users to locate data that is pertinent to them. For example, instead of entering a fixed name or department in a public query, a token can be used that will substitute the name, department, or other attribute of the user who executes the query. Other text tokens allow values to be queried for, that could not otherwise be expressed (e.g., Nulls and empty strings). The table below lists the available text tokens:

Text Token Meaning
{My Name} The name of the user running the query.
{My Department} The department of the user running the query. Since a user may belong to many departments, their current department is substituted.
{My Home Department} The home or primary department of the user.
{My Employee ID} The Employee ID of the user running the query.
{My Group} The group of the user running the query.
{My Default Category} The default category pertaining to the query record type for the user running the query (i.e. the default Corrective Action category will be substituted in Corrective Action queries, the default Subtask category for Subtask queries, etc.)
{My Default Issue Category} or
{My Default NonConformance Category}		 The default Issue category for the user running the query will be substituted in.
{My Default Action Category} The default Action category for the user running the query will be substituted in.
{My Default Subtask Category} The default Subtask category for the user running the query will be substituted in.
{My Department Name} The descriptive name of the department in which the user running the query is currently operating in. Note that the {My Department} token (above) is more useful in selecting CATSWeb records, since the descriptive department name is rarely used.
{My E-mail Address} The E-mail address of the user running the query.
{My Database Name} The 8-character database name that CATSWeb automatically places in fields such as UDDatabaseCreated and UDDatabaseEdited.
{My Database Login Name} If the CATSWeb system is running in multiple database mode, this is the "Company" or "Database" value that is entered in the login page to login to the CATSWeb database.
{No Value} Finds records that have any of these following in the field it is specified for:
  • Null
  • Empty string
  • String containing only spaces.
{Has A Value} Finds records that have none of the following in the field it is specified for:
  • Null
  • Empty string
  • String containing only spaces
{Null} Finds records that have a Null in the field it is specified for. Note that CATSWeb only leaves fields at Null values if they were not included in the form (i.e. had an order value of 0) when the record was created or last edited. If a field is present in a form, it will receive a non-Null value (ex: empty string) during Adds and Edits, even if the user did not enter anything into the field. Using the {No Value} token instead of the {Null} token is typically preferred, as it minimizes confusion about when a field value is or is not Null.
{Not Null} Finds records that do not have a Null in the field it is specified for. Using the {Has A Value} token instead of the {Not Null} token is typically preferred, as it minimizes confusion about when a field value is or is not Null.
{Empty} Finds records that have an empty string in the text or memo field it is specified for. If specified for any other type of field, it will be interpreted the same as {Null}, since there is no concept of "empty" for the other field types.
{Not Empty} Finds records that do not have an empty string in the text or memo field it is specified for. If specified for any other type of field, it will be interpreted the same as {Not Null}, since there is no concept of "empty" for the other field types.
{Blank} Finds records that have an empty string, or a string containing only spaces, in the text or memo field it is specified for. If specified for any other type of field, it will be interpreted the same as {Null}, since there is no concept of "blank" for the other field types.
{Not Blank} Finds records that do not have an empty string, or a string containing only spaces, in the text or memo field it is specified for. If specified for any other type of field, it will be interpreted the same as {Not Null}, since there is no concept of "blank" for the other field types.

CATSWeb Administrators will recognize many of the text tokens as being Session Parameters. In fact, the two-letter code for the parameter may also be used here, to provide parameters that are not typically utilized (e.g., "{EC}").

Back to Top

Ownership Settings

If your CATSWeb system includes the optional Query and Filter Ownership feature, and you have permission to specify ownership, an Ownership section will be present near the top of the query, filter or advanced search form. If ownership is specified for a public query, filter or search, only the owner(s) will be able to modify or delete it. The Ownership settings are automatically cleared and ignored when the query, filter or search is saved privately.

To specify ownership, select the Owner Type and click the iconic reload button (). Choose the owner from the list.

Back to Top


Query Charts and Indicators

If your CATSWeb system includes the optional Query Charts and Indicators feature, a Query Chart or Indicator section will be present near the top of the query form. This section will not be present on filter forms, as filters do not support charts or indicators.

To add a chart or indicator to your query, select the Type and click the iconic reload button (). Choose a Template and specify the fields and other data values that may be required. When you submit the query, the chart or indicator will be shown near the top of the page. The data records selected by your query will be shown below the chart or indicator, unless you chose not to include them via the Include in Results setting.

If you wish to enter a data expression instead of selecting field captions from the list(s), check the Use Expression box and click the iconic reload button again. Note that data expressions always use the actual field names instead of field captions.

If you have included a chart in your results, you may float your mouse over the chart items to read their values. You may also click on the chart items to drill down and view only the data records for the item (if your administrator has enabled Drill Down in the Template).

The Templates available for Query Charts and Indicators are actually Chart or Indicator Display Parts of the applicable type. Administrators specify their attributes such as size, colors, margins, etc. via the template's Display Part configuration page. If you are a system administrator, you may refer to these guidelines for managing Query Chart and Indicator templates.

The following settings are available for some types of templates, and work the same as their corresponding setting in Display Parts:

Back to Top

Optional Links in Results

  • Present in Queries, this section is used in the same way as the Optional Links in Results section of the Drill Down form. You do not need to define links for the record key fields (ActionID, SubtaskID, IncidentID, etc.) since CATSWeb provides these links automatically.

Back to Top

Query Options

The following options may be selected:

  • Show Input Parameter List - The query output will include a list of the query field parameters that you entered.
  • Show SQL Statement Generated - The query output will include the actual SQL (Structured Query Language) expression that is generated from your inputs and sent to the database server. Note that the SQL expression will contain literal table field names which may not agree with the names (captions) assigned to each field by your administrator.
  • Suppress Links In Data - If the record ID (Log Number) is included in the output fields, it will normally be shown as a link. Clicking the link will take you to a page that displays details of that record. Selecting this option causes the record ID to be output without the link. This is desirable if the query data is to be loaded into a spreadsheet or other program.
  • Suppress Page Header - Selecting this option causes the query output to be displayed without the usual CATSWeb page header. This is desirable if the query data is to be loaded into a spreadsheet or other program.
  • Use Or Between Parameters - The default behavior of CATSWeb queries is to logically AND together the various query parameters you enter. This generally results in the output record set getting smaller (fewer records selected) as you enter more and more parameters. Checking this option causes CATSWeb to use an OR between parameters instead of an AND. This generally results in the output record set getting larger (more records selected) as you enter more and more parameters. For example, you may wish to find all Issues that are assigned to you OR were entered by you. Checking this option will enable you to construct such a query (by entering your name in all the parameter fields that you wish to search through).
  • Case Sensitive - This option is only available when CATSWeb runs on an Oracle database. Checking the box makes the query parameters case sensitive. For example, entering "*ABC*" will find records containing "ABC", but not records containing "abc", "AbC" or "abC". When CATSWeb runs on a SQL Server database, query parameters are always case insensitive.
  • Override Field Date Format - This option overrides the Format value set on the Field Definition page for the date fields on the selected Form Category. When this box is checked, the query Default Date Format (see below) will be used for all dates.
  • Default Date Format - This date format will be used for all dates that do not have a format defined in their field definition Format property. If the Override Field Date Format option is selected, this date format will be used for all dates, regardless of the field definition Format setting.
  • Paginate Display - By default, a query returns all results for display. When many results are returned, the query can take a long time to run and may actually time out. Checking this option causes CATSWeb to use pagination for query results. When enabled, only the number of records selected by the Rows per page will be returned for a given page. You can navigate forward and backward to a different page to see additional results. If pagination is enabled without setting a numeric Rows per page, pagination will be ignored and all rows will be displayed. See CATSWeb Paging Controls for details.
  • Rows per page - When Paginate Display is enabled, this setting provides the number of rows to return for each page. If pagination is enabled without setting a number of rows per page, pagination will be ignored and all rows will be displayed. See CATSWeb Paging Controls for details.

Back to Top


Save Options

The query may be optionally saved for later reuse. To save a query, select Public or Private and specify a name for the query in the Save As field. Public queries will be available to all users, while Private queries will only be available to you. If the Save As name for the query already exists, the query will be overwritten with the new query.

If you have recalled a saved query, the Delete checkbox will also be available. Checking this box and submitting the query causes it to be deleted from either the public or private query list.

Back to Top

Exporting Query Results

Query results may be exported to spreadsheet programs and other software simply by saving the HTML source of the query results. All browsers provide this capability (typically from the File menu), and most spreadsheet programs are capable of importing HTML files. HTML tables in the query output will be converted to tables in the spreadsheet program.

When running queries specifically for exporting, you may wish to check the Suppress Links In Data and Suppress Page Header query options and disable Paginate Display. See above for descriptions of these options.