Defining queries

• Define the query mode: 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.
• 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.
• 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.
• Define the sorting order of the columns: You can order and prioritize the properties in each column in ascending, descending, or random order.
• 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.

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

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

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 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.

5. Enter descriptive Caption text. This text will be read by assistive technology to describe the results of your query.
6. (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).

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

7. (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.

8. (optional) (Filters tab) Define the filtering rules:
   Click for details
   • Select the Property to be compared.
   • Select the type of Comparison, for example, Equal, Not Equal or Less.
   • Enter the Value or select a date for comparison.

   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.
   • 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
   Click for details

   • 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. If this option is enabled, do not use the Subtotal option on the display properties.
   • 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
    Click for details

    • Select the Property to sort by.
    • The Random order sorting property is available for each query. When added as a sort property to a query, the results are returned in a random order, and the order is different each time the query is refreshed. See Creating a query that returns the results in a random order for more information.
    • 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 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 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.

Advanced options for Summary

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.

The SQL is formatted. You can click Copy SQL to copy the generated SQL code to your clipboard.

Advanced options for Filters

Filters refine a 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.

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) Select Advanced mode. 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.

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.

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) 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.

Allow query to run for (maximum)

When in Advanced mode, 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.

Advanced options for Display

In Advanced mode, the following options are available on 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.
• 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.

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

Note: Do not use the Subtotal 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 only supports subqueries.

Advanced options for Template

The Template tab is only available when in Advanced mode and 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.