Building IQA queries: Tutorials

Queries can be built using only one business object. This example shows how to build a query that uses the NetContactData business object. Using the NetContactData business object as a source allows the query to find the full names, informal names, and email addresses of all contacts who live in a specific city and state. This example uses Austin, TX as the city and state.

Do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Enter a Name and Description.
3. (Sources tab) Under Add source in the Common section, select Contacts to add the NetContactData business object.



4. (Filters tab) Select the query filters:
   1.  From the Property (Add a filter) drop-down, select State Province to filter the query results by city:
      • Select Equal from the Comparison drop-down.
      • Select Texas from the Value drop-down.

      

   2. From the second Property (Add a filter) drop-down, select City to filter the query results by state:
      •  Select Equal from the Comparison drop-down.
      •  Select Austin from the Value drop-down.

      

      The query results will show only contacts who have Austin listed as the city and Texas listed as the state in their default address.

5. (Display tab) Select the columns that you want to display when you run the query, such as:
   • Full Name
   • Email
   • Informal

   

   1. Deselect any columns that you do not want to display when you run the query.
   2. If needed, change the order of the selected columns by selecting numbers from the drop-downs in the Order column.
   3. Click Refresh to refresh the query display columns.

   

6. (Sorting tab) Select preferred sorting options. For example, sort the results by last name in ascending alphabetical order:
   1. From the Property (Add sort) drop-down, select Last First.
   2. From the Direction drop-down, select Ascending.

   

7. Click Run to display the query results.
8. Click Save.
9. Select the location where you want to save the query. Choose a non-system folder.
10. Click OK.

Access and run your query from the Intelligent Query Architect or the Document system.

Western Australia shares the same code with the state of Washington, USA (WA). Because Western Australia and Washington state share the same code, you must use a few other business objects in the query to ensure you are correctly pulling data for Western Australia and not Washington, USA. This example shows how to build a query that finds all members in Western Australia by using the CsContactBasic, CsCountryNames, and CsStateProvinceCodes business objects.

Notes: The same situation occurs for Northern Territory, AU. If you are trying to use any of the CsContact business objects as standalone business objects, such as in billing special pricing, we recommend using NetContactData.StateProvinceId. See Creating special pricing rules for Northern Territory or Western Australia for details.

Do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Enter a Name for the query.
3. (Sources tab) Select All sources, then select CsContactBasic, CsCountryNames and CsStateProvinceCodes. Click OK.

Notes: 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 business objects.
   1. From the Relations drop-down, select Custom. Add the following relations:
      1. CsContactBasic.State Province Equals CsStateProvinceCodes.State/Province
      2. CsContactBasic.Country Left Join CsCountryNames.Country
      3. CsCountryNames.Country Code Left Join CsStateProvinceCodes.Country Code

      Click the plus (+) icon to add each join.

5. (Filters tab) Select the query filters:
   • From the Property (Add a filter) drop-down, select [CsContactBasic] State Province Id:
     • From the Value drop-down, select Western Australia.
   • From the Property (Add a filter) drop-down, select [CsCountryNames] Country Code:
     • Property:  [CsCountryNames] Country Code
     • From the Value drop-down, select AU.
   • From the Property (Add a filter) drop-down, select [CsContactBasic] Member Type
     • From the Value drop-down, select Regular Member.

     Notes: Western Australia has the same abbreviation as Washington (WA), which results in the query returning duplicate rows for Washington and Western Australia. Adding the Country Code property to the filters tab will resolve duplicates for state/provinces with the same abbreviation.

6. (Display tab) Select the following display options:
   • [CsContactBasic] Full Name
   • [CsContactBasic] iMIS Id
   • [CsContactBasic] Email
   • [CsContactBasic] Informal
   • [CsStateProvinceCodes] Title
7. Click Run.
8. Click Save.

This example query finds customers who have purchased more than $100 within a specific date range by using the CsContactBasic and CsActivityBasic business objects.

Do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Name the query Customers who spent $100+ in 2017.
3. (Sources tab) Click All sources, then select CsContactBasic and CsActivityBasic. Click OK.
• Use the default join in the Relations area: CsContactBasic.iMIS Id Equals CsActivityBasic.Id.



4. (Filters tab) Select the query filters:
• From the Property (Add a filter) drop-down, select [CsActivityBasic] Activity Type:
• Select ORDER from the Value drop-down.
• From the Property (Add a filter) drop-down, select [CsActivityBasic] Transaction Date:
• Select Between from the Comparisondrop-down.
• Input 1/1/2017 and 12/31/2017 into the Value fields.
• From the Property (Add a filter) drop-down, select [CsActivityBasic] Amount:
• Select Greater from the Comparison drop-down.
• Input 100 into the Value field.



5. (Display tab) Define the columns to display in the output:
• Choose [CsContactBasic] iMIS Id, [CsActivityBasic] Amount, and [CsContactBasic] Full Name.
6. (Sorting tab) Specify the sorting priority.
• Sort the results by [CsContactBasic] Full Name.
7. Click Save.
8. Click Run to run the query.

The following example describes how to build a query of members defined as anyone who is a member of a group with Is member selected. This query uses the NetContactData business object, and the Filters tab, the Display tab, and the Sorting tab (accessed through the Define tab) to filter and refine the query’s visual output. Using the Security tab to restrict access to the query makes the query’s output secure.

Do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Enter a Name for the query. For example, My Members.
3. (Sources tab) Under Add source in the Common section, select Contacts to add the NetContactData business object.
4. (Filters tab) Filter the list to only members of Is member groups:
   1. From the Property (Add a filter) drop-down, select Is Member.
      • Use the default Comparison selection: Equal.
      • Select Yes from the Value drop-down.
5. Click the Run tab. The results should show only people who are in Is member groups.
6. (Display tab) Configure the look of the query results:
   1. From the Selected and Available lists, select the following properties to display:
      • iMISId
      • Full Name
      • Member Type
      • Company
      • Country
      • State Province
      • City

   Reorder the columns by selecting different numbers in the Order column.

   Notes: The Property name search box dynamically filters available columns to display only the items searched for in the Property name box. For example, searching for .id brings up only items that contain .id.

7. (Sorting tab) Display the most recently joined members at the top of the list:
   1. From the Property drop-down, select Join Date.
   2. From the Direction drop-down, select Descending.
8. Click Run. The results should show all members sorted by most recent join date first.
9. Make sure that only Staff users have access to run this query:
   1. Click the Security tab.
   2. In Access Mode, select Advanced.
10. 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.

The following example shows how to build a query of contacts with their associated chapters by using the Chapter and NetContactData business objects.

Do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Enter a Name for the query.
3. (Sources tab) Click All sources, then select Chapter and NetContactData. Click OK.
4. Join the sources:
   1. From the Relations drop-down, select Custom.
   2. From the drop-downs, select Chapter.Chapter Code Equals NetContactData.Chapter.
   3. Click the plus (+) icon to add the join.



5. (Display tab) Select the following display properties:
   • [NetContactData] Id
   • [NetContactData] Full Name
   • [Chapter] Chapter Name
   • [Chapter] Chapter Code
6. Click Refresh to refresh the query display columns.
7. Click Run.

The query results show any member enrolled in a chapter.

The following example describes how to build a query to display images of members by using the CsContactBasic and CsNamePicture business objects. Use the Filters tab to filter the query to a list of members of profile pictures.

The following example describes how you can build a query to display images of members:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Name the query. For example, Pictures of members.
3. (Sources tab) For this example, we know we want the results to return images of members, so we need to know information about the members:
   1. Select All sources, select the CsContactBasic business object and the CsNamePicture business object, then click OK.
   2. Next, we need to make sure each row of data displays only the related information:
      1. Under Relations, select Custom from the drop-down.
      2. Select from the drop-downs so the equation reads CsContactBasic.iMIS Id Equals CsNamePicture.Id.
      3. Click the plus (+) icon to add the join.

      Notes: If you do not see any information, or the information you see is not useful, modify the returned display. Click the Display tab to change the results, then run the query again.

4. (Filters tab) Narrow the list to members with profile pictures:
   1. From the Property (Add a filter) drop-down, select [CsNamePicture] Id and [CsContactBasic] iMIS Id.
5. (Display tab) Configure the look of the query results:
   1. Select the following properties to display:
      • [CsContactBasic] iMIS Id
      • [CsContactBasic] Full Name
      • [CsContactBasic] Member Type
      • [CsNamePicture] Picture Logo
      • [CsNamePicture] Id

   Notes: The Length field is only available to text and images. Entering a value will change the IQA image size and limit the number of characters allowed in a field. This option is disabled for fields that are numeric, boolean, date, and so forth.

6. (Sorting tab) Sort the names in alphabetical order:
   1. From the Property drop-down, [CsContactBasic] iMIS Id.
   2. From the Direction drop-down, select Ascending.
7. Click Run to see the query's output.



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

Using the File upload property in Panel Designer, members can upload files directly to their account pages. If the files have an image extension (such as .PNG, .JPG, or more), staff users can create an IQA query to neatly display all images uploaded through that panel source.

The following example shows how to build a query that displays images stored in a panel data source.

Do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Enter a Name for the query. For example, Badge Photos.
3. (Sources tab) Choose All sources, then find the panel data source that has the images you want.
4. (Display tab) Select properties that contain the images.
   1. Under Length, input the image width (in pixels). The size of the image will increase or decrease depending on the number.

5. Click Run. The image results appear in the IQA results.

This example query returns the results in a random order by using the Random order property in the Sorting tab.

Do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Enter a Name for the query.
3. (Sources tab) Choose the necessary source.
4. (Filters tab) If there are filters required for the search query, add them.
5. (Display tab) Choose the display properties.
6. (Sorting tab) From the Property drop-down, choose Random order. When Random order is selected, the Direction value is ignored.



7. Click Run to review the query results.
8. Click Refresh to confirm new results display each time the query is refreshed.
9. (optional) By default, if the query results are longer than one page, the results’ order will change if the user navigates through the pages, which may be confusing for the user. To keep query result pages from refreshing, enable Use cached results on the Summary tab.

The Organization Administrator query is a pre-built query in iMIS that returns all company (organization) administrators.

Querying administrators of all companies

This example shows how to use the Organization administrators query to find administrators in all companies.

Do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Enter a Name for the query.

3. (Sources tab) Expand the Contacts section, then select Organization administrators.



4. Save the query in a non-system folder.

5. (optional) (Display tab) Other properties can be added from the Selected and Available lists as needed by selecting the desired property.

Querying administrators for a specific company

To find the administrators for a particular company, add the following filter to the Organization administrators query in the Filter tab:

• Property: Organization.ID
• Value: Enter the company's iMIS ID.

The query output should show the organization ID, the organization, the Administrator ID, and the name of the Administrator.

This example uses the out-of-the-box Promotions used query to find event registrations that used promotion codes. The query displays each event registration that used a promotion, detailing the event name, registrant name, promotion applied, and discount amount, among other information.

Do the following:

1. Go to Events > Find registrations.
2. Select the Promotions used query.
3. (optional) To find specific registrations, enter any search filter, then click Find.
4. Click View registration to view details such as the event dates, registration option, registration status, and the remaining balance.

The Promotions used query is at: $/EventManagement/DefaultSystem/Queries/Find registrations/Promotions used. To customize this query, create a copy of the query to prevent your changes from being overwritten in a future upgrade. For more information, see Finding and editing existing queries.

Example: The finance department needs to know how much was invoiced each day during the month of December.

Do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query.
3. Sources tab:
   1. Add the DateDimension and InvoiceSummary business objects.
   2. Add the following relation: DateDimension.The Date = InvoiceSummary.Invoice Date

   

4. Filters tab:
   1. Add the [DateDimension] The Date property as a filter.
   2. From the Comparison drop-down, select Between.
   3. In the Value fields, enter 12/1/2022 and 12/31/2022.

   

5. Display tab:
   1. Select [InvoiceSummary] Invoice Amount.
   2. Select DateDimension.The Date.
   3. Enable Group By on [DateDimension] The Date.

   

6. Select the Format in which properties will be displayed when the query is Run.
7. Click Run.

In this example, we are going to create a query that contains filters and links. The filters can be used after the query is run, and the links are clickable links to the query results.

Do the following to create the query:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query.
3. (Sources tab) Select Add source, then select the CsContactBasic business object.
4. (Filters tab) We want to allow people who are viewing the query to narrow the list to members by name:
   1. From the Property (Add a filter) drop-down, select Full Name.
   2. From the Comparison drop-down, select Contains.
   3. From the In Search? drop-down, select Required.
   4. Enter "Name contains" into the Search Label field. This defines a label that will be used for the Required filter.

   

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

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

7. Click Run to view the results, then click Save.
8. Next, add the actual links:
   1. Navigate back to the Display tab.
   2. In the row Full Name, enter the following 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.

9. Lastly, we want to change the column header of Full Name to something a little shorter:

   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.

10. From the Order column, arrange the columns to your desired order.
11. Click Save.
12. 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.
2. Select New > Query.
3. (Sources tab) Under Add source, select Contacts from the Common section. This adds the NetContactData business object.
4. (Filters tab)
5. Create a filter on Member Type:
   • Comparison: Equal
   • Value: Regular Member
6. Create a filter on State Province Id:
   • Comparison: Equal
   • Value: Texas
7. Click Add Condition and select Or.
8. Create a filter on Member Type equal to Regular Member:
   • Comparison: Equal
   • Value: Regular Member
9. Create a filter on Join Dates:
   • Comparison: Between
   • Value: 1/1/2010 and 12/31/2015
10. (Display tab) Select the Only display unique results option.
11. Select the following columns:
    • Id
    • State Province
    • Join Date

Notes: A default list of columns is displayed when you select the Display tab. Deselect the columns you do not need.

12. Click Refresh.
13. In the Order column, assign the following:
    • Id – 1
    • State Province – 2
    • Join Date – 3
14. (Sorting tab) Sort the results by iMIS Id to specify the sorting priority.
15. Save your settings and run the query.

Do the following to find all members in New York who attended an event:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab): Enter the name and description for this query.
3. (Sources tab) Select All sources > Common > Business Objects > CsContactBasic and > Activity.
4. Use the default relation between the two business objects: FK_ Name_Activity (When CsContactBasic.Imis Id = Activity.Id)
5. (Filters tab):
   • Filter on [CsContactBasic] State Province Id
     • Property: [CsContactBasic] State Province Id
     • Comparison: Equal
     • Value: New York
   • Filter on [CsContactBasic] Member Type
     • Property: [CsContactBasic] Member Type
     • Comparison: Equal
     • Value: Regular Member
   • Filter on [Activity] Activity Type
     • Property: [Activity] Activity Type
     • Comparison: Equal
     • Value: MEETING
   • Filter on [Activity] Transaction Date
     • Property: [Activity] Transaction Date
     • Comparison: Greater Equal
     • Value: 1/1/2009

     

6. (Display tab) Select the columns to display and the order. Select the Only display unique results option.



7. Click Run. The results are a subset of the list in the previous example.


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. Use the default relation between the two business objects: FK_Name_Activity (When CsContactBasic.iMIS Id = CsActivityBasic.Id)
4. (Filters tab)
5. Filter on [CSActivityBasic] Activity Type:
   • Comparison: Equal
   • Value: PLEDGE
6. Filter on [CSActivityBasic] Amount:
   • Comparison: Greater
   • Value: 50

   

7. (Display tab) Select to display the [CsContactBasic] iMIS Id property:
   • Function: Count
   • Alias: Pledges
   • Order: 1



Notes: A default list of columns is displayed when you select the Display tab. Deselect the columns you do not need.

8. 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)
7. Filter on [CsEventHistory] Event Code:
   • Comparison: Equal
   • Prompt: Optional or Required.

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

   

8. (Display tab) Select the following columns:
   • [CsContactBasic] iMIS Id
   • [CsContactBasic] Full Name
   • [CsContactBasic] Company
   • [CsEventHistory] Event

   Notes: 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. Click Refresh.
10. In the Order column, assign the following:
    • [CsContactBasic] iMIS Id – 1
    • [CsEventHistory] Event – 2

    

11. (Sorting tab) Sort the results by CsEventHistory.Event.
12. Save your settings and run the query.

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

Notes: 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)
4. Filter on [CsActivityBasic] Activity Type:
   • Operator: Where
   • Comparison: Equal
   • Value: AWARD
5. Click Add Condition.
6. Filter on [CsContactBasic] Member Type:
   • Operator: And
   • Comparison: Equal
   • Value: Regular Member
7. (Display tab) Select the following columns:
   • [CsContactBasic] iMIS Id
   • [CsContactBasic] Full Name
   • [CsContactBasic] City
   • [CsActivityBasic] Description
   • [CsActivityBasic] ProductCode
8. Click Refresh.
9. In the Order column, assign the following:
   • [CsContactBasic] City – 1
   • [CsContactBasic] Full Name – 2



10. (Sorting tab) Sort the results by CsContactBasic.iMIS Id.
11. 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 a hard-coded goal

This example walks you through how to build a simple Progress Tracker query that displays this year's total revenue with a hard-coded goal that we hope to achieve by the end of the year.

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query Progress Tracker - Order Revenue This Year.
3. (Sources tab):
   • Click All sources and select CsOrders, then click OK.
   • Click All sources and select CsOrderLines, then click OK.
   • The two sources will automatically be joined.
4. Save the query.
5. (Filters tab) Since we only want to see the revenue from this year's sales, we are going to limit the results to return only transactions that occurred this year:
   1. From the Property drop-down, select [CsOrders] Order Date.
   2. From the Comparison drop-down, select Within Years.
   3. In the Value field enter 0.

   

6. (Display tab) In order to display this year's current total revenue, we need to calculate the sum and then give it a special name:
   1. Select [CsOrderLines] Extended Amount.
   2. Deselect all other properties.
   3. Click Refresh.
   4. From the Function drop-down, select Sum.
   5. In the Alias field, enter Sum_Total.

   6. If needed, change the Format of the property to be displayed.

   Notes: This step is required for the Progress Tracker to understand the data.

   

7. Click the Run tab. You should see only one row and one column that displays the current year's total revenue.

Next, we need to give the Progress Tracker a goal and an end date. For this example, we want the goal to be 2,000,000, and the end date to be the end of the year. Since neither of these values are available using a business object, we will have to use a couple of simple SQL statements.

8. Click the Define tab, then click the Display tab.
9. Scroll down to the Custom section. This is where you can add custom SQL expressions.
   1.  In the SQL Expression field enter CAST (2000000 AS INT)
   2. In the Alias field enter Goal_Amount
   3. Click Add.

Notes: Only system administrators have access to the SQL Expression textbox.

10. Next we need to create a custom display that will tell the Progress Tracker what to do when the new year begins. We want the Progress Tracker to automatically roll forward, so once the new year begins, the new end goal is the last day of the new year:
    1. In the SQL Expression field, enter DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1)
    2. In the Alias field, enter End_Date
    3. Click Add.

    

11. Save the query, then click the Run tab.

    

12. To display the results using the Progress Tracker content item, go to RiSE > Page Builder > Manage content.
13. Create a new content record, or Edit an existing content record to display the Progress Tracker.
14. From the Manage content window, select Add content.
15. Open the Utility folder, select Progress Tracker, then click OK.
16. Change the CSS class to read ProgressTracker2.
17. In the Source query field, select the Progress Tracker - Revenue This Year query.
18. Scroll down to the Caption Options section:
    1. From the Progress bar location drop-down, select Below captions.
    2. Select the Display percent, Display total, and Display days remaining checkboxes.
    3. Enter a description for each caption.
19. Save and Publish the page.

Using a dynamic amount and goal

In this example, we are going to build a Progress Tracker query that shows the number of registrants for a particular event. Instead of a set-date goal, the goal is the maximum capacity for the event. The end date will be the start date of the event.

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query Progress Tracker - Event Registrations.
3. (Sources tab) Click Add Source, choose the CsEvents business object, then click OK.
4. (Display tab) In order for the Progress Tracker to properly interpret this information, we have to create special display columns:
   1.  Select for the following properties:
      • Event Code
      • Max Registrants
      • Attendees
      • Begin Date
   2. Click Refresh.
   3. In order for the Progress Tracker to understand the data we are giving it, some properties need to have a particular alias:

      

      • Max Registrants - Represents the goal.
        • Alias - Goal_Amount
      • Begin Date - Represents the end date for the goal.
        • Alias - End_Date
      • Attendees - Represents the current total number of registrations.
        • Alias - Sum_Total
5. If needed, change the Format of the property to be displayed.
6. Save the query.
7. Go to the event dashboard and add a new Progress Tracker content item:
   1. Rename the CSS class to ProgressTracker2.
   2. From the Source query field, select the Progress Tracker - Event Registrations query.
8. The query we built displays information for all events in the database, but we only want to display the information that is specific to the individual event. To do this, we will need to filter the Progress Tracker results based on the EventKey parameter in the URL:
   1. In the Filter Options section from the Filter on data column drop-down, select EventCode.
   2. In the URL parameter field, enter EventKey.
9. In the Caption Option section from the Progress bar location drop-down, select Below captions.
10. Enable the Display percent, Display total, and Display days remaining options. Enter a description for each.
11. Save and Publish the page.

    

In this example, we are going to create a query that identifies the active members that have post-purchased content. This will enable you to add the query to an individual's account page for easy access to downloadable links.

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query. For example, Post-purchased content.
3. (Sources tab) Click All sources and select the following:
   • Group
   • GroupMember
   • GroupUpdateInstruction
   • NetContact
   • CsProduct
4. Click OK.
5. Make sure the default joins are:
   • Group.Group Key = GroupUpdateInstruction.Group Key
   • Group.Group Key = GroupMember.Group
   • GroupMember.Contact = NetContactData.Contact Key
6. Add the following relation: GroupUpdateInstruction.Item ID = CsProduct.Product Code



7. Save the query.
8. (Filters tab) Add the following filters:
   1. From the Property (Add a filter) drop-down, select [Group] Group Status.
   2. From the Comparison drop-down, select Equal.
   3. From the Value drop-down, select Active.

Repeat the same steps for the [GroupMember] Active property, but in the Value field select Yes.

If you want to display the query on individual's account page, you will need to add a filter to specify the logged-in user. Do the following:

1. From the Property (Add a filter) drop-down, select [NetContact] Contact Key.
2. From the Comparison drop-down, select Equal.
3. In the Value Field, enter "@SelectedUser".



9. (Display tab) Select the following columns:
• [CsProduct] Related Content Message
• [NetContact] Full Name
10. Click the Run tab. You will only see results if the logged-in user has post-purchased content. Delete the @SelectedUser filter to see all individuals that have post-purchased content.

This example details how to add a product's thumbnail as a display column in a query's results.

Do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Name the query Product Thumbnails.
3. (Sources tab) From the Add source area, select Products.
4. (Display tab) Enter the following information in the Custom section:
   • SQL Expression: char(60) + 'img src="' + REPLACE(vBoCsProduct.IMAGE_URL,'~','') + '" style="max-width:100px;" /' + char(62)
   • Alias: Image

   

5. Click Add.
6. (Display tab) Continue adding the desired display columns.
7. Click Run.

This example details how to easily identify the reporting structure of organizations.

Do the following to create an IQA query that displays a company's roster:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query Organization Hierarchy.
3. (Sources tab) Click All sources and then select OrganizationHierarchy.
4. (Filters tab) The following filters are optional:
   1. From the Property (Add to filter) drop-down, select Child Full Name.
   2. From the Comparison drop-down, select Contains.
   3. From the In Search? drop-down, select Optional.
5. (Display tab) Select all available properties.
6. Click Run.

Enter the name of the organization to find the roster members. The query will return contacts (individuals or organizations) who report directly to the organization, as well as contacts who report to child organizations. The Depth displays how many levels of hierarchy between the roster member and the parent organization.

This example goes over how to identify a contact's top-level parent organization. For example, Brian Davidson's primary organization is Versaton New Zealand, but Versaton New Zealand's primary organization is Versaton Global Headquarters. Brian's top-level parent organization would be Versaton Global Headquarters.

Do the following to create an IQA query that displays a contact's top-level parent organizations:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query Top organization.

3. (Sources tab) Click All sources and then select TopLevelParentOrganzation.

4. (Filters tab) The following filters are optional:
   1. From the Property drop-down, select Child Full Name.
   2. From the Comparison drop-down, select Contains.
   3. From the Prompt drop-down, select Optional.
5. (Display tab) Select all available properties.
6. Click Run.

The results of this query will display one row per contact, and includes columns for their primary organization ID as well as the top-level organization in their reporting chain. The Depth displays the number of hierarchy levels between the contact and their top organization.

To determine who has not registered for a specific event, you must first create a query of everyone who has registered for the event. After that query is created, you can create a second query that uses the first query as a source.

The following example details how to find everyone who has not registered for the Annual Conference.

Creating the first query: Finding all registrants of an event

Do the following to create a query to find everyone who has registered for a specific event:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Enter a Name and Description for your query.
3. (Sources tab) Under Add source in the Common section, select Event registrations.
4. (Filters tab) From the Property (Add a filter) drop-down, select Event Code and set it Equal to the code of the event you need.



5. (Display tab) Enable any properties to display, such as:
   • Event Code
   • Full Name
   • Ship To Id
6. Deselect the columns you do not need.
7. Click Refresh.
8. Click Run to verify the results.



9. Save the query.

Creating the final query: Finding all non-registrants of an event

Do the following to create a query that returns results of everyone who has not registered for a specific event:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Enter a Name and Description for your query.
3. (Sources tab):
   1. Under Add source in the Common section, select Contacts.
   2. Select All sources.
   3. Locate the query you created in the section above, then select the query to be used as a source.
   4. Click OK.
   5. Define the following Relation: NetContactData.Id Not Exist CsRegistration.Ship To Id

   

   6. Click the + icon.

   Important! Make sure NetContactData is listed first. If this source is not listed first, use the arrows to move the source location.

4. (Filters tab) Add the desired filters, such as excluding company records, inactive members, or certain member types.



5. (Display tab) Select the properties you want displayed in the query.
6. Click Run. The full list of contacts not registered for the selected event are returned.

You can join certain invoice business objects with CsEvent to provide detailed invoice information to your specific event dashboards.

Example: You want to know how many registrants for the annual conference have a balance due. To do this, you must create a query and add it to the specific event dashboard.

Creating the query

To create the query, do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Enter a Name, such as Event registration with balance.
3. (Sources tab) Select All sources and add CsEvent and EventInvoice.
4. (Filters tab) Add the following filters:
   • Filter on [EventInvoice] Balance
     • Function - None
     • Comparison - Greater Equal
     • Value - Constant 0
   • Filter on [EventInvoice] Event Title
     • Function - None
     • Prompt - Required
5. (Display tab) Add the following properties:
   1. [CsEvent] Event Code
   2. [EventInvoice] Balance
   3. [EventInvoice] Bill To ID
   4. [EventInvoice] Invoice Number
   5. [EventInvoice] Registrant Full Name
6. Save and Run the query.

Adding the query to an event dashboard

To add the query to the event dashboard, do the following:

1. Go to RiSE > Page Builder > Manage content.
2. Go to @/iCore/Staff_Dashboards/Specific_Event and Copy the Event Detail and Event Dashboard content records.
3. Paste the records into a Shared content folder.
4. Open the copied Event Detail record and select Add content.
5. From the Content gallery select the Query Menu shortcut.
6. Select the query you just created in the Source query or folder field, then click OK.
7. Click Save & Pubish.
8. Open the copied Event Dashboard content record:
   1. From the Event Dashboard content item, select Configure.

   

   2. Click the Dashboard tab.
   3. From the Content to display field, select the Event Detail content record previously copied.

   

   4. Click OK, then click Save & Publish.
9. Go to RiSE > Site Builder > Manage shortcuts.
10. Edit the EventDashboard and EventDetail shortcuts to point to the new content records. Click Update.



11. Go to Events > Find event and select any event. From the Dashboard tab, you’ll see the new query.

The following query identifies the Contact Status for all contacts. The query returns the Code and Description for each status, including custom statuses.

Do the following to create an IQA query that displays the status of each contact:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query.

3. Sources tab:

   1. Add the NetContactData and CsGeneralLookupTables business objects.
   2. In the Alias field for CsGeneralLookupTables, enter StatusRef.
   3. From the Relations drop-down, select Custom:
      1. From the first drop-down, select NetContactData.Status. Select Left Join from the second drop-down, then select StatusRef.Code.
      2. Click the plus (+) icon.
4. (Filters tab) Add the [StatusRef] Table Name property as a filter:
   1. From the Comparison drop-down, select Equal.
   2. Enter MEMBER_STATUS in the Value field.
   3. Remove any properties you do not need.
5. (Display tab) Select the following properties:
   1. [NetContactData] Id
   2. [NetContactData] Full Name
   3. [StatusRef] Code
   4. [StatusRef] Description
6. Click Refresh.
7. Save and Run the query.

The results return one row per contact with the Code and Description of their current status.

Example: Create a link that takes you on behalf of a contact, allowing you to register them for an event.

Notes: This feature is only compatible with event registrtions.

Do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query.
3. (Sources tab) Select All sources, then select the CsRegistration business object.
4. Click OK.

Next, add links to the query results that will let you register or view current registration someone On behalf of.

5. (Display tab) Populate the Hyperlink field on the Event Code property with the following: ~/Event.aspx?EventKey=[code_EventKey]&ID=[code_ID]&EditRegistrationObo=true.

Notes: 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. The Event.aspx target page also needs two parameters: Id and EditRegistrationObo in order to function as On behalf of.

6. Click Save.

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

Do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query Registrations.
3. (Sources tab) Select All sources, then select the following business objects:
   • CsEvent
   • CsFunction
   • CsRegFunctions
   • CsRegistration

Notes: Use the Ctrl key to select multiple business objects at once. Use the Quick find field to filter the list.

4. Click OK.
5. (Display tab) Select the following display properties:
   • [CsEvent] Title
   • [CsFunction] Title
   • [CsRegFunctions] Registered Quantity
   • [CsRegFunctions] Status
   • [CsRegistration] Full Name
   • [CsRegistration] Order Date
   • [CsRegistration] Status
6. Click Refresh.
7. (Sorting tab) Add the following sorts:

Priority	Property	Direction
1	[CsEvent] Title	Ascending
2	[CsRegistration] Last First	Ascending
3	[CsFunction] Is Event Registration Option	Descending
4	[CsFunction] Title	Ascending

8. Save the query.
9. Click Run. 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.
10. Click Define, then click Display.
11. Select the Group By checkbox for the following properties:
    • [CsEvent] Title
    • [CsRegistration] Full Name
    • [CsRegistration] Order Date
    • [CsRegistration] Status

Notes: Do not use the Group By option to display query results in a hierarchical grid of summary rows and detail rows.

12. Click Run. You should now see one row per order with a toggle button to the left of each row.
13. Select the toggle button to see the details of the order.
14. Save the query.
15. Use theQuery Menu content item to display the query results on any page.

The above query example can become overwhelming after just a few registrations have been entered. To simplify the query, add dynamic filters.

Displaying event registrations for a specific contact

Example: Build a query that returns results of a contact's event registrations, and then display the results on the contact's page.

Do the following:

1. Make a copy of the Registrations query from the example above.
   1. Open the query and click Save As.
   2. Name the new query Registrations for Contact.
   3. Click OK.
2. (Filters tab) To return registration data for a single user, add the following filter:
   1. From the Property drop-down, select [CsRegistration] Ship To Id.
   2. From the Value drop-down, select Dynamic, then choose Selected ID.
3. Save the query, then click Run. The only results that display are the registrations that you (the Selected ID) are registered for.

To use this query to display a contact's event registrations, go to the contact's account page, add the Query Menu content item, and select the Registrations for Contact query. The query will display on the account page with only the selected contact's registrations displayed.

Using the "@url" filter

For this example, use the Registrations query to view the registrations for one specific event:

1. Open the Registrations query and click Save As.
2. Name the new query Registrations for Event, then click OK.
3. (Filters tab) Add a filter that will only show the event whose key is passed in the URL parameter:
   1. From the Property drop-down, select [CsRegistration] Event Code.
   2. From the Value drop-down, select URL param.
   3. Update the default Value text to the following: "@url:EventCode"
4. Save the query.

Notes: When working on a query filtered by URL parameter, you might not see any results on the Run tab. Try temporarily filtering on the Value of the URL parameter, such as a specific Event Id, until you have the query looking the way you want.

5. Add the query to the event dashboard:
   1. Navigate to a specific-event dashboard and add a new Query Menu content item to the page.
   2. Select the Registrations for Event query as the source.
   3. Save and Publish the page.

A list of registrations for the event will display on the page.

Using the "@Now" filter

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

Do the following:

1. Go to RiSE > Intelligent Query Architect > New Query.
2. (Summary tab) Name the query.
3. (Sources tab) Under Add source in the Common section, select Contacts. This adds the CsContactBasic business object.
4. (Filters tab) From the drop-down, select Date Added:
   • Comparison: Between
   • Value: 1/1/2015 and @Now
5. (Display tab) Customize the fields to display.
6. Save the query.
7. Click Run. The results display the contacts created so far this year.

Example: Use the Query Menu content item and the Link feature to display completed orders to users.

Do the following:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Name the query Completed Orders.
3. (Sources tab) Select All sources, then choose the following business objects:
   • CsContactBasic
   • CsOrderHistory
4. (Display tab) Select the following properties:
   • [CsContactBasic] Full Name
   • [CsOrderHistory] Transaction Date
   • [CsOrderHistory] Id
   • [CsOrderHistory] Product Name
   • [CsOrderHistory] Product Code
   • [CsOrderHistory] Quantity
5. Update the Alias or Link for the following properties:

Property	Alias	Link	Notes
[CsOrderHistory] Id	code_ID		Properties with an Alias prefaced with code_are hidden in the query results but can still be used as a reference in the Link.
[CsOrderHistory] Product Code	code_ProductCode
[CsContactBasic] Full Name		~/MyAccount&ID=[code_ID]	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.
[CsOrderHistory] Product Name		~/ItemDetail&iProductCode=[code_ProductCode]	This Link assumes that you have a shortcut named ItemDetail pointing to a content record containing the General Product Display content item.

6. (Sorting tab) Select the [CsOrderHistory] Transaction Date property and sort by Descending.
7. Save the query.
8. Go to RiSE > Page Builder > Manage content, and add the Query Menu content item to a content record.
9. In the Source query or folder field, select the Completed Orders query.
10. Click OK, then Save & Publish the content record.
11. Go to RiSE > 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.

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

Create the query

Do the following to create the query:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query Contact Gift Contributions by Year.
3. (Sources tab) Select All sources:
   1. Select GiftsReceived.
   2. Click OK.
4. (Filters tab) Add filters so that only gifts that were given by the selected user from this year and last are returned in the query:

Property	Comparison	Value
ID	Equal	Dynamic | Selected ID
Date Received	Within Years	1

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

5. (Display tab) Select the following properties:
   • Calendar Year
   • Fund
   • Amount
6. Click Refresh.
7. For a multi-series chart in the Query Chart Viewer, you must have at least one property that is selected as a Group By column. This should be the property that you want to display on the x-axis. Select the Group By checkbox for the Calendar Year property.

Notes: Do not use the Group By option to display query results in a hierarchical grid of summary rows and detail rows.

8. For the Amount property, select the Sum function.
9. Click Run. 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.

Add the query to a content record

Next, add the query to a content record:

1. Navigate to the account page and enable on Easy Edit.
2. Select Open in Content Designer.
3. From the desired location, select Add content.
4. Open the Utility folder, then select the Query Chart Viewer.
5. Click OK.
6. In the configuration for the content item, set the following settings:
   1. Chart data:
      • Source query = Select the query that you 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

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.