Building IQA queries: Tutorials

There are an infinite amount of queries you can build using the Intelligent Query Architect. Once a query is built, staff users can use it to email the generated list of contacts (non-staff users cannot email from a query), display the results on a content page, build a report to distribute to your staff, and much more.

Below are examples on how to create unique queries using a variety of different business objects. These examples range from beginner to advanced.

Beginner

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

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

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

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

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

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

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


    8. Example query results: Members from New York

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

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

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

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

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

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

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

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

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

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

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

Intermediate

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

  9. Save your settings and run the query.

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

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

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

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

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

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

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

Note: The primary difference between Cs and non-Cs objects is that the non-Cs objects contain the .NET security, but Cs objects are views pointing to the underlying non-.NET tables. When you use a non-Cs object, you might notice additional fields such as Access Key and Contact Key. These fields are important when you are linking to objects with security, such as a Campaign business object where a user must have certain security to be able to view data.

This query shows individual members with an AWARD activity type.

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

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

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. From the Staff site, go to RiSE > Intelligent Query Architect > New > Query.
  2. Name the query Progress Tracker - Order Revenue This Year.
  3. (Sources tab):
    • Click Add source and select CsOrders, then click OK.
    • Click Add source 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.
    4. Click the + button.
  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. From the drop-down, select View all columns.
    2. Select Display for CsOrderLines.Extended Amount.
    3. From the Function drop-down, select Sum.
    4. In the Alias field, enter Sum_Total.

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

    5. Deselect Display for all other properties.
  7. Click the Run tab. You should see only one row and one column that displays the current year's total revenue.

    8. 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 the + button.
  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 the + button.
  11. Save the query, then click the Run tab.

    You results should look similar to this:

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

    20. You new Progress Tracker should look like this:

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. From the Staff site, go to RiSE > Intelligent Query Architect > New > Query.
  2. Name the query Progress Tracker - Event Registrations.
  3. (Sources tab) Click Add Source:
    1. Select CsEvents, 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.  Enable Display for the following properties:
      • Event Code
      • Max Registrations
      • Attendees
      • Begin Date
    2. 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 iPart 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.

    Your Progress Tracker should look similar to this:

Advanced

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

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

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

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

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

  8. Select the Define tab, then select the Display tab.
  9. Select the Subtotal checkbox for the following properties:
    • CsEvent.Title
    • CsRegistration.Full Name
    • CsRegistration.Order Date
    • CsRegistration.Status

  10. Select the Run tab. You should now see one row per order with a toggle button to the left of each row.
  11. Select the toggle button to see the details of the order.

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

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

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 Prompt drop-down, select No.
    5. Click the + button.

  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.

For this example, we will 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) Next, we need to 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.EventCode.
    2. From the Comparison drop-down, select Equal.
    3. In the Value field, enter "@url:EventKey".
    4. Click the + button.

  4. Save the query.

    5. Note: When working on a query filtered by URL parameter, you may 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. Once the query is ready, replace the Value with the "@url" filter.

  5. Next, we need to 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.

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

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

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

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

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

  8. For CsOrderHistory.Product Name, enter the following for the Link:
    9. ~/ItemDetail&iProductCode=[code_ProductCode]

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

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

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

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

  1. From the Staff site, go to RiSE > Intelligent Query Architect > New > Query.
  2. Name the query Contact Gift Contributions by Year.
  3. Make sure you are in Advanced Mode.
  4. (Sources tab) Click Add Source:
    1. Select GiftsReceived, then click OK.
  5. (Filters tab) We only want to see gifts that were given by the selected user from this year and last:

    1. To filter on the selected user, only add a filter that selects ID Equals "@SelectedId". Click the + button.
    2. To filter on just this year and last, add a filter with Date Received as the property, Within Years as the comparison, and 1 as the value. Click the + button.

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

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

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