Use Cases for Customizable Data Export

The Customizable Data Export (CDE) can support many different reporting needs. This guide walks through common use cases and suggests filters and fields for each scenario so you can get started quickly.

Who this is for: Administrators and managers who know the basics of CDE and want practical examples for common reporting scenarios.

You can jump to a use case:

• Payroll exports
• Billing and client invoicing
• Time off and leave reporting
• Approvals, compliance, and audits
• Data cleanup and quality checks
• Analysis in Excel, Google Sheets, and BI tools
• Using the Company Lock Date to keep refreshes efficient
• Pulling data programmatically with the REST API
• Tips for saving layouts and reusing exports

Use these as starting templates: pick the scenario that matches what you're trying to do, then configure your CDE filters and fields to match.

Before you begin: For a full overview of every filter and field option in CDE, see Customizable Data Export.

Payroll exports

Use this pattern when you need a file for your payroll system, or to confirm total hours and pay by employee for a specific pay period.

Recommended filters

• Specific Dates: Set the date range to your payroll period (for example, first to last day of the pay period).
• Include: Only worked time if you process time off separately, or All worked and time off if your payroll file needs both.
• Filter by Approval Status: Only approved or locked to ensure you only export final timesheets.
• Filter by Billable Status: All (payroll usually needs all hours, not only billable time).
• Optional: Use Mark exported entries as and Don't include entries already marked as to create incremental payroll exports.

Suggested fields

Start with Simple fields and add Advanced fields as needed:

• Employee name and ID
• Entry Date
• Hours worked
• Client, Project, Task (if you group hours by work type or cost center)
• Billing or cost rate (if you want to compare costs vs pay)
• Timesheet ID and Timesheet Status (from Advanced) for audit tracking

Tip: Save this layout as a favorite report titled something like "Payroll Export – Biweekly" so you only adjust the date range each period.

Billing and client invoicing

Use this pattern when you need to prepare invoices for clients, review billable hours, or reconcile billable vs. non-billable work.

Recommended filters

• Specific Dates: Set the date range that matches your billing cycle (for example, monthly or project phase).
• Include: Only worked time for most billing workflows.
• Filter by Billable Status: Billable to focus on billable hours, or All if you want to show non-billable work side by side.
• Filter by Approval Status: Often Only approved or locked if you only invoice on approved time.

Suggested fields

• Client, Project, Task
• Employee name
• Entry Date
• Hours
• Billable status
• Billing rate and extended billable amount (if available)
• Notes or descriptions (if you include them on invoices)
• Internal IDs (Client ID, Project ID) for mapping to accounting systems

Tip: Use Pivot Tables in Excel to summarize hours and billable amounts by Client and Project, then compare to invoices or import into your billing system.

Time off and leave reporting

Use this pattern to review vacation, sick time, or other leave usage across the organization. It is helpful for HR reporting, accrual reviews, or compliance checks.

Recommended filters

• Include: Only time off so you focus on leave entries.
• Specific Dates: Choose the period you want to analyze (for example, year to date, quarter, or a custom span).
• Filter by Approval Status: Choose All if you want to see pending usage, or Only approved or locked for final history.

Suggested fields

• Employee name and ID
• Entry Date
• Hours of time off (or days, depending on your configuration)
• Time Off type (Vacation, Sick, etc.)
• Timesheet Status (for context)
• Manager or Approver (if available)

Tip: Combine this with your Time Off reporting or accrual reports in ClickTime to validate balances and spot unusual trends.

Approvals, compliance, and audits

Use this pattern when you need to investigate who approved time, when a timesheet changed status, or why hours are missing or locked. This is helpful for audit trails and compliance reviews.

Recommended filters

• Specific Dates: Choose the date range for the entries or pay periods you are reviewing.
• Include: All worked and time off so you do not miss any entries for the timesheets in question.

Suggested fields (Advanced)

Switch to Advanced fields so you can include timesheet level information:

• Timesheet ID
• Timesheet Status (for example, Not Submitted, Submitted, Approved, Locked)
• Timesheet Approver (if available)
• Timesheet Status Changed On
• Date Created (when the entry was first saved)
• Employee name and ID
• Entry Date and Hours
• Client, Project, Task (for context)

Note: The Timesheet Status Changed On field displays time in PST. The Date Created field displays the timestamp in UTC and reflects when the entry was first created, not when it was edited.

Data cleanup and quality checks

Use this pattern when you want to find entries that might need correction: missing notes, wrong projects, unusual hours, or entries created after a cutoff date.

Recommended filters

• Specific Dates: Choose the period you want to audit (for example, the last month or quarter).
• Include: All worked and time off, unless you are focused on only worked time or only time off.
• Filter by Approval Status: Start with Not locked / not approved to focus on entries that are easier to fix.

Suggested fields

• Employee name
• Entry Date
• Hours
• Client, Project, Task
• Notes or comments
• Timesheet ID and Timesheet Status
• Date Created and Timesheet Status Changed On (Advanced)

Tip: Use filters and sorting in Excel to quickly find entries with zero hours, missing notes, or unexpected projects or tasks.

Analysis in Excel, Google Sheets, and BI tools

Use this pattern when your goal is flexible analysis rather than a fixed export for another system. You might want to build dashboards, trend reports, or pivot tables that you refresh regularly.

Recommended filters

• Specific Dates: Choose a period that balances detail and performance (for example, quarter to date or year to date).
• Include: Choose All worked and time off or limit to only the types of time you care about for your analysis.
• Filter by Approval Status: Select All if you want to see work in progress trends, or Only approved or locked for finalized history.

Suggested fields

• Employee, Department, Division, or other organization fields
• Client, Project, Task or Program fields
• Entry Date and Hours
• Billable status and billing rate (if you measure utilization or revenue)
• Cost rates (if available and needed for margin analysis)
• Internal IDs and Timesheet ID for stable joins and relationships

To keep external reports up to date, combine this CDE configuration with Data Linking. For details, see:

• Data Linking Overview
• Data Linking with Excel
• Data Linking with Google Sheets

Using the Company Lock Date to keep refreshes efficient

As your data grows, re-downloading your entire history on every refresh becomes slow and unnecessary. Most historical time data does not change — once a period has been closed and reconciled, those entries are final. The Company Lock Date is what tells you exactly where that boundary is.

Any time entry on or before the lock date is read-only and will never be modified. You can download that history once and keep it as a stable base in your analysis tool. On subsequent refreshes, you only need to pull data from the day after the lock date through today.

• Historical base: Set Specific Dates from the beginning of your data through the current lock date. Download once, filter by Only approved or locked, and store this as your permanent historical data set. You will not need to download this range again until your administrator advances the lock date.
• Incremental refresh: Create a separate saved CDE layout with a start date of the day after the lock date. Use this layout for all ongoing refreshes via Data Linking. It pulls only current-period data, which you then merge with your historical base.
• When the lock date advances: Move the newly locked date range into your historical base and update your incremental layout's start date to match.

Tip: Store the current lock date as a named cell or parameter in your spreadsheet or BI tool so you can update your date ranges in one place when it advances.

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

Pulling data programmatically with the REST API

If you are building an automated pipeline or prefer to load data directly into a database or BI tool without a file export step, the ClickTime REST API is an alternative to CDE and Data Linking.

When retrieving time data via the API, use the GET /Reports/Time endpoint. It is designed for bulk retrieval and returns up to 2,500 rows per request. The GET /TimeEntries endpoint returns only 100 rows per request and is intended for smaller, targeted lookups such as retrieving entries for a single timesheet period — not for bulk or historical downloads.

• Apply the same lock date strategy described above: request your full history through the lock date once, then make incremental requests from the day after the lock date on each subsequent run.
• Page through results using the API's pagination parameters until no more rows are returned.

API reference: For authentication, parameters, and response details, see the ClickTime REST API v2 documentation.

Tips for saving layouts and reusing exports

For each use case above, you can save your CDE configuration as a favorite layout so you do not have to rebuild it each time.

• Use the Save these report settings as a favorite report titled field to name your layout before you run the report.
• Choose a clear name that includes the purpose and time frame, for example: Payroll Export – Biweekly or Client Billing – Monthly.
• Open and re-run the saved layout from the Customizable Data Export section of the Company → Reports page.
• If a layout is no longer needed, open it and click Delete this saved report to keep your list tidy.

Next steps: Once you are comfortable with these patterns, you can create your own CDE layouts tailored to your organization and link them to Excel or BI dashboards for ongoing analysis.