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
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 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 set up stage and becomes a simple one-step update action once all the settings are configured properly. This power tool involves advanced application (e.g. Excel) skills on the part of the account administrator/manager in charge of setting it up.

Linking Requirements

The following requirements and restrictions apply to accounts that want to use the Linking feature:

• 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 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.
• Data Linking does not include support for Mac versions of Excel.
• 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. Any custom fields that are created will automatically be added to the Customizable Data Export selector interface.
• 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.

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 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. 20090131).
• 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. 20100131).
• 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 copy/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 being performed. Any person with access to the file will be able to update it and see all the data available in the report.

The URL provided in the saved Customizable Data Export is based on the login information of the user who created the report. If for some reason the user changes his/her 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 by mistake, the link between the external application and ClickTime will no longer work.