Building IQA queries: Tutorials

This example outlines how to build a query using only one business object.

You can construct a query that uses the CsContactBasic business object as a source to find the full names, informal names, and email addresses of all your contacts who live in a specific city and state. This example uses Austin, TX as the city and state:

1. From the Staff site, go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Enter a Name and Description for your query.
3. (Sources tab) Click Add Source:
   • Select the CsContactBasic business object.
4. (Filters tab) Select the query filters:
   1.  From the Property drop-down, select City to filter the query results by city:
      • Select Equal as the Comparison.
      • Enter Austin as the Value.
      • Select the + button.
   2. From the second Property drop-down, select State/Province to filter the query results by state:
      •  Select Equal as the Comparison.
      •  Select Texas as the Value.
      •  Select the + button.

      Only contacts who have Austin listed as the city and Texas listed as the state in their default address will display in the query results.

5. (Display tab) From the drop-down, select View all columns:
   1. Select the columns that you want to display when you run the query, such as:
      • Full Name
      • Informal
      • Email
   2. Deselect any columns that you do not want to display when you run the query.
   3. If needed, change the Order of the selected columns.
   4. Refresh the query display columns.
6. (Sorting tab) Select your preferred sorting options. For example, we will sort the results by last name in ascending alphabetical order:
   1. From the Property drop-down, select Name (Last, First).
   2. From the Direction drop-down, select Ascending.
7. Click Save.
8. Select the location where you want to save the query.
9. Click OK.

You can access and run your query from the Intelligent Query Architect or the Document system.

1. From the Staff site, go to RiSE > Intelligent Query Architect > New > Query.
2. Mode: Basic
3. (Summary tab) Enter the name and description for this query.
4. (Sources tab) Select Add Source > Common > Business Objects > CsContactBasic.
5. (Filters tab) select:
   • Filter on StateProvince.
     • Property: StateProvince
     • Comparison: Equals
     • Value: New York
     • Click + to add the filter.
   • Filter on MemberType
     • Property: MemberType
     • Comparison: Equals
     • Value: Regular Member
     • Click + to add the filter.
6. (Display tab) Select View > Selected columns. Select the columns to display and the order.
7. Click Run. Depending on your system’s display settings, your results could display similarly to the following image.

This query extracts customers who have purchased more than $100 within a specific date range.

1. From the Staff site, go to RiSE > Intelligent Query Architect > New > Query.
2. (Sources tab) > Add Source.
• Common folder > business objects.
• Select the CsContactBasic and CsActivityBasic business objects.
• Use CTRL to select multiple objects
• Make sure the default join in the Relations area is Contact.iMIS Id Equals CsActivityBasic.Id.
3. (Filters tab).
• Mode: Basic
• Filter on CsActivityBasic.Activity Type.
• Property: CsActivityBasic.Activity Type
• Comparison: Equals
• Value: ORDER
• Click + to add the filter.
• Filter on CsActivityBasic.Transaction Date.
• Property: CsActivityBasic.Transaction Date
• Comparison: Between
• Value: "1/1/2016","12/31/2016"
• Click + to add the filter.
• Filter on CsActivityBasic.Amount.
• Property: CsActivityBasic.Amount
• Comparison: Greater
• Value: 100
• Click + to add the filter.
4. (Display tab) Define the columns to display in the output:
• Using the Order drop-down list on the (Display tab), assign:
• CsContactBasic.iMIS Id Order 1
• CsActivityBasic.Amount Order 2

📘 Note

A default list of columns displays when you select the Display tab. Deselect the columns you do not need. If a column you need is not in the default list, select All from the View drop-down list and scroll down the page to select the desired column.

5. (Sorting tab) Specify the sorting priority.
• Sort the results by CsContactBasic.iMIS Id.
• Click + to add the selection.
6. Save your settings.
7. Run the query.

The following example describes how you can build a query of members defined as anyone who is a member of a group with Is member selected:

1. From the Staff site, go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Name the query. For example, My Members.
3. (Sources tab) For this example, we know we want the results to return all members of any group marked as Is member, so we need to know information about groups and their members:
   1. Click Add Source, select the CsContactBasic business object, then click OK.
4. We recommend saving your query periodically while you are creating it. Click Save As, and select a save location.
5. (Filters tab) Next, we want to narrow the list to only members of Is member groups:
   1. Click Define, then click filters. The filters tab is where we tell the query whose information we want to see.
   2. From the drop-down in the Property column, select Is Member.
      • Leave the Comparison as Equals.
      • From the drop-down in the Value column, select Yes.
      • Click the + button.
6. Now let's test the query:
   1. Click the Run tab.

   This time you should only see people who are in Is member groups.

7. (Display tab) Next, we will want to make the query results look a little better:
   1. Click the Define tab, then select Display. This is where you select what information displays and what order it is displayed in.
   2. From the drop-down at the top of the table, select View all columns to see all of the information you could include in the query.
   3. Select the following properties to display:
      • iMISId
      • Full Name
      • Member Type
      • Company
      • Country
      • State Province
      • City

      You can reorder the columns by selecting different numbers in the Order column.

8. (Sorting tab) For this example, we want the most recently-joined members displayed at the top of the list:
   1. From the Property drop-down, select Join Date.
   2. From the Direction drop-down, select Descending.
   3. Click the + icon.
9. Click the Run tab. This time you should see a list of all of your members sorted by most recent join date first.
10. Lastly, we want to make sure that only Staff users have access to run this query:
    1. Click the Security tab.
    2. In Access Mode, select Advanced.
    3. From the drop-down, select All Staff Full Control.
11. Click Save.

Now you can use your new query in any area that queries can be used. Try adding a Query Menu content item to a page, and select your query for display.

In this example, we are going to use the query built in the example above and take it a little further. We are going to add filters that can be used after the query is run, and add clickable links to the query results.

Make sure you are in Advanced Mode to follow these steps:

1. Find the query defined above and select Edit.
2. (Filters tab) We want to allow people who are viewing the query to narrow the list to members by name:
   1. From the Property drop-down, select Full Name.
   2. From the Comparison drop-down, select Contains.
   3. From the Prompt drop-down, select Required.
   4. In the text box to the right of the Prompt drop-down, you can define a label that will be used for the Required filter. Enter Name contains in this field.
   5. Click the + button.
3. Click the Run tab to see the new filter. Instead of a list, you will see a prompt for Name contains, which is required in order to see results:
   1. Enter "an" in the box (without the quotation marks), then click Find. The results return a list of all members whose name contains the letters "an." If there are no results, try a different set of letters.

   Next, we want to add links to the query results that will link to a member's profile page. To do this we will need the contact's ID to be used as a URL parameter to tell the page which contacts to display.

4. Click the Define tab, then select the Display tab:
   • iMIS Id - In the Alias field enter code_ID.

   When an IQA query is run, any column with an Alias that begins with code_ will not be displayed, but can be used for links and other IQA functions.

5. Click Run to view the results, then click Save.
6. Next, let's add the actual links:
   1. Navigate back to the Display tab.
   2. In the row Full Name, enter this in the Link field: ~/party.aspx?ID=[code_ID]

   Each result of a member's name will now display as a link to the party.aspx page with a URL parameter of ID that equals the contact's ID. In other words, it will link to their account page.

7. Lastly, we want to change the column header of Full Name to something a little shorter:
   1. In the Alias column for Full Name, enter Name. This will mean that when the query results are displayed, the column header for this column will be Name instead of Full Name.
8. Click Save.
9. Click the Run tab:
   1. Enter a filter to see results.
   2. Click a contact's name to verify the links goes to the correct account page.

This query extracts individual members who live in Texas or those who joined between 2010 and 2015, eliminating duplicates when the same rows are returned by the multiple subqueries.

1. Go to RiSE > Intelligent Query Architect. Select New > Query.
2. (Sources tab) Browse to the Common > Business Objects folder, and select CsContactBasic.
3. (Filters tab) Select the Advanced mode.
4. Create a filter on Member Type:
   • Comparison: Equal
   • Value: Regular Member
5. Click + to add the filter.
6. Create a filter on State Province:
   • Comparison: Equal
   • Value: Texas
7. Click + to add the filter.
8. Click Add Filter.
9. Select Or from the Property drop-down list.
10. Create a filter on Member Type equal to Regular Member:
    • Comparison: Equal
    • Value: Regular Member
11. Click + to add the filter.
12. Create a filter on Join Dates:
    • Comparison: Between
    • Value: 1/1/2010 and 12/31/2015
13. Click + to add the filter.
14. (Display tab) Select the Only display unique results option.
15. Select the following columns:
    • iMIS Id
    • State Province
    • Join Date

📘 Note

A default list of columns is displayed when you select the Display tab. Deselect the columns you do not need. If a column you need is not in the default list, select View all columns from the drop-down, and scroll to find and select the desired column.

16. In the Order column, assign the following:
    • iMIS Id – 1
    • State Province – 2
    • Join Date – 3
17. (Sorting tab) Sort the results by iMIS Id to specify the sorting priority.
18. Click + to add your selection.
19. Save your settings and run the query.

1. From the Staff site, go to RiSE > Intelligent Query Architect > New > Query.
2. Mode: Basic
3. (Summary tab): enter the name and description for this query.
4. (Sources tab) Select Add Source > Common > Business Objects > CsContactBasic and > Activity.
5. Use the default relation between the two business objects:
   FK_ Name_Activity (When CsContactBasic.Imis Id = Activity.Id)
6. (Filters tab):
   • Filter on CsContactBasic.State Province
     • Property: CsContactBasic.State Province
     • Comparison: Equal
     • Value: New York
     • Click + to add the filter.
   • Filter on CsContactBasic.Member Type
     • Property: CsContactBasic.Member Type
     • Comparison: Equal
     • Value: Regular Member
     • Click + to add the filter.
   • Filter on Activity.Activity Type
     • Property: Activity.Activity Type
     • Comparison: Equal
     • Value: MEETING
     • Click + to add the filter.
   • Filter on Activity.Transaction Date
     • Property: Activity.Transaction Date
     • Comparison: Greater Equal
     • Value: 1/1/2009
     • Click + to add the filter.
7. (Display tab) Select View > Selected columns. Select the columns to display and the order. Select the Only display unique results option.
8. Click Run. The results are a subset of the list in the previous example.
Click to view image


Example query results: Members from New York who attended the 2009 Conference

This query extracts the total number of members who have made a pledge of $50 or more.

1. Go to RiSE > Intelligent Query Architect. Select New > Query.
2. (Sources tab) Browse to the Common > Business Objects folder, and select the following business objects:
   • CsContactBasic
   • CsActivityBasic
3. (Filters tab) Select the Advanced mode.
4. Filter on CSActivityBasic.Activity Type:
   • Comparison: Equal
   • Value: PLEDGE
5. Click + to add the filter.
6. Filter on CsActivityBasic.Amount:
   • Comparison: Greater
   • Value: 50
7. Click + to add the filter.
8. (Display tab) Select to display the CsContactBasic.iMIS Id property:
   • Function: Count
   • Alias: Pledges
   • Order: 1

📘 Note

A default list of columns is displayed when you select the Display tab. Deselect the columns you do not need. If a column you need is not in the default list, select View all columns from the drop-down, and scroll to find and select the desired column.

9. Save your settings and run the query.

This query joins two business objects to extract the registrants for a specified event. At runtime, the user supplies the event code.

1. Go to RiSE > Intelligent Query Architect.
2. Browse to the folder in which you want to store a new query. Select New > Query.
3. (Summary tab) Enter a Name for the query.
4. (Sources tab) Browse to the Common > Business Objects folder, and select the following business objects:
   • CsContactBasic
   • CsEventHistory
5. In the Relations area, make sure the default relation is CSContactBasic.iMIS Id = CsEventHistory.Id.
6. (Filters tab) Select the Advanced mode.
7. Filter on CsEventHistory.Event Code:
   • Comparison: Equal
   • Prompt: Optional or Required.

   📘 Note

   Enter a prompt in the field, for example, Event Code.

8. Click + to add the filter.
9. (Display tab) Select the following columns:
   • CsContactBasic.iMIS Id
   • CsContactBasic.Full Name
   • CsContactBasic.Company
   • CsEventHistory.Event

   📘 Note

   A default list of columns is displayed when you select the Display tab. Deselect the columns you do not need. If a column you need is not in the default list, select View all columns from the drop-down, and scroll to find and select the desired column.

10. Click Refresh.
11. In the Order column, assign the following:
    • CsContactBasic.iMIS Id Order – 1
    • CsEventHistory.Event Order – 2
12. (Sorting tab) Sort the results by CsEventHistory.Event.
13. Click + to add the selection.
14. Save your settings and run the query.

📘 Note

If you want to show current registrants of open events, link to csRegistration instead of csEventHistory and set the default relation as CsContactBasic.iMIS ID = csRegistration.Ship To ID. 📘 Note

The primary difference between Cs and non-Cs objects is that the non-Cs objects contain the .NET security, but Cs objects are views pointing to the underlying non-.NET tables. When you use a non-Cs object, you might notice additional fields such as Access Key and Contact Key. These fields 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.

This query shows individual members with an AWARD activity type.

1. Go to RiSE > Intelligent Query Architect. Select New > Query.
2. (Sources tab) Browse to the Common > Business Objects folder, and select the following business objects:
   • CsContactBasic
   • CsActivityBasic
3. (Filters tab) Select the Advanced mode.
4. Filter on CsActivityBasic.Activity Type:
   • Operator: Where
   • Comparison: Equal
   • Value: AWARD
5. Click the + icon to add the filter.
6. Click Add Filter.
7. Filter on CsContactBasic.Member Type:
   • Operator: And
   • Comparison: Equal
   • Value: Regular Member
8. Click the + icon to add the filter.
9. (Display tab) Select the following columns:
   • CsContactBasic.iMIS Id
   • CsContactBasic.Full Name
   • CsContactBasic.City
   • CsActivityBasic.Description
   • CsActivityBasic.ProductCode
10. In the Order column, assign the following:
    • CsContactBasic.City Order – 1
    • CsContactBasic.Full Name Order – 2
11. (Sorting tab) Sort the results by CsContactBasic.iMIS Id.
12. Click + to add your selection.
13. Save your settings and run the query.

The Progress Tracker content item uses a specifically-designed query to display progress towards a goal. The query design depends on the type of goal that is trying to be achieved. The two examples below detail how to build queries that will be used in the Progress Tracker content item.

Using IQA, you can build a query that displays summary information on one row with the ability to drill down and see more details. This example demonstrates how to build a query that displays a list of event registrations with a drop-down to see the registration options and program items for which users have registered.

1. From the Staff site, go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query Registrations. Make sure you are in Advanced mode.
3. (Sources tab) Click Add Source:
   1. Select CsEvent, CsFunction, CsRegFunctions and CsRegistration, then click OK.

   📘 Note

   You can use the Ctrl key to select multiple business objects at once. Use the Quick find field to filter the list.

4. Set up the relationships between the four selected business objects:
   1. The automatic join of CsRegistration and CsRegFunctions on the Order Line will not work in this context, so delete the automatic join by clicking the X on the right. Leave the automatic join of CsEvent and CsFunction as-is.
   2. From the Relations drop-down, select Custom:
      1. From the drop-downs, select CsRegistration.Event Code Equals CsFunction.Event Code.
      2. Click the + button.
      3. Add another relation. From the drop-down, select Custom.
      4. From the drop-downs, select CsRegistration.Ship To Id Equals CsRegFunctions.Ship To Id.
      5. Click the + button.
5. (Display tab) Select the information you want to see in the query:
   1. From the View drop-down, select View all columns.
   2. Select the Display checkbox for the following properties:
      • CsEvent.Title
      • CsFunction.Title
      • CsRegFunctions.Registered Quantity
      • CsRegFunctions.Status
      • CsRegistration.Full Name
      • CsRegistration.Order Date
      • CsRegistration.Status
   3. Re-order and alias the properties as appropriate.
6. (Sorting tab) Sort by the name of the event, then the name of the registrant. Then you will sort the functions. Display the registration option, then sort the program items by name. This will require four sorts total:
   1. From the Property drop-down, select CsEvent.Title, with the Direction of Ascending. Click the + button.
   2. From the second Property drop-down, select CsRegistration.Last First with the Direction of Ascending. Click the + button.
   3. From the third Property drop-down, select CsFunction.Is Event Registration Option with the Direction of Descending. Click the + button.
   4. From the fourth Property drop-down, select CsFunction.Title with the Direction of Ascending. Click the + button.
7. Save the query, then select the Run tab.

   Notice that there is a row in the query per function registration, and the overall event registration data is repeated on each of these rows. Modify the query so that there is just one row per registration with a drop-down to see the selected registration options and program items.

8. Select the Define tab, then select the Display tab.
9. Select the Subtotal checkbox for the following properties:
   • CsEvent.Title
   • CsRegistration.Full Name
   • CsRegistration.Order Date
   • CsRegistration.Status
   
10. Select the Run tab. You should now see one row per order with a toggle button to the left of each row.
11. Select the toggle button to see the details of the order.

12. Save the query. You can use the Query Menu content item to display the query results on any page.

The above query example can quickly become overwhelming after just a few registrations have been entered. In this example, we will look at a couple of different ways to dynamically filter the query results to only display needed information.

This example uses the Query Menu content item and the Link feature to display completed orders to users.

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Enter the name Completed Orders for the query.
3. (Sources tab) Browse to the Common > Business Objects folder, and select the following business objects:
   • CsContactBasic
   • CsOrderHistory
4. (Filters tab) Select the Advanced mode.
5. (Display tab) Select the following columns:
   • CsContactBasic.Full Name
   • CsOrderHistory.Transaction Date
   • CsOrderHistory.Id
   • CsOrderHistory.Product Name
   • CsOrderHistory.Product Code
   • CsOrderHistory.Quantity
6. Give the following properties an Alias prefaced with code_, so that the column value is hidden in the query results but can still be used as a reference in the Link:
   • CsOrderHistory.Id – Alias code_ID
   • CsOrderHistory.Product Code – Alias code_ProductCode
7. For CsContactBasic.Full Name, enter the following Link:

~/MyAccount&ID=[code_ID]

📘 Note

This Link assumes that you have a shortcut named MyAccount pointing to a content record containing Contact content items that can use the ID URL parameter.

8. For CsOrderHistory.Product Name, enter the following for the Link:

~/ItemDetail&iProductCode=[code_ProductCode]

📘 Note

This Link assumes that you have a shortcut named ItemDetail pointing to a content record containing the General Product Display content item.

9. (Sorting tab) Select the CsOrderHistory.Transaction Date property and sort by Descending.
10. Save the query.
11. Go to Page Builder > Manage content, and add the Query Menu content item to a content record.
12. In the Source query or folder field, select the Completed Orders query.
13. Click OK, then save and publish your content record.
14. Go to Site Builder > Manage sitemaps, and add the content record to a navigation item. When the query results are displayed in your website, the results in the Account and Product columns are linked to the pages you defined in your query.

The Query Chart Viewer dynamic content item builds a dynamic chart based on the query data. The chart can be set to a range of sizes, skins, and chart types. For more information, see Query Chart Viewer.

In this example, you will learn how to build a query that will be used to display a stacked bar chart of a contact's gift contributions over two years. Each year will be broken up into the different funds the contact contributes to. For example, this Giving history by calendar year chart:

1. From the Staff site, go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query Contact Gift Contributions by Year.
3. Make sure you are in Advanced Mode.
4. (Sources tab) Click Add Source:
   1. Select GiftsReceived, then click OK.
5. (Filters tab) We only want to see gifts that were given by the selected user from this year and last:
   1. To filter on the selected user, only add a filter that selects ID Equals "@SelectedId". Click the + button.
   2. To filter on just this year and last, add a filter with Date Received as the property, Within Years as the comparison, and 1 as the value. Click the + button.

   📘 Note

   The date "within" comparisons are zero-based. This means that if you want to filter to just this year's activities, you should set Within Years to 0. For two years you would set it to 1, and so forth.

6. (Display tab) Select the Display checkbox for the following properties:
   1. Calendar Year
   2. Fund
   3. Amount
7. For a multi-series chart in the Query Chart Viewer, you must have at least one property that is selected as a Subtotal column. This should be the property that you want to display on the x-axis. Select the Subtotal checkbox for the Calendar Year property.
8. For the Amount property, select the Sum function.

9. Click the Run tab. Here you will see a list of the gifts that you have made in the last two years with the ability to drill down to see the breakdown by fund.
10. Save the query.
11. Now we need to configure the content item. Navigate to the account page and turn on Easy Edit. Open the page for edit and add the Query Chart Viewer content item.
12. In the configuration for the content item, set the following settings:
    1. Chart data:
       • Source query = Select the query that we just created.
    2. Display options:
       • Type = Bar - Vertical
       • Enabled stacked series = checked
    3. Chart data:
       • Series data column = CalendarYear (This will be the x-axis label)
       • Series data format = Year
       • Data axis = Amount (This will be the value on the y-axis)
       • Data axis format = Integer value (This will be the categories each column is broken down into)
       • Label axis format = No Format
    4. Set the other settings as desired. The following are a few of the settings we selected:
       • Height = 400
       • Width = 0 (When the width is set to 0 it will stretch to fit the width of the container)
       • Skin = Metro
       • Legend Position = Right
13. Now when you view the account page it will display a bar chart indicating the selected user's gifts broken down by year and fund contributed to.