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 NetContactData 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. 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, choose Contacts. This adds 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 as the Comparison.
      • Enter Texas as the Value.

      

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

      

      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)
   1. Select the columns that you want to display when you run the query, such as:
      • Full Name
      • Email
      • Informal

   

   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, 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 the Run tab to review the list of Austin members.



8. Click Save.
9. Select the location where you want to save the query.
10. Click OK.

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

Note: 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, it is recommended to use NetContactData.StateProvinceId. See Creating special pricing rules for Northern Territory or Western Australia for details.

Do the following to build a query that finds all members in Western Australia:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Enter the name and description.
3. (Sources tab) Select CsContactBasic, CsCountryNames and CsStateProvinceCodes, 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 business objects.
   1. From the Relations drop-down, select Custom:
      1. From the drop-downs, select CsContactBasic.State Province Equals CsStateProvinceCodes.State/Province.
      2. Click the plus (+) icon.
      3. Add another relation. From the drop-down, select CsContactBasic.Country. Select Left Join from the second drop-down, then select CsCountryNames.Country.
      4. Click the plus (+) icon.
      5. Add one more relation. From the drop-down, select CsCountryNames.Country Code. Select Left Join from the second drop-down, then select CsStateProvinceCodes.Country Code.
      
      6. Click the plus (+) icon.
5. (Filters tab) select:
   • Filter on State Province Id.
     • Property: [CsContactBasic] State Province Id
     • Value: Western Australia
   • Filter on Country Code:
     • Property:  [CsCountryNames] Country Code
     • Value: AU
   • Filter on Member Type
     • Property: [CsContactBasic] Member Type
     • Value: Regular Member

     Note: 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 (name)
   • [CsStateProvinceCodes] Title (This will give you the name of the state/province, not just the abbreviation)
7. Click Run. Depending on your system’s display settings, your results could display similarly to the following image.



8. Save your changes.

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

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query: Customers who spent $100+ in 2017
3. (Sources tab) > All Sources.
• Select the CsContactBasic and CsActivityBasic business objects.
• Use CTRL to select multiple objects
• Make sure the default join in the Relations area is CsContactBasic.iMIS Id Equals CsActivityBasic.Id.



4. (Filters tab):
• Filter on [CsActivityBasic] Activity Type
• Property: [CsActivityBasic] Activity Type
• Value: ORDER
• Filter on [CsActivityBasic] Transaction Date
• Property: [CsActivityBasic] Transaction Date
• Comparison: Between
• Value: "1/1/2017","12/31/2017"
• Filter on [CsActivityBasic] Amount.
• Property: [CsActivityBasic] Amount
• Comparison: Greater
• Value: 100

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. Save your settings.
8. 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. 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. Under Add source, select Contacts from the Common section. This adds the NetContactData business object.
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 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.
6. Now let's test the query. 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.

   Note: The Quick find box dynamically filters available columns to display only the items searched for in the Quick find box. For example, searching for .id brings up only items that contain .id.

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

The following example details how you can build a query of contacts with their associated chapters:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Name the query.
3. (Sources tab) Select the Chapter and NetContactData sources. Give the two sources the following relation: Chapter.Chapter Code = NetContactData.Chapter



4. (Display tab) Select the following display properties:
   • [NetContactData] Id
   • [NetContactData] Full Name
   • [Chapter] Chapter Name
   • [Chapter] Chapter Code

   

5. Click Refresh.
6. Click Run. Any contact in a chapter displays in the query results.

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

      

      It is always a recommended best practice to test along the way while building a new query. Click the Run tab.

      Note: 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. 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 members with profile pictures. From the drop-down in the Property column, select [CsNamePicture] Id and [CsContactBasic] iMIS Id.
6. (Display tab) Next, we will want to make the query results look a little better:
   1. Select Display. This is where you select what information displays and what order it is displayed in.
   2. Select the following properties to display:
      • [CsContactBasic] iMIS Id
      • [CsContactBasic] Full Name
      • [CsContactBasic] Member Type
      • [CsNamePicture] NamePicture.Picture Logo
      • [CsNamePicture] NamePicture.Id

      

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

7. (Sorting tab) For this example, we want the names to be sorted in alphabetical order:
   1. From the Property drop-down, [CsContactBasic] iMIS Id.
   2. From the Direction drop-down, select Ascending.
8. Click the Run tab.



9. 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 (.png, .jpg, etc.) you can create an IQA query to neatly display all images uploaded through that panel source.

The following example describes how you can build a query to display images stored in a panel data source:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Name 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 which properties you want displayed. Select which properties you want displayed, making sure to choose the property containing the images.
   1. Under Length, type the image width. The smaller the number (such as 100 px), the smaller the image. The larger the number (such as 300 px) the larger the image.

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

Example: The association offers a list of preferred medical professionals where patients can search for a physician. The patient also has the option to click Find and a list of all medical professionals appears. The list needs to be in random order, so that the same medical professionals do not always appear at the top of the list.

Do the following to create a query that returns the results in a random order:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Summary tab) Enter a Name and Description.



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. Use the refresh icon to confirm new results display each time the query is refreshed.



8. (optional) If the query results return more than one page and the user navigates through the pages, the query results are refreshed each time the user navigates to a new page. This means if a user navigates from page one to page two then back to page one, the results on page one will be different than they previously were. To keep the pages from refreshing, enable Use cached results on the Summary tab.

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

Querying administrators of all companies

To use the Organization Administrator query, do the following:

1. Go to RiSE > Intelligent Query Architect.
2. Click New > Query.
3. Name the query, then click the Sources tab.
4. From the right-side where the common query sources are, expand the Contacts section.
5. Select Organization administrators.



6. Save the query in a non-system folder.
7. Add additional Display properties as needed.

Querying administrators for a specific company

If you need to find the administrators for a particular company, add the following filter to the above query:

• Organization.ID
• Value - Enter the company's iMIS ID.
• Add the filter.

Results will look like the following:

To find event registrations that used promotion codes, do the following:

1. Go to Events > Find registrations.
2. Select the Promotions used query. The query displays each event registration that used a promotion, detailing the event name, registrant name, promotion applied, and discount amount, among other information.



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

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

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

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

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

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

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

Note: 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. Save the query.
6. 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.
7. 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.
8. In the Caption Option section from the Progress bar location drop-down, select Below captions.
9. Enable the Display percent, Display total, and Display days remaining options. Enter a description for each.
10. 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 Group, GroupMember, GroupUpdateInstruction, NetContact and CsProduct. Use CTRL to select multiple objects.
4. Click OK.
5. Next, we need to add relations. Make sure the default joins are Group.Group Key = GroupUpdateInstruction.Group Key, Group.Group Key = GroupMember.Group and GroupMember.Contact = NetContactData.Contact Key.



We need one more join. From the drop-down in the Relations area, select Custom. Join GroupUpdateInstruction.Item ID = CsProduct.Product Code. Click the + button.

6. Save the query.
7. (Filters tab) Since we only want active members of an active group, we need to 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".



8. (Display tab) Select the following columns:
• [CsProduct] Related Content Message
• [NetContact] Full Name
9. 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 to create an IQA query that displays thumbnail images of products:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query Product Thumbnails.
3. (Sources tab) From the Add source area on the right-side, select Products.



4. (Display tab)
   1. 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

      

   2. Click Add.
5. (Display tab) Continue adding the desired display columns.
6. 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.
Click to view image



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.
Click to view image



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.
Click to view image



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.
   Click to view image

   

   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.
   Click to view image

   

   5. Define the following Relation: NetContactData.Id Not Exist CsRegistration.Ship To Id
   Click to view image

   

   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.
Click to view image



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.
Click to view image

You can join certain invoice business objects with CsEvents 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. From the Relations area, add the following joins:
   1. CsEvent.Event Code = EventInvoice.Event Code
5. (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
6. (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
7. 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.

In this example, we are going to create a query that enables you to click a link from a query that automatically takes you on behalf of an event registrant. Keep in mind that this feature is currently only compatible with event registrations.

Do the following to add a link for an On behalf of contact:

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.

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

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



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

5. Click Save.

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. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query Registrations.
3. (Sources tab) Click All Sources.
4. 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.

5. 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 plus (+) button.
      3. Add another relation. From the Relations drop-down, select Custom.
      4. From the drop-downs, select CsRegistration.Ship To Id Equals CsRegFunctions.Ship To Id.
      5. Click the plus (+) button.

      

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

   

7. Click Refresh.
8. (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 (Add sort) drop-down, select [CsEvent] Title, with the Direction of Ascending.
   2. From the second Property (Add sort) drop-down, select [CsRegistration] Last First with the Direction of Ascending.
   3. From the third Property (Add sort) drop-down, select [CsFunction] Is Event Registration Option with the Direction of Descending.
   4. From the fourth Property (Add sort) drop-down, select [CsFunction] Title with the Direction of Ascending.

   

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

10. Select the Define tab, then select the Display tab.
11. Select the Group By checkbox for the following properties:
    • [CsEvent] Title
    • [CsRegistration] Full Name
    • [CsRegistration] Order Date
    • [CsRegistration] Status



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

12. Select the Run tab. 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. 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.

Displaying event registrations for a specific contact

For the first example, we will build a query that returns results of a contact's event registrations, and then display the results on the contact's page. First, we will need to filter the query to display only a selected contact's registrations:

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) We only want to view registrations for a single user at a time. To do this, we will need to add dynamic filters:
   1. From the Property drop-down, select [CsRegistration] Ship To Id.
   2. From the Comparison drop-down, select Equal.
   3. In the Value field, enter "@SelectedId".
   4. From the In Search? drop-down, select No.
3. Save the query, then click the Run tab. The only results that display are the registrations that you are registered for.
4. 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.
5. Save and Publish the page.

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 all of 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 (Add a filter) drop-down, select [CsRegistration] Event Code.
   2. From the Comparison drop-down, select Equal.
   3. In the Value field, select URL params from the drop-down. You can then enter the URL parameter EventCode in the text field.



4. Save the query.

Note: 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 now display on the page.

Using the "@Now" filter

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) Under Add source in the Common section, select Contacts. This adds the CsContactBasic business object.
4. (Filters tab)
5. Filter on Date Added:
   • Comparison: Between
   • Value: 1/1/2014 and @Now
6. (Display tab) Customize the fields to display.
7. 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:
   • CsContactBasic
   • CsOrderHistory
4. (Display tab) Select the following columns:
   • [CsContactBasic] Full Name
   • [CsOrderHistory] Transaction Date
   • [CsOrderHistory] Id
   • [CsOrderHistory] Product Name
   • [CsOrderHistory] Product Code
   • [CsOrderHistory] Quantity
5. 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
6. 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.

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

8. (Sorting tab) Select the [CsOrderHistory] Transaction Date property and sort by Descending.
9. Save the query.
10. Go to Page Builder > Manage content, and add the Query Menu content item to a content record.
11. In the Source query or folder field, select the Completed Orders query.
12. Click OK, then save and publish your content record.
13. 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. Go to RiSE > Intelligent Query Architect > New > Query.
2. Name the query Contact Gift Contributions by Year.
3. (Sources tab) Click Add Source.
4. 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 plus (+) 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. Click Refresh.
8. 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.

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

9. For the Amount property, select the Sum function.



10. 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.
11. Save the query.
12. 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.
13. 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
14. 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.