Tutorials: Building IQA queries

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

You can construct a query that uses the CsContact 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 CsContact 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 Name to filter the query results by state:
      •  Select Equal as the Comparison.
      •  Select TX as the Value.
      •  Select the + button.

      Only contacts who have Austin listed as the city and TX 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.

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 ran, 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, and 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 display:
   1. From the drop-down, select View all columns.
   2. Select the Display checkbox for iMIS ID.
   3. Click Refresh.

   If you run the query now, you will see the IDs for the contacts are displayed. These IDs do not look nice, but they are needed in order to correctly build the links. Let's take a look at how you can hide these IDs from the display.

5. Click the display tab:
   • iMIS ID - In the Alias field enter code_ID.

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

6. Click Run to view the results, then click Save.
7. 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.

8. 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.
9. Click Save.
10. 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.

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

   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.

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

    

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

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

You new Progress Tracker should look like this:

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

      

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

    Your Progress Tracker should look similar to this:

    

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

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

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

4. Set up the relationships between the 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.
   Click to view image

   

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

   

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

    

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.

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

   

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

The query will display on the account page with only the selected contact's registrations displayed.

Using the "@url" filter

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

   

4. Save the query.

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

   

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

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

   

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

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

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



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