Defining queries

Essentials of query definition

Name the query: Follow a structured naming convention to make queries easier to locate.
Add source(s) for the query: Add one or more sources (business objects 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.
Use existing relationships between sources or create new ones: If you have chosen more than one source for your query, there must be relationships between the sources. The most common relationships are used by default. However, you might need to create new relationships. You can associate business objects and queries through fields that contain the same data.

Important! Cross Joins should rarely be used, if at all. Cross Joins create performance issues and could cause your site to run slow.

Set filters to narrow query results: Use logical operators (such as Equal, Not Equal or Between, for example) to refine query results. When you define two or more sources in the Sources tab, the property names in the Filters tab also include the source name. For instance, the property [CsContactBasic] Full Name has [CsContactBasic] as the source and Full Name as the property.
Select the columns to display: 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. When you define two or more sources in the Sources tab, the property names in the Display tab also include the source name. For instance, the property [CsContactBasic] Full Name has [CsContactBasic] as the source and Full Name as the property.
Define the sorting order of the columns: You can order and prioritize the properties in each column in ascending, descending, or random order. When you define two or more sources in the Sources tab, the property names in the Sorts tab also include the source name. For instance, the property [CsContactBasic] Full Name has [CsContactBasic] as the source and Full Name as the property.
Test the query: It is important to test the query and edit it, if necessary, to make sure the desired results are returned.
Save the query to an appropriate folder: 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.
Create a group that automatically refreshes: You can create a group where an automated task can refresh the query to determine the members of the group by the query results.
Modify security access to the query: You can modify the security on a filter to assign read, write, and run privileges.

Tips for building queries

Review Best practices for writing IQA queries for information about how to keep queries from causing performance issues for your website.
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.
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.
Do not put slashes (/) in IQA names. Doing so will cause errors when importing. To avoid these errors, update all existing IQA queries with slashes in their name and update any content items or reports associated with the queries.
When loading a query page, you may encounter different messages indicating the duration it took for the page to load and a link to the IQA queries: Best Practices article. The messages might include the following:
- If it takes between two and three seconds for the query to run, you will see the following yellow message:
- If it takes between two and three seconds for the query to run, you will see the following yellow message:

IQA tab options

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

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.

Viewing Query Options

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.

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.

Add to 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:

Go to RiSE > Intelligent Query Architect > New > Query.
(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.
Select a property that has a discrete drop-down list of values, such as State Province.
Select the Multiple option.
A default Value is displayed. Browse to select another 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.

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.

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:

Go to RiSE > Intelligent Query Architect > New > Query.
(Filters tab)

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

Adding properties and refreshing the Display tab

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.
SQL Expression - Enter a unique SQL statement to calculate data for a column in the query. The SQL Expression field does not support subqueries.

Building a query

The following procedure shows how to define a new query. To edit an existing query, you can follow the same procedure. In some cases, a system administrator can navigate to the web page where the results are displayed and click Edit Source Query.

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:

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

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

Enter descriptive Caption text. This text will be read by assistive technology to describe the results of your query.
(Sources tab) Choose a common source or query under Add source, or select All Sources to view a full list of sources. Find a business object or query to use as a source for the query (most business objects are located in the Common folder).

Adding business objects as sources

Note: Any business object source can be given an Alias to make it easily identifiable when defining the query.

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

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.

(optional) (Filters tab) Define the filtering rules:
(Display tab) Define how results columns are displayed
(Sorting tab) Specify the sort order for each column
Select Run to test the query. If edits to the query are necessary, select Define.
(Security tab) The default setting for a new query is All Staff Full Control. If the query needs to be available to public users, this setting must be changed accordingly. The following options are available:
- Access mode: Assigns access and privileges to the query and its results:
  - Share (Everyone) - Configures the access list to Everyone Full Control, allowing unrestricted access to all users, including the public, provided they can access the query location.
  - Private – The access list is configured to include only System Administrators and the user creating the query. This ensures that only system administrators and the person who created the query have access to it.
  - Advanced – Select a security group from the drop-down. The selected users included in the group are listed under the Current Access List.
- Current Access List – Displays the names of the authorized users. The lists changes based on the selected Access mode.
- Available via the REST API - This setting will grant API access to individual queries; however, the user trying to access the query via the API must still possess the relevant permissions as specified in the query itself. It is important that Available via the REST API is only enabled for queries that meet at least one of the following criteria:
  - The query does not contain any personally identifiable information, or
  - The query is properly filtered to only show information the requesting party is asking for, or
  - The query is secured so that only staff users have access to it
Click Save. Select the folder in which to save the query.
(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.