Intelligent Query Architect (IQA) is a query-building tool that extracts data from iMIS. IQA simplifies query-building tasks by providing a user-friendly interface that makes it easy for a user with limited SQL experience to build queries and reports.
Use IQA to:
- Display targeted information to users, such as a list of committee members, participation lists on contact account pages, and lists of payments and invoices
- Create reports with the Microsoft SQL Server Reporting Service (SSRS)
- Present queries to users with runtime prompts
- Allow users to create ad hoc, dynamic reports
- Organize data for mailings and letter merges
- Gather data for business analysis
To increase efficiency, you can copy and combine existing queries to define new ones. Users can access IQA queries through various locations within iMIS, but role-based security settings control access to queries and the underlying data.
Important! Poorly written queries can cause performance issues in iMIS. Before starting to work with queries in the Intelligent Query Architect, review the Best practices for writing IQA queries.
Key Features
IQA allows you to specify many data display options, including:
- Fields displayed from one or more business objects, including the ability to display concatenated or calculated fields.
- The sort order for displaying rows in a list.
- The selection criteria to locate rows to display, including the ability to prompt the user for runtime criteria. For example, you can specify that the user can enter an invoice number to locate an invoice.
- Which page to display if it is appropriate to show additional information for the record being viewed. For example, you can specify that the Invoice Detail window should open if a user clicks on an invoice number link in the list.
And you can design a query once and use it in many locations throughout the system.
Task by role
The tasks a user performs depend on their security authorization. This is role-based. Use the security controls and display options to make sure that the correct query results and also that the access to the results are appropriate for each type of user. Typically, an administrator would set permissions and security, an authorized user would define the query, and a lower-level user would run and export the available queries.
Understanding iMIS queries
Queries are a central concept in iMIS. They are used to extract data from the database, but they also drive many application elements. For example, queries are used as the source for many user interface elements, especially for the display of data to users. Queries are also used as sources for reports.
The data sources for queries can be other queries or iMIS Business Objects. Queries can be simple, using a single source; or they can be complex, with multiple sources and different types of joins. For example, a simple query can gather and display all contacts from the province of Ontario. A complex query might organize donors by giving levels and frequency of gifts to generate a detailed report.
Query definitions are stored in the Document system. Access to queries is granted through security settings.
Outputs
You can generate a variety of outputs from IQA:
- Web page display – Queries can deliver results in a quick list view (Run) or in a formatted web page that enhances your organization’s website.
- Content item display – Queries drive the data displayed to users in many content items. See Displaying query results in content items for details.
- Mail merge file
- Reports – Combine query results with report formats in Microsoft SQL Server Reporting Services (SSRS) to create, access, modify, and share reports.
- File
Concepts and Terms
Business objects are collections of self-contained data that represents an entity or process, and shields the physical data. You can use business objects to build the query definition, or you can base the query on an existing query.
Use joins to specify which fields are equivalent so the files can relate to each other correctly. The most common joins in iMIS center on member ID numbers.
Store the query in the Document system, iMIS’s server-side file system.
Define the results display with content items, web part controls that allow you to create and edit website content.
Administering IQA
The steps to set up and use IQA include:
- Administrator defines security permissions and global settings for queries.
- An authorized user builds the query definition using Business Object data and/or other queries, with appropriate joins between the data. The user defines the results display or report format and stores the query in the Document system.
- An end user selects a query and supplies any required parameters. The user runs the query and views the results in a web page or a report.
To access IQA from the Staff site, go to RiSE > Intelligent Query Architect.
Note: IQA can be accessed only by an administrator or a user who is allowed to set up queries. Only system administrators have access to the SQL Expression textbox on the query Display tab.