Reporting (Advanced): Customize the queries in custom reports
This article provides a detailed reference for data and keywords that can be used to build custom reports and edit pre-built reports in the Report Designer tool. It covers various data sources and formats to help you use this data accurately and effectively in your reports.
Covered here:
Customize the queries in custom reports, explained
The Report designer tool lets you build your own reports. It understands data names, values, date filters and other specific keywords listed below. Use this reference to customize the Report designer queries. Here, you can find all available data names and keywords. Also, you can find some examples to inspire:
- Data names: Invoice Amount, Time Entry Billable Hours, Task Assignee, Tasks Completed Count
- Values: 200, 10000, Customer = Otto Mann
- Date filters: 2023, yesterday, last month
In the search field, keywords are divided by colors:
- Blue: these keywords affect the X axis (except for bar charts, where the Y axis is affected)
- Green: these keywords affect the Y axis (except for bar charts, where the X axis is affected)
- Gray: these attributes function as additional filters
The data is split into two categories: Workflow and Time and Billing. Both categories also include account information, which is listed below in the Accounts section. If you don't see any category, click the setup icon and select them as sources.
Reporting data names
Accounts
| Data name | Description |
| Account Name | Full name of client company or account |
| Account Type | Type of account: company or individual |
| Account Tag | Account tag Example: You may want to see revenue by tag and compare old and new clients or tax return and bookkeeping revenue |
| Account Assignee | Full name of employee assigned to account |
| Firm Name | Your firm name |
Workflow
Under the Workflow category, you will find all the data below in one list.
Pipelines
| Reporting data name | Description |
| Pipeline | Pipeline name. Includes only pipelines with jobs |
| Pipeline Stage | Pipeline stage name |
| Pipeline Stage Order | Pipeline stage number, where 0 is the first stage on the left |
Jobs
| Reporting data name | Description |
| Job Name | Job name |
| Job Description | Job description |
| Job Due Date | Job due date |
| Job Is Archived | Indicates if job is archived (can be true or false) |
| Job Id | Job ID |
| Job Archived Date | Job archiving date |
| Job Start Date | Job start date |
| Job Assignee | Full name of employee assigned to job |
| Jobs Count | Active (not archived) jobs count |
| Job Priority | Job priority |
| Job Days In Current Stage | Days passed since job was last moved to the current stage You can identify stages in the pipeline where jobs spend the most time and optimize workflow |
Tasks
| Reporting data name | Description |
| Task Name | Task name |
| Task Create Date | Date the task was created |
| Task Start Date | Task start date |
| Task Due Date | Task due date |
| Task Completed Date | Date when the task was marked as completed |
| Task Description | Task description |
| Tasks Count | Task count You can evaluate the number of tasks assigned to each team member and estimate the workload |
| Task Id | Task ID |
| Task Status | Task status |
| Task Priority | Task priority |
| Task Tag | Tag or tags assigned to task |
| Task Assignee | Full name of the task assignee |
| Tasks Completed On Time Count | Number of tasks completed on time – due date is greater or equal to the date when the task was marked as completed |
| Tasks Completed Count | Number of completed tasks |
Time and Billing
Under the Time and Billing category, you will find all the data below in one list.
Invoices
| Reporting data name | Description |
| Invoice Amount | Invoice amount Using this metric, for example, you can compare the number of invoices for different amounts |
| Invoice Number | Invoice number |
| Invoice Paid Date | Date and time when the invoice was paid |
| Invoice Service | Service name You can analyze your revenue by service |
| Invoice Posted Date | Date and time when the invoice was sent to the client For example, you can use this metric to compare how much time passes in average between this date and Invoice Paid Date |
| Invoice Tax Rate | Invoice tax rate in percent |
| Billable Revenue Per Hour |
Average revenue earned per billable hour Calculated by dividing the total paid invoice amounts by the total number of billable hours worked |
| Utilization Rate | The ratio of billable hours to total hours |
| Revenue Per Hour |
Average revenue earned per billable hour Calculated by dividing the total paid invoice amounts by the total number of all hours worked, billable and non-billable |
| Amount Overdued <15 Days |
Overdue invoices amount up to 15 days past due You can use such data to measure the average time it takes for invoices to be paid and identify overdue amounts |
| Amount Overdued 15-29 Days | Overdue invoices amount within 15-29 days past due |
| Amount Overdued 30-59 Days | Overdue invoices amount within 30-59 days past due |
| Amount Overdued 60-89 Days | Overdue invoices amount within 60-89 days past due |
| Amount Overdued 90+ Days | Overdue invoices amount more than 90 days past due |
| Amount Overdued | The total amount of unpaid invoices |
| Invoice Status | Current invoice status: posted, partial, paid, processing or scheduled |
| Paid Revenue | Paid revenue amount |
| Unpaid Revenue | Unpaid revenue amount |
| Tax Amount | Invoice tax rate multiplied by the amount |
| Bill Service Amount | Line items amount |
Time entries
| Reporting data name | Description |
| Time Entry Started Date |
Date and time when the timer started |
| Time Entry Duration (Seconds) |
Time entry duration in seconds |
| Time Entry Rate |
Billable time entry rate |
| Time Entry State |
State of time entry: running, paused or finished |
| Time Entry Finished Date |
Date and time when the timer was stopped |
| Time Entry Invoicing Status |
Time entry invoicing status: non billable, unbilled, billed or status unknown |
| Time Entry Billable Hours |
Number of billable hours recorded in time entries |
| Time Entry Assignee |
Full name of the time entry assignee Using this data, you can get insights about your team members' workload and efficiency |
| Time Entry Duration (Hours) |
Time entry duration in hours |
| Time Entry Tag | Time entry tag |
| Time Entry Billable Amount |
Total cost of hours worked Calculated only for time entries marked as billable by multiplying the duration of each time entry by its hourly rate |
Keywords
Common
| Keyword |
Example | What you'll find |
| top n | top 5 Invoice Paid Revenue Customer | top five client accounts with the largest total payment amount |
| bottom n | bottom 5 Invoice Paid Revenue Customer | top five client accounts with the lowest total payment amount |
| sort by | Invoice Paid Revenue sort by Invoice Service | total payment amount sorted by services |
| by... | Invoice Service by Time Quantity | time entries total duration sorted by services |
Date
| Keyword |
Example | What you'll find |
| after/before | Invoice Amount for Invoice Posted Date before march 2024 | total amount of invoices posted before march 2024 |
| between...and... | Invoice Amount for Invoice Posted Date between 01/01/2024 and 02/21/2024 | total amount of invoices posted between 01/01/2024 and 02/21/2024 |
| daily/hourly/weekly/monthly/quarterly/yearly | Tasks Completed Count monthly | amount of completed tasks sorted monthly |
| daily/weekly/monthly/quarterly year-over-year | growth of Invoice Amount by Invoice Posted Date quarterly year-over-year | invoice total amount dynamics sorted quarterly and compared year-over-year |
| day/month/quarter/week/year | Task Count 2023 | total number of tasks in a specific year |
| day of week/month/quarter/year | Task Count by Task Created Date day of week by year | total number of created tasks sorted by days of the week and by year |
| week of month/quarter/year | Task Count by Task Created Date week of month by year | total number of created tasks sorted by weeks of the month and by year |
| month of quarter | Task Count by Task Created Date month of quarter by year | total number of created tasks sorted by months of the quarter and by year |
| quarter of year | Task Count by Task Created Date quarter of year by year | total number of created tasks sorted by quarters of the year and by year |
| growth of...by... | growth of Task Count by Task Completed Date | total number of completed tasks dynamics |
| growth of...by...daily/monthly/quarterly/weekly/yearly | growth of Task Count by Task Completed Date quarterly | total number of completed tasks dynamics sorted quarterly |
| hourly | Task Count hourly | total number of tasks sorted hourly |
| last month/quarter/week/year | Task Count hourly last month | total number of tasks for the last month, sorted hourly |
| last n days/months/quarters/weeks/years | Task Count hourly last 3 months | total number of tasks for the last three months, sorted hourly |
| n months/years | Task Count hourly last 3 months | total number of tasks for the last three months, sorted hourly |
| n days/weeks/months/quarters/years ago | Task Count hourly 3 months ago | total number of tasks three months ago, sorted hourly |
| this day/month/quarter/week/year | Task Count hourly this month | total number of tasks for this month, sorted hourly |
| today/yesterday | Task Count hourly yesterday | total number of tasks yesterday, sorted hourly |
Time
| Keyword | Example | What you'll find |
| hourly | Task Count hourly | total number of tasks sorted hourly |
| last hour/next hour | Task Count last hour | total number of tasks for the last hour |
Number
| Keyword | Example | What you'll find |
| average | average Invoice Amount | average total amount of your invoices |
| count | count Invoice Amount by Customer | total number of invoices sorted by client accounts |
| max/min | max Invoice Amount by Customer | max total amount of invoices sorted by client accounts |
| sum | sum Invoice Paid Revenue | total amount of payments |
Comparison
| Keyword | Example | What you'll find |
| = (equal) | Invoice Amount = 1000 by Customer | only client accounts with invoices of exactly $1000 |
| >= (greater than or equal) | Invoice Amount >= 1000 by Customer | only client accounts with invoices of exactly or exceeding $1000 |
| > (greater than) | Invoice Amount > 1000 by Customer | only client accounts with invoices exceeding $1000 |
| <= (less than or equal) | Invoice Amount <= 1000 by Customer | only client accounts with invoices amounts of exactly or less than $1000 |
| < (less then) | Invoice Amount < 1000 by Customer | only client accounts with invoices amounts less than $1000 |
| != (not equal) | Invoice Amount != 1000 by Customer | only client accounts without invoices amounts of $1000 |