Creating a New Data Insights Query

You can create a new Data Insights Query from scratch by right-clicking on the Data Insights query folder and selecting Add query:

This will change the right-hand side of the manager to display the Query Creation Tools:

Creating a new query will always follow the same four steps:

1. Selecting a Query On option.

   This dictates what kind of data you are intending to look at, i.e viewing Award Details or Client Enrolments.

2. Selecting your Query fields.

   Essentially choosing what information you wish to see, such as Client Names, Enrolment Dates, Status, etc.

3. Setting up your Must match criteria.

   Select what fields or details should be used as Filtering Criteria – for example, Show me enrolments between these two dates.

4. Giving your query a Name and Saving it.

   In order to record your query and keep it for future uses, giving it a name and hitting the Save button will make it appear in the left hand list for when you next return to the Data Insight Manager.

Creating a Data Insight Query - Walkthrough

The first field to be selected for your new query is the Query on drop down: 

This will dictate what kind of dataset you are intending to query on, and it is recommended you choose the most efficient/suitable option for your needs.

For example, if you need to specifically see information relating to Client Enrolments, you should definitely select 'Client enrolment' from this list. Technically, you can see information for clients enrolments by simply selecting 'Client', but this is not an efficient method to view this information and may lead to numerous issues and oddities when running your query. 

Always try and follow the rule of thumb - Select the best 'Query on' option that relates to your intended output.

Selecting this item will then populate the Available fields area, listing all the VETtrak fields that are related to the item you selected. In this example, selecting Client Enrolment has then prepared a robust list of Enrolment specific information, as well as listing data options with a relationship to the enrolments, such as Client information, etc. 

These fields will then be used to populate what information you wish to see in your output (via the Query fields area) and which fields are to be used to filter your query (via the Match criteria area).

In this list there are a couple of different item types you choose from:

1.  Standard Field- This corresponds to basically any field you can identify within VETtrak.
   • For example, selecting the field Date of Birth from the client details table will make use of this specific field within the Client Wizard:
     
2.  Table - Refers to a table that is related to the initial Query on data set you have selected. Opening this item will show fields that are within that database table.
   • For example, as we have selected Client Enrolment we have the option to view/make use of the Awards table - as Awards are routinely related/linked to Enrolments in VETtrak:
     
     
     Double clicking (or pressing the Follow Relationship button) for an item like this within the Available fields list will navigate to a new list of fields related to that table:
     
     
     
3.  Table (Filterable) - Functions like the above Table item, however it also relates to a particular key table within VETtrak which allows you use it as a Filter option.
   • For example, you can make use of the Qualification Table (Filter) option to add a simple qualification filter to your query that behaves just like a qualification filter would work within VETtrak:
     
     
     To navigate this area, you can scroll through the list to identify the field/table you require - Or you can use the Search area \ at the very top to filter items within the current list and quickly display them:
     
     
     This is a very convenient way to find the Field/Table you require and either add it to the right area, or navigate to another list of table fields:
     
     
   • In this example, we will add some Client information to our Query fields area. This will allow me to view a list of client names/details as part of my output. 
   • To select fields from the Available Fields list and add them to the Query fields area, you can:
     1. Double click the particular field to immediately add it to Query fields
     2. Click+Drag the item into the Query fields panel
     3. Press the corresponding 'Add' button
        
        
   • Using these steps, prepare a list of Query fields that best reflects the output you wish to see.
   • In this example, I am preparing a standard Enrolment export report, displaying Student names and the Qualification they are enrolled in – including their enrolment dates:
     
     Note: You can change the order of items in this list by either clicking and dragging the fields, or by using the Up and Down buttons on the left-hand side.

The next step is to configure the 'Must Match' criteria area to filter our query's results.

To set up your filter criteria, you can select Fields and Table Filters from the Available Fields area and add them to the bottom right panel titled MUST match criteria. You can then select a particular Operator to define how this criteria is to be calculated:

For example, in the picture the first criteria we are making use of is:

• Qualification (Table) Filter, with the Operator of 'Equals' and the value of AUM08 - Automative Manufacturing.

This translates to 'Only show enrolments with the Qualification AUM08 - Automative Manufacturing selected'

Changing the operator can completely change how this filter works, such as using it to:

• Select Multiple qualifications (Is any of)
• Exclude a particular qualification (Is NOT any of)

The operators available for a field differ depending on the field type. 

For a full list of the operators available - and what fields they apply to - take a look at our Data Insights Operators article.

The next query filters I have chosen are both related to Enrolment dates:

These are designed to provide a Date Range for my Enrolment information output, by making use of:

• Enrolment State Date with the operator of Greater than or equal to and the date of 01/01/2018
• Enrolment Finish Date with the operator of Less than or equal to and the date of 01/01/2019

This translates to:

• Show me Enrolments that:
  • Start ON or AFTER the date of 01/01/2018
  • Finish ON or BEFORE the date of 01/01/2019

I can choose to change these dates at any time to move my date range, and retrieve different results. I can also change the qualification as well to view different output.

With these filters selected, I have essentially created a simple Student Enrolment List Report to show me Students enrolled into a Qualification within a Date Range.

Of course you can add additional filters to shape your data further, depending on your needs. Some basic examples of how I could expand this query could include details such as:

• Enrolment Status
• Referral Source
• Employer linked to Enrolment
• Is an Award Recorded

...and more than could be realistically listed here. Data Insights does factor in almost any data relationship that you have created via your data entry.

A good rule of thumb is to always start with limited criteria, and then build upon it to work towards your output goal. This makes troubleshooting your query easier.

Now is a good time to enter a Name for your query at the top of this panel, and press the Save button  to record the query to your database. The query will then appear on the left hand side in the Data insight query list.

At this point, you can now press the Run Query () to display the output of your query!

This will change the Query panel to display the Results tab, and show your query output:

The details you have retrieved appear in a list, and can be sorted by clicking/dragging headings into the gray bar at the top of the list. To return to the Query editor screen, press the Query tab at the top of this window.

The real strength of Data Insights at this point is that now you have this list of people, unlike a report that only displays the data - you can now choose what you wish to do with your output. This can range from simply exporting the information into a spreadsheet to be reviewed separately, contacting the listed people via SMS/Email, running additional reports or even amending particular information within the Students' enrolments.

For more information about what exports/actions can be taken with your output data - take a look at our Performing Actions On Your Data Insights Query Output article.

Duplicating a Data Insight Query

Alternatively you can save time by creating a copy of an existing query. You can do this by right-clicking on the Query in the directory list and selecting Duplicate query:

This will display a window confirming the Query you are duplicating, and allowing you to allocate a name for the new query:

Once this is saved, the query will be ready to edit as you see fit.

NEXT: Performing Actions On Your Data Insights Query Output

RELATED: VETtrak Training Webinar - Data Insights