Report data sources

A data source is a set of records from your account that you can pull into a report sheet or build a chart from. You pick one from Import from data source in a report (see Build a custom report) or from New chart in the chart library.

Every source has a column picker: some columns are ticked to start with, and the rest are there to add. Names follow your organization's Terminology settings, so a source listed here as Dockets or Job Costing Bid Items may carry your own words for dockets or bids. United States and Canadian accounts see Field Tickets for dockets unless they have set their own name.

A source reads only the records you are allowed to see. The sources below need View finance reports, like the rest of Reports, unless the table says otherwise.

Report on dates ahead of today

Most sources record what has already happened, so their date filter stops at today. These sources also hold dates still to come, so their date filter lets you pick days after today:

On these sources the quick ranges are This Month, This Quarter, Year to Date and Last Year, then Next 30 Days, Next 90 Days, Next Month, Next Quarter, Rest of Year and Next Year. This Month and This Quarter run to the last day of the month or quarter, so a This Month allocations sheet includes the shifts still to come this month. On every other source they end today. Rest of Year runs from today to 31 December.

Quick ranges count from today in your organization's time zone. Left empty, Order Demand reads today and the next 30 days, Ops Allocations / Shifts loads the last 30 days, and the other sources above read every date.

Financial

On Material Transactions, Amount is the charge to the client: the Schedule of Rates amount once rates have been calculated, otherwise quantity × unit price.

Labor & Payroll

Worker Competencies, Worker Roles, Role Requirements, Inductions and Worker Compliance Audit also need View payroll, which Owners and Admins have.

Pay Rates leaves out workers linked to a supplier, because their supplier invoices you for their time (see Pay a worker through a supplier). It also leaves out timesheets of workers whose employment type is excluded from payroll, unless the timesheet already had pay worked out (see Leave an employment type out of payroll). Their timesheets are still on Timesheets.

On Pay Rates, Activity Start Time and Activity End Time bound each pay item's own part of the shift, so a shift split by a break gives a row per part. Timesheet Start Time and Timesheet End Time are the shift's own start and end, the same on every row of that timesheet: use these for payroll exports.

AI Confidence shows High, Medium or Low for each pay item. On a sheet that combines matching rows, pay items with different ratings still combine into one row, which shows the first of their ratings.

Quantity shows 0 on a loading, weekend penalty or RDO deduction line, because another line already carries those hours. Line Quantity shows each line's own quantity, those lines included; on an RDO deduction line that is the hours deducted. On every other line the two are the same.

For a payroll export that pays a loading or penalty as its own pay item at hours × rate, such as a MYOB import, use Line Quantity as each pay item's quantity. Don't total hours from it, because it counts loading, penalty and RDO deduction hours a second time. For total hours, sum Quantity over worked lines, using Line Type to leave out allowances: an allowance with no quantity of its own shows the shift length.

Line Quantity is not ticked by default. To add it, select Edit Filters on the sheet's bar, tick Line Quantity under Columns to Include, and select Import to Current Sheet. It lands as the last column, so the columns already on the sheet stay where they are: if another sheet copies the quantity by cell reference, point that reference at the new column.

Worker Roles and Role Requirements

Use these two sources together to see who can take a job: Worker Roles lists the roles each worker holds, and Role Requirements lists the competencies each role needs (see Set role requirements).

• Worker Roles has a row for each role a worker holds, so a worker with three roles has three rows. A worker with no role has none; use Employees to list every worker.
• Role Requirements has a row for each competency a requirement accepts. A requirement that either of two competencies meets has two rows, one per competency. If every competency a requirement accepts has been archived, the requirement keeps one row with Accepted Competency blank.
• Mandatory says Yes for a mandatory requirement and No for a recommended one. Supervisor Role says Yes for a role set as Supervisor.
• Deleted workers and roles, and archived competencies, are left out.
• Role Requirements also needs View cost codes, which Owners, Admins and Managers have. Without it you see "The Role Requirements report needs permission to view cost codes as well as worker credentials. Ask an admin for access."

Inductions

Inductions reports on the inductions in your competency catalog, with a row for each worker and induction. It is the Assignar Pay counterpart of Ops Inductions.

• Who is listed. A worker is listed for an induction if they hold a record of it, or if they have shifts in the Date Range on its project. For a client's induction, shifts on any of that client's projects count. A worker whose only record was revoked or archived is listed only if they have shifts.
• Project and client come from the induction, not from the worker's record. A project's induction shows its project and that project's client. A client's induction shows only the client.
• Status is worked out as on a project's Inductions tab. Inductions are checked on the first day of the Date Range, or today if that day has passed:
  • Current: the worker holds it.
  • Expiring: the worker holds it, but it expires on or before the last day of the Date Range.
  • Expired: the worker's record has expired.
  • Pending Verification: the worker has no current or expired record, but one is waiting for someone to verify it. The row shows that record.
  • Missing: the worker has no record that counts, for example none was recorded, or it was rejected or revoked.
• Date Range runs from today to 30 days ahead if you leave it empty, as on the project's tab; the field shows those dates with "Defaults to today and the next 30 days — pick a range to override." It can pick days after today, and offers only Next 30 Days and Next 90 Days. It can be up to a year long. To list inductions expiring in the next 60 days, set the range from today to 60 days ahead and set Status to Expiring.
• Project shows that project's inductions and its client's, and counts shifts on that project only, so each worker's status matches the project's Inductions tab. Client shows the client's inductions and the inductions of its projects.
• Issued Date, Expiry Date, Issuer and Attachments come from the record that decided the status. Recorded Via says how that record was made: Induction Form (the worker passed the induction form), Manual, or Assignar Import (from Assignar Operations). Status, Tied To, Recorded Via and Verification State show words in your language, such as Missing or Not Required, not stored codes. Days Until Expiry counts from today; a negative number means it has expired.
• Shifts in Range, not ticked by default, counts the worker's shifts in the Date Range that need the induction.
• Inductions also needs View projects. Without it you see "The Inductions report needs permission to view projects as well as worker credentials."
• When you import or refresh a sheet yourself, you see only the workers whose credentials you are allowed to see. A refresh schedule runs without you signed in, so a scheduled refresh lists every worker, like the other sources. It leaves Attachments blank for inductions marked sensitive.

Location and Business Unit columns

Once your account has started coding, Timesheets and Pay Rates offer six more columns: Working Location, Home Location, Business Unit, Entity Code, GL Code and BU Source. They also get three more filters: Working location, Home location and Business Unit. Before coding starts, none of these appear.

• The values are the ones recorded when the timesheet was created, so moving a project or worker later does not change past rows. A timesheet created before coding started shows —.
• Every pay item on Pay Rates shows its timesheet's values. Hire billing slices are timesheets too, so they appear on Timesheets.
• A location filter includes everything under the location you choose. Timesheets created before coding started never match a location or Business Unit filter.
• The columns are not ticked by default and land after the existing ones, so your saved sheets don't change. On a sheet that combines matching rows, a ticked column keeps rows with different values apart, for example one row per Business Unit. Totals are split between those rows, never changed.

On Timesheets and Pay Rates, Business Unit shows the unit's code only, for example 012, the same code your payroll export carries. A sheet that already has Business Unit ticked shows the code instead of the code and name the next time it refreshes, so update any lookup, pivot or chart filter that matches on the old text. The Business Unit filter still lists each unit as its code and name.

Location and Business Unit columns on documents

Once your account has started coding, Dockets, Invoice Line Items, Supplier Invoice Lines and PO Line Items offer five more columns, so your accounting system can post each record or line with the same coding as payroll: Working Location, Business Unit, Entity Code, GL Code and BU Source. They also get two more filters: Working location and Business Unit. There is no Home Location here, because documents don't record one. Before coding starts, none of these appear.

• The values are the coding saved for the record or line, so a later change to a project or location does not change past rows. A record or line created before coding started shows — in every one of these columns, and never matches a location or Business Unit filter.
• Business Unit and Entity Code show the code only, so a code such as 012 keeps its leading zero. BU Source says where the Business Unit came from. On a line whose Business Unit was chosen by hand, it shows Set manually.
• A location filter includes everything under the location you choose.
• Only people who work in your organization see these values. If someone outside your organization, such as a customer, can run one of these reports, the columns show — for them.
• The columns are not ticked by default and land after the existing ones, so your saved sheets and their totals don't change.

Field Operations

The four workpack sources also need View workpacks. Asset Meter Readings also needs View assets, which Owners, Admins and Managers have. Order Demand needs View orders instead of View finance reports. Project Milestones needs View projects instead. When you import or refresh a sheet yourself, it lists only the projects you can open; a refresh schedule runs without you signed in, so a scheduled refresh lists every project's milestones, like the other sources.

On Project Milestones, Status filters on Open, On Track, Due Soon, Warning, Critical, Achieved or Cancelled. Days late and percentages are worked out on your company's today, as on the project's Progress tab. Columns not ticked by default include the planned start, original planned date, forecast basis, start, target and actual quantity, progress source, Run Rate (per Day) and Run-Rate Forecast Date.

Order Demand

Order Demand shows the crew each order asks for, day by day, so you don't have to work it out from budgeted hours. Each line under Roles & shifts on an order (see Add roles and shifts) gives a row for every day it is needed. A Labour line for 5 Flaggers, Monday to Friday, gives five rows a week, each with a Quantity of 5.

• Which days have a row. A line has a row on each of its weekdays inside its dates, from the day it was added to the order. A day you changed under Customise specific days or weekdays shows that day's quantity and times, and a day turned Off has no row. A past day keeps the quantity and times it had, even after the line's dates or pattern change, as it does on the scheduling board.
• Shift Hours runs from the start time to the end time. A shift that ends at or before its start runs past midnight, so 22:00 to 06:00 is 8 hours. Crew Hours is Quantity × Shift Hours. Both are blank on a line with no times.
• Allocated counts what the scheduling board counts for that line and day: workers on a Labour line, machines on an Asset line, and only complete worker-and-machine pairs on an Operator + asset line. From today on, a deactivated or archived worker, or an archived asset, doesn't count; on past days they still do. Unfilled is Quantity less Allocated, and never below 0.
• Materials have a row with their quantity, but are never allocated, so Crew Hours, Allocated and Unfilled are blank on them.
• Kind says which type of line a row comes from, in the same words as the order.
• Lines that run as a Duration are left out, because the machine is on site for the whole hire rather than asked for day by day (see Put plant on hire). A removed line keeps its rows on the days before you removed it, and has none from that day on. Deleted orders are left out. Orders of every status are included: add Order Status to tell them apart.
• Demand Date runs from today to 30 days ahead if you leave it empty, and the field shows those dates. Pick only a start and the range runs 30 days from it. It can pick days after today and be up to a year long. A longer range shows "The Order Demand report date range is longer than a year. Pick a shorter range." When the range holds more demand than can be read at once, you see "The Order Demand report covers too many days of demand to read at once.": filter by order, project or client, or pick a shorter range.

Allocations

Allocations has a row for each worker or asset booked on an order on a day. To plan crews before you quote, add these columns, which are not ticked by default and land after the existing ones, so your saved sheets don't change:

• Role / Resource is the role the booking fills, named as on the scheduling board: the role for a worker, the asset class for an asset, and both, such as Operator + Roller, for an operator with a machine. A booking that fills none of the order's roles shows No role.
• Asset Class is the asset class the role asks for, blank for a worker-only role.
• Requirement Kind is Labour, Asset, Operator + asset or Material, blank on a No role booking.
• Start Time, End Time and Scheduled Hours are the booked shift. A shift that ends the next morning counts its hours past midnight: 22:00 to 06:00 is 8 hours. Scheduled Hours is a number, so a chart or pivot can total it.
• Crew is the crew the booking was deployed from, blank when it was booked on its own.
• Allocation UUID and Role UUID match each booking to the Allocation ID and Role ID columns of Worker Compliance Audit.

Procurement

Job Costing

Forms

Until you choose a form, the dialog says "Select a form to load its submissions" and has no columns to pick. Both sources also need View forms.

On Form Submissions, a table question fills one cell. Form Table Rows gives each row of the table its own row in the sheet, so you can total a table's numbers. For example, a downtime form with a table of stoppages (cause, start, end and a duration) gives one sheet row per stoppage:

• Table columns are named after the question and the column, such as Downtime: Cause. A form with more than one table gives rows from every table: Table says which table a row came from, and the other tables' columns are blank on it.
• Shift, Shift Date, Asset and Project come from the shift the form was filled in on. Asset is the machine the form was about, or otherwise the machine scheduled with the worker on that shift. A form filled in outside a shift leaves them blank; if your form asks for the shift or asset itself, use those questions' columns instead. Asset says Restricted if you don't have View assets, and Project says Restricted if you don't have View projects or you are limited to certain projects and the shift's project isn't one of them, so those rows never combine with rows that have no asset or project.
• The submission's other answers are on every one of its rows, so you can group by them too. Geospatial (map) answers are the exception: they aren't on this source, so use Form Submissions for them.
• Numbers you can total. Number columns, and Calculated columns formatted as a number, currency or duration, hold numbers. A duration is in hours, so 1h 30m shows as 1.5.
• Total downtime by cause, asset and shift. Pick a Date Range, then tick only Shift, Asset, the cause column and the duration column. Rows with the same shift, asset and cause combine into one, and their durations add up.
• Submission answers are not added up per row. An answer from the rest of the submission (other than a Geospatial one), such as a total, repeats on each of its rows, so rows with different values stay apart instead of being summed.
• A submission with an empty table has no rows, so it doesn't appear here. Use Form Submissions to count submissions.
• Up to 10,000 rows. The limit counts table rows, not submissions, and keeps the newest submissions' rows.

Reference Data

Assets also needs View assets, which Owners, Admins and Managers have.

On Projects, Date Range keeps every project that runs on at least one day of the range: it starts on or before the last day and ends on or after the first. A project with no Start Date counts as already started, and one with no End Date as still running, so a project with neither date is always included.

Assignar Operations

With the Assignar Operations reporting add-on and a connected Assignar Operations integration, the panel has an Assignar Operations group. Its sources carry an Ops badge and read live from Assignar Operations, so an import takes a few seconds while "Querying Assignar Operations…" shows.

When the add-on is on but the integration is not connected, the group shows "Connect Assignar Operations" with a Go to Integrations button. In the chart library the same sources are greyed out with "Requires a connected Assignar Operations integration."

Things that work differently from the Assignar Pay sources:

• Filter on any field. Besides the starting filter, Filter by any field lets you add conditions on any column, such as a project or client. On a text column, contains and doesn't contain take one value, or several separated by commas. contains keeps rows that match any of the values. doesn't contain removes rows that match any of them, and keeps rows where the column is blank. To search for a comma itself, type ^,: Smith^, John matches Smith, John. This works in is one of too.
• Up to 50 columns. These sources have hundreds of columns, so you can tick at most 50. Past that you see "Maximum of 50 columns reached. Deselect one to add another."
• Last 30 days to start with. Ops Timesheets, Ops Allocations / Shifts, Ops Digital Dockets and Ops Form Results load the last 30 days when you leave the date empty. Form results still in progress have no submitted date, so Ops Form Results leaves them out.
• Days to Expiry on competencies, competency exceptions, documents and inductions counts from when the sheet was last refreshed, not from today. A negative number means already expired.
• One docket spans many rows. Ops Digital Dockets has a row for every answer, including each sub-question and each entry in a repeating group, so counting rows counts answers. Use the Count of Digital Docket Results measure to count dockets. For the answer itself use Results Answers, which holds every type of answer; Answer Text, Answer Number, Answer Datetime and the Answer Shift columns hold only answers of their own type. They are blank on other rows, which is expected, not missing data. Use them to filter or do sums. Answer Shift Total Time is in hours, rounded to two decimals, as in Assignar Operations.
• One form result spans many rows. Ops Form Results has a row for every answer, including each sub-question and each entry in a repeating group, so counting rows counts answers. Use the Count of Form Results measure to count form results. For the answer itself use Result Answers, which holds every type of answer; Answer Text, Answer Number and Answer Datetime hold only answers of their own type.
• No pivot by question. Each question is a row, not a column. Pivoting questions into columns is not supported yet.
• Task and journal columns add rows. Each answer repeats once per task-shift on the order, or once per journal entry, and can hit the 10,000-row cap.
• Form result people are labelled by role. Each person on a form result has their own columns, prefixed with their role, such as Created by Dashboard Users Full Name and Answer Fieldworkers Full Name. The unprefixed Dashboard User columns show the same person as Created by Dashboard Users, as in Assignar Operations.
• An asset can repeat across rows. Ops Assets has a row for each mix of an asset's attachments, skills and journal entries, so counting rows over-counts assets. Use the Total Assets measure instead.
• Ops Costs figures repeat too. Units and Cost are values for one row, and a project on several rows repeats them. Add the Total Units and Total Cost measures to have them summed for you.
• An Ops Documents row belongs to one thing. Entity Name says which; the columns for the other kinds of record are blank on that row.
• Refresh by hand. A refresh schedule skips these sheets; see Keep report data current.

Sources that can go on a map

Timesheets, Dockets and Form Submissions carry a location, so they are the ones you can show as a Map chart in the chart library. Only timesheets captured through a form have a location.