Data Linking Overview

ClickTime can easily integrate your ClickTime data with your pre-existing external reports. This method allows applications such as Microsoft Excel and Google Sheets to request and retrieve data from the ClickTime system without using the standard web application login and graphical user interface.

Data Linking starts with the Customizable Data Export Report. General information on using that report is available in the Customizable Data Export Overview Guide here.

This guide will provide basic details for connecting ClickTime data with external systems via Data Linking. We also provide separate guides for linking specifically with Microsoft Excel and Google Sheets. Please see the Excel Linking guide and Google Sheets Linking guide for more information. 

This information is also summarized in this PDF file. 

General Information
Linking Requirements
Using the Customizable Data Export for Linking
Using the Company Lock Date 
Advanced Link Configuration
Security Concerns

Please note - XML linking requires a Team (or above) plan. Basic/Starter plans will not have the option to generate an XML link from the Customizable Data Export.

General Information about Data Linking

If your company already uses an internal reporting system based on reports, charts, pivot tables, etc. in Excel, Sheets, or similar applications, and some of the existing ClickTime reports do not fully match or replace your custom reporting, then Data Linking is the solution for you.

Linking allows your company to update your spreadsheet or database with fresh data from your ClickTime account on demand. This functionality allows you to request a saved Customizable Data Export report (which can include time entries, project details, or user information, based on your saved report configuration) via a single secure web request without logging into ClickTime using the standard web application login interface.

This is a multi-step process during the setup stage and becomes a simple one-step update action once all the settings are configured properly. This powerful tool involves advanced application (e.g., Excel) skills on the part of the account administrator/manager in charge of setting it up.

Linking Requirements

• Data Linking requires a Team/Corporate subscription (or above) - Basic/Starter plans will not be able to generate an XML link. Please contact ClickTime Support (support@clicktime.com) if you have questions about upgrading in order to utilize XML Linking.
• The Customizable Data Export allows access to time entry data. It does not support integration of expense information. For expense data integration, please refer to our dedicated expense reporting features.
• Data Linking does not include support for Mac versions of Excel. This is due to technical differences in how Excel for Mac handles external data connections.
• A secure connection (SSL/HTTPS) is required to use this feature.

Using the Customizable Data Export for Linking

• The starting point for Linking is the Customizable Data Export report. Only administrators and managers with permissions to access reports in your ClickTime account can configure and save a Custom Export.
• To create a Customizable Data Export for Linking, go to the Company --> Reports page and choose the "Customizable Data Export" at the bottom left. You will need to "Save" your report in order to see the XML link (this report is also available in the "Miscellaneous" section of the page).
• Any field currently available through the Customizable Data Export selector interface can be exported and used in your spreadsheet/database.
• Any field that is not available through the Customizable Data Export may not exist in the ClickTime system to begin with. Such fields can be accommodated by creating custom fields, which will automatically be added to the Customizable Data Export selector interface. This ensures you can tailor your exports to include all necessary information for your external systems.
• Select the fields you need to further manipulate in your external spreadsheet/database, and save the report settings as a favorite report with a title.
• By default, data will only be exported for the date range you specify in the report options. However, you can override this date range later via the advanced link configuration parameters detailed in the next section.
• We recommend using XML for optimal data manipulation results in spreadsheets such as Excel.
• We recommend using CSV for optimal performance when connecting to a database, BI, or reporting tool.
• The screenshot below illustrates the report settings.

Using the Company Lock Date to limit what you re-download

If you refresh your linked file regularly, you can make that process faster and more reliable by taking advantage of the Company Lock Date.

Any time entry on or before the lock date is read-only — it will never be edited or updated. This means you only need to download that historical data once. For your linked report, set the StartDate parameter to the day after the lock date. This limits each refresh to current-period data only, keeping response times fast and your data set consistent.

• Historical data (on or before the lock date): Download once as a separate one-time export and store it as the stable base of your data set. It will not change.
• Current-period data (after the lock date): Use your linked report with a StartDate set to the day after the lock date. Refresh this as often as needed.
• When the lock date advances: Update your StartDate parameter to match the new day after the lock date, and add the newly locked period to your historical base.

Where to find the lock date: Company > Preferences > Time and Expenses > Company Lock Date. For details, see Time and Expense Settings – Company Lock Date.

If you have custom fields with duplicate names, the Customizable Data Export may duplicate these rows of data. For this reason, we highly recommend you always use unique display names when creating custom fields.

Please Note: You must use the "Save these report settings..." options when running the Report in order to generate an XML link.

For the purpose of data linking, exported entries in the saved Customizable Data Export should not be marked with a label. Any labels will be ignored when data is retrieved by your spreadsheet/database. However, time entries previously marked with a label via another report in your ClickTime account can be excluded from this report by using the picker "Don't include entries already marked as...".

Once you have run your report with these settings, you should see a URL link like the one shown in the following screenshot, under the heading "Excel Linking", which you should copy. You may also retrieve this URL by returning to Company Reports, clicking on your saved report and scrolling to the bottom of the report picker screen. You will use this URL to link your external application to your ClickTime account.

(If you do not see the "Excel Linking" section on the download export page, you will need to re-run the report, making sure to give the Report a name as seen in this screenshot).

Please note: Many users integrate their Customizable Data Export with Excel. However, this feature allows you to link your ClickTime account with other external applications, including Google Sheets and many databases.

The Excel Integration and Google Integration guides provide detailed instructions on how to utilize your Linking URL to integrate with some of the most common applications, including Microsoft Excel.

Advanced Link Configuration

A typical URL you might use for your HTTP request will resemble this example:
https://rpt.clicktime.com/rpt?ID=2zYcls4UUBs&Email=usersemail%40usersdomain.com&UserKey=UfI6GfZJydI%3d&StartDate=20090131&EndDate=20100131&Format=xml

Required URL parameters:

• ID. The ID of the saved Customizable Data Export report.
• Email. The email address of the owner of the saved report. This email address is used for authentication.
• UserKey. This is used for authentication.

Optional parameters:

• StartDate. The beginning date to use for the date range of data to be retrieved. Providing a value causes the value stored in the saved report definition to be ignored. This should be provided in an ISO date format (e.g., 20250131).
• EndDate. The end date to use for the date range of data to be retrieved. Providing a value causes the value stored in the saved report definition to be ignored. This should be provided in an ISO date format (e.g., 20251231).
• Format. This defines the format of the exported data that gets returned. The possible values for this are Text (tab-delimited), CSV (comma-delimited), and XML (preferred format for Excel on Windows). If no value is provided, the default format is XML.
• DivisionID. Append one or more (scrambled) Division IDs to the query string. Multiple IDs are separated using the URL-encoded comma value of "%2C".
  NOTE: Scrambled ID values can be obtained from either the query string while editing a Division or from the "Export Division List" report under the Miscellaneous category.
• JobID. Append one or more (scrambled) JobID values to the query string. Multiple IDs are separated using the URL-encoded comma value of "%2C".
  NOTE: Scrambled ID values can be obtained from either the query string while editing a job, or from the "Export Job List" report under the Miscellaneous category.

For best results, we recommend editing the link in a text editor (such as Notepad) and then copying/pasting the new link into Excel.

Security Concerns

Linking allows an administrator or manager to access ClickTime data without having to log in to the ClickTime application, simply by updating the linked file. When updating the file, no other extra security checks are performed. Any person with access to the file will be able to update it and see all data available in the report. Therefore, it is crucial for administrators to ensure that the linked file itself is stored in a secure location and access is restricted to authorized personnel only.

The URL provided in the saved Customizable Data Export is based on the login information of the user who created the report. If the user changes their login email and/or password, the URL will no longer work and the linked file will no longer connect to ClickTime to retrieve data. If you are the creator of such a report and have any security concerns or are afraid the file is being accessed by unauthorized users, simply change the password you use to access your ClickTime account. Similarly, if you change your password, the link between the external application and ClickTime will no longer work.