Custom Query Overview

Custom queries let you search, sort, and output your data in any way needed.

When you query data (simple or complex), you are essentially asking a question and expecting an answer. If you track it, you can report on it using queries. Custom queries not only answer your questions, but you can search, sort, and output your data according to your preferences. Custom queries are also useful for automating data management tasks.

Note:
To run a query, you must be either:
  • an administrator
  • a user whose View Details for Individuals permission is set to Allow
Additionally, you must have the Edit Group permission in order to create a query using Groups attributes or have a report with those attributes shared with you. For more about permissions, see Edit a User's Permissions.

Key Takeaways:

  • When you create a custom query, you can save it right away or review the results first.

  • Rather than re-create complex queries every time you want to retrieve data, save your queries and run them again with a single click. You can also share them with others who have permissions to run them.

  • You can also use queries to make bulk changes to records or create mailing lists for contacting many people at once. For example, you can:

    • Send an email to everyone returned by a report
    • Create mailing labels from a report
    • Add to a group people returned by a report
    • Export the results of a report to a text file
    • Mark individuals returned by a report inactive
    • Change a profile field or privacy selection for multiple records simultaneously

  • Download the PDF guide here.


One custom query lets you: invite to Realm, activate/deactivate, change profiles in mass, export to print, export to spreadsheet, send mass emails, print mailing labels, print the church directory, add to pathways, and add to groups

Create Custom Queries

Custom queries are for automating data management tasks or analyzing and reporting information gathered across your entire data set. If you track it, you can report on it using queries.

Note:

Permissions Required

To run a query, you must be either:
  • an administrator
  • a user whose View Details for Individuals permission is set to Allow
Additionally, you must have the Edit Group permission in order to create a query using Groups attributes or have a report with those attributes shared with you. For more about permissions, see Edit a User's Permissions.
  1. Click Reporting > Reports > Custom Query.
  2. Select a main attribute, and make any necessary selections.
  3. If there are sub-attributes related to the main attribute you selected, the Select an optional sub-attribute drop-down list displays. Make a selection, or click the X in the blue circle to remove the sub-attribute.
  4. If needed, add another main attribute:
    • If the main attribute works in conjunction with the previous one, click And.
    • If the main attribute is not tied to the previous one, click Or.
  5. You can also narrow your results using the drop-down list under Show. For more information on those options, see Queries Show Options.
  6. Select whether to include inactive and/or deceased profiles.
  7. Click Show Results.
    1. Simplify or expand your results. Click Edit Columns, and select the columns you want to see. You can delete columns by pointing to them and clicking the ellipsis icon. This will not change your report results, just the amount of detail displayed for each record in the results. Edit Columns may be available, depending on the Show option you choose.
    2. Sort your results. Click a column name to sort the results based on that category.
  8. If you're satisfied with the results, click Save As.
  9. Give the report a name and, if needed, a description.
  10. Click Save.
  11. To view your saved reports, click Reporting > Reports > My Saved Reports. To more quickly locate a saved report, click the magnifying glass icon, and enter the report name.
Note:

When query selections no longer match...

Over time, you might have to rename fields, change field selections, and alter permissions. This will affect your saved reports and could even make them impossible to run. When this happens, you will be notified that the original report can no longer be executed exactly as created. Acknowledge the message to continue.

Rerun an Unsaved Query

Rerun an unsaved custom query.

Note:

Permissions Required

To rerun a query, you must be either:
  • an administrator
  • a user whose View Details for Individuals permission is set to Allow
Additionally, you must have the Edit Group permission in order to rerun a query using Groups attributes or have a report with those attributes shared with you. For more about permissions, see Edit a User's Permissions.
  1. Click Reporting > Reports > Custom Query.
  2. Click the report name of the query you want to rerun.
  3. Click Edit Report.
  4. The original report attributes display. Delete them, or add new ones.
  5. Click Show Results.
  6. If you're satisfied with the results, click Save As. This saves your custom query to My Saved Reports.

Edit a Query

Tweak that query 'til it's just right.

  1. Create a query, or run a saved one.
  2. Click Edit Report.
  3. The original report attributes display. Delete them, or add new ones.
  4. Click Show Results.
  5. To overwrite the original report, click Save. To save it under a new name and description, click Save As.
    Note:

    When query selections no longer match...

    Over time, you might have to rename fields, change field selections, and alter permissions. This will affect your saved reports and could even make them impossible to run. When this happens, you will be notified that the original report can no longer be executed exactly as created. Acknowledge the message to continue.

Queries Show Options

Defines the Show options in queries.

People and Business Options

All
Displays all profiles that match all of the query's criteria.
People
Displays individuals who match all of the query's criteria.
Businesses
Displays businesses that match all of the query's criteria.
All Family
Displays the entire family if anyone in the family matches all of the query's criteria. This includes children who are attached to multiple families.
All Adults
Displays all individuals who have the "Adult" family position if anyone in the family matches all of the query's criteria.
All Children
Displays all individuals who have the "Child" family position if anyone in the family matches all of the query's criteria.
All Matching Children
Displays all individuals who have the "Child" family position when those individuals (not family members) match all of the query's criteria.

Primary Family Options

When a child is linked to two families, and you have both of the child's parents in Realm, one family is designated as the primary.
Primary Family
Displays the entire primary family if anyone in the primary family matches all of the query's criteria.
Primary Adult
Displays all individuals of the primary family who have the "Adult" family position if anyone in the primary family matches all of the query's criteria.
Primary Children
Displays all individuals of the primary family who have the "Child" family position if anyone in the primary family matches all of the query's criteria.

Giving Options

Contributor View
Requires the Giving Amount or Pledge Amount attribute.

Queries Field Descriptions

Fields you'll encounter working with queries.

Saved Reports Page

Last Generated On
This is the date you last ran this report, not the date it was run by others who share it.
the ellipsis icon
Click to share, edit, copy, or delete a report. This button is only visible when you point to a report.

Custom Report Page

Select a main attribute...
Main attributes are primary pieces of information in your data, like "Address" or "Ministry Area". For example, you can query on the main attribute "Ministry Area" to gather all the profiles in a certain ministry area.
Select an optional sub-attribute...
Some main attributes have optional sub-attributes that you can use to narrow your query results. These fields are directly related to the main attribute, such as "Address Type" or "Group Type". For example, to find all the guests in a certain ministry area, you could select "Ministry Area" as the main attribute, then select the sub-attribute "Roster Type" is equal to "Guest".
And
Use And to add another main attribute that works in conjunction with the previous attribute you selected. When you use the AND statement, both parts of the query must be true in order for results to display.
Or

Use Or to add a main attribute that is not tied to the previous attribute you selected. When you use the OR statement, any part of the query can be true in order for results to display.

Some tips regarding the use of AND and OR statements in your reports:
  • If you search on a giving field in multiple OR statements, you will get multiple columns for the amount. So, if you search for anyone who gave $1000 to the Budget fund OR anyone who gave $1000 to the Building fund, the results grid will have two amount columns.
  • If you search on a ministry area/group field, you will get multiple columns for each AND/OR statement. So, if you search for anyone who was in Administration AND Small Groups, OR anyone who was just in Bible Fellowships, you would get 3 Ministry Area fields in the result.
  • If you search on any other field, it will combine the results. So, if you search for anyone who had an allergy to peanuts OR an allergy to soy AND someone was allergic to both, you would just get one allergy field in the results with all that person's allergies.
Show
Select a filter for your report results. While the report includes all of the parameters you select, like address, privacy settings, and so on, only records matching the Show filter will display. This means you can change the filter at a later time without editing the rest of the report. This filter can also change the columns that display. Your Show selection is saved with the report. Tip: Use the Contributor view filter to display the total giving amounts of individuals (or couples, if giving is shared) and giving numbers (if your church uses these) returned by the report. This view is displayed by default if your custom report includes giving or pledge attributes.
Include Inactive Profiles
Select to include the records of individuals who have been marked inactive. If necessary, these individuals can be marked active, either singly or in mass, using the drop-down list at the top of the results.
Include Deceased Profiles
Select to include the records of individuals who have been marked as deceased.
Miscellaneous Search Criteria

Account...Profile...has a login / doesn't have a login —Use these criteria to search for individuals who have or don't have accounts they can use to log in. For those without logins, you can send individual Realm invites using the ellipsis icon at the top of each person's profile page. You could also send a mass email from the report.

Groups...Date First Attended —This finds the day an individual was first marked present for any event in any group.

Groups...Ministry Area/Group —The drop-down list does not include inactive groups.


query with selections Ministry Area or Group is equal to 11th Grade

Giving...Shared Giving...is yes —While a report using these criteria will display individuals who use a shared giving number, the displayed Giving Amount, if included in the report, will be that of each single family member, not the shared, combined total.

Date of Birth...is month of...Select All months...Ignore Year (selected) —Use these criteria to create a report listing birthdays. To sort, click the Date of Birth column header.

Sacrament (or your equivalent word) such as Baptism...is complete, Parish/Church Address —

  • is equal to. This will list all parishes/churches where this phrase matches completely and exactly any address part recorded for the church. Addresses have several parts: street number and name, second street number and name, city, state, and postal code. So "325 Huckston St." or "Albany" or "NY" or "12252" would return the Albany Worship Center at that address. "Huckston" would not, nor would "1225".
  • is not equal to. See above. The same applies, but in reverse. If you entered "Huckston", the Albany Worship Center would still be included in the list. If you entered "325 Huckston St.", it would not.
  • starts with. This also searches each address part separately. Again, consider the church above. If you enter "325", Albany Worship Center will be returned. But so would any other address whose postal code begins with 325 since postal code is a separate, self-contained address part.
  • contains. This also searches address parts separately. So, in the example above, a search on "Albany" would return a list that includes Albany Worship Center, but a search on "Albany NY" would not. Separately, "Albany" is a recognized city, and "NY" is a recognized state, but, together, they are neither a city only nor state only.

Sacrament (or your equivalent word) such as Baptism...is complete, Volume, Page, Entry... is equal to— If you search on a specific Volume, Page, Entry field value, make sure to use the format "NUMBER comma NUMBER comma NUMBER". You cannot leave a blank value for volume, page, or entry.

Results Page
Pledge Amount (total) / Pledge Amount (shared total) —When reporting on pledge amount, these columns display alongside each other. The first is the amount pledged by the individual listed; the second is the same amount combined with any amount pledged by the person with whom the individual has shared contributions.

Shared Reports Page

the ellipsis icon
Click to copy or delete a shared report. Since shared reports can be changed by their creator, copying a shared report allows you to customize it and save it as your own. Then, your saved report cannot be changed by someone else. This button is only visible when you point to a report.