IQA tab options

The following options are available during each step of the query creation process.

In This Article 

    Summary tab
    Filters tab
    Display tab
    Template tab

Summary tab

  • Only provide caption for screen readers – Enabling this option provides captions for screen readers, so screen readers can find, navigate, and understand queries.
  • 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 ideal for queries that do not require real-time results, such as those displayed on the Staff site Dashboards.

    • When Use cached results is enabled, query results may be cached for up to six hours to ensure quicker response times for frequently accessed queries. The setting evaluates query execution time to decide whether to provide a live result or a previously cached result. If the historical runtime exceeds approximately one second, the system displays the last cached result while refreshing data in the background periodically.

    It is recommended to only enable Use cached results for the following:

    • Long-running queries with a small number of result rows, commonly used on dashboards.
    • Queries where real-time data is not critical.

    Do not enable Use cached results for the following:

    • Queries where parameters vary significantly, for example, based on the logged-in user.
    • Queries returning thousands of rows or with high variability, as it may cause performance issues.

    For best results, use this option for stable, low-row-count queries that display consistent data sets, especially on dashboards or similar interfaces.

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

Filters tab

Filters allow you to narrow the number of search results down by adding conditions and constraints on the type of data to include in your query. You can limit your results by eliminating duplicates, using comparisons, setting parameters, and using logical operators.

After you select a property, you can select a filter in the Value column that will allow you to compare that property against another property with a compatible data type from the same source.

  • Function – Allows the user to find Avg, Count, Min, Max or Sum on a field.

    Functions allow for performing calculations on multiple rows in a single table column, returning a single value.

    • Avg - Returns the average of the values in a specified column (numeric fields only).
    • Count - Returns the total number of values in the specified field (numeric and non-numeric data types).
    • Min - Returns the smallest value in the specified field.
    • Max - Returns the largest value from the specified field.
    • Sum - Returns the sum of all the values in the specified column (numeric fields only). Null values are excluded from the result set.

  • Comparison – Allows the user to compare values for the selected property.

    • Equal – Enables you to compare two values. If the values are the same, the results return TRUE. If the values are different, the results return FALSE.
    • Not Equal – Enables you to compare two values. If the values are not equal, then the results return TRUE. If the values are equal, the results return FALSE.
    • Less – Enables you to compare two values. If the first value is smaller than the second, the results return TRUE. If the first value is not smaller than the second, then results return FALSE.
    • Less Equal - Enables you to compare two values. If the first value is smaller than or equal to the second, the results return TRUE. If the first value is not smaller than the second and is not equal to the second, the result returns FALSE.
    • Greater - Enables you to compare two values. If the first value is greater than the second, the results return TRUE. If the first value is not greater than the second, the result returns FALSE.
    • Greater Equal - Enables you to compare two values. If the first value is greater than or equal to the second, the results return TRUE. If the first value is not greater than or equal to the second, the result returns FALSE.
    • Between - 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.
    • Starts With – Enable you to check if one value starts with another value. Enter multiple values for a pattern comparison by separating each value with a comma and in quotes. For example, "D","R".
    • Ends With – Enable you to check if one value ends with another value. Enter multiple values for a pattern comparison by separating each value with a comma and in quotes. For example, "D","R".
    • Contains - 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 by separating each value with a comma and in quotes. For example, "D","R".
    • Not Contain - Enables you to specify items to exclude in a search. Enter multiple values for a pattern comparison by separating each value with a comma and in quotes. For example, "D","R".
    • Empty – Enables you to determine if the value does not have any content.
    • Not Empty – Enables you to determine if the value has content.
    • Within Days – Allows you to evaluate if a date is within a specified range of days from another date.
    • Within Months – Allows you to evaluate if a date is within a specified range of months from another date.
    • Within Years – Allows you to evaluate if a date is within a specified range of years from another date.

      • Note: 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.

  • Multiple - Selecting this option allows you to select more than one comparison value for the same property. Multiple comparison values can also be entered by separating each value with a comma and in quotes. For example, "Regular Member", "Student Member", "Staff Member".

  • Value - Depending on the Comparison operator you selected, you can select the type of filter in the Value column:

    • Constant – Enter a value or select from a value list.
    • Property – Compare one database field to another in the same source. The drop-down displays the fields from the IQA source that are of the same type as the property you want to compare.
    • Dynamic – You can dynamically filter your query results. The following values are available:
      • LoggedIn User Key
      • Selected Contact
      • Selected ID

      Note: If you have a Dynamic filter named Me, that filter refers to the currently logged-in user. The Me filter is no longer supported.

    • URL param – Filters by a specified URL parameter. Specify the name of the URL parameter. These filter options are not available if your Comparison operator is Between, Starts With, Ends With, Contains, Not Contain, Empty or Not Empty.

  • In Search? - Each filtered property can be added to the query's search form to allow your users to perform their own search and narrow down results.

    • Optional – Users can choose to enter a value for the property to help narrow down the results returned.
    • Required – Users must enter a value for the property so that it limits the results returned.

  • Search Label - Define a label that will be used for the filter’s search field.

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.
  • Do not use URL params when creating filters using date properties. Instead, use Dynamic values.

Defining a filter property with several values

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) A Multiple column is displayed between the Comparison and Value columns. For properties that have a discrete list of values, a checkbox is displayed 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 is displayed. Browse to select another value.

    6. Depending on the Comparison operator you selected, you can select the type of filter in the Value column:

    • Constant – Enter a value or select from a value list.
    • Property – Compare one database field to another in the same source. The drop-down displays the fields from the IQA source that are of the same type as the property you want to compare.
    • Dynamic – You can dynamically filter your query results. The following values are available:
      • LoggedIn User Key
      • Selected Contact
      • Selected ID

      Note: If you have a Dynamic filter named Me, that filter refers to the currently logged-in user. The Me filter is no longer supported.

    • URL param – Filters by a specified URL parameter. Specify the name of the URL parameter. These filter options are not available if your Comparison operator is Between, Starts With, Ends With, Contains, Not Contain, Empty or Not Empty.
  6. From the 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. The selected values appear in the Value column.

Limiting the rows returned by a query

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)
    1. Select the Limit the number of results returned option.
    2. 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.

Allow query to run for (maximum)

The query Filters tab gives you the option to set the maximum amount of time a query will attempt to generate before the query times out. It is recommended that this setting is kept at 30 seconds.

Choosing 1 minute or 3 minutes is not recommended and could severely decrease the site’s performance.

Display tab

To choose which columns to display in your query results, go to the Display tab. From there, select the properties you want to display by checking the corresponding boxes under Available. You can search for specific properties or select all at once. After selecting your desired properties, click the Refresh button to see them under Selected. You can also add more attributes such as Alias or Hyperlink.

  • Function – Allows the user to find Avg, Count, Min, Max or Sum on a field.

    • Functions allow for performing calculations on multiple rows in a single table column, returning a single value.

    • Avg - Returns the average of the values in a specified column (numeric fields only).
    • Count - Returns the total number of values in the specified field (numeric and non-numeric data types).
    • Min - Returns the smallest value in the specified field.
    • Max - Returns the largest value from the specified field.
    • Sum - Returns the sum of all the values in the specified column (numeric fields only). Null values are excluded from the result set.

    When the query includes a property with a display function such as Count, the other properties included for display are used to determine unique results. For example, consider the Min function which returns the smallest value for a column. If you add an additional column, the results are grouped by that additional column. If you include a contact’s ID, Min returns the lowest value for that ID with one row returned per unique ID.

    If you add additional columns, those columns will be grouped as well; you will get the lowest value returned for that unique combination of values. For example, if you have a query for gifts that has columns for ID and Min(GiftDate), you will receive one row per ID with that contact's earliest gift date. If you add a column for Gift Item, you will receive multiple rows for contacts who have donated to more than one gift item; one row per donor per gift item, with Min(GiftDate) returning the earliest date that the donor donated to that gift item.

  • 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. You can hide headers in multiple columns by using HideHeader1, HideHeader2, etc. Additionally, using HideHeader -- renders the header as text for screen readers. Any text after the endash (--) will render for screen readers.
  • Hyperlink: You can add a hyperlink for each column listed in the Display tab so that the user can click on the hyperlink to go to a specified page for more details. See Linking IQA results for more information.
  • Group By: 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 Group By option. See Hierarchical grid display for more information.

    • Note: If a property has Group By enabled with a Hyperlink defined and that link is referencing another property in the query, then the linked property must also have Group By enabled. Not enabling Group By on both properties will result in a query error.

    Note: Do not use the Group By option to display query results in a hierarchical grid of summary rows and detail rows or when the Only display unique results is enabled.

  • Order: You can order and prioritize the properties in each column in Ascending or Descending order.
  • 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, and so forth.

  • Format – Choose how a property is displayed. Formatting is available for DateTime, numeric, and currency fields. The format is based on either the user's browser language or the organization's default culture, depending on how the Display dates and numbers setting is configured (Settings > Organization).

  • SQL Expression - Enter a unique SQL statement to calculate data for a column in the query. The SQL Expression field does not support subqueries.

Template tab

The Template tab allows query designers to get creative with how the query results are displayed.

Use the Insert data source field drop-down to easily insert query display properties in the accepted format: {#query.DisplayColumnAlias}. Use the HTML tab to insert custom HTML or CSS to create a unique layout for the query data. This tab also accepts Content Html variables.

See Creating templates to display query data for more information.