Report Builder allows you to create and share your own custom reports using multiple filters, sorting options, subtotals, charts, graphs, and other advanced tools.
How to Create a New Report
Understanding the Report Builder Menu
Formula Help
How to Edit Existing Reports
How to Save a Report as a New Report
How to Share a Report
How to View and Copy a Shared Report
How to Delete Reports
Known Issues & Limitations
Report Builder Video
To create a new report, go to Company --> Reports and click on "Create A New Report" in the "Report Builder" section.
This will open up a new report page.
Clicking on any button in the Report Builder toolbar (see image below) will open a contextual work field for that particular icon.
After adding filters, sorting options, subtotals, charts, graphs, and other advanced tools you want to add to the report, click on the "Save" button.
Understanding the Report Builder Menu
Add Formula Column
Create Report Layout
Specify Sort Order
Add Filters
Add Groupings
Add Subtotals (Aggregate)
Create Charts
Create a Crosstab / Pivot Table
Specify Report Paging
Below is a step-by-step description of how to create a new report using the Report Builder. Every icon on the Report Builder main menu is described in detail.
When you open the Report Builder to create a new report, you will first see a fixed set of columns displaying a default array of the most common data fields in ClickTime. Starting from this default set you can customize the look of your report. You can start by adding columns. For instance, you may want to see an "Amount" column that displays the result of multiplying worked hours by billing rates.
You can achieve this by using the "Formula Column" icon on the main toolbar of the Report Builder. Click on the "Formula" column thumbnail on the menu. Enter a name for the new column that will be created. Insert the column name that will be part of the formula. To do that, select the column from the drop-down menu (see image below) and click on the "Insert" button to its right. Manually add the formula that you want to use. The Report Builder uses the standard formula symbols also used by Excel. If you need help with a formula, please read the Formula Help section below.
You can then specify the type and format of the data to be displayed in the resulting column. Click on "Add" to add the new column to the report.
If you need to edit or delete a column you have previously added, you can always click on "Modify" or "Remove".
The layout icon allows you to specify what columns should be visible in the report.
The Report Builder supports up to 15 custom fields. You can use the Customizable Data Export if you need more than 15 custom fields.
Click OK to save your changes.
You can add as many sort orders as columns in a report. Click on the "Sort" icon on the main menu, then select a data column and a sort order direction. Click on "Add" to save the sort order that you just specified. If you want to edit or delete a sort order, simply click on the corresponding "Replace" or "Remove" buttons.
You can add as many filters as columns you have in a report. Click on the "Filter" icon on the main menu, select the column you want your data to be filtered by, select a comparison value, and enter a term or value to filter by. Click on the "Add" button to save the filter you created. If you want to edit or delete a filter, simply click on the corresponding "Replace" or "Remove" buttons.
Below is an example of a filter created by the "Task" column, displaying only rows of time entry that contain the value "Research".
If you need multiple Filters, set up the first one and then click "Add" to apply it.
Next, create another filter (overwriting the "Value" field with the additional details) and click "Add" to add the second filter.
By default, Report Builder will create additional "AND" filters, so the more filters you add the fewer results you'll see. If you want to change an "AND" filter to an "OR", simply click on the "AND" text.
The Filter option can also be used to exclude time entries by using the "<>" option. For example, this Filter would not include any time entries where the words "Market Research" were included in the Project name:
You can also add parenthesis (using the arrows) to combine "AND" and "OR" filters. For example, if we wanted to create a report that was restricted to time entry for one Client, as well as to only pull data for three specified Projects within that Client, you could do so in the following method:
First, add all of your Filters - we recommend adding the Client filter first and then the Project filters (it will be easier if you don't need to change the order after you've created the filters). Then, click the "AND" text wherever you'd like to change it to "OR":
Next, on the second set of arrow options (which will actually be the third filter), click the arrow that points to the right:
This will add a parenthesis that starts on the row above it and ends on the row you clicked on:
You can now extend the parenthesis to the end of your selection by clicking the right-pointing arrow on the row(s) below:
In this example, there are only three Projects in the criteria, so no additional steps are needed to close the parenthesis and get this result:
If needed, continue extending the parenthesis so it captures all of the Projects you've added.
Groupings will group data by a specified column. Data will be indented in the report and various shades of gray will be used to visually represent the data that has been grouped. Click on "Add" to save the groupings you have created. Click on "Replace" to edit the grouping or "Remove" to delete a grouping you no longer want.
Using Aggregate will allow you to add totals of any fields with a number (Hours, Cost, etc). The most function is to calculate a "Sum".
With the Report Builder, you can also create different types of charts from your data. The image below shows you how you can create a pie chart that will display the allocation of hours by Client.
The Label Column represents the slices in the pie chart. Select Client as your Label Column. The Data Column will determine how each label (i.e. Client) will be represented in the chart. Select hours as your Data Column and specify sum as the aggregate. Click on "Add" to save the chart settings you just created. The pie chart will look similar to the image below.
Alternatively, you can create line charts that include regression trends. The image below shows you how you can create a line chart that will display a regression trend by hours by month.
Please note that the Charts will not export into another program. If you need to download/save a chart image, you can right-click the image and choose to either "Open image in new tab" or "Save image as..." to save it directly to your computer.
Additionally, you can download the data into Excel and use Excel's chart options to create a chart within your Excel workbook.
Create a Crosstab / Pivot Table
With the Report Builder, you can create crosstab reports. A crosstab is also called a pivot table. Each column of the crosstab table is represented by the unique values in the Header Values Column. Each row of the crosstab table is represented by the distinct values in the Label Values Column. The "cells" are derived from the Aggregate Values column by adding, counting, or averaging (depending on the Aggregate Function) all the values unique to each crosstab and Label Column.
In the example below, you can see a crosstab report that calculates the allocation of hours worked by every person for each Client. The columns in the report represent People (employees). For that, we selected the "Person" column in the Report Builder as the Header Value.
The rows in the report represent Clients. To obtain that, we selected "Clients" in the Label Values drop-down menu. To show that we wanted to calculate the number of hours worked by each employee for every Client, we selected "Hours" in the Aggregate Values Column and "Sum" from the Aggregate Function menu.
The resulting crosstab report will look similar to the one shown in the image below and will be displayed below the report you created with the Report Builder. You can build a custom crosstab with up to 100 columns. If you want to delete a crosstab, click on the "Remove" button next to it.
When using the "Entry Date" as your Header, you will also have the option to see totals by Day, Month, Quarter, and Year:
The above option will show you daily totals by person:
This section of the Report Builder allows you to specify the way you would like the report to be displayed on screen. If you want to see the entire report at once, select "Show all rows". If you want to see it broken down into pages, select "Show paging" and specify how many rows you want per page. With this option, a page navigation menu will be added to your report. Click "OK" to save your preferences.
|
Column |
Formula |
|
Billable Amount |
IIF([Billable] = 1,[Billing Rate] * [Hours],0) |
|
Month and Year |
MonthName(Month([Entry Date])) & " " & Year([Entry Date]) |
|
Week and Year (First of the Year through 7th of the Year) |
DatePart("ww", [Entry Date], 1, 1) & " " & Year([Entry Date]) |
|
Week and Year (Calendar Week - 2019) |
DatePart("ww",DateAdd("d",+2,[Entry Date])) & " " & Year([Entry Date]) |
|
Quarter |
IIF(Month([Entry Date]) <= 3,"Q1", IIF(Month([Entry Date]) >= 10,"Q4", IIF((Month([Entry Date]) >= 7 And Month([Entry Date]) <= 9),"Q3","Q2") ) ) & " " & Year([Entry Date]) |
|
Profit |
([Billing Rate] - [Cost]) * [Hours] |
|
Billable Profit |
IIF([Billable] = 1,[Profit],[CostAmount] * (-1)) |
|
Cost Amount |
([Cost] * [Hours]) |
Saving Changes to an existing report
To save changes to filters, sorting options, subtotals, charts, graphs, and other advanced tools of an existing report, click the "Save" button next to the drop-down menu.
Editing Report Title and Description
To edit a report's title or description, click the "Edit Title / Description" button found in the report's drop-down menu.
How to Save a Report as a New Report
The Report Builder lets you save multiple versions of a report without needing to build the report from scratch. To rename an existing report, select "Make A Copy" from the existing report, rename the new report, and click "Save" from the new report's drop-down menu.
You can share any of your saved reports with an Administrator or Manager with reporting permissions. Shared reports will respect Manager permissions, meaning if a Manager does not have permission to see billing rates in reports, billing rate data will be hidden in any reports shared with them.
To share a saved report, simply click the "Share" button located directly next to the "Save" button. This will open the report sharing pane where you can manage the list of people who may view your report. Viewers of a shared report are unable to make any changes to the original report; only the report's owner may edit the shared report. However, report viewers can save a copy of the shared report as their own.
How to View and Copy a Shared Report
Reports that are shared with you are listed in the Report Builder section of the Reports page in ClickTime.
To make a copy of a shared report, open the shared report and select the "Make a Copy" button found in the drop-down menu.
Deleting a report is irreversible. Once a specific report is deleted, you will no longer see it in your report lists.
To delete a report, open the existing report and select "Delete" from the drop-down menu. Then, confirm that you want to delete the specific report by clicking "OK".
- When using the "Export" option, the download page will be blank. Please make sure to "Save" your report before exporting if you want to access it again/change the criteria. Otherwise, you will need to re-create it after downloading.
- The option to change column order is not currently supported. If you need to change the order of your columns, please export the report to Excel and change the columns there.
- The option to change the order of your charts is not currently supported. We recommend downloading to Excel and changing the order there.
- Formatting of the Entry Date field may change depending on your computer or Excel settings. Please feel free to reach out to our Support Team if this is creating any issues for you and we'll be happy to assist as best we are able.
- You can enter the date range of your report at any time. The Calendar picker pop-up is not currently supported with Internet Explorer - please use another browser if you need to select the dates from the calendar.
- The "Start" and "End" Time data fields are not currently available in Report Builder. If you need to include these in a report, please use the Customizable Data Export and make sure to select those fields. Start/End Times will also be visible in the Horizontal and Vertical Timesheet reports.
- Report Builder will display up to 15 Custom Fields in the Layout tab (more information on Custom Fields can be found here). They will be sorted in alphabetical order by "Custom Field Name". You can change the internal "Custom Field Name" to include certain columns. If you need to create custom reports with data from more than 15 Custom Fields, please use the Customizable Data Export, include the necessary fields, then download and format the report in Excel.
- When using the "Group" option, the resulting CSV file may not be formatted in the manner you expected. To resolve, please either remove the Group option and export to CSV, or export to Excel to retain the formatting.
- When using the Pie and Bar Charts options, the charts will display up to 10 values - anything that is less than the top 9 values will be summarized as "Other".
Comments
0 comments
Article is closed for comments.