Query examples

Using Basic mode

This query extracts those individuals whose companies are located in the state of Texas.

  1. In the Staff site, go to RiSE > Intelligent Query Architect > New > Query.
  2. (sources tab) > Add Source.
    • Common > Business Objects.
    • Select CsContact twice.

      • Note: When you add multiple instances of a source, the system automatically appends a numeric value (beginning with the number 1) to the second source and all following sources in order to distinguish between each instance. For example, if you add the CsContact business object three times, the system labels the second and third instances CsContact1 and CsContact2, respectively.

    • In the Relations area of the Sources tab, make sure the custom relation is CsContact.Company Id Equals CsContact1.iMISId.
  3. (filters tab).
    • Mode: Basic
    • Filter on CsContact.MemberType.
      • Property: CsContact.MemberType
      • Comparison: Equals
      • Value: Regular Member
      • Click + to add the filter.
    • Filter on CsContact1.StateProvince.
      • Property: CsContact1.StateProvince
      • Comparison: Equals
      • Value: TX
      • Click + to add the filter.
  4. (display tab) Define the columns to display in the output.
    • Select the following columns:
      • CsContact.iMISId
      • CsContact.Company
      • CsContact.MemberType
      • CsContact1.StateProvince

      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 View all columns from the drop-down list and scroll down the page to select the desired column.

    • Using the Order column on the Display tab, assign the following order:
      1. CsContact.iMISId
      2. CsContact.Company
      3. CsContact.MemberType
      4. CsContact1.StateProvince
  5. (sorting tab). Specify the sorting priority.
    • Sort the results by CsContact.Id.
    • Click + to add your selection.
  6. Save your settings.
  7. Run the query.

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

  1. In the Staff site, go to RiSE > Intelligent Query Architect > New > Query.
  2. (sources tab) > Add Source.
    • Common folder > business objects.
    • Select the CsContact 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/2011","12/31/2011"
      • 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:
      • CsContact.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 CsContact.iMIS Id.
    • Click + to add the selection.
  6. Save your settings.
  7. Run the query.
  1. In 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 > CsContact.
  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.


    8. Example query results: Members from New York

  1. In 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 > CsContact and > Activity.
  5. Use the default relation between the two business objects:
    FK_ Name_Activity (When CsContact.Imis Id = Activity.Id)
  6. (filters tab):
    • Filter on CsContact.State Province
      • Property: CsContact.State Province
      • Comparison: Equal
      • Value: New York
      • Click + to add the filter.
    • Filter on CsContact.Member Type
      • Property: CsContact.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.


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

Using Advanced mode

This query extracts individual members who live in Texas or those who joined between 1993 and 1997, 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 CsContact.
  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/1993 and 12/31/1997
    • Click + to add the filter.
  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

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

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:
    • CsContact
    • 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 CsContact.iMIS Id property:
    • Function: Count
    • Alias: Pledges
    • Order: 1

    9. 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:
    • CsContact
    • CsEventHistory
  5. In the Relations area, make sure the default relation is CSContact.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:
    • CsContact.iMIS Id
    • CsContact.Full Name
    • CsContact.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:
    • CsContact.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 CsContact.iMIS ID = csRegistration.Ship To ID.

Note: The primary difference between Cs and non-Cs objects is that the the non-Cs objects contain the .NET security, but Cs objects are basically just 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:
    • CsContact
    • 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 CsContact.Member Type:
    • Operator: And
    • Comparison: Equal
    • Value: Member
  8. Click the + icon to add the filter.
  9. (display tab) Select the following columns:
    • CSContact.iMIS Id
    • CsContact.Full Name
    • CsContact.City
    • CsActivityBasic.Description
    • CsActivityBasic.ProductCode
  10. In the Order column, assign the following:
    • CsContact.City Order – 1
    • CsContact.Last Name Order – 2
  11. (sorting tab) Sort the results by CsContact.iMIS Id.
  12. Click + to add your selection.
  13. Save your settings and run the query.

In this example, the query finds all of the contacts created this year using the @Now variable to dynamically filter the query.

  1. Go to RiSE > Intelligent Query Architect. Select New > Query.
  2. (summary tab) Enter a Name for the query.
  3. (sources tab) Browse to the Common > Business Objects folder, and select the CsContact business objects:
  4. (filters tab) Select the Advanced mode.
  5. Filter on Date Added:
    • Comparison: Between
    • Value: 1/1/2014 and @Now
  6. Click the + icon to add the filter.
  7. (display tab) Customize the fields to display.
  8. Save your settings and run the query. When you run the query, it lists all of the contacts created so far this year.

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:
    • CsContact
    • CsOrderHistory
  4. (filters tab) Select the Advanced mode.
  5. (display tab) Select the following columns:
    • CsContact.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 CsContact.Full Name, enter the following Link:
    8. ~/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:
    9. ~/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.