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, 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.
The Edit Query Interface
This is where we build the initial query that will populate the ETO Results report.
Note the Results Objects and Query Filters sections and that the divider can be moved up or down to make either section larger or smaller.
Review the folder structure found within the Data section on the left. The folders can be expanded and closed by using the toggles (plus signs), as can fields that are made up of sub-fields, such as Name. Some of the fields are pre-set measures, and appear as orange rectangles. For example, Count of Participant Site Identifier. That field operates just like using the Count on the Advanced Filter applied to the Participant Unique Identifier field in the Query Wizard as seen in the image above.
In the bottom left corner, the tab reads Query 1. At the top of the page, there is a button labeled Add Query in the case that queries from different universes are required within a single report.
In the upper right corner, click Run Query to generate the report. Note that you can always return to this page if it’s later determined that additional fields need to be added to the report.
Getting to Know Identifiers
Identifiers ensure data quality by anchoring discrete pieces of data to a unique number. Without identifiers, participants with the same name would result in a single set of data for a single unique name. You would not be able to distinguish different data elements with the same Effort Name and TouchPoint data would results in inflated values. Below is a list of the most important identifiers to know and use.
The Identity Folder:
- Participant Enterprise Identifier: to be used when reporting across Sites. This is an unwieldy “string” and should not be used for Site reporting.
- Subject Unique Identifier: This is a distinct number assigned to Participants and Entities and attached to TP reporting. It should only be used when reporting on both.
- Participant Site Identifier: This is a best practice identifier. Because it is attached to all tables in the database, include it in all queries to ensure proper data connections.
- Case Number: This may or may not be the same as the Participant Site Identifier. It is an optional number that sites may use to record an internal identifier. While this can be used in reporting, be sure that you continue to add Participant Site Identifier as well and use the Participant ID when creating formulas or merged variables.
- Family Site Identifier: This number is attached to a Family Group when using Manage Household feature. If you do not use this feature (you may be creating families using Collections, for example), then you do not need to use this Identifier.
- Family Enterprise Identifier: This is the same as above, except it allows Families to be reported on across sites.
In the Standard Entity Universe, you will also see:
- Entity Site Identifier: This serves the same purpose as the Participant Site Identifier and should be used when reporting on Entities when possible. It is an alternative to the Subject Identifier.
TouchPoint Specific Identifiers
- Response Set ID: for Collection responses – will return one value for the entire collection
- Collection Type ID & Name: The type of Collection, eg, Entity or Participant or Collection
- Collection ID & Name: returns data relative to a specific collection (family, class, etc.) Identifier: The ID of this TP Form; corresponds to the unique Name of the TP.
- Response ID: the identifier attached to each response in a TP form