Creating Custom Metrics for Insights

Learn how to create custom metrics for your Insights reports and dashboards.

 
Permissions Needed: Access Groups: Insights - OR - Reports Viewer (or higher) and Insights Can Edit permission

Overview

You can create custom metrics for your Insights reports and dashboards—by either creating a brand-new metric from scratch or cloning an existing metric and modifying it to meet your needs. This article provides general how-tos for creating custom metrics and provides guidance for creating some common custom metrics.

If you need to create more advanced custom metrics or have specific business needs, please contact your Business Intelligence Consultant or Support.

Getting Started with the Metric Building Language

Metrics are written using MAQL (Multi-Dimension Analytical Query Language), a query language similar to SQL. All metrics in MAQL start with the keyword SELECT.

The Fees Actual metric is an example of a simple aggregation (turning a fact into a metric):
SELECT SUM(Time Entry: Fees in Cents/Currency: Base Unit)

Metrics can include the basic arithmetic operations:
addition (+), subtraction (-), multiplication (*), and division (/)

Examples:

Bill Rate: Allocations
SELECT Fees Allocated / Hours Allocated

Margin: Fees
SELECT Fees Actual - Cost Actual + Expenses: Non-Billable

Relational and logical operators are also supported:
<, >, =, <=, >=, !=, AND, OR

In the metric editor, different colors represent different elements:

•      Attributes appear in purple
•      Attribute values appear in orange
•      Metrics appear in green
•      Facts appear in blue

For a list of available functions and operators, see the following external articles:

• Mathematical Functions
• Aggregation Functions
• Logical Operators
• Relational Operators
• Conditional Statements
• Filter Expressions

How to Create a Custom Metric from Scratch

You can create custom metrics in the Advanced Editor, and they will be available for use in both dynamic and classic reports.

In this example, we will create a custom metric from scratch to display a number custom field called Accounting Code.

Here is the metric definition: SELECT SUM( Accounting Code)

1. Navigate to the Advanced Editor.
2. Select the Manage tab.
   
3. In the Data section located on the left, select Metrics.
   
4. In the top-right corner of the metrics list, select Create Metric.
   
5. In the Metric Editor window, select Custom metric.
   
6. Enter a name for the custom metric.
   Tip: We recommend starting your custom metrics with a special character (e.g. ! ) to differentiate them from standard metrics.
7. In the editor, type SELECT SUM( .
   
8. In the Elements section to the right of the editor, select Facts.
   
9. Search for Accounting Code. The custom field appears under a Project Custom Fields heading.
10. Select the fact, then select Add Selected. The fact is added in the editor.
    
11. In the editor, type ) . This closes the SUM function.
12. Select Add at the lower-right of the Metric Editor.
    

Once you finish creating a custom metric, it can be added to reports like any other metric:

• In the report editor for dynamic dashboards, the custom metric appears in the metrics (   ) list. See the Using the Report Editor for Insights Dynamic Dashboards article for more information.
• In the report editor for classic dashboards, the custom metric appears in the What list. See the Insights Classic: Edit a Report article for more information.

How to Clone and Modify an Existing Metric

When you need to make some adjustments to an existing metric for your use case, you can save time by duplicating and modifying the existing metric instead of creating a custom metric from scratch.

In this example, we will clone and modify the standard Fees Actual: Invoiced, Paid metric to look at invoices due in 2024 only.

Here is the metric definition:

SELECT SUM (Time Entry: Fees In Cents/Currency: Base Unit) 
WHERE Invoice: Payment Status = paid 
AND Year (Invoice Due) = 2024

1. Navigate to the Advanced Editor.
2. Select the Manage tab.
3. In the Data section located on the left, select Metrics.
4. Search for the Fees Actual: Invoiced, Paid metric then select it.
   
5. On the metric page, select Duplicate.
   
   The metric is duplicated and the custom metric page opens.
6. Update the custom metric’s name to Fees Actual: Invoiced, Paid - 2024 only.
7. Select Save.
   
8. Update the description to Calculates sum of actual fees that appear on an invoice, for invoices created in 2024.
9. Select Save.
   
10. Select Edit to open the metric editor.
    
11. In the editor, type AND at the end of the metric definition.
    
12. In the Elements section to the right of the editor, select Attributes.
    
    
13. Search for Invoice Due.
14. Select the Year (Invoice Due) attribute, then select Add Selected. The attribute is added in the editor.
    
15. In the editor, type = 2024 .
    
16. Select Save.
    

How to Create a Quick Custom Metric in a Dynamic Report

When working with dynamic reports, you can create a quick custom metric to do simple calculations. The following restrictions apply to quick custom metrics:

• Quick custom metrics are not reusable and will not be available to use on other reports.
• The metrics or facts used in the quick custom metric must also be added to the report; this may clutter the report.
• Quick custom metrics are limited to one mathematical operation; more complex calculations cannot be done.

For these reasons, we typically recommend creating reusable custom metrics via the Advanced Editor; however, a quick custom metric may be useful for simple calculations.

In this example, we will create a quick custom metric that gets the difference between the Hours Allocated and Hours Scheduled metrics.

1. Create or edit a dynamic report.
2. Add at least two metrics or facts to the report.
   In the Metrics section, a Plus (“   ”) icon appears.
   
3. Hover over the Plus (“   ”) icon, then select Create metric.
   
4. Enter a name for the quick custom metric. Press Enter to save the name.
   
5. In the Outcome is drop-down menu, select the calculation to perform with the metrics:
   
   • Sum
   • Difference
   • Ratio
   • Product (Multiplication)
   • Change
     
6. In the Where section, select the A and B metrics. Below the Outcome is menu, you can see how the A metric and B metric will be used in the calculation.
   
   The report regenerates to show the new quick custom metric.
7. In the Format menu, select an option for formatting the metric.
   
8. Select Save to the upper right to save the report.

Formatting Metrics

After creating a custom metric, you can format it to ensure that it appears in a certain way whenever it gets added to reports. For example, you can specify the number of decimals, truncate large numbers, apply text and background colors, and more. See the Insights Classic: Formatting Metrics article for more information.

The metric format defined in the Advanced Editor will apply to the metric when it is added to either classic and dynamic reports. You can also update how the metric is formatted within an individual report. See the following articles for more information:

• For the dynamic report editor: How to Format Metrics in a Report
• For the classic report editor: How to Format a Metric in One Report Only

How to Make a Custom Metric Visible to Users

When you create a custom metric, it is initially hidden and only visible to you. You can make the custom metric visible to all users to allow other report editors to see and add the metric while building reports.

1. On the custom metric page, select Sharing & Permissions.
   
2. Check Visible to all users.
   
3. Select Save Permissions.
   

Metric Folders

You can organize custom metrics into folders to group related metrics.

How to Create a Metric Folder

1. Navigate to the Advanced Editor.
2. Select the Manage tab.
3. In the Data section located on the left, select Metrics.
4. In the Folder section located on the left, select Add Folder.
   
5. Enter a name for the folder.
   Tip: We recommend starting your folders with a special character (e.g. ! ) to differentiate them from the standard folders.
6. Select Add.
   
   

How to Move a Single Custom Metric

1. On the custom metric page, select Move to Folder.
   
2. Select the folder to move the metric to. Changes are saved automatically.
   

How to Move Multiple Custom Metrics

1. Navigate to the Advanced Editor.
2. Select the Manage tab.
3. In the Data section located on the left, select Metrics.
4. In the metrics table, check all the custom metrics that you want to move.
   
5. Select Move… at the top of the table.
   
6. Select the folder to move the metrics to.
   

Custom Metric Examples

See the examples below of commonly created custom metrics.

Scheduled Hours Greater Than Allocated Hours

Here’s an example of a custom metric that compares allocated and scheduled hours. When scheduled hours are greater than allocated hours, the custom metric displays a red Y; otherwise, it displays a black N.

Here is the metric definition:

SELECT IF (Hours Scheduled > Hours Allocated) THEN 1 ELSE 0 END

Here is the metric format:

[=1][red]Y;[=0][black]N

To create this metric, follow these step-by-step instructions:

1. Navigate to the Advanced Editor.
2. Select the Manage tab.
3. In the Data section located on the left, select Metrics.
4. In the top-right corner of the metrics list, select Create Metric.
5. In the Metric Editor window, select Custom metric.
6. In Name your metric, enter Scheduled Hours Greater Than Allocated Hours.
7. Now we are going to compare scheduled and allocated hours.
   1. In the editor, type SELECT IF.
   2. In the Elements section to the right of the editor, select Metrics.
   3. Search for Hours Scheduled.
   4. Select the metric, then select Add Selected. The metric is added in the editor.
   5. In the editor, type > .
   6. Search for Hours Allocated.
   7. Select the metric, then select Add Selected. The metric is added in the editor.
8. Now we are going to define what the custom metric returns when scheduled hours are greater than allocated hours, and vice versa.
   1. In the editor, type THEN 1 ELSE 0 END.
      
9. Select Add at the lower-right of the Metric Editor to save the custom metric.
   The metric page opens.
10. Now we are going to apply formatting to display either a red Y or black N.
    1. In the Metric Format section, select Edit.
    2. In the Number format editor, enter [=1][red]Y;[=0][black]N.
       
    3. Select Apply in the lower-right.

Here is the custom metric in a sample report:

Changing the Hours Actual Metric to Only Look at Time Entries Up to the End of Last Week

Here’s an example of modifying the Hours Actual standard metric to only look at actual hours up to the end of last week.

Here is the metric definition:

SELECT SUM(Time Entry: Time In Minutes)/60 
WHERE Week (Sun-Sat) (Time Entry) < THIS

To create this metric, follow these step-by-step instructions:

1. Navigate to the Advanced Editor.
2. Select the Manage tab.
3. In the Data section located on the left, select Metrics.
4. Search for the Hours Actual metric, then select it.
   
5. Select Duplicate.
6. Rename the metric to Hours Actual, Up to Last Week.
7. Select Edit to open the metric editor.
8. Now we are going to add a condition that only time entries up to last week are included in the custom metric.
   1. In the editor, type WHERE at the end of the metric definition.
   2. In the Elements section to the right of the editor, select Attributes.
   3. Search for Week (Sun-Sat) (Time Entry).
   4. Select the attribute, then select Add Selected. The attribute is added in the editor.
   5. In the editor, type < THIS . In this comparison, "THIS" refers to this week.
      
9. Select Save.

Here is the custom metric in a sample report:

Duration Between Two Dates

Here is an example of a custom metric that calculates the duration between two date custom fields. The custom fields are for tasks and are called Sync Start Date and Sync End Date.

Here is the metric definition:

SELECT Date (Sync End Date) - Date (Sync Start Date) 
BY Task: Name 
WHERE Date (Sync Start Date) != (empty value) 
AND Date (Sync End Date) != (empty value)

To create this metric, follow these step-by-step instructions:

1. Navigate to the Advanced Editor.
2. Select the Manage tab.
3. In the Data section located on the left, select Metrics.
4. In the top-right corner of the metrics list, select Create Metric.
5. In the Metric Editor window, select Custom metric.
6. In Name your metric, enter Sync Duration.
7. In the editor, type SELECT .
8. Now we are going to add the calculation to get the duration.
   1. In the Elements section to the right of the editor, select Attributes.
   2. Search for the date custom field that is the later date: Sync End Date.
   3. Select the Date (Sync End Date) field. This will use the actual date in the calculation.
   4. Select Add Selected. The custom field is added in the editor.
   5. In the editor, type - .
   6. Search for the date custom field that is the earlier date: Sync Start Date.
   7. Select the Date (Sync Start Date) field. This will use the actual date in the calculation.
   8. Select Add Selected. The custom field is added in the editor.
9. Now we are going to add a BY clause to specify that the metric only breaks down by task name and not by any other attributes that may be added to a report.
   1. In the editor, type BY.
   2. In the Elements section to the right of the editor, select Attributes.
   3. Search for Task: Name.
   4. Select the attribute, then select Add Selected. The attribute is added in the editor.
10. Now we are going to add a conditional so that the duration is only calculated when the task has values for both date custom fields.
    1. In the editor, type WHERE .
    2. In the Elements section to the right of the editor, select Attributes.
    3. Search for the date custom field that is the earlier date: Sync Start Date.
    4. Select the Date (Sync Start Date) field.
    5. Select Add Selected. The custom field is added in the editor.
    6. In the editor, type != .
    7. In the Elements section to the right of the editor, select Attribute Values.
    8. Search for the date custom field that is the earlier date: Sync Start Date.
    9. Select the Date (Sync Start Date) field. A list of values opens.
    10. Select (empty value).
    11. In the editor, type AND .
    12. Repeat substeps a-j for the other date field: Sync End Date.
        
11. Select Add at the lower-right of the Metric Editor to save the custom metric.

Here is the custom metric in a sample report:

Days Since Date in a Custom Field

Here is an example of a custom metric that calculates how many days have passed since the date entered in a date custom field.

Here is the metric definition:

SELECT THIS - Date (Sync Start Date) 
WHERE Date (Sync Start Date) != (empty value) 
AND Date (Sync Start Date) < THIS

To create this metric, follow these step-by-step instructions:

1. Navigate to the Advanced Editor.
2. Select the Manage tab.
3. In the Data section located on the left, select Metrics.
4. In the top-right corner of the metrics list, select Create Metric.
5. In the Metric Editor window, select Custom metric.
6. In Name your metric, enter Days Since Sync Start.
7. Now we are going to add the calculation to get the days passed.
   1. In the editor, type SELECT THIS - . In this calculation, "THIS" refers to today.
   2. In the Elements section to the right of the editor, select Attributes.
   3. Search for Sync Start Date.
   4. Select the Date (Sync Start Date) attribute. This will use the actual date in the calculation.
   5. Select Add Selected. The attribute is added in the editor.
8. Now we are going to specify that the date custom field must have a value and must be earlier than today.
   
   1. In the editor, type WHERE .
   2. In the Elements section to the right of the editor, select Attributes.
   3. Search for Sync Start Date.
   4. Select the Date (Sync Start Date) attribute. This will use the actual date in the calculation.
   5. Select Add Selected. The attribute is added in the editor.
   6. In the editor, type != .
   7. In the Elements section to the right of the editor, select Attribute Values.
   8. Search for Sync Start Date.
   9. Select Date (Sync Start Date). A list of values opens.
   10. Select (empty value), then select Add Selected. The attribute value is added in the editor.
   11. In the editor, type AND .
   12. In the Elements section to the right of the editor, select Attributes.
   13. Search for Sync Start Date.
   14. Select the Date (Sync Start Date) attribute. This will use the actual date in the comparison.
   15. Select Add Selected. The attribute is added in the editor.
   16. In the editor, type < THIS . In this comparison, "THIS" refers to today.
       
9. Select Add at the lower-right of the Metric Editor to save the custom metric.

Here is the custom metric in a sample report:

To create a "days until" custom metric instead, you would flip the calculation and flip the boolean comparison:

SELECT Date (Sync Start Date) - THIS
WHERE Date (Sync Start Date) != (empty value) 
AND Date (Sync Start Date) > THIS

Actual Hours from All Consultants

Here is an example of a custom metric that sums up actual hours from all users with an account role with the word “consultant” in it (e.g. Sr. Consultant, Staff Consultant, Jr. Consultant, etc.).

Here is the metric definition:

SELECT Hours Actual WHERE Role Name [Role: Name] 
ILIKE "%consultant%"

To create this metric, follow these step-by-step instructions:

1. Navigate to the Advanced Editor.
2. Select the Manage tab.
3. In the Data section located on the left, select Metrics.
4. In the top-right corner of the metrics list, select Create Metric.
5. In the Metric Editor window, select Custom metric.
6. In Name your metric, enter Actual Hours, All Consultants.
7. In the editor, type SELECT .
8. Now we are going to aggregate actual hours.
   1. In the Elements section to the right of the editor, select Metrics.
   2. Search for Hours Actual.
   3. Select the metric, then select Add Selected. The metric is added in the editor.
9. Now we are going to add a condition that the user’s account role contains the word "consultant".
   1. In the editor, type WHERE .
   2. In the Elements section to the right of the editor, select Attribute Labels.
   3. Search for Role: Name.
   4. Select the Role: Name attribute. A list of labels opens.
   5. Select the Role Name attribute label, then select Add Selected. The attribute label is added in the editor.
   6. In the editor, type ILIKE "%consultant%" . "ILIKE" is a case insensitive filter, which means it will match role names with both "consultant" and "Consultant".
      
10. Select Add at the lower-right of the Metric Editor to save the custom metric.

Here is the custom metric in a sample report:

Number of Projects with Custom Field Value

Here is an example of a custom metric that counts how many projects have a certain value in a custom field. This example uses a custom field called Product Area and a custom field value Project Management.

Here is the metric definition:

SELECT COUNT(Project: Name) WHERE Product Area = Project Management

To create this metric, follow these step-by-step instructions:

1. Navigate to the Advanced Editor.
2. Select the Manage tab.
3. In the Data section located on the left, select Metrics.
4. In the top-right corner of the metrics list, select Create Metric.
5. In the Metric Editor window, select Custom metric.
6. In Name your metric, enter Number of Projects: Project Management.
7. Now we are going to add the function to count projects.
   
   1. In the editor, type SELECT COUNT( .
   2. In the Elements section to the right of the editor, select Attributes.
   3. Search for Project: Name.
   4. Select the Project: Name attribute, then select Add Selected. The attribute is added in the editor.
   5. In the editor, type ) .
8. Now we are going to add a conditional to only count projects that have the value “Project Management” in the “Product Area” custom field.
   1. In the editor, type WHERE .
   2. In the Elements section to the right of the editor, select Attributes.
   3. Search for Product Area.
   4. Select the Product Area attribute, then select Add Selected. The attribute is added in the editor.
   5. In the editor, type = .
   6. In the Elements section to the right of the editor, select Attribute Values.
   7. Search for Product Area.
   8. Select the Product Area attribute. A list of values opens.
   9. Select the Project Management value, then select Add Selected. The custom field value is added in the editor.
      
9. Select Add at the lower-right of the Metric Editor to save the custom metric.
   The metric page opens.
10. Now we are going to apply formatting to display the count without any decimals.
    1. In the Metric Format section, select Edit.
    2. In the Number format editor, enter #,##0 .
       
    3. Select Apply in the lower-right.

Here is the custom metric in a sample report: