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.
- an administrator
- a user whose View Details for Individuals permission is set to Allow
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
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.
Permissions Required
- an administrator
- a user whose View Details for Individuals permission is set to Allow
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.
Permissions Required
- an administrator
- a user whose View Details for Individuals permission is set to Allow
- Click .
- Click the report name of the query you want to rerun.
- Click Edit Report.
- The original report attributes display. Delete them, or add new ones.
- Click Show Results.
- 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.
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
- 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.
-
- 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.
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
-
- 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.