The list of folders and subfolders containing fields referred to as dimensions, details, and measures. Different field types are demarcated with different symbols - blue parallelograms, green diamonds, and orange rulers. The fields that appear are dependent on the Universe selected.
To start, Users may want to drill down into the folders (Identity, Program Enrollment, Point of Service Efforts, etc.), sub-folders (Demographics, Flattened Demographics, Duplicate Match Criteria, etc.), and toggled dimensions (Name, Family Name, etc.) to become familiar with the data and how it's organized.
Result Objects
To create a new Query, drag and drop elements from the Data Tab into the Results Objects Box:
Drag elements into this box that should display results in the report (ex: Name, Gender, POS, Reasons for Dismissals)
Dimensions, Details, and Measures can be pulled into the Results Box; Filters CANNOT be pulled into the Results box
Any elements dragged into this box will be a distinct column in the report.
Note: different element types are classified by icons
Dimensions - Appear as blue pinwheels. These are representative of something in ETO, for example: Name, DOB, Program Start Date, etc
Details - appear as green pinwheels. These are fields within an object (not all objects contain details), for example: Prefix, First Name, Middle Name, etc
Measures - Appear as orange rulers. The objects are grouped numerical data based on dimension data in the universe, for example: Sum, Count, Min, Max
Filters - Appear as a red funnel. These objects have the ability to narrow data, but can only be placed in the Query Filters section of a query, for example: Active, Enrolled, and Dismissed filters.
Query Filters
In ETO Results, drag and drop fields that you want to filter by into the Query Filters section. By default, filters are set as constant. A constant filter will always be applied to the report. In this example, Response ID and Date Taken both require constant filters.
To set the filter, click the down arrow to the right of where it reads “In List” and select the type of filter needed. In this example, we are selecting Is not null for Date Taken.
Note all of the additional Query filter types available in ETO Results, including Greater than or Equal to, Less than or Equal to, Not Between, In List, Not In List, Except, etc.
Filters will often be set to Prompt, especially for Date Ranges, such as Date of Contact, Program Start Date, DOB, Date Taken for Assessments, etc.
Once Between is selected as the filter type, Prompt must be selected for both fields – meaning the starting date and ending date of the range.
Be sure to select starting with the field on the left and ending with the one on the right. The order they are selected will determine the order they will appear in the Prompt window each time the report is run. This order can be adjusted in Edit Query on the Properties tab, under Prompt Order. The default order is typically alphabetical.
NOTE: The language that appears in the fields can be edited. In the image below, Enter Program Start Date (Start) and Enter Program Start Date (End) were edited to read Between "Start Date" and "End Date".
Prompts will collapse into one if the text matches. The text for all standard filters (Active, Dismissed, etc.) is Begin Date and End Date.
NOTE: By default, prompting filters are required. Required filters are marked with red arrows to their left. To set a filter so that it’s optional instead of required, click the button next to the prompt text to open the prompt properties.
Here you can select to make the prompt optional.
Depending on the field type the filter options will vary. Dates, arbitrary text, and string values such as exclusive choice and non-exclusive choice have pre-determined filter options, in addition to Is null and Is not null.
When the saved Query is run, the prompt is presented to the User along with any text that was included in the Query description, as seen below.
If you have more than one filter, the default relationship is “And”, but if you double-click on the word “And”, you can create an “Or” filter relationship as well. For example, if you used filters for [Race] Equal to Hispanic and [Gender] Equal to Female, an “And” relationship would return only Hispanic Females. An “Or” relationship would return Hispanic Males, Hispanic Participants with no gender entered, and Females of all races.
If you have more than two filters selected, you can create tiers of “And” and “Or” relationships by dragging dimensions in the Query Filters section to the right. These tiers are generally referred to as “nested” filters.
Program Enrollment Filters
The list of objects below will return "Yes/No" values and can be useful for Query filtering.
Is Active set to "Yes" in the Query filter will return only those Participants or Entities who are currently Active in any Program.
The "Was" objects, when added to the Result Objects pane, will automate a date range. For this reason, it is unnecessary to also include a Universe filter (see below). These objects will limit the data to anyone who "was" active/dismissed/enrolled during a Particular date range.
Universe Filters
Universe filters are pre-built filters available in certain folders within a Universe. When pulled into the Query Filter pane, they automate data filtering for the report.
Below are some examples:
Active Filter: This filter will return a list of Participants who were active within the selected date range.
Enrolled Filter: This filter will return a list of Participants who were enrolled within the selected date range.
Caseload Filters can be confusing. Here is an explanation of the purpose of each:
Current User Caseload Date Range Filter: This filter will only return a list of Participants with a Caseload start date/end date in the selected date range for the Caseload of the current User running the report.
Current Caseload Filter: This filer will automatically return a list of Participants who are currently active on a Caseload.
Active Caseload Date Range: This filter will return a list of Participants who were active on a Caseload in the selected date range,
Enrolled Caseload Date Range: This filter will return a list of Participants who were enrolled onto a Caseload in the selected date range.
Dismissed Caseload Date Range: This filter will return a list of Participants who were removed from a Caseload in the selected date range.
Optimized Queries
Building Queries in ETO Results is an art form. The following guide presents general concepts that guide Query development generally, but will not apply in every circumstance. Many Users may need to take a “trial and error” approach to building more efficient Queries, using the rules herein.
Selecting a Universe:
See also: Which Universe to Use
The ETO Results Report Developer has three Universe tools at his or her disposal: Standard Universes, Custom Universes, and Query Wizard Universes.
Standard Universes: Standard Universes (such as Standard Participant Universe) are frequently improved and generally offer the most efficient method of querying data. Social Solutions generally recommends using the Standard Universes whenever feasible.
Custom Universes: Enterprises can develop their own custom Universe using the Manage Report Universes feature.
Query Wizard Universes: Generally, Queries built and shared to ETO Results by the Query Wizard will run slower than corresponding Queries in the Standard Universes. However, with a low-volume data set, Query Wizard Universes can sometimes improve performance.
Factors Affecting Query Processing
Volume of Data: The higher the volume of data queried, the slower the report will run. In more complex Queries, the volume of data will exacerbate the slower performance. Conversely, a low-volume data set may not experience any performance issues whatsoever. Report Builders using low-volume data sets may not need to utilize any of the tips herein.
Folder Location: Certain folders are inherently slower or faster than others. This means that a Report Developer can utilize the faster folders with reckless abandon, while the slower folders should only be utilized when necessary.
Certain Folders are just plain slower (or faster) than others:
The Fastest Folders
Program Enrollment
Identity and Flattened Demographics (with the exception of Caseloads)
The Slowest Folders
Effort Qualifiers
Caseloads
Assessments
Audit Trail Data
General Tips for Building Optimized Queries:
Build a Custom Universe using the Manage Report Universes feature: This feature is specific to focus areas available in the Enterprise-specific Universes (etoxxx Participant Universe and etoxxx TouchPoint Universe). The most efficient custom Universes will be built on a narrow focus for large data sets. This may be limited to a single TouchPoint form, along with Program Enrollment, Family information, and flattened demographics, as an example.
Start with the end in mind: Know exactly which data you’ll need to build your formulas and present your data. Limit the data pulled to only what the report needs.
Include a data range filter (active, dismissed, enrolled, start date, end date, etc.).
Filter by specific function, prompt name, and value (Date Taken, Program Enrollment Dates, etc.)
Avoid combining data unnaturally: Data from different main folders (TouchPoints, Program Enrollment, Collections), do not naturally identify with each other, but rather, through the “Identity” folder. Everything is linked to the subject. A Query that contains dimensions from many main folders will “join” the data in complex manners, leading to slow run times.
How to remedy/troubleshoot:
Separate your data into multiple Queries and limit each Query to a single “folder” where possible.
Avoid Query-side Measures: The standard “Query-side” measures (such as Count of Participants), can be useful to a new report builder. However, they create Query-side processing that can slow down the execution of the Query. All of these measures can be built simply on the report side with a custom variable.
Avoid “Results from another Query”: When using multiple Queries to create a resulting data set, it can be tempting to use the data from one Query to filter the results of another Query. Using this “Results from another Query” function can be very powerful, but also very slow. This method should only be used in low-volume data sets.