10 Basics & Best Practices for Queries

Below are 10 basic elements and best practices for building queries in Gnosis. See also, Introduction to Gnosis Reporting, for general navigation and training videos.

1. There are 3 elements to a Gnosis query:

• Schema selection = The area of data you are targeting.
• Query Criteria = The exact group/data you are targeting.
• Return Fields = The individual lines of data to be returned for review.

These three elements are all used on the Ad Hoc Query, reporting, and recipient groups setup screens.

2. Start with the right schema.

Most often the default schema, *Persons with H'hold & Org Related Info, will suffice and allow you to return a variety of data on a target group; other detailed reports will require a different schema.  See Schema Selection for more guidance. If you are copying an existing Gnosis Report or Recipient Group, be mindful of the schema being used.

3. Using the Ad Hoc Query screen.

Think of the Ad Hoc Query screen as a sandbox for the following activities:

• Practice for query building
• Quick or small queries which may not require a full report
• A sandbox to work on a new or complex query before entering the full report or recipient group setup process.

4. Remember it's easy to copy/paste query criteria between the Ad Hoc, recipient group, and reporting screens.

All three of the query-building areas provide easy copy/paste buttons. All three elements (item 1 above) will be copied during this process. See Copying Criteria for guidance.

5. Rarely should you need to start a query from scratch.

Your system came with a plethora of standard reports for all areas of your organization. Review your Reporting Module to locate a report to copy and edit, or you're likely to find the exact report you are looking to create!

Use the Category dropdown filter and Report Name Search field to help locate helpful reports.

6. How to remove duplicated data rows:

Duplicate or multiple return lines per record may appear for a couple of different reasons. See Dealing with Duplicate Data Rows to resolve this issue.

7. Steps to complete when copying an existing report.

Most standard reports created by Connect4 Support will be locked for editing, however, you can Copy these reports to make them your own. To ensure that you can access this new report again in the future, be sure to complete the following during the copy/setup process.

• Report Name: Update the name even if only to remove the "Copy of" text.
• Report Description: Update to reflect your changes to the criteria. Don't overlook this opportunity to describe your edits for future use of this report by you and others!
• Report Owner: Click the X to remove the previous owner and enter your name.
• Report Editing Locked: Uncheck/check as desired.
• Continue through the setup process to edit the criteria and or return fields as desired.

8. Know your query output options.

In Gnosis version 1.2.790 and higher, the output options for each report will be displayed following the report name. A single click of these icons will produce the corresponding outputs.

9. Create flexible reports instead of one-time reports.

Using the Enter Value prompt and entering exact criteria filters, like actual dates, income classes, etc. will result in a one-time use report. Instead, use the Enter Prompt for Value or Choose Special Value options to make the report more versatile and reusable.

10.  Know your database.

The better you know your database, the more successful you will be in building queries - meaning the more familiar you are with the exact field names in Gnosis records and processes, the easier it will be to locate and use these fields to their fullest potential in a query. 

A Final Note on Gnosis Queries,

Queries, in general, require database knowledge and practice; complex queries require experience. It is best to have at least a couple of staff members who are able to build different queries for your organization.

If you are struggling with a specific query, you may submit a ticket to [e-mail] for guidance.