Home > Main Menu > Reports > Queries

Queries

View: Selection Criteria

User Defined Queries selectively retrieve Concern records or Account Information. Query selection criteria and settings are saved in the system.

The button options at the top of the window include: New Query  |  Edit Query  |  Copy Query  |  Delete Query

1. Click on New Query
2. Type in a ‘Query Name’
3. Select desired options (see below for more detail)
4. Click on Save to Save the Query
5. Choose the data required
6. Click on OK
7. Select the query from the Select Query dropdown
8. Click the Perform Search button to run the Query
9. Click on the buttons for Report, Chart, View Details or Excel as desired:


• Click the Report button to produce a report ready to print or email
• Click the Chart button to produce a Chart based on the Query results
• Click the View Details button go to the specific Concern View and Summary Report for the selected (highlighted) Concern record in the list of results
• Click the Excel button to save the data in a Microsoft Excel spreadsheet .xls format  (Microsoft Excel software is required) 

Account Information Only
A new Query will have an Account Information Only checkbox option.  When this option is selected, Account Contact information and Equipment information can be selected within a Query.  Concern specific fields will not be available.  This is a permanent selection which cannot be changed once it is applied to the Query.

Summary Totals Only
A new Query will have a  Summary Totals Only checkbox option.  When this option is selected, the query will count the number of items that match the primary "sort by" selection . It will then display a summary report which displays only totals.   This is a permanent selection which cannot be changed once it is applied to the Query.

   Example 1: To get total number of Concerns for each Account.

1. Create New Query
2. Give the Query a name
3. Check "Summary Totals Only"
4. Choose Account->Account Name->Display
5. Choose Account->Account Name->Sort By
6. Press Ok
7. Choose your new query from the drop down list
8. Press Search

    Example 2: To get total number of of each type of Concern for each Account.

1. Create New Query.
2. Give the Query a name.
3. Check "Summary Totals Only".
4. Choose Account->Account Name->Display
5. Choose Account->Account Name->Sort By
6. Choose Concern->Concern Type->Display
7. Choose Concern->Concern Type->Sort By
8. Press Ok
9. Choose your new query from the drop down list
10. Click Perform Search

Access
User access to each saved Query can be controlled two ways:

• Public View allows other users to run your saved Query
• Public Edit allows other users to edit your saved Query

Status
Option to specify the Status of concerns that will be included in the Query.  Select Open, Closed or All.

Account Category
The Account Category field is unique because it can contain multiple items.  Queries including the Category field should be limited with appropriate Selection Criteria. Including the Category field without any limitation will produce a separate line item result for each Category field item.  For example, an Account with 4 Categories will appear 4 times in the results.

Data Names

• Data Names are the database fields available within a Query

• Data Names are organized by source, for example Account information, Concern information etc.

                           
        OPTIONS:

• Display
  Check Display to include and display a field in the Query results.  Left unchecked, a field can still be filtered as a runtime or standard selection without being displayed.
   

• Sort By
  Check Sort By to specify the report sort sequence on a certain Data Name.  When multiple Data Names are selected for sorting, the report will be sorted with the priority based on the order that each Sort By column is checked.  The Sort By order will be displayed in the Sort Key window at the top of the screen. Uncheck all Sort By selections to establish a new Sort Key order.
   

• Total
  Total check boxes are available for numeric data fields including User Defined Fields when the Data Type for the field is defined as Numeric Only.  When checked, totals will occur in the last row of the report for the column of data.
   

• Runtime Select
  Enables a user with the option to enter unique Selection Criteria each time the Query is run without having to edit the saved Query itself.  The result will be a pop up window prompting the user to enter Selection Criteria each time the Query is run. 

  • Multiple Runtime Selection Criteria may be used and they can also be utilized selectively at runtime. 

  • Click the Proceed button without entering any Selection Criteria to see unfiltered results.

  • A Note: Using no filters on any fields may cause the Query process to take a very long time or stop working.

See also