The Customizable Data Export allows you to download all timesheet data in a time period, and to choose which fields you'd like to include in your file. This can be useful for building your own reports based on ClickTime data, reviewing time entries on a large scale, and more. This guide is a quick introduction to the basic concepts of generating a file using the Customizable Data Export (which will also be referred to as the "CDE") function.
For more details on using Data Linking (also referred to as Excel Linking) with the results of a Customizable Data Export, please see the Data Linking guides. If you have specific questions about the tool, fields, or anything else, please contact ClickTime Support with those details and we'll be happy to assist.
Accessing the Customizable Data Export
Creating a Customizable Data Export
Date Created Field (New in June 2020)
Saving a Customizable Data Export
Viewing a Customizable Data Export
Video - Using Pivot Tables with the Customizable Data Export
You can access the Customizable Data Export from the Company --> Reports page. It is located in the bottom-right of the screen:
as well as within the "Miscellaneous" section of the page:
Once you load the Customizable Data Export page you will see a variety of options:
On the left you can set the criteria you are looking for in your report.
Export as: This report can be exported in as a CSV (comma-separated-values) file, Excel file, Text (tab-delimited), or XML file. Select the format you are most interested in.
Specific Dates: Set the date range you would like to download data for.
Sort By: Set the default sort method you'd like your report to generate as. Of course, you can always change sort order once you've opened the file in another program.
Include: The CDE can be used to export "all worked and time off", as well as "only worked time" or "only time off" time entries.
Filter by Approval Status: You can decide if you'd like to download "all" timesheet data, "only approved or locked" time, or only "not locked / not approved" time entries.
Filter by Billable Status: You can choose if you'd like to download "all" timesheet data, or specify if you are only interested in "billable" or "non-billable" time. Your organization can note how you determine Billable or Non-Billable time from the Company --> Preferences page (more details here).
Mark exported entries as: If you plan on regularly running the same CDE report, but want to only get newly entered time entries, you can use the ClickTime labels function to "label" the time entries you download now. More information on Labels can be found in this help video.
Don't include entries already marked as: This is where you can determine if you want to exclude any time entries you've applied a "label" to in a previous export, or if you'd rather get all time entries by selecting "(non chosen; export all entries)
Below this is the "Save" option, which will cover in the Saving a Customizable Data Export section of this guide (see below).
In addition to the general criteria, the CDE also allows you to specify exactly which data fields you are interested in downloading with the report. These are separated by the "Simple" and "Advanced" options.
Simple: The "Simple" data fields are the most commonly used options. If you are doing basic reporting or just want to review time entries, these options will probably be sufficient.
Advanced: The "Advanced" data fields include all of the "Simple" fields as well as much more. They are organized by the aspect of ClickTime they refer to. One of the biggest differences here is that we include data on the timesheet itself, so you can use this information to see details on the current/previous status of the timesheet, as well as when it was changed, and who by. The "Timesheet ID" field can also be used to group the same timesheet(s) together in pivot tables/etc.
Another component of the "Advanced" data fields is that it will allow you to get details regarding the Status and internal IDs for all the data fields.
ClickTime will display a preview of time entries from the last 10 days below the criteria section to give you an idea of how your report will look:
After you've set the basic criteria and selected the fields you are interested, you can now use the "Run Report" option to generate a file. Before you do so, there is one other aspect of this report you may want to be aware of, which is to save your criteria for future use.
ClickTime added a new column to the "Advanced" section of the Customizable Data Export in June 2020.
The column is called "Date Created" and it will show the day and time that the entry was originally saved to ClickTime.
Please note the following:
- This timestamp for entries after April 28th, 2020 will display in the GMT time zone. If you wish to view the date in your local time zone, you can add a new column with a formula to re-format the time zone to whatever you'd like. This article may be a helpful resource if you need to do this.
- The timestamp for entries before April 28th, 2020 will display in Pacific Time.
- This timestamp is simply when the entry was first added. If the entry was created, and then updated, the timestamp will show you the initial creation day/time and not the edits.
If you expect to regularly want to generate a report with the same data fields (and other settings) you can use the Save these report settings as a favorite report titled field. Type the name you'd like to assign this report for future reference before running, and then click the "Run Report" option:
If you give your report a name, you'll see that Report is easily accessible from the Company --> Reports page in the Customizable Data Export section of the page going forward:
If you determine you no longer need to use these settings anymore, you can open up the report and click "Delete this saved report" to remove it from the list of saved Customizable Data Exports:
After running the Customizable Data Export, you'll be brought to a new page where you can use the "download_export" link to save the report to your computer. We recommend saving the report to your computer instead of trying to open it directly in the program of your choice. The report will be in the format you have specified.
Once you have saved the report, you can then open up the program of your choice and choose to load the report from there.
In addition to the downloadable file, all Corporate and Enterprise accounts will also see the "Excel Linking" section of the page, which contains the data link that can be used to populate an external system such as Microsoft Excel, Google Sheets, Power BI, SQL databases and much more, with ClickTime Data.
These features are outlined in more detail in the following guides:
Once you've opened your Customizable Data Export in Excel, you can use the data to create reports in several methods. One of the features that can be very useful to analyze data in Excel is the "Pivot Tables" option, which allow you to aggregate and format your results in a variety of different ways.
This video provides an introduction to Pivot Tables using the Customizable Data Export as the source data, so we hope this is a helpful resource: