Use the data exporter
Export user timesheets, project timesheet hours, expense items, and expense reports from Consult as spreadsheets.
This guide explains how to use Consult’s data exporter tool.
Permissions required
Only users that have Set Admin Settings as part of their role can access the data exporter tool.
Note: If you can access Tools > Data Exporter in the top navigation, you have sufficient permissions.
Use the data exporter
To export data:
- From the navbar, go to Tools > Data Exporter.
- Choose an export from Select Query. A short description of the chosen export is shown beneath the field, and a sample of the output where one is available.
- Complete the inputs the chosen export asks for.
Only the fields that export needs are shown, and a field marked with
*cannot be left blank. - Click Export.
This downloads an Excel file with the requested data.
Note: The list of exports can differ between Consult clients, since an export can be limited to specific ones. Only the exports available to your organisation are offered, and an export that is not offered cannot be run.
User timesheets export
This option exports the weekly timesheet statuses of all users for the current month.
This export applies to the current month, so no date input is required.
The exported file is named in the format User-timesheets-overview-{YYYY-MM-DD HH_MM_SS}.xlsx.
The export is generated from the following:
- Time scope: The current month and year at the time of export.
- Users: All users with an active account.
- Status: For each week, the submission for each user, using its status if present.
Data in the spreadsheet
Headings are generated from the past four weeks, including the current week.
The columns in the spreadsheet:
The row data in the spreadsheet:
Project timesheet hours export
This option exports every timesheet line captured against the projects you choose, priced at the rate each week was booked at. It is the export to use to verify what a project cost, or to reconcile captured hours against what was billed.
Complete the inputs as follows:
- Projects: Required. Choose one or more projects to report on.
- From Date and To Date: Optional. Leave both blank to export every line ever captured against the chosen projects.
The exported file is named in the format ProjectTimesheetHours-{from}-to-{to}.xlsx when both dates are given, and ProjectTimesheetHours-{YYYY-MM-DD}.xlsx, using the date of the export, when they are not.
Columns in the spreadsheet
One row is written per captured timesheet line, with the following columns:
| Column | Contents |
|---|---|
| Project Code | The code of the project the line was captured against. |
| Project Name | The name of that project. |
| Resource Name | The user who captured the hours. |
| Deliverable | The deliverable the time went to, for WBS projects. Blank for others. |
| Timesheet Comment | The comment the user captured on the line. |
| Date | The date the hours were worked, as YYYY/MM/DD. |
| Project Rate | The rate that applied in that week, not the resource’s current rate. |
| Hours Captured | The hours on the line. |
Rows are sorted by project code, then resource, then deliverable, then date. Two footer rows close the file: Total Hours and Total Cost, the latter being every line’s hours multiplied by its own week’s rate.
Only lines that carry hours are exported.
A day the user left blank or captured as 0 still has a timesheet row behind the scenes, but it is a placeholder rather than captured time, so it is left out.
Note: A resource whose rate changed partway through a project bills each week at the rate that week was booked at. The export reflects that, so the total cost of a project can differ from the same hours priced at the resource’s current rate. See understanding rate changes.
User expense items export
This option exports all expense claims submitted in Consult for a given period.
The export contains multiple sections, rendered as separate tables in a single spreadsheet.
The exported file is named in the format AllUsers-ExpenseReports-<dateRange>.xlsx.
Sections in the spreadsheet
The export organises data into sections, each with its own set of columns:
- Disbursements: Expenses where the Type is
MealsorToll Fees. - Travel: Mileage claims.
- Accommodation: Expenses where the Type is
Accommodation. - Other: All other expenses.
- Totals: Subtotals by category and a grand total.
Expense reports export
This option exports all approved expense reports submitted through Consult for a selected date range. It provides a bulk overview of approved expense reports, including the people involved in approval and payment.
Set the period to export in From Date and To Date.
Both are required.
The exported file is named in the format ExpenseReports-{from}-to-{to}.xlsx.
Data in the exported spreadsheet
The columns in the spreadsheet:
The row data in the spreadsheet:
