Defining queries

IQA provides two modes for query definition: Basic and Advanced. The general steps for defining a query are the same in either mode, but additional functionality is available in Advanced mode. Use Basic mode for querying and displaying simple lists of results from one or more sources with optional filters to narrow the results. Use Advanced mode for more complex queries and displays.

Basic Mode Advanced Mode
  • Define sources and (optional) filters
  • Specify display details
  • Use calculations, such as sums and averages
  • Use queries in iMIS searches
  • Use SQL expressions in queries
  • Use complex filters, such as displaying only unique results
  • Allow or require end user to supply parameters at run time
  • Aggregate data and display
  • Assign aliases to field names for display
  • Link a display property to another page

See Reporting in IQA for information about using queries for reports.

Note: iMIS starts you in Basic mode, but it remembers the last mode you were in, even after you log out.

Define the query as Basic or Advanced, depending on complexity. Use Basic mode for simple queries without calculations like sums and averages or SQL expressions.

Follow a structured naming convention to make queries easier to locate. See Building a library of queries for guidelines.

Add one or more sources (business objects and/or queries) for the query. For example, you could set up a query based on the Contacts business object alone, or on both Contacts and Activities, or on a previous query with an additional source.

If you have chosen more than one source for your query, there must be relationships among them. The most common relationships are used by default. However, you might need to create new ones. You can associate business objects and queries through fields that contain the same data.

Use logical operators (such as equal, not equal, and between) to refine query results.

You can select the columns you want to display from the sources you have selected and the order in which you want the columns to appear.

You can order and prioritize the properties in each column in ascending or descending order.

It is important to test the query and edit it, if necessary, to make sure the desired results are returned.

To save a valid query, you must select at least one source and one column. If you select more than one source, the relationship between the sources must be defined. In most cases, a default relationship is inherited from your other sources.

You can create a group where an automated task can refresh the query to determine the members of the group by the query results.

You can modify the security on a filter to assign read, write, and run privileges.

  • Review information on Business Object Designer to help select the appropriate objects for your query.
  • Base the structure and location for your query file/folder on the needs and access levels of your users. See Building a library of queries for information and hints.
  • Start with a single source object and add additional objects as needed.
  • Some attributes (relations, default columns, default column order) are inherited from the sources you select. You can modify these default settings during query definition/editing.

The following procedure shows how to define a new, simple query. To edit an existing query, you can follow the same procedure. In some cases, a system administrator can navigate to the webpage where the results are displayed and click Edit Source Query. See Editing query-generated lists for more information.

If you have been granted access to an IQA folder, you can edit or create queries in that folder. Do the following to create or edit a query:

  1. From the Staff site, go to RiSE > Intelligent Query Architect.
  2. Edit a query, or select New > Query.
  3. Select Basic mode from the drop down.
  4. Enter a Name and (optional) a Description for the query. Do not use apostrophes.

    5. Note: Use a naming convention that ensures that the query has a unique name. iMIS does not require a unique name.

  5. Enter descriptive Caption text. This text will be read by assistive technology to describe the results of your query.
  6. (Sources tab) Click Add Source. Find a business object or query to use as a source for the query (most business objects are located in the Common folder). Select the desired source and click OK.

    7. Note: When you add a source more than once, the system automatically appends a numeric value (beginning with the number 1) to the additional sources to distinguish between the sources. For example, if you add the Contact business object three times in order to set up a variety of relationships, the instances would be labeled Contact, Contact1, and Contact2.

  7. (optional) Select an additional source and click OK. Use the up and down arrows to reorder the data sources as needed.

    8. If you select more than one source, you must define the relationships among the sources. Most sources have a common relationship as a default. Use the default, choose the desired relation from the list, or select Custom from the drop-down list to define a new one.

  8. (optional) (Filters tab) Define the filtering rules:
      • Select the Property to be compared.
      • Select the type of Comparison, for example, Equal, Between, or Empty.
      • Enter the Value or select a date for comparison.
      • Click + to add the filter. You can add additional filters to your query. Additional filters can be reordered using the up/down arrows.

        Note: You cannot move filters between filter sets.

        • When you select Between as the Comparison operator, the Value column changes to display two fields with the word and between the fields. The specified values are inclusive.
        • The pattern comparisons (Contains, Not Contains, Starts With, Ends With, and so forth) use a simple text field even for a Property that normally contains a discrete drop-down list of values. 
        • The Contains comparison type performs a string comparison to find one string within another string. A partial match (for example, only a portion of a word) is required.
        • Enter multiple values for a pattern comparison (Contains, Not Contains, Starts With, and Ends With) by separating each value with a comma and in quotes. For example, "D","R".
        • Click the calendar icon next to any Date field to select a date. If you need to specify a specific time on the selected date, first enter the time in the Date field, using 24-hour format (for example, enter 23:00 for 11:00 pm), then click the calendar icon to select the date.
        • When entering numbers for a comparison on a date field (Within Days, Within Months, Within Years), be aware that such comparisons are zero-based. For example, if you select the Last Updated column and select Within Days, you must enter zero (0) in the Value field to get all records that were updated within one day, or enter 1 to get records that were updated within two days.
  9. (Display tab) Define how results columns are displayed
      • Select the columns you want to work with (View selected columns, View default columns, or View all columns) from the drop-down.
      • Select the Only display unique results option to prevent duplicate results from displaying. When the query includes a property with a display function such as Count, that property is used to determine unique results. If the query includes more than one display function, only the first function property displayed (defined by the display order) is used.
      • Select the results columns to display to users. Select the order in which you want the columns to display to users (from left to right). The order is based on the Order column, not the order in which they were chosen. Subsequent sorting selections go at the end of the sort order list (per session) by order listed within each session.

        • Note: If you are defining a query for a merge output process, make sure you select all columns needed for the merge.

  10. (optional) (Sorting tab) Specify the sort order for each column
      • Select the Property to sort by.
      • Select the sort Priority for each property.
      • Select the Direction of data in each column: Ascending or Descending.
      • Click + to add the sort.

      The user can sort columns in the output by clicking on a column header. A carat symbol (^ ) is displayed in the column header, indicating that the column is now a sort column. The user’s sort selection will override all prior definitions on the Sorting page. This user-selected change will adjust the definition in the query to reflect the current column sort.

      Note: If you choose not to define any parameters in the Sorting tab, then the results returned using SOA might be inconsistent within a single page. If you choose to define one or more properties in the sorting tab, then the resulting set of returned pages should be consistent and return the same results within a page.

  11. Select Run to test the query. If edits to the query are necessary, select Define.
  12. (Security tab) Access mode: Assign access and privileges for the query and its results display:
    • The default setting for a new query is All Staff Full Control. Queries that must be accessible to public users will require modification. To grant public users access to a query, update the Access mode to Authenticated Users Full Control.
    • Names of authorized users are listed in the Current Access List.
    • Access Key and Contact Key are important when you are linking to objects with security, such as a Campaign business object where a user must have certain security to be able to view data.
  13. Click Save. Select the folder in which to save the query.
  14. (optional) Select the Report tab to save this query as a report definition. You can customize these reports and export them to PDF or XLS format. See Reporting in IQA for more information.

The Advanced mode gives you powerful methods to query, filter, display, and report your results. The steps to build a query in Advanced mode are the same as in Basic mode, with additional options described in the following sections.

Use Advanced mode to define queries with calculations and/or SQL expressions, and to aggregate data. You can set up complex filters and assign aliases to field names for display.

To view examples of advanced queries, see Building IQA queries.

In Advanced mode, the Summary tab provides a few extra options:

  • Use cached results - Enabling this option will cache the query results so that future requests for the query data can be served faster. This option is meant to be used for summary queries. For example, queries that display on the Staff site Dashboards. It is recommended to not enable this option for non-summarized queries.

    • Once this option is enabled, the query will periodically refresh. Query results can cache data for approximately one to six hours, depending on the complexity and size of the query. We recommend that this option is only enabled for queries that display summary lists. Once the query is originally cached, it cannot be manually refreshed. If you must have absolute current data, do not cache the query results. Enabling this for a non-summarized query could hinder the query’s performance.

  • SQL - View the SQL that will run for the specific IQA query.

In Advanced mode, the following options are available on the sources tab:

  • Use FORCE ORDER option: Require SQL Server to join the tables in a query in the order you specify.
  • Use NOLOCK locking hint: Require SQL Server to ignore record locks.
  • Use join method for security enforcement: If enabled, when users run the query, iMIS checks to make sure they have permission to read every source within the query before displaying the results, whether the source is a business object, another query, or both. When disabled, iMIS does not check the security for every source and instead only enforces the security setting for the main query.

Filters refine a query. You can limit your results by eliminating duplicates, using comparisons, setting parameters, and using logical operators.

Tips for filters:

  • If you installed iMIS on a case-sensitive SQL Server, the filters you define are case-sensitive. You cannot define case-insensitive filters on queries when iMIS runs on a case-sensitive SQL Server.
  • Select the Require user to provide at least one valid value option when you want to require a value for every filter you define for a query.
  • Click X (remove icon) to remove both the filter set and all related filters from the query.

When a filter Property contains a drop-down list of values, you can select a single value from the list or you can select more than one. The selected values are used as an Or comparison.

Do the following to define multiple values for a filter property:

  1. Go to RiSE > Intelligent Query Architect > New > Query.
  2. (Filters tab) Select Advanced mode. A Multiple column displays between the Comparison and Value columns. For properties that have a discrete list of values, a checkbox displays in the Multiple column.
  3. Select a property that has a discrete drop-down list of values, such as State Province.
  4. Select the Multiple option.
  5. A default Value displays. Browse to select another value.
  6. From the Available Values list, select the values that you want to use in the filter. You can multi-select values by holding Shift or Ctrl while selecting values with your mouse.
  7. Click OK. The selected values appear in the Value column. 

You can set an advanced filter on a query to set a hard limit or a default value for the number of returned rows.

Do the following to limit the number of rows returned by a query:

  1. Go to RiSE > Intelligent Query Architect > New > Query.
  2. (Filters tab) Select Advanced mode.
  3. Select the Limit the number of results returned option.
  4. Enter the Value for the maximum number of rows to display.

Note: To create a top n records query result, specify at least one property on the Sorting tab.

In Advanced mode, the following options are available on the display tab:

  • Function: Allows the user to find Count and Min or Max on a field.
  • Alias: Creates a label name for the field to be displayed to users. The alias entered can contain special characters.

    • Use key_ or code_ as a prefix in the Alias field to hide columns under certain conditions:

    • If you use key_ as a prefix, this field will be hidden if the current column is a GUID. 
    • If you use code_ as a prefix, this field will always be hidden from the initial result set.
  • HideHeader: Use HideHeader in the Alias field in situations where you want the column to display, but you want the column header to be hidden.
  • Link: You can add a link for each column listed in the Display tab so that the user can click on the link to go to a specified page for more details. See Linking IQA results for more information.
  • Subtotal: Specify whether the property should be subtotaled in the display. You can display query results in a hierarchical grid of summary rows and detail rows by using the Subtotal option. See Hierarchical grid display for more information.
  • Length: Specifies the length of string and varchar fields in the query. Entering a value will limit the number of characters allowed in a field. This option is disabled for fields that are numeric, boolean, date, etc.