Query Wizard is a tool designed as a supplement to the numerous reports available in ETO software. Unlike the other wizards, Query Wizard does not create screens for data entry. Query Wizard is a tool utilized to generate custom reports and contains real-time data.
A Query is any request for data from the database. By building a Query, you are asking ETO software questions of your data. The answers will be returned to you in a grid format.
The Query Wizard only returns distinct records in the output. This means that any rows of data which are precisely the same will be displayed only once (thereby eliminating all true, full duplicate rows). For example, if there are two John Smiths who live in the same city and state, and the Query is only returning first and last name, City and State, the Query Wizard will only return one row of data instead of two. This can be avoided by including the Unique Identifier or another “unique per record” field in all Queries.
It is important to remember that by default, Queries pull back information from across your entire Site (or Enterprise for Users logged in as Enterprise managers) and from both Dismissed and Active Participants. Filters can be applied so that data has a smaller scope or is specific to Program history.
Before using Query Wizard, Site administrators should consider what they are looking for in their data, and if there is already a report in ETO that can give them this information. Reports should be utilized whenever possible; Query Wizard should be considered the last resort for accessing data as standard reports are more attractive and easier to run. Query Wizard should be considered only after you determine that there is no report in the software that can compile the information you need.
Take into consideration your data security when providing Users access to Query Wizard. By default, Department Heads will only see information associated with the Participants who are associated with the Program they are in when running a Query, Site Managers will pull Queries containing data across the Site, Enterprise Managers will pull data across the Enterprise. If filters are applied to pull data specific to a smaller scope, those filters override the default scope. Giving Users with roles lower than Department Head access to the Query Wizard will give them access to all of the data in that Site.
In-Depth Instructions
Step 1 – On the Navigation Bar, click Wizards. Then select "Query Wizard".
Step 2 – Select one of the nine subjects available then click Continue.
1. Start Over – Clears all subjects selected and restarts the Query process.
2. Select Existing Query – If you have previously saved a Query, you can select it from this drop-down menu and skip straight to the results.
3. Select a Subject - Your options will include:
Participants – Participant information, custom demographics, outcome results at a summary and detail level, and system-wide information (placements in employment, education, etc.).
Entities – Basic Entity information (types, sub-types, addresses, etc.), outcome results at a summary and detail level, and customized Entity attributes.
General (Program Level) – Program-level details, how many in Program, receiving services, and general outcomes.
Staff – Staff-level performance.
Family – Family member information and family assessments.
Participant Efforts – Effort qualifier information associated with Participant efforts, effort information, single value effort qualifier, and multiple value effort qualifiers.
Entity Efforts – Effort qualifier information associated with Entity efforts, effort information, single value effort qualifier, and multiple value effort qualifiers.
General Efforts – Effort qualifier information associated with general efforts, effort information, single value effort qualifier, and multiple value effort qualifiers.
Funds – The fund voucher feature.
4. Selections can be made by double clicking, or a single click followed by clicking the arrow in the middle of the two boxes pointing right.
5. Subject Selected – The subject that the Query will be run on.
6. Subject Details – A description for a subject will appear to the far right once the subject is highlighted.
7. Continue – Click this button to move on.
Step 3 – Select one or more focus areas from the list available and click "Continue".
Always select the first focus areas, “Participant Information,” when building a Query in the Participants subject (“Entity Information” for Entity subject, etc.).
Additional focus areas should be selected based on the data that needs to be pulled.
There is no limit to the number of focus areas selected, but the speed of the Query may be affected if a large number of focus areas are selected.
Most Queries can be created with 4 focus areas or fewer.
Select only the fields that you need. It is often more efficient to create several different Queries instead of trying to pull everything into one report.
The list of Focus Areas may grow as continual improvements are made to the software.
1. Start Over – Restarts the Query process from Step 2.
2. Select Existing Query – If you have previously saved a Query, you can select it from this drop-down menu and skip straight to the results.
3. Previous Steps – Lists the previous results and the selections you made.
4. Select a Focus Area – Pre-defined set(s) of data within the selected subject including: demographics, Program history, assessments taken, efforts recorded, employment information, etc. The area listed are related to the subject selected in the previous step.
5. Selections can be made by double clicking, or a single click followed by clicking the arrow pointing right.
6. Focus Areas Selected – The areas you have selected to run the Query on.
7. Focus Details – A description for a focus area will appear to the far right once the focus area is highlighted.
8. View More – Click this link to expand the details on the highlighted focus area.
9. Continue – Click this button to move on.
Step 4 – Select the details for the Query and then click Continue.
1. Start Over – Restarts the Query process from Step 2.
2. Select Existing Query – If you have previously saved a Query, you can select it from this drop-down menu and skip straight to the results.
3. Previous Steps – Lists the previous results and the selections you made.
4. Select Details – Check any boxes that you want to appear in your Query. Checking a box will add a column to your Query results with a header named after that data element.
If you select a detail, but do not select a filter option, you will receive a list of all subjects, even if the chosen field has no data.
5. Filter – By default, all of the filters are in the OFF position (although the text reads ON). Clicking on the ON or OFF label will toggle the filtering options.
If a filter says ON in green lettering, the filter is actually off. Click this link to turn it on.
When a filter says OFF in red lettering, the filter is actually on.
A filter that is turned on will show additional information to the right of the filter.
Each data element has three available filters: regular, null, and advanced. Each type of filter has its own unique options, as detailed next.
If more than one filter is utilized in the same Query, the logic is AND logic.
6. Turn Filter – This is the first, or the top filter, and is also referred to as the regular filter. It allows the User to filter on a specific value for a question depending on the type of question.
Text-search filters search all of the text in a field for matches to your entry. These filters display a text box for searching “Where [field] is Like __.” Enter one letter, a partial name or word, or the full word.
Limited Choice/Exclusive Choice Filters such as Program, Outcomes, Entity Type, and Exclusive Choice Demographics, among others. To choose more than one selection, hold the CTRL key down as you click on each choice, or hold shift and use the up and down arrow keys to select several consecutive choices. These are also sometimes referred to as string values in the Query Wizard.
Date filters select birth dates, dates of contact, etc. Filter for one particular date, a range between two dates, all entries less than or greater than a particular date, or all dates not equal to the date you choose. Note: greater than and less than are not inclusive.
Numeric Filters work very similarly to Date Filters. An example of a Numeric Filter is the Age field, found under the Additional Participant Information focus area. Note: greater than and less than are not inclusive.
Boolean Filters refers to binary (yes/no or true/false) outcomes or responses with only two responses, such as the Standard Demographic Gender.
7. Filter Advanced – This is the third, bottom, or last filter. It allows for calculations (i.e. count, average, min, max, sum, and average).
Distinct Count delivers a count of the total number of distinct values that you would get if you were to display all of the values for this focus area.
Count is used to indicate the number of instances of the same value in a set of Query results. The count, unlike the distinct count, shows a duplicated number.
Min is used on date fields, such a Program Start Date, or Date of Contact. The min or minimum date is the oldest date (which has the smallest numeric value).
Max is used on date fields, such a Program Start Date, or Date of Contact. The max or maximum date is the most recent or newest date (which has the largest numeric value).
Sum is used to summarize numeric values found in demographics, assessments, point of service elements, and job information. Boolean fields have numeric values: Yes = 1 and No = 0.
Average is used to average numeric values found in demographics, assessments, point of service elements, and job information (like wage). Boolean fields have numeric values: Yes = 1 and No = 0.
8. Filter Nulls – This is the second, or the middle filter. It allows you to filter by responses that either are blank (is null) or are not blank (is not null).
The null filter cannot be used in conjunction with the regular filter.
If looking for a data set that only includes records with data, turn on the null filter and select Is Not Null.
The null filter is great for building quality assurance Queries that show where data is missing.
Include the staff name in Queries that show records where data elements are null. Then, provide the Query results to the staff so that they can go back and complete their data entry.
9. List All / Include Nulls – Check this box to list all details in and include fields with no value.
10. Each detail you selected in the previous step is separated by a header.
11. Continue – Click this button to move on.
This is the last step in the process. As such, additional focus areas cannot be added to this Query. However, modifications to filters and selections within the existing focus areas can be made after the Query is run and saved.
Step 5 – A message indicating the number of records in the Query results will appear. Click "Yes" to view the records.
Clicking “No” resets the Query Wizard back to Step 2.
Step 6 – Your results will show in a grid. You will have the following options to work with the data:
1. Start Over – Restarts the Query process from step
2. Download .txt file of Results – Downloads/saves/opens a Text version of the Query results.
3. Download .csv file of Results – Downloads/saves/opens an Excel version of the Query results.
4. Save Query – This makes the Query available to the User to run in the future instead of rebuilding it each time. Prompts can be set on data elements in the Query.
It is common for Users saving Queries to leave the scope on the default selection of Program. However, Site is typically the accurate scope selection. If the saved Query is going to be shared to Users across the Site, the scope of the Query will not respect confidentiality based on Program information assigned to staff accounts. Scope can always be modified at the top of the edit query page (accessed from the Manage Queries page).
If you check the "Insert as Foci" box, this saves your Query as a new focus area that will appear on step 3. You can then add this Query to any other Query you run as a focus area. Custom foci are helpful in finding out where specific data is missing. A common example of this would be if you wanted to find which Participants in ETO are over the age of 18 and have not taken an assessment. You would first need to save a Query as a foci that contains Participants who have had the value recorded. Then you can insert that foci and filter for Is Null.
Note, if you are trying to scope something to a Site-wide Program group, you can leave “Site access” as the scope and select the Program group under the Active Program or Program History focus area. The Programs Group scope only works with Enterprise-level Program groups. The Program Groups scope for a Query would be used if you are building a Query that should pull from multiple Programs across multiple Sites.
5. Reset Grid – If you have grouped by a column header, clicking this button will reset the results not to be grouped.
6. Requery – Takes you back to Step 4. You also have the ability to sequence the output by selecting the number you want the results to appear in from the drop-down menu in the "Seq" column.
Step 7 – You will have the following ways to save your data:
1. Save data as a Microsoft Word document
2. Save data as a Microsoft Excel document
3. Save data as a CSV document
4. Save data as a PDF document
5. Copy HTML data to clipboard (to copy and paste into an HTML editor)
6. Print data
7. Toggle Row Number (adds row numbers to the first column with the arrow)
Step 8 – Working with Query result:
1. Click the blue arrow to open up the fly-out options for that Participant. This is the same fly-out box you see when you quick search a Participant.
2. Filter the results by clicking the filter icon under the header.
3. Clicking on the header of any of the columns will sort the grid based on that column. Click the header again to sort in the inverse order.
4. Enter search terms into the box to filter the results.
5. The User may also click and drag a column to insert it into a different part of the grid.
Step 9 – You can group data by dragging a header from the grid to the grey box that says "drag a column header and drop it here to group by column."
Grouping is essentially counting records that are alike within the specified column.
You can expand or contract the groupings with the toggles (+/-).
NOTE: You can save your Queries to be reused.
Quick-Reference Guide
Navigation Bar > "Wizards" > "Query Wizard"
Select a subject.
Click "Continue".
Select one or more focus areas.
Click "Continue".
Select the details and filters.
Click "Continue".
Click "Yes" on the pop-up Save, work with, filter, search, and group the Query with the various options.