Queries

Basic Query Creation

IMPORTANT– BEFORE proceeding to create a Query in Aegis, please check your browser and enable Pop-ups (turn OFF Pop-up Blocker for the Aegis CRM site). This will allow the viewing of Sample Results and Reports when creating queries.
In this manual, DONORS will be used as conventional name. Users may have the persons who donate named as Constituents, Partners, Accounts, etc. in their DB.
For exercise – Users will be taken through a scenario creating a basic query that will return All Gift Donors of $100 plus over the past 12 months in California.

Creating a BASIC Query STEPS

  1. Navigate to the Queries module, New Query sub-module.
  1. The New Query record displays with three tabs: General, Criteria, and Output.
    • GENERAL – Basic Info/Security: Name, Query Type, Access, & Description.
    • CRITERIAFilter Data: Build filters of data. Available Joined tables based on Query Type. Multiple Tables & Fields can be utilized
    • OUTPUTConfigure Results: Select Fields to display in the finished query. Sorting, & Aggregation available.

GENERAL TAB

  1. In the Query Name field, enter $100+ Gifts Past Year in CA
    • Ensure that the name is easily recognizable and reusable, especially if the plan is to share the Query with others.
  1. From the Query Type dropdown, select the desired value.
    • EXAMPLE: Contacts
    • If unsure of Query Type – read section at begininig of this chapter on Query Types and Query Type Examples.
  1. From the Access dropdown, select the desired value.
    • EXAMPLE: Private
    • Private – Only You / Everyone – All Users / Admin – Only Admins
  1. Turn on the Mark as a Favorite Query toggle.
    • On enables users to see the Query displayed the Favorite Queries, Home Dashboard component.
  1. Turn on the Not Editable By Other Operators toggle.
    • Prevents users who are provided access from editing the Query.
  1. In the Description field, enter the desired text to descibe the Query.
    • EXAMPLE: Displays $100 and over donations over the past 12 months for all donors from the state of California.
    • Used to further describe the query and ensure easily understood.
  1. Click on the Save button from the button bar.
  1. Click the OK button.
  1. The query saves with the Query Name embedded in the Title section.

CRITERIA TAB

Criteria is the tab where filters are created based on the fields from the available (displayed) tables. Those tables are reliant on the Query Type chosen under the General tab. Criteria determines which data displays in the Output of this query.
  1. Click on the Criteria tab.
  1. Expand the Donors table.
  1. Drag and drop State to the Drag Criteria Here To Add column. After dropping, a filter configuration pop-up displays.
  1. The AND/OR selection box remains greyed out UNTIL a second criteria statement is added.
  1. From the Is Equal to dropdown, select the desired Operator.
    • EXAMPLE: Is Equal to
    • Operator values change based on the field type being added: Dropdowns,
      Text fields, Date pickers, etc. will display as appropriate.
  1. In the Criteria field, enter the desired value.
    • EXAMPLE: CA
    • States in Aegis follow the two-letter State designation
  1. Click on the Save button.
  1. The Criteria is added to the right-hand column.
  1. Right click on the Criteria – users can Edit or Remove Criteria as desired.
  1. Return to the Table list in left-column.
  1. Contract the Donors table
    • Do not have to contract; just easier to see full table.
  1. Expand the Gifts table.
  1. Drag & drop Sum of Gifts Last 12 Months to Drag Criteria Here To Add column.
  1. From the Is Equal To dropdown, select the desired Operator.
    • EXAMPLE: Is Greater than or Equal to
  1. In the Criteria field, enter the desired value.
    • EXAMPLE: 100
  1. Click on the Save button.
  1. The Criteria is added to the right-hand column.
    • NOTE: When multiple criteria lines are initially added, each separate statement displays by default as related to the other by an & (AND).
    • The criteria below reads: Donor’s State is Equal to CA AND the Donor’s Sum of Gifts Last 12 Months is Greater than or Equal to $100.
    • Later in this chapter, users will see how they can use OR instead of AND, or with AND statements, to create more complex critera filters.

OUTPUT TAB

The Output Tab is where users decide which fields should display in the finished query. Users can also decide how to sort & aggregate data from this tab.
  1. Click on the Output tab.
    • Similair layout to the Criteria tab. In this tab, users drag and drop the listed tables fields to the right to decide which to display (not filter).
  1. Expand the Donors table.
  1. Drag and drop the Donor ID, Full Name, and City fields to the Selected Outputs column (right-hand column).
  1. Expand the Gifts table.
  1. Drag and drop the Sum of Gifts Last 12 Months, and Total Gifts Last 12 Months fields to the Selected Outputs column (right-hand column).
    • The Total Gifts Last 12 Months field provides a Count of gifts as output.
NOTE: the Do Not Aggregate Results toggle AND the Cross Tab Fields radio button functions will be covered in separate sections AFTER this exercise.
  1. Ensure the Sort Fields radio button selected.
  1. Click on the Field One drop down and select City.
  1. Ensure the Ascending radio button is selected to the right of Field One.
    • Users can choose to Sort in Ascending or Descending order.
    • Users can Sort up to five different fields.
    • The field selected for sorting does NOT have to be present as an Output
      to be sorted: a user could add Ful Name to Output but choose to sort the
      results based solely on the Last Name.
  1. Save the Query.

RESULTS

In this section users will learn how to view and manipulate the results of the query by exploring the functions of the # Results Count, Sample Results, Export, and Schedule buttons.
These buttons are static and located at the Top above the current Queries tab.

Results Count

Results Count enables users to quickly determine if the number of results returned meets their expectations.
  1. Click on the # Results Count button.
  1. A Popup box will display with the Results Count.
  1. Close the Results popup box.

Sample Results

Sample Results enables users to quickly determine if the fields and the values returned meets their expectations.
  1. Click on the Sample Results button
  1. Users will receive a scrollable list of the first 200 results to review.
  1. Close the Query Results popup box.

Export

The Export function enables users to export the Query to their email or to a secure location. Users also have the option of deciding upon:

  • Field Delimiters of Comma, Tab, Pipe, or Tilde.
  • Record Delimiters of Carriage Return/Line Feed or just Line Feed.
  • Text Qualifiers of None, Quote, or Apostrophe.

Users can encrypt the results, force User Name and Password recognition, and send to a secure FTP site URL if desired.

  1. Click on the Export button.
  1. The Job section, Description field will inherit the name of the Query, users can edit this if desired.
NOTE: If an Export exceeds 20 MBs users will no longer receive an email with the attached, exported excel file, they will instead receive an email notification with a link to the Excel file stroed in their Aegis CRM FTP folder.
  1. IF user clicks the Export button – and no advanced securtity has been enforced or setup – an Excel file will display shortly in the user’s email with full results.

Schedule

The Schedule function enables users to define a schedule for the results of a Query to be delivered to themseleves or any of the Available Users of the CRM. Users also haved the option of entering Additional Addresses which are addresses outside the user community.
Users can select the Start Date, Time, and Frequency of the scheduled export as well as add a Note that will be delivered with each exported instance.
Users can also define the configuration of the Exported file which has the same features as described in the Export section on the previous pages.
  1. Click on the Schedule button.
  1. Once users have set up the desired configuration, they can scroll to the bottom of the screen and click the Schedule button. The job will be added to the Aegis job queue, run at the defined time and delivered to the Selected Users/Additional Users.

Using Cross Tab Fields STEPS

The Cross Tab Fields function enables users to leverage criteria, fields, output, groupings, and values to create a pivot table of selected data. Users can experiment with different Query Types, Folders, and Fields to display from the Criteria and Output sections. They can then select the desired Group Header and Value Fields One, Two, and/or Three, to discover valuable insights into their data that would not be available via a regular query or report.
The example below will instruct users on how to create a basic Cross Tab Query using the Contacts Query Type with minimal Criteria. Users should build off this example to build Cross Tab Queries under other query types and try using multiple Criteria. Once the basics of Cross Tab Fields are understood, it can be a very useful tool for more in-depth use of queries.
  1. Click on the Queries module>New Query sub-module.
  1. In the Name field and type CA Gifts Per City – CROSS TABS QUERY
    • Best practice is to label any Cross Tabs Query in the Name field.
    • The query output will NOT display in Sample results or in a Dashboard.
  1. From the Query Type dropdown, select Contacts.
  1. In the Description field, enter Displays Total Gift Counts, Revenue, and Activity per California city by utilizing Cross Tabs function under Output tab. Must export to Excel; will not display in Sample Results.
  1. Click the Save button.
  1. Click on the Criteria tab.
  1. From the Donors folder, Drag and Drop the State field to the Add column.
  1. From the Operator dropdown, select Is Equal to.
    • Users will have to enter criteria property to run a Cross tabs query.
  1. In the Criteria field, enter CA.
  1. Click on the Output tab.
  1. Click on the Cross Tab Fields radio button on the upper far right.
    • Do NOT attempt to add Selected Outputs YET.
    • IF users are editing an existing non-Cross Tab Query OR have already added Selected Output fields, those values will delete by clicking on the Cross Tab Fields button – users will need to re-Add them.
  1. From the Group Header dropdown, select State.
    • The Group Header is what the data organized by in the Excel output.
  1. From the Value Field One dropdown, select Gift Count.
  1. From the Value Field Two dropdown, select Sum of Gift Value.
  1. From the Value Field Three dropdown, select Activity Count.
Now users must ADD Selected Output field(s) to display:
  1. From the Output folder column, expand the Donors table.
  1. Drag & drop the City field to the Selected Outputs column (right-hand column).
  1. Click on the Save button.
NOTE: Sample Results button does NOT work with Cross Tab Fields function.
  1. Click on the Export button.
  1. Open Email once the exported excel file displays.
    • Be patient – could take several minutes to appear in email.
  1. Click on the Excel file to preview it.
  1. Observe the Excel file:
  1. Users can clearly see the listing of California Cities and the Gift Count Total, Sum of Gift Value, and Activity Count Total per City.
    • User see the same columns and column values 2X as the data is filtered for ONLY the state of California, which is why the CA-Gift Count and Gift Count Totals, as well as the other columns, are repetitive.

Using Do Not Aggregate Results STEPS

The Do Not Aggregate Results function enables users to NOT aggregate (sum up) the results of calculated fields that are brought in as Selected Outputs under the Output Tab. Users may turn aggregation off when desiring to see each individual line item of a normally calculated field. For instance, when calculated fields are non-aggregated for Activity, and the Activity Count calculated field is used, users see a line item for EVERY activity record per donor and not an aggregated number.

If this button is unchecked, then the system WILL aggregate the results of the calculated field. Aggregation targets whatever the calculated field is counting.

  • For a list of ALL calculated fields affected by the Do Not Aggregate Results radio button, go to Appendix A: Calculated Fields by Query Type.

The following example will demonstrate a Query with a Query Type of Activity and the Activity Count calculated field selected. The following scenarios will be covered:

  1. Sample Results run with the Do Not Aggregate Results NOT SELECTED
  2. Sample Results run with the Do Not Aggregate Results SELECTED.
    • Note: By Default, all new Queries have the Do Not Aggregate Results radio button NOT SELECTED.
  3. Sample Results run with the Do Not Aggregate Results NOT SELECTED AND expanded fields preventing aggregation.
  1. Click on the Queries module>New Query sub-module.
  2. In the Name field and type Activity Count – Do Not Aggregate Example.
  3. From the Query Type dropdown, select Activity.
  4. In the Description field, enter Used to demonstrate the Do Not Aggregate Results radio button function under the Output tab.
  5. Click the Save button.
  6. Click on the Criteria tab.
  7. From the Activity folder, Drag and Drop the Activity Count field to the Add column.
  8. From the Operator dropdown, select Is Greater than or Equal to.
  9. In the Criteria field, enter 0.
  10. Click Save.
  11. From the Activity folder, Drag and Drop the Date Entered field to the Add column.
  12. .From the Operator dropdown, select Is Greater than or Equal to.
  13. In the Criteria field, enter 05/28/2024.
    • This date for this field should be changed to a reasonable range to test the function dependent on the DB being used.
  14. Click Save.
  15. Select the Output tab.
  16. From the Activity Folder, add the following fields:
    • Date Entered
    • Activity Count (THIS field is the Calculated field)
  17. From the Donor Folder, add the following fields:
    • Full Name
  18. Leave the Do Not Aggregate Results toggle unselected.
    • This effectively means results WILL be Aggregated.
    • This toggle defaults as unselected in every new query.
  1. Click on the Sample Results button.
  2. Observe Sample Results – Aggregation occurs and SELECTED Activity Counts equal 2 for Donors with more than one Activity recorded during date period.
  1. Close the Sample Results.
  2. Return to the Output tab.
  3. Select the Do Not Aggregate Results toggle.
    • This effectively means results WILL NOT be Aggregated.
  1. Save the Query. If not saved, aggregation change will not execute.
  2. Click on the Sample Results button.
  3. Observe Sample Results – Aggregation does NOT occur. ALL Activity Counts equal 1 as ALL Donors Activities are recorded separately during date period.

Commonly Used Queries

The following section will provide overviews and steps for building queries that address specific, but commonly used, functions and query builds in the CRM.

Building OR Statements in Queries STEPS

In this section users will learn how to build the previously presented OR examples:

  • All Donors by City in San Francisco OR San Diego.
  • All Gifts by Year Received of 2024 AND Month Received of January OR Month Received of May.
  • All Activity with an Activity Value of 10+ AND a Year Received of 2024 OR 2023 AND Month Received of January OR Month Received of May.

Query 1 (OR) Build: All Donors by City in San Francisco OR San Diego.

  1. Click on the Queries Module>New Query sub-module.
  2. In the Name field, enter All Donors in San Francisco OR San Diego.
  3. From the Query Type dropdown, select Gifts.
  4. Click on the Criteria tab.
  5. Expand the Donor folder.
  6. Drag and Drop the City field to the selection column.
  7. Keep Is Equal to as the Operator.
  8. In the Criteria field, enter San Diego.
  9. Click Save.
  10. Drag and Drop the City field to the selection column (second time).
  11. Change the AND/OR selector to OR.
  12. Keep Is Equal as the Operator.
  13. In the Criteria field, enter San Francisco.
  1. Click Save.
  1. Click on the Output tab.
  2. Expand the Donor folder.
  3. Drag & Drop the City & Full Name fields to the Selected Outputs column.
  4. Expand the Gifts folder.
  5. Drag & Drop the Date Received & Gift Value fields to Selected Outputs column.
  6. From the Sort Fields section, select City from the Field One dropdown.
  7. Click Save.
  8. Click the Sample Results button.
  9. Observe the results.

Query 2 (OR) Build: All 2024 Gifts Received in January and May

  1. Click on the Queries Module>New Query sub-module.
  2. In the Name field, enter All 2024 Gifts Received in January and May.
  3. From the Query Type dropdown, select Gifts.
  4. Click on the Criteria tab.
  5. Expand the Gifts folder.
  6. Drag and Drop the Year Received field to the selection column.
  7. Keep Is Equal to as the Operator.
  8. From the Criteria drop down, select 2024.
  9. Click Save.
  10. Drag and Drop the Month Received field to the selection column.
  11. Keep Is Equal as the Operator.
  12. From the Criteria drop down, select January.
  1. Drag and Drop the Month Received field to the selection column (second time).
  2. Confirm the AND/OR selector is set to OR.
  3. Keep Is Equal as the Operator.
  4. From the Criteria drop down, select May.
  5. Click Save.
  1. Click on the Output tab.
  2. Expand the Donor folder.
  3. Drag & Drop the Full Name fields to the Selected Outputs column.
  4. Expand the Gifts folder.
  5. Drag & Drop Month Received & Gift Value fields to Selected Outputs column.
  6. From the Sort Fields section, select Month Received from Field One dropdown.
  7. Click Save.
  8. Click the Sample Results button.
  9. Observe the results.

Query 3 (OR) Build: All 2024 & 2022 in January & May – Activity of $10+.

  1. Click on the Queries Module>New Query sub-module.
  2. In the Name field, enter All 2024 Gifts Received in January and May.
  3. From the Query Type dropdown, select Activity.
  4. Click on the Criteria tab.
  5. Expand the Activity folder.
  6. Drag and Drop the Activity Value field to the selection column.
  7. From the Operator drop down, select Is Greater than or Equal to.
  8. In the Criteria field, enter 10.
  9. Click Save.
  10. Drag and Drop the Year Received field to the selection column.
  11. Keep Is Equal as the Operator.
  12. From the Criteria drop down, select 2024.
  13. Drag and Drop the Year Received field to the selection column (second time).
  14. Confirm the AND/OR selector is set to OR.
  15. Keep Is Equal as the Operator.
  16. From the Criteria drop down, select 2022.
  17. Click Save.
  18. Criteria should appear as screenshot with progress made:
  1. Drag and Drop the Month Received field to the selection column BUT drop it on the & symbol at the top – NOT in the white space/NOT on the Year Received.
  2. Keep Is Equal as the Operator.
  3. From the Criteria drop down, select January.
  4. Drag and Drop the Month Received field to the selection column (second time).
  5. Confirm the AND/OR selector is set to OR.
  6. Keep Is Equal as the Operator.
  7. From the Criteria drop down, select May.
  8. Click Save.
  1. Click on the Output tab.
  2. Expand the Donor folder.
  3. Drag & Drop the Full Name fields to the Selected Outputs column.
  4. Expand the Activity folder.
  5. Drag & Drop Activity Value & Month/Year Received to Selected Outputs.
  6. From Sort Fields, select Month/Year Received from Field One dropdown.
  7. Click Save.
  8. Click the Sample Results button.
  9. Observe the results.

Using an Existing Query within a New Query STEPS I

First Query: All Donations YTD

  1. Click on the Queries Module>New Query sub-module.
  2. In the Query Name field, enter All Donations YTD.
  3. From the Query Type dropdown, select Gifts.
  4. Click on the Criteria tab.
  5. Expand the Appeals folder.
  6. Drag and Drop the Appeal ID field to the selection column.
  7. From the Operator drop down, select Is Equal to.
  8. In the Criteria field, enter 24_1WT.
    • Users should substitute Appeal ID(s) from their system.
  9. Click Save.
  10. REPEAT Steps 6-9 for as many Appeal IDs as desired.
    • Purpose is to create a “baseline” query – a type of template – that
      contains ALL Appeals Year to Date in one Query.
    • This query is maintained to include any new Finalized Appeals as leverageable values for this query as a query within other queries.
  11. IMPORTANT – ensure OR is selected at top of each criteria filter submission.
  1. Click on the Output tab.
  2. Expand the Donor folder.
  3. Drag & Drop the Donor ID & Full Name fields to the Selected Outputs column.
    • Since this Query is acting as a type of filter for other queries, it is not necessary to build out the Output tab as it will not be inherited.
  1. Click Save.
  2. Save the Query.
  3. Click the Sample Results button.
  4. Observe the results.
  5. Click the Results Count button.
  6. Record the Count # for comparison.

Second Query: All Donations YTD

  1. Click on the Queries Module>New Query sub-module.
  2. In the Query Name field, enter All Donors YTD.
  3. From the Query Type dropdown, select Contacts.
  4. Click on the Criteria tab.
  5. Expand the Gifts folder.
  6. Drag and Drop the With Gifts field to the selection column.
  7. From the Operator drop down, select Contained In.
  8. In the Criteria field, select All Donations YTD from the dropdown.
    • This is where the user is selecting the “Query within the Query”
  1. Click Save.
  2. Click on the Output tab.
  3. Expand the Donor folder.
  4. Drag & Drop the Donor ID, Full Name, and any other desired fields to the Selected Outputs column.
  5. Click Save.
  6. Save the Query.
  7. Click the Sample Results button.
  8. Observe the results.
  9. Click the Results Count button.
  10. Record the Count # for comparison to the First Query.
    • Should be less records due to the elimination of same donors giving to multiple Appeals.

Third Query: All Donors YTD – NO Direct Mail Flag

  1. Click on the Queries Module>Locate Query sub-module.
    • We will be COPYING the All-Donors Query to create the third query.
  2. In the Name Contains field, enter All Donors YTD.
  3. Open the Query.
  4. In Query Name field, amend text to read: All Donors YTD – NO Direct Mail Flag.
  5. Click Save.
  6. At the Save dialog box, select Save As New.
    • Copying a Query saves time with the setup of initial Criteria and Output.
  7. Click on the Criteria tab.
  8. Existing criteria filter of With Gifts Contained In “All Donations YTD” displays.
  9. Click on the Donors folder.
  10. Drag the No Direct Mail flag field to the selection column.
  11. In the Operator dropdown, keep Is Equal To as the value.
  12. In the Select an Item dropdown, keep True as the value.
  13. Click Save.
  1. Save the Query.
  2. Click the Sample Results button.
  3. Observe the results.
  4. Click the Results Count button.
  5. Record the Count # for comparison.
    • Should be less records due to the elimination of donors with No Direct
      Mail flag checked.

Using an Existing Query within a New Query STEPS II

First Query: Donations for 2023

  1. Click on the Queries Module>New Query sub-module.
  2. In the Query Name field, enter Donations for 2023.
  3. From the Query Type dropdown, select Activity.
  4. Click on the Criteria tab.
  5. Expand the Activity folder.
  6. Drag and Drop the Activity Date field to the selection column.
  7. From the Operator drop down, select Is Greater than or Equal to.
  8. In the Criteria field, enter 12/01/2023.
  9. Click Save.
  10. Click on the Output tab.
  11. Expand the Donor folder.
  12. Drag & Drop the Donor ID & Full Name fields to the Selected Outputs column.
    • Since this Query is acting as a type of filter for other queries, it is not necessary to build out the Output tab as it will not be inherited.
  13. Click Save.
  14. Save the Query.
  15. Click the Sample Results button.
  16. Observe the results.
  17. Click the Results Count button.
  18. Record the Count # for comparison

Second Query: Solicitations for 23_1WT Appeal

  1. In the Query Name field, enter Solicitations for 23_1WT Appeal.
  2. From the Query Type dropdown, select Letters.
  3. Click on the Criteria tab.
  4. Expand the Appeal folder.
  5. Drag and Drop the Appeal ID field to the selection column.
  6. From the Operator drop down, select Is Equal to.
  7. In the Criteria field, enter 23_1WT.
  8. Click Save.
  9. Click on the Output tab.
  10. Expand the Donor folder.
  11. Drag & Drop the Donor ID.
  12. Click Save.
  13. Save the Query.
  14. Click the Sample Results button.
  15. Observe the results.
  16. Click the Results Count button.
  17. Record the Count # for comparison.

Third Query: Donors Solicitated for Appeal 23_WT with Donation Last 12 Months

  1. In the Query Name field, enter Donors Solicitated for Appeal 23_WT with Donation Last 12 Months.
  2. From the Query Type dropdown, select Contacts.
  3. Click on the Criteria tab.
  4. Expand the Activities folder.
  5. Drag and Drop the With Activities field to the selection column.
  6. From the Operator dropdown, select Contained In.
  7. From the Select an item drop down, select Donations for 2023.
    • The FIRST “Query within the Query”.
  8. Click Save.
  9. Expand the Letters folder.
  10. Drag and Drop the With Letters field to the selection column.
  11. From the Operator dropdown, select Contained In.
  12. From the Select an item drop down, select Solicitations for 23_1WT Appeal.
    • The SECOND “Query within the Query”.
  13. Click Save.
  14. Click on the Output tab.
  15. Expand the Donor folder.
  16. Drag & Drop the Donor ID and Full Name fields.
  17. Click Save.
  18. Save the Query.
  19. Click the Sample Results button.
  20. Observe the results.
  21. Click the Results Count button.
  22. Record the Count # for comparison.

Unique Tables

Creating a Contacts Query using Functions

There are two tables within query types that users should be aware of that enable faster and more efficient pulling of Donor level data in queries – Nightly Calculated Fields and Functions.
  1. Click on the Queries Module>New Query sub-module.
  2. In the Query Name field, enter Donors Responding to 24_1WT.
    • Users should substitute their desired Appeal for 24_1WT.
  3. From the Query Type dropdown, select Contacts.
    • REMINDER: Functions table is ONLY available for Contacts query type.
  4. Click on the Criteria tab.
  5. Expand the Functions folder.
  6. Drag and Drop the With Response to Appeal field to the selection column.
  7. From the Operator drop down, select Contained In.
  8. In the Appeal ID field, enter 24_1WT.
  1. Click Save.
  2. Click on the Output tab.
  3. Expand the Donor folder.
  4. Drag & Drop the Donor ID & Full Name fields to the Selected Outputs column.
  5. Expand the Functions folder.
  6. Drag & Drop the Valid Email field to the Selected Outputs column.
    • The Valid Email field indicates via 0 or 1 in the results whether or not a Donor has a usable email for communications.
  1. Click Save.
  2. Save the Query.
  3. Click the Sample Results button.
  4. Observe the results – receive a list of Donors who responded to the Appeal.
    • For more than 200 results, users need to Export the data.

Jump to