Getting Started with Data Lab

Follow

Data Lab provides organizations with direct and flexible access to all their data. 

  • Data Lab power users can query all of their TrackTik data as it is generated, using Data Lab’s query language, TQL (TrackTik Query Language), to build customized reports and answer critical data requests.
  • Managers, administrators, and even executives across the organization can track personalized key performance indicators in highly customizable, shareable dashboards to monitor business performance & health, facilitating better-informed decision-making at all levels of the organization.  

With Data Lab, you can add value to your client’s experience and your service delivery by personalizing how your organization interacts with its data to drive better security practices and business processes.

In This Section:

Why Data Lab?  

 

Log In To Data Lab

 

Start Writing TQL Queries

 

Selected TQL Use Cases and Queries 

TQL Data Structure

TQL Cheat Sheet

Common TQL Keywords

Selected TQL Functions

TQL Tips and Tricks

Joins

TQL Playground vs. TQL Widget

 

Creating Widgets

Standard Fields

Attribute Filters

WhereQL

Top Level Filter Binding

The Toolbar Tab

Counter

Tree Map

Pie Chart

Line Chart

The Options Tab

Column Chart

The Options Tab

Gauge Chart

The Options Tab

Data Table

Rich Text Widget

TQL Query Widget

Youtube Video

Vimeo Video Widget

Horizontal Chart

List Widget

Heat Map Widget

Map Widget

Options Tab

The Summary Table Widget

List of Widget Operators

List of Widget Measures

List of Widget Measure Pills

 

Why Data Lab?


Here are three ways Data Lab can help you bring your business to the next level.


It’s Simple
Create widgets and dashboards right away using Data Lab’s simple graphical user interface. Write your own queries in minutes with TrackTik Query Language (TQL), a simplified query language based on SQL.  


It’s Powerful
Need a simple overview of clock-in and clock-out times during a period? Generate it on the fly with a TQL query.
Do you need to generate a report for each pay period to see the number of regular, overtime, and holiday hours your employees worked? Write the query and save it as a widget. Refer to the widget as often as you need to.


It’s Scalable     
Whether you are an advanced SQL user or have never written a query before, you will find Data Lab easy to use.
Start by accessing, tour, and report data. As your business grows, get insights on pay runs and customer financial metrics. Learn which of your services are most profitable—query patrol and dispatch data to assess operational efficiency.

 

Log In To Data Lab

  

◊ Go to https://datalab.tracktik.app/#/.

◊ Enter your portal URL.

◊ Enter your username and password.

Click Sign In, and you’re on your way!

    

Start Writing TQL Queries

 

TrackTik Query Language (TQL) is the foundation of all widgets and dashboards in the Data Lab. Once you have mastered the fundamentals of TQL, you will use the same syntax to filter, modify and tweak the data you generate in widgets and dashboards.

 

To begin writing TQL queries, click on the TQL Playground link. You can write queries in the playground or using a TQL Query widget. To find out more about writing TQL queries and using the TQL Query widget, please see the TQL Cheat Sheet.

 

datalabcreateetc.png

 

 

 

Selected TQL Use Cases and Queries

 

Below are some examples of use cases for which TQL can be helpful. Each use case also features a query to help you get started with the TQL syntax.

 

 

Use Case

TQL Query

Many of my clients would like to have more visibility on which employees are creating device licenses.

SELECT 
createdBy.firstName,
createdBy.lastName,
number  
FROM
app_licenses

I want to see a list of employees with a termination date.

SELECT 
firstName,
lastName,
employmentProfile.terminationDate
from
employees
where
employmentProfile.terminationDate <> ''

I need to know that I have the latest version of the app installed so that there are no security issues and have all the latest features. Therefore, I need a report showing licenses by app version.

SELECT 
applicationVersion,
deviceModel,
deviceOs,
deviceIMEI
FROM
mobile_devices

I want a report of all employees who do not have pay rates. This will tell me whether the employee capturing this information is doing their job.

SELECT 
payRateType,
employee.firstName,
employee.lastName,
hourlyRate,
yearlyPayRate
from
employment_profiles
where
hourlyRate   = ""
or yearlyPayRate   = ""

I need to know when guards are punching in and out late to manage my overtime and so that I know my guards are in the right place at the right time.

SELECT 
employee.firstName,
employee.lastName,
startDateTime,
endDateTime,
shiftId.startDateTime,
shiftId.endDateTime
From
work_sessions

To better understand financial and administrator metrics, I need to see the number of invoices generated per day.

SELECT 
count(id) as number,
date
from
invoices
group by
date

I need to see which bill items are generating the most revenue. This information would also be useful for marketing to determine which new products/services we would like to offer.

SELECT 
billItem.name,
sum(invoiceLine.lineSubTotal) as sum
from
billable_items
group by
billItem.name
order by
sum(invoiceLine.lineSubTotal) desc

To ensure data integrity, I want to access billing, payroll, and holiday information from positions to ensure we have entered the correct data.

SELECT 
billingSetting.type,
name,
id,
beginDate,
endDate,
 account.name,
 billingSetting.rate,
billingSetting.billItem,
payrollSetting.payPositionRate,
billingSetting.overtimeRule.name,
  billingSetting.holidayMultiplier,
billingSetting.holidayGroup.name,
payrollSetting.positionHourlyRate,
payrollSetting.holidayRate,
payrollSetting.holidayMultiplier
from
positions

I need a list of employee skills with information on the last user who updated the skills associated with the employee.

SELECT 
updatedBy.firstName,
updatedBy.lastName,
employee.firstName,
employee.lastName,
updatedOn
from
employee_skills

I need a  list of user logins with IPs for each login.

SELECT 
user.firstName,
user.lastName,
loggedInOn,
ip
from
user_login_logs

I need a list of my clients who have client portal access.

SELECT 
firstName,
lastName,
email,
username,
status,
client.name
from
client_users

I need to get approved hours, billable hours, payable hours from a shift.

SELECT 
shift.approvedHours,
shift.clockedHours,
shift.payableHours,
shift.billableHours
from
payroll_payrun_items
 

 

TQL Data Structure

 

You will find the TQL Data Structure to be similar to a SQL database. The endpoints you will use to query data are organized into folders that make it easy to navigate and query the information you need.

 

Data Models: Each of the endpoints, and fields in each endpoint, are grouped into a folder. Click on the arrows to expand the folders, endpoints, and fields.

 

Entities: Here, you will find the basic data entities in the TrackTik system. For example, clients, positions, regions, and zones.

 

  • Accounts: Accounts include all sites, zones, departments, and regions.
  • Addresses: Here, you can query addresses belonging to sites, zones, departments, regions, and employees.
  • App_Licenses: This endpoint contains information about app licenses associated with sites, zones, and departments.
  • Client_Users: Here, you will find information about users who can access the system using client portal access.
  • Clients: This endpoint contains information about the sites in your system.
  • Comments:  Query user and system-generated comments under this endpoint. Some examples are: when report statuses are changed, when ticket statuses are updated, when patrol statuses are updated, and when shifts are closed automatically when the user did not punch out.
  • Contacts: With this endpoint, you can query a list of your customer contacts.
  • Departments: Query a list of departments in your TrackTik portal.
  • Employee Roles: Here, you can query a list of roles to which employees are assigned.
  • Employees: Query a list of your employees.
  • Event_Activities:  Query a list of system events by account and/or by an employee. Some examples of events include a report is created, a tour is started, a user clocks in, clocks out, or the panic button is activated.
  • Files: Query-specific data on the files you have uploaded to the system. Note: You will not see the actual file. For example, if you have uploaded a picture, the picture will not be visible, but you will see the image URL.
  • Geo_Locations: Query a list of geocoded addresses in the system.
  • Mobile_Devices: Query information about mobile devices being used in the TrackTik system.
  • Notes: Query information on notes entered in the system, for example, notes by or on employees or notes associated with sites.
  • Positions: Find information about zones, site, and department positions throughout the TrackTik portal.
  • Regions: Get information about your TrackTik regions.
  • Role_Access_Policies: For each role in TrackTik, you can get information on the conditions under which your access is granted or blocked. In TrackTik, you can find this information under Roles and Permissions > IP Block Scenario.
  • Role_Permissions: Query the permissions associated with each role you have created in TrackTik
  • Roles: Get information about the roles in your TrackTik system.
  • User_Roles: Get information about which roles are associated with which users.
  • Users: Query a list of users and other information, such as when the user last logged in.
  • Vehicles: Get information about the vehicles in your TrackTik database.
  • Work_Sessions: Query information about each time a user works. Get their clock in and clock out times, how they accessed the system, associated shift information, where the work session took place, and more.
  • Zones: Query a list of zones and associated zone information.

Common: 

  • Back_Office_Settings: Get information about scheduling, billing, and payroll settings.
  • Holiday_Groups: Query information about the holiday groups you have set up.
  • Site_Locations: Query information on locations that you have added to individual sites.

Billing:  

  • Bill_Items: Query information about the bill items you have set up in TrackTik. These are the bill items you create under Settings > Bill Items in your TrackTik portal.
  • Billable_Items: Billable items are items that have appeared or are ready to appear on an invoice. You can query them under this endpoint.
  • Billing_Adhoc_Items: If you have created ad hoc billing items at the site, they will appear under this endpoint. Ad hoc items appear on invoices once and are not included in the regular billing cycle of the contract.
  • Client_Billing_Settings: Query information about the billing configurations you have set up under the billing tab of the site.
  • Contract_Service_Models: Get information on the service models that appear on your contracts.
  • Contract_Occurrences: Get information on individual instances of your contracts like the “contractId” and the “occurrenceDate.”
  • Contract_Occurrence_logs:
  • Contracts: Get information about the contracts you have set up at your sites.
  • Invoice_Line_Taxes: Get information on the taxes that appear on the lines of your invoices.
  • Invoice_Lines: Get specific information from the individual lines of your invoices.
  • Invoices: Query information about invoices you have generated.
  • Master_Service_Types: This endpoint returns information about the Job/Service Types in your system.
  • Payment_Methods: Query information about the different methods your clients can use to pay their invoices.
  • Position_Billing_Settings: Query the billing information associated with positions. Examples include the position, holiday billing rate, the billing overtime rule, the holiday rate, and the holiday rate multiplier.
  • Price_Tiers: Query a list of price tiers in your TrackTik portal.
  • Task_Price_Tiers: Get information on the price tiers associated with dispatch and patrol jobs.
  • Tax_Class_Items: Query a list of individual tax class components.
  • Tax_Classes: Query a list of tax classes. They determine which taxes and how much are applied to your invoices. Each tax class may consist of individual tax class items.

Scheduling: 

  • Break_Rule_Complaince_Logs: This endpoint shows whether or not employees took breaks when and how they were supposed to.
  • Break_Rule_Conditions: Get information about pre-defined breaks, such as break duration and break reminders.
  • Break_Rule_Exceptions: Query information about breaks that were not taken as planned.
  • Break_Rule_Relations: Get information about break rule relations, such as whether a break rule is associated with a position.
  • Break_Rules: Query a list of break rules.
  • Break_Sessions: See when breaks were started and ended, the work session with which the break was associated, and the method the employee used to begin and end the break session.
  • Holiday_Date_To_Classes: Use this endpoint to view a list of holidays in the system, see which dates they fall, and see the type of holidays they are.
  • Holiday_Dates: Use this endpoint to query a list of dates on which your holidays fall.
  • Holiday_Group_Date_Relations: See the dates on which your holidays fall and the groups to which those holidays are assigned.
  • Holiday_Recurrents: Get a list of the recurrent holidays in your system. Recurrent holidays fall on the same day each year.
  • Open_Shift_Requests: Get information on who requested which open shifts.
  • Overtime_Rule_Items: Get information about custom overtime rules in the portal.
  • Overtime_Rules: Get a list of all overtime rules in your portal.
  • Position _Skills: Get information on the skills that are associated with positions. These are skills that employees need to have to be able to work shifts at these positions.
  • Schedule_Off_Periods: View information about the employee’s scheduled time off periods, as entered in their HR profile.
  • Schedule_Time_Off_Request_Types: Query information on the types of time off requests employees can make.
  • Schedule_Time_Off_Requests:
  • Shift_Logs: Get information about which changes were made to scheduled shifts and who made those changes.
  • Shift_Notes: See shift notes' contents and shift note information for both system and user-generated shift notes.
  • Shift_Proposal_Queue_Items: See information associated with shifts logged to the proposal queue.
  • Shift_Templates: Get information on shifts in the schedule template.
  • Shifts: See specific information about scheduled shifts.

Checkpoint:

  • Checkpoint_Question_Answers: Query information on answers that guards have given to checkpoint questions.
  • Checkpoint_Scans: Get information about all checkpoint scans performed.
  • Checkpoint_Tour_Assignments: See which checkpoints are assigned to which tours.
  • Checkpoint_Tour_Schedule_Occurrences: See when tours are assigned to checkpoint tour schedules.
  • Checkpoint_Tour_Schedules: Get a list of checkpoint tour schedules.
  • Checkpoint_Tour_Session_Comments: See the comments entered by guards each time a tour session is performed.
  • Checkpoint_Tour_Sessions: See information on each tour session your guards complete.
  • Checkpoint_Tours: See a list of checkpoint tours.
  • Checkpoints: See a list of checkpoints in your TrackTik portal.

Dispatch: 

  • Cities: If you have saved cities in your portal for dispatch purposes, you can query them under this endpoint.
  • Dispatch_Custom_Locations: See a list of tasks and a list of custom locations to which they were dispatched, if any.
  • Dispatch_Slas: Get a list of dispatch SLAs and their parameters under this endpoint.
  • Dispatch_Tasks: Under this endpoint, you will find a list of tasks that you have dispatched.
  • Task_Site_Instructions: Instructions you have entered for a specific task at a specific site can be found under this endpoint.
  • Task_Site_Settings:  Under this endpoint, you will find the default site instructions you entered under the Mobile Dispatch tab at the site. You will also see whether the site is an alarm organization and whether it is a banned site and should not receive dispatch services.
  • Task_Types: See a list of the jobs/service types saved in the system and whether they can be dispatched, schedulable, priority, and more.
  • Vendors: If you have entered vendors into TrackTik, you can query them here.
  • Workflow_Instance_Logs: Every time a dispatch job is performed and that dispatch job is linked to a workflow, you will query this endpoint and see information about the workflow nodes.
  • Workflow_Instances: Get the current status of a workflow, as well as its configurations and settings.
  • Workflow_Statuses: Get information about workflow status nodes, including background color, text color, and alert thresholds.
  • Workflows: Query information about workflows.

Config:

  • Data_View_Categories: You can query information about the folders you create for widgets and dashboards in Data Lab under this endpoint.
  • Data_View_Modular_Hooks: Use this endpoint to view information about the modular hooks you have created to inject dashboards and widgets into user portals.
  • Data_Views: You can see information about the widgets and dashboards you have created in Data Lab under this endpoint.
  • Guid_Resources: Use this endpoint to get information about custom ids and the ids they are associated with.
  • System_Settings: See information about which modules are active.
  • Twilio_Accounts: Use this endpoint to query Twilio account information.
  • User_Notification_Settings: See information about user notification configurations in the system.
  • Whitelisted_Ips: Query information about IPs you have whitelisted in the system.

HR: 

  • Employee_Account_Assignments: View information on which employees have been assigned to which accounts.
  • Employee_Account_Bans: See which employees have been banned from which accounts.
  • Employee_Availabilities: View the employee availability information as saved in the availabilities tab in the employee file.
  • Employee_Classes: Use this endpoint to get a list of employee classes and associated information.
  • Employee_Skills: See which skills and attributes are assigned to which employees.
  • Employment_Profiles: See specific information about the employee contained in their employment profile.
  • Employment_Termination_Reasons: Query the termination reasons for terminated employees.
  • Skill_Categories: See a list of the categories to which employee skills are assigned.
  • Skills: Use this endpoint to get a list of skills and associated information, such as skill category, region, and field labels in the skill setup screen.

Patrols:

  • Geo_Fences: Get information about the geo-fence configurations in your portal.
  • Mobile_Runsheet_Occurrences: Query the instances where a runsheet is used (not performed by a guard) for a job within a zone.
  • Mobile_Runsheet_Sessions: Each time a runsheet is performed, a mobile runsheet session is created. Query the mobile runsheet sessions here.
  • Mobile_Runsheets: Query a list of runsheets and associated information.
  • Mobile_Schedule_Occurrence_Logs: Under this endpoint, you will find information about what happened within a mobile schedule occurrence, such as when the user's status was en route, on-site, and when the user completed the job.
  • Mobile_Schedule_Occurrence_Reassignments: Query information about mobile run sheet jobs that were re-assigned.
  • Mobile_Schedule_Occurrences: Query information each time a mobile schedule is used. Mobile schedules are set up under the mobile dispatch tab of the site and are used to schedule patrol hits. An occurrence is whether that mobile schedule is used when a job is assigned to it.
  • Mobile_Schedules: Query information about the mobile schedules that have been created at the site to schedule patrol hits.

Lone Worker:

  • Lone_Worker_Check_In_Logs: Use this endpoint to find information about when lone workers checked in.  See how and when they checked in to signal their presence.
  • Lone_Worker_Check_In_Tickets: Use this endpoint to get information on the system exception tickets that have been created with Lone Worker.
  • Lone_Worker_Check_Ins: Under this endpoint, you will find information on lone worker check-ins. Unlike lone_worker_check_in_logs, you will find information under this endpoint relating to the guard’s shift, as well as a relation to the check-in log information.
  • Lone_Worker_Phone_Calls: When a lone worker calls in, you will find information under this endpoint about the number they called and the number they were calling from.  You will also have access to user and account information.
  • Lone_Worker_Phones: Query information about phones that are authorized to make lone worker check-in calls. You will find these phone numbers under call-in timekeeping settings in the TrackTik portal.
  • Lone_Worker_Schedule_Check_Ins: View information about the scheduled lone worker check-ins for accounts and positions.
  • Lone_Worker_Time_Keeping_Settings: Query additional information under call-in timekeeping settings at a site. Query information such as whether lone worker check-ins are enabled, the number of minutes after which a check-in will be considered late, and whether check-ins should be restricted from specific phone numbers.

Message Board:

  • Message_Board_Post_Acknowledgements: See information on the employees who have acknowledged message board posts.
  • Message_Board_Post_Categories: See a list of message board post categories and associated information, including the name, description, and details of the category.
  • Message_Board_Post_Recipients: See a list of employees who have received specific message board posts.
  • Message_Board_Posts:  See information on message board posts and associated information, such as the message content, the status, and when the message expires.

Payroll: 

  • Payroll_Adhocs: From this endpoint, you can query a list of ad hoc items associated with payrolls.
  • Payroll_Codes: Query a list of payroll codes and associated information, such as the type, the multiplier, and whether the code is payable or billable.
  • Payroll_Deductions: Use this endpoint to get information on deductions that appeared on payruns.
  • Payroll_Payrun_Items: Get a list of the individual lines associated with invoices.
  • Payroll_Payruns: Get information on the payruns you have generated in TrackTik.
  • Payroll_Schedule_Occurrences: In this endpoint, you will find information on the usages of payroll schedules when preparing payruns.
  • Payroll_Schedules: Query a list of payroll schedules, including payrun period start date and time, overlapping strategy, and weighted overtime setting.
  • Payroll_Position_Settings: Get information about the payroll settings specific to positions. Information includes break settings, the position pay rate, the holiday group, pay codes, and the holiday multiplier.
  • Position_Premiums: Get information about pay premiums associated with the position. Information includes the premium name, the premium code, and the premium status. This information is set in the payroll section when setting up a position in the TrackTik portal.

Post Order:

  • Post_Order_Acknowledgements: See which employees had acknowledged post orders at sites, zones and departments, and see when the employee made those acknowledgments.
  • Post_Order_Subjects: Query a list of post-order subjects by account.
  • Post_Orders: Get details about post orders at sites, zones, and departments, the post order subject, the post order details, and who created the post order, and when.

Reporting: 

  • Report_Fields: This endpoint contains information about the individual fields on the reports that guards have filed. You can get the report template information, the report information, such as where and when the user filed the report and the contents and labels of each field.
  • Report_Flag_Categories: Under this endpoint, you will find information about the parent incident categories under settings > incident categories > parent categories.
  • Report_Flag_Group_Assignments: Get information about the groups to which incident categories are assigned. You can find incident category groups in the TrackTik portal under settings > incident categories > groups/industries.
  • Report_Flag_Groups: Query information on the incident category groups referenced in the previous point.
  • Report_Flags: Query a list of incident categories and associated information, such as severity level, parent category, and whether the incident category will be shown in the default group.
  • Report_Template_Fields: Get details on the fields contained in report templates. Unlike the report_fields endpoint, this endpoint contains information about fields that have not yet been filled out.
  • Report_Templates: Query a list of report templates and associated information, such as when the template was created and by whom, the template details, and the template name.
  • Reports: Under this endpoint, you will find information about reports that have been filed. Find information such as the account and position to which the report is associated, information on who submitted the report, as well as approval information.

Asset Management:

  • Schedulers: If you use asset management, you can use this endpoint to find information about service schedules for existing assets.

Logs:

  • System_Usage_Logs: Here, you will find information on run sheet route optimization and sending SMS messages. See when these activities were performed and who performed them.
  • User_Login_Logs: Use this endpoint to get information about when users log into the system, their IP, their method of accessing the system, and the browser they were using.

Functions

Under the data models, you will find a list of available TQL functions.

 

  

 

Selected TQL Functions

In TQL, you can use functions to help you do things, such as performing math operations, applying conditions when displaying data in fields, filtering data, and much more.  Use this section to help you decide which functions are appropriate in which situations.

Function

Example Query

AVG: Displays an average when a column is numeric.

COUNT: Returns the number of rows that meet the conditions you enter.

SUM:  Calculates the sum of a column or values.

CONCAT: This function combines two or more strings.

MAX: This function returns the largest value of the selected data.

MIN: ‘Min returns the smallest value of the selected data.’

IF: Adds a condition that returns one value if the condition is true and another value if the condition is false. The first value in quotations is returned if true; the second if false.

YEAR: Extract the year part of a date or date/time field.

DATE: Extract the date part of a date/time field.

MONTH: Extract the month from a date or date/time field.

MONTHNAME: Get the name of the month from a date or date/time field.

QUARTER: Extract the quarter from a date or date/time field.

MINUTE: Extract the minute part of a date/time field.

RIGHT_STR: Extract a specified number of characters starting from the right of the string.

LEFT_STR: Extract a specified number of characters starting from the left of the string.

NOW: Displays the current date and time.

REVERSE: Reverses the contents of a specified column.

ROUND: Rounds the specified column to the indicated number of decimal places.

SECOND: Extract the seconds from a date/time field.

WEEK: Extract the number of the week of the year from a date or date/time field.

HOUR: Extract the hours from a date/time field.

REPLACE: Replace a selected string with a different string. The first string is what to replace. The second string is what will replace the first.

AddMinutes: Add the specified number of minutes to a date/time.

AddHours: Add hours to a date/time.

MinutesBetween: Returns the difference between two date/time fields.

HoursBetween: Returns the difference in hours between two date/time fields in hours.

TimeZonify: Converts a timestamp from UTC to a time zone.

FROM_UNIXTIME: Converts a UNIX timestamp to a human-readable timestamp in UTC.

CONVERT_TZ: This function converts a date/time field from one time zone to another.

LOCATE: This function returns the position of the first occurrence of a substring within a string. If the substring is not found in the string, 0 is returned.

GROUP_CONCAT: This function returns a string with concatenated values from a group. This example would return a concatenated string of user first names in a single column.

NOW: Now is a function that you can add to the select statement that will output the date stamp of the current moment when you execute the query. This function takes no arguments.

CHAR_LENGTH: This function returns the number of characters in a string. In this example, the query will return the number of characters.

 

Relative Dates

Relative dates help save time by eliminating the need to write out dates as a string, like ‘2019-06-01’. Use relative dates when adding where statements to TQL queries.

Relative Date

Meaning

YESTERDAY_START

The start of the day before. It will appear as yesterday’s date if selected in a query.

YESTERDAY_END

The end of the day before. It will appear as today’s date if selected in a query.

TODAY_START

The beginning of today. It will appear as today’s date in a query.

TODAY_END

The end of today. It will appear as tomorrow’s date in a query.

TOMORROW_START

The beginning of tomorrow. It will appear as tomorrow’s date if selected in a query.

TOMORROW_END

The end of tomorrow. It will appear as the date of the day after tomorrow if selected in a query.

LAST_WEEK_START

The first day of the previous week. It will appear as the date of the first Sunday of the week.

LAST_WEEK_END

The last day of the previous week. It will appear in the query as the last Sunday of the previous week.

LAST_MONTH_END

The end of the previous month. It will appear in the query as the first of the current month.

LAST_QUARTER_START

The first day of the previous quarter. It will appear as the date of the first day of the previous quarter.

LAST_QUARTER_END

The last day of the previous quarter. It will appear as the first day of the current quarter in the query.

LAST_YEAR_START

The first day of the previous year. It will appear in the query as January 1 of the previous year.

LAST_YEAR_END

The last day of the previous year. It will appear in the query as the first day of the current year.

NEXT_WEEK_START

The first day of next week. It Will show in the query as the date of the first Sunday of the week.

NEXT_WEEK_END

The last day of the following week. Appears in the query as the date of the last Sunday of the following week.

NEXT_MONTH_START

The first day of the following month. It will appear in the query as the date of the first day of the following month.

NEXT_MONTH_END

The last day of the following month. It will appear in the query as the first of the month following next month.

NEXT_QUARTER_START

The first day of the following quarter. It will appear in the query as the date of the first day of the next quarter.

NEXT_QUARTER_END

The last day of the next quarter. It will appear in the query as the first day of the quarter following the next quarter.

NEXT_YEAR_START

The first day of the following year. It will appear in the query as the January 1 of the following year.

NEXT_YEAR_END

The last day of the following year. It will appear in the query as the first day of the year after next.

THIS_WEEK_START

The first day of the current week. It will appear in the query as the date of the first Sunday of the week.

THIS_WEEK_END

The last day of the current week. It will appear as the date of the last Sunday of the week.

THIS_MONTH_START

The first day of the current month. It will appear in the query as the first day of the current month.

THIS_MONTH_END

The last day of the current month. It will appear on the first day of the following month.

THIS_QUARTER_START

The first day of the current quarter. It will appear as the date of the first day of the quarter.

THIS_QUARTER_END

The last day of the current quarter. In the query, the date will appear as the first day of the following quarter.

THIS_YEAR_START

The first day of the current year. The date will appear as January 1 of the current year.

THIS_YEAR_END

The last date of the current year.

 

 

TQL Cheat Sheet

Use TrackTik Query Language (TQL) to generate custom reports that contain the data you need when you need it. TQL is a simplified version of SQL that will allow you to get up and running in minutes. Use this section as a reference when writing TQL queries.

 

Remember that when you write TQL queries, you are creating API calls to TrackTik API endpoints. For more information on the TrackTik API, please visit ://{yourportalurl}/rest/v1, where {yourportalurl} is the URL you use to access TrackTik.

 

You can find the data structure of the TrackTik API on the left-hand side of the TQL Playground.

 

 

Common TQL Keywords

Use these keywords when building TQL queries.

Keyword

Explanation

Example

Select

Use ‘select’ to choose the fields from the endpoint you are querying.

From

This keyword precedes the name of the endpoint you are querying.

Where

Use ‘where’ to filter the records returned by the query.

And

Use 'and to combine conditions in where statements. When using and, all conditions must be true for the records to be returned.

Or

Like the ‘and’ keyword, the ‘or’ keyword connects multiple conditions in a ‘where’ statement. Only one of the conditions needs to be true for records to be returned.

Group By

Use the ‘group by’ keyword to group data that have the same values into rows. For example, when you would like to find the number of invoices by customer, you can group invoices by customer name. Therefore, it is often practical to use ‘group by’ with functions such AVG (average), SUM (sum), or COUNT (count).

Order By

User ‘order by’ to put the query results in ascending or descending order by column. By default, records will be sorted in ascending order.

Having

Use the ‘having’ if ‘where’ with an aggregate function would not be possible.

As

Allows you to add an alias to a given column. This can make the column easier to read and more intuitive to understand.

 

TQL Tips and Tricks

Refer to these TQL tips and tricks to help you work more efficiently with Data Lab

 

Joins

There is no need for JOINS in TQL. Instead, use the relations indicated by the down-arrows in each of the endpoints. In this example, you can use the employmentProfile relation under employees to get the employee’s hourly rate and employment date.

Relation

Query

 

TQL Playground vs. TQL Widget

There are several advantages to running your query in the TQL Query Widget rather than the TQL Playground.

 

  • You can use filters rather than adding ‘where’ statements in your queries.
  • You can view archived/historical items.
  • You can ignore syntax validation.

Follow these steps to access the TQL Query Widget and run your query.

  • From anywhere in Data Lab, click on the plus sign in the upper left and select the New Widget option.

datalabcreateetc.png

 

  • Select the TQL Query option.

  • Write the query in the box.

 

Widget Title: The name of the TQL Widget. Choose a meaningful name to make it easy to find the widget when searching for it.

Query Box: Enter the query in this box.

Green Check Mark: This checkmark indicates that the TQL syntax is valid.

Apply Button: When you write a new query, this button will be active. Click the button to run the query.

Ignore Validation: Check this box to turn off the validation of TQL syntax. This feature can be helpful as new fields and endpoints are released and that, soon after release, are not yet recognized as valid TQL syntax.

Include Archived/Inactive: Check this box when querying data no longer active, for example, canceled shifts, or terminated sites, or employees. The example above queries canceled shifts, so this option is checked in addition to the ‘where’ statement at the end of the query.

 

  • WORKING WITH RELATIONS: When working with relations, it is best practice to work from more specific to less specific. For example, if you need a subtotal of each line on an invoice, query the invoice_lines endpoint and work towards invoices.

Requirement Example

Query

“I need a report showing the invoice date, the service date, the quantity or number of hours being billed, the price per hour (unit price), the total amount of tax per line, and the subtotal per line.”

 

  • TQL USES API ENDPOINTS: Remember that TQL queries use TrackTik API endpoints. Therefore, refer to the API documentation for more information and requirements when working with TQL. For more information on the TrackTik API, please visit ://{yourportalurl}/rest/v1, where {yourportalurl} is the URL you use to access TrackTik.

 

Creating Widgets

Widgets are components that you can use as standalone entities or incorporate into dashboards. They are based on TQL and can be modified using TQL conditional statements. Choose the best widget type for the data that you would like to display.

 

Select the widget you would like to create.

 

 

Once you select a widget, you will be able to configure mandatory and/or optional fields to configure the widget's output. Many of the fields pictured below are common to all widgets.

Standard Fields

 

 

 

  1. Widget Title: Give the widget a meaningful, descriptive name.
  2. Data Source: This the endpoint from which the widget will query data.
  3. Group By: Select a field by which to group the data output by the widget. For example, you may select invoices as the data source and then group them by client. This will show you the number of invoices for each client.
  4. Label: Enter the name of the field that you are grouping by. This field will populate by default once you have selected a field to group by.
  5. Measure: Measures are properties on which calculations can be made. Examples include COUNT, SUM, AVG, MIN AND MAX, DISTINCT, and EXPRESSION.
  6. Label: Enter a descriptive label for the measure.
  7. Attribute Filters: These are described in greater detail below. These filters allow you to select an attribute of the data source and filter by that attribute. For example, you might select clients as the data source and select name as an attribute filter. You will then be able to filter clients by the client name.
  8. WhereQL: Just as you can add a ‘where’ statement to a TQL query, you can also add a where statement to your widget filters the output. For more information on where statements, please see the TQL section of this manual.
  9. Top Filters Binding: Here, you can determine exactly which fields the region, date, and account filters will be bound to. For example, the filters that appear in the following points may filter on the invoice date, invoice due date, invoice creation date, service start date, etc. In other words, this option allows you to determine exactly which date field the date search will be bound to. For the client filter, you can choose whether it will filter on the client name, the client parent account, the client zones, etc. This will be discussed in greater detail below.
  10. Click on the curly braces to view, download or copy the widget JSON.
  11. Date Range: Filter by the date that you bound to this filter in step 9.
  12. Region Filter: Filter by the region information you bound in step 9.
  13. Account: Filter by the account criteria you selected in step 9.

 

Note: In some widgets, such as the Summary Table, you will find a field to enter a TQL having a statement. This is the HavingQL field:

 

Use the measures and dimensions you set to write a having statement. Having statements are used with aggregate functions, such as COUNT, AVG, SUM, etc. In the example above, all records with a count of greater than 30 would be returned.

 

Attribute Filters

When you create a widget, you are querying data from a specific source. The source you query has a set of properties. The attribute filters allow you to filter your widget output by any of the selected properties. Below is an example of attribute filters for the invoices endpoint. Select any of these properties to filter the widget output.

 

 

Once you have selected a property, you will click on one or more attributes to filter on. In this example, invoices are the data source, and the contract is the property. A list of attributes appears. You can check one or more to filter by those attributes. The list of properties and attributes depends on the data source you select.

 

 

WhereQL

You can write a WhereQL statement to filter the output of your widget. Omit the ‘where’ keyword from the statement. For example, you could write: where client.name = ‘Ace’. For more information on TQL, please refer to this section.

 

Top Level Filter Binding

In this section, you can define the filters that will appear at the top of your widget. For example, if your data source is invoices, you can bind the data filter to the invoice due date, the invoice creation date, the date on which the invoice was updated, etc. You can also disable top-level filters completely by selecting that option from the drop-down list.

 

 

The Toolbar Tab

In the toolbar tab, you can choose to show or hide the toolbar for a widget. Once the toolbar is shown, you can then select the options that will appear in the toolbar.

 

 

  1. Toolbar: Click this option to use the configurations in the toolbar tab.
  2. Display Toolbar: Use this slider to activate or deactivate the toolbar option. When this option is active, space appears at the top of the widget, displaying the other options when they are active.

  1. Display Counts: Selecting this option will show a count of the number of items in the toolbar.

 

 

 

  1. Attribute Filters: Like the attribute filters described above, select attributes on which you can filter the widget output. These filters will become available in the Toolbar space described in point 2.

Counter

The counter widget displays a count of the data you select. It is perfect if you want to get a simple count of employees, sites, invoices, etc.

 

 

 

 

In addition to the options discussed above,  you can also select the Common Filters option. Depending on the data source you select, you will be able to check one or more boxes to apply filters commonly used with that data source. In the above example, since accounts are the data source, the user can check the Has Employee Assigned option to view a count of accounts with at least one employee assigned.

Go to the OPTIONS tab to add a background color and an icon to your Counter widget. In the same tab, you can also add conditional formatting:

  • Operator
    Define a condition:
    • = (equals)
    • > (greater than)
    • >= (greater than or equal to)
    • < (less than)
    • <= (less than or equal to)
  • Value
    Enter any INT or FLOAT data type.
  • Color
    Select a conditional format color. 

countercolorformatting.PNG

 

TreeMap

A treemap is a hierarchical representation of the data you select. The map consists of rectangles and nested rectangles that are color-coded depending on the data displayed. A treemap is a good way to get a quick visual representation of data.

 

 

In addition to the configurations described above, you can select a color palette for the widget's output under the options tab. Select a palette from the dropdown menu.

 

 

Pie Chart

Use a pie chart to represent data in proportion to other data. The proportions are the slices of the pie. A pie chart would be perfect to view invoices by customer, sites by city or state, or employees by job title, for example.

 

 

 

Line Chart

A line chart is ideal for tracking changes in data over time. For example, you can view the number of employees included in pay runs over time, the number of invoices generated by month, or the number of employees terminated by month.

 

 

The Options Tab

For the line chart, the following configurations are available under the options tab.

 

  1. Options: Click here to configure the options for this widget.
  2. Color: Select from the drop-down. This will be the color of the line on the line chart.
  3. Target Value: When you enter a target value, a dotted line will appear on the chart indicating this value.
  4. Target Line Color: Select a color from the drop-down. This will be the color of the target line described above.

 

Column Chart

Use the column chart widget to show a comparison between values. Some examples of use cases include the number of billable items by bill item name, the number of scheduled patrols by site, or the number of scheduled shifts by an employee.

 

The Column Chart widget has an additional standard filter option:  Default Sort Order. You can order your data records in either ascending (ASC) or descending order (DESC), on either your data measure (COUNT, SUM, etc.) or your data dimension (Group By dimension).

The Options Tab

 

For the column chart, the following configurations are available under the options tab.

 

  1. Options: Click here to configure the options for this widget.
  2. Color: Select from the drop-down. This will be the color of the line on the line chart.
  3. Target Value: When you enter a target value, a dotted line will appear on the chart indicating this value.
  4. Target Line Color: Select a color from the drop-down. This will be the color of the target line described above.

 

Gauge Chart

Use the gauge chart to display a target value and compare how close current data is from the target.

 

 

The Options Tab

 

For the gauge widget, the following options are available.

 

 

  1. The Options Tab: Click this tab to configure the following options.
  2. Active Color: On the gauge, this will be the color of the actual entities from the data source you are using.
  3. Target Value: Enter a value used as the target number (set in the “DATA SOURCE” tab).

 

Data Table

Use the table widget to arrange your data in rows and columns.

 

 

Pivot Table

 

Use the pivot table widget to group and summarize data. You can include sums, averages, and other statistics that help you better understand your operations.

 

You can group by as many different criteria as you need to with a pivot table.

 

 

By default, the data will appear like this in the pivot table.

 

You can click on any of the arrows to expand the data or click on “SHOW FIELD LIST” to rearrange the rows and columns.

 

You can then select or deselect the columns you need, apply filters, and decide which data should appear in columns and rows.

 

 

Markdown Text Widget

Use this widget to add markdown text to a widget and use it in a dashboard.

 

Widget Title: Add a meaningful name for your widget.

Description: Add a more detailed description for your widget.

 

Use the options to format the markdown text.

 

  1. Make the text bold.
  2. Italicize the text.
  3. Make the text a heading.
  4. Set quotes
  5. Make generic list
  6. Make numbered list
  7. Add a link.
  8. Add an image
  9. Toggle preview view
  10. Toggle side-by-side view
  11. Toggle fullscreen

 

 

TQL Query Widget

For more information on the TQL Query widget, please see the section on TQL Tips and Tricks.

 

Youtube Video

You can link to a video on Youtube and display it on a dashboard with the Youtube Video widget.

 

 

Widget Title: Enter a meaningful name for your widget.

The URL of the video: Enter the URL of the Youtube video.

 

 

Vimeo Video Widget

The Vimeo Video Widget allows you to add a video to your dashboard like the Youtube Video Widget.

 

 

Widget Title: Enter a meaningful name for your widget.

The URL of the video: Enter the URL of the video.

 

Horizontal Chart

Like the Column Chart, the Horizontal Chart allows you to compare data or values.

 

 

The Horizontal Chart widget has an additional standard filter option:  Default Sort Order. You can order your data records in either ascending (ASC) or descending order (DESC), on either your data measure (COUNT, SUM, etc.) or your data dimension (Group By dimension).

For more information on how to configure the options in this and other widgets, please refer to the following sections:

 

Standard Fields

Attribute Filters

Widget Measures

Widget Operators

 

 

 

List Widget

The list widget provides a list of items from the data source you select. This example shows a list of invoices.

 

 

In addition to the standard configuration option found in all the widgets, defining the sort order is also an option.

 

Select the sort attribute as the item on which to sort, and then click one of the buttons to choose whether the sort order will be ascending or descending.

 

 

For more information on how to configure the options in this and other widgets, please refer to the following sections:

 

 

Standard Fields

Attribute Filters

Widget Measures

Widget Operators

 

Heat Map Widget

With the heat map widget, you can create data visualizations with at least two dimensions using colors that you select.

 

 

For more information on how to configure the options in this and other widgets, please refer to the following sections:

 

 

Standard Fields

Attribute Filters

Widget Measures

Widget Operators

 

Map Widget

You can plot geo-coded entities with the map widget, such as sites and reports on a map.

 

 

  1. Use this row of icons to zoom in, zoom out, refresh the map, recenter the map, toggle satellite view, display entities on the map in clusters.
  2. Select the latitude attribute.
  3. Select the longitude attribute.

 

Options Tab

 

  1. Set the default zoom that will apply to the map. Enter a number between 1 and 15.
  2. Set the default latitude of the map.
  3. Select the default longitude of the map.
  4. Check the box to display the current longitude and latitude.
  5. When the previous option is checked, the current longitude and latitude will display as shown.

 

For more information on how to configure the options in this and other widgets, please refer to the following sections:

 

Standard Fields

Attribute Filters

Widget Measures

Widget Operators

 

The Summary Table Widget

 

The summary table is a widget that you can use to create statistical data in a table form. The example below is a summary table of invoices grouped by client region.

For more information on how to configure the options in this and other widgets, please refer to the following sections:

 

Standard Fields

Attribute Filters

Widget Measures

Widget Operators

 

List of Widget Operators

The following is a list of operators available when creating or modifying widgets using attribute filters. Use these operators when you need to filter the output of widgets you create.

 

BETWEEN: Use this when the selected output should be between the values you select.

CONTAINS: The output should contain the value you enter into the field.

ENDSWITH: Use this operator when the output should end with the value you enter.

EQUAL: Use this operator when the output of the field you select should equal the value you enter.

GT: Use this operator when the widget output should be greater than the value you select.

GTE: Use the greater than or equal to operator when the widget's output for the field you select should be greater than or equal to a value you enter.

IN: Use the in operator when the value you enter should be in the field you select.

ISNOTNULL: Use this operator to specify that the contents of the field you select should not be null.

ISNULL: Use this operator when the contents of the field you select should be null.

LT: This operator indicates that the contents of the selected field should be less than the value you enter.

LTE: Use this operator if you want the field's contents you select to be less than or equal to the value you enter.

NOT: Use this operator when the contents of the field you select should not be the value you enter.

STARTSWITH: Use this operator when you would like the value of the field you select to start with the value you enter.

 

List of Widget Measures

Measures are ways to perform calculations on a set of data... Many of them are aggregate functions that you would use when writing TQL queries, such as SUM, AVG, or MIN.

 

 

COUNT: Gets the number of occurrences of the field you select.

AVG: Gets the average of the field you select.

SUM: Total of the field you select.

MIN: Returns the smallest value of the field you select.

MAX: Returns the largest value of the field you select.

DISTINCT: Use this measure to select unique values.

EXPRESSION:  Use an expression to combine values, operators, and TQL functions that evaluate a value.

 

List of Widget Measure Pills

Depending on the widget measure you select, several widget measure pills will appear above the measured field. The pill you select will help you further refine and format the output.

 

 

N/A: Apply no filters or suffixes to your data.

Round(0): Round data to the nearest whole number.

Round(2): Round data to 2 decimal places.

Floor: Returns the largest number equal to or less than the number in the field.

Ceiling: Returns the smallest number that is greater than or equal to the number in the field.

Suffix: Select a suffix for the data.

 

        

Comments

0 comments
Article is closed for comments.