Understand the custom report builder

When creating a report in the custom report builder, you’ll need to decide which data sources to select, how to configure your report fields, and how to configure your chart settings.

Below, learn about the different aspects of building a report and how to set up your report. For a video walkthrough of the custom report builder, including example reports, check out the Building Custom Reports in HubSpot lesson in HubSpot Academy.

Data sources

The first step of building a report is to select which data sources to include. Data sources are the objects, assets, and events that you want to report on. The primary data source will determine the focus of the report, with all other data sources relating to that source. Whether a data source is primary or secondary will have a significant impact on the data returned in your report.

Data join summary

When you're selecting the data sources to include in a report in the custom report builder, you can view an overview of how the data sources are being joined, as well as what records will be included or excluded from your report based on your selection. You can also preview your selected data sources in a preview table.

To view the Data join info, click first to expand the right side panel. The Data join info panel will display a breakdown of how your selected data sources are joined. Using this data, you can confirm if the correct data sources have been selected and this will enable you to view the total number of records available to include in your report, which is based on each data source's association to the Primary data source.

To view the Data preview, click up to expand the bottom panel. The Data preview panel will display an in-depth preview of your data. The table will populate with properties for each selected data source.

Connecting multiple data sources

Internally, HubSpot stores each source's data in separate tables, similar to a set of spreadsheets. When you select multiple data sources, HubSpot may need to use additional sources to connect the tables. For example, contacts and deals can be joined directly without connecting additional sources, as those objects are closely associated in HubSpot. However, some data sources require connective data sources. For example, if your report's primary source is Deals and you want to include blog post data in the report, the Contacts and Web activities sources will also be selected automatically.

In the report builder, connective data sources will appear with a grey check mark to indicate automatic selection. Automatically selected sources count towards the 5 possible sources for your report.

When HubSpot cannot create a path between data sources, those sources will become unavailable for selection. For example, if your primary source is Contacts and you select Blog posts as a secondary source, the Marketing email source will become greyed out and unavailable. 

Understanding your data

When reporting on multiple data sources, it's important to understand how HubSpot breaks them down. For example, you're building a report that measures individual contacts by their count of deals in a table. In your table, you include two columns: Contact ID and Count of deals. If you select Contacts as the primary source and Deals as a secondary source, the report will show a list of your contacts and the number of deals associated with each. This table will not include any deals that aren’t associated with a contact.

If you were to change the primary source to Deals and the secondary source to Contacts, the report will show the number of deals associated with each contact, but will also include a row with (No value) listed in the Contact ID column that includes all of the deals not associated with contacts. The table total would then show a higher total sum of Count of deals

Also, when a report includes multiple data sources, a record might be counted more than once depending on the filters added. This occurs because associated records can meet filter criteria more than once per association. For example, a report has a primary data source of Companies and a secondary source of Contacts. If a company is associated with two contacts:

• If you add the Create date (Contact) field, the report will display contacts with a known create date that are associated with a company. Therefore, the company will be counted twice because it’s associated with two contacts with a known create date. For example, in the screenshot below each contact is counted even though they are associated to the same company.
  
  
• If you add the Create date (Company) field, the report will display companies with a known create date. The number of contacts associated with each company can be viewed by clicking on the report and drilling into the data. For example, each company will be counted even if there are no contacts associated.

Fields

Fields in the report builder can be set as one of two types: dimensions and measures. The field's type reflects whether an aggregation is performed on the field's data. Setting an aggregation method on a field can be helpful if you want to return the field's data in a specific way, such as a sum or an average. For example, you may want to view the revenue of your deals as a sum for each type of deal to understand which types of deals result in the most revenue.

Please note: any NULLs of fields with no values will not be counted in aggregations.

• Dimension: a field with no aggregation, displayed as grey. Dimensions can be any data type, including dates, numbers, booleans, and strings. This type of field can only be added to the X-axis and Break down by slot. 
  
  
• Measure: a field with a set aggregation method, displayed as green. Measures are typically numerical or quantitative values, such as the number of associated deals. This type of field can only be added to the Y-axis. 
  
  

By default, most fields are dimensions, with no aggregation method. However, you can adjust a field’s aggregation method by editing the field. The aggregation method can be set to any of the following:

• None: no aggregation is performed on the field's data, and the field is set as a dimension.
• Distinct count: returns the field’s count of distinct values, as opposed to including duplicate values. Available for all field types. Using Distinct count can result in the visualization displaying fewer data points than expected. For example, a deal report includes a Date contract signed field set to Distinct count. If there are two deals with the same Date contract signed date, the field will calculate them as a distinct count of 1. Drilling into the report will display both deal records. 
  
  
• Sum: returns the field's data as a total. Available for number field types.
• Average: returns the average of the field's values. Available for number field types.
• Median: arranges values from smallest to largest and returns the middle value. Available for number and date field types.
• Min: returns the field's smallest value. Available for number and date field types.
• Max: returns the field's largest value. Available for number and date field types.

Aggregation methods also determine how data appears in visualizations. For example, tables will only display totals for columns that use measure fields with the aggregation method set to Sum.

Rollup fields

When building a report with more than one data source, the report builder provides rollup fields to calculate data between associated objects.

For example, in a report where Contacts is the primary source and Companies is a secondary source, the company field Number of page views is a rollup property that calculates the number of page views for a given company based on associated contact activity. This type of field displays in yellow when hovered over in the left sidebar.

Using these fields in a report can result in unexpected data. In the above example, you may instead want to use the contact field Number of page views, because the contact field will calculate the number of page views per individual contact in the report. Using the company field Number of page views would instead result in displaying page view data for a contact based on all the page views associated with its associated company.

Chart visualizations

When building a report, you’ll need to choose a chart type and any additional settings to configure how your data is visualized. Below, learn about chart axes along with the available chart settings.

Chart axes

When adding fields to a report that uses a bar, line, area, or combination chart, you can choose which axis a field's data will be displayed on. In general, the X-axis is recommended for fields that contain category or text-based information, such as Lifecycle stage, while the Y-axis is recommended for fields that contain numeric information, such as Count of contacts.

Whether a field has aggregation will also determine where it can be placed on a given chart.

For horizontal bar charts, dimensions, which have no aggregation, can only be added to the Y-axis and Break down by slots. Measures, which have an aggregation method, can only be added to the X-axis slot."

Multiple Y-axes

Bar, line, area, and combination charts can be configured to display multiple Y-axes. This can be useful when you want to compare two fields that have different scales but use the same X-axis data. For example, you could use multiple Y-axes to compare the count of contacts and sum of associated deals per lifecycle stage.

You can then configure each Y-axis separately within the chart's settings. 

Compare by

Bar, line, area, KPI, and gauge charts can be configured to display comparative data. Click and drag a date field to the Compare by section to set a date range for comparing your data.

"Too many data points requested" error troubleshooting

When adding a field with a large amount of data to the Break down by channel, the report builder might return a Too many data points requested error.

To resolve this error, it's recommended to avoid breaking down a report by a field with a large amount of data, such as First name. However, you can also the following steps to reduce the amount of data returned:

• Click the down down arrow icon next to the Break down by field, then click Set limit. 
  
  
• Add a filter to the report to limit the Break down by channel to a certain number of data points. For example, if you're breaking your report down by Contact owner, you can add a filter to only include the most relevant HubSpot users. 
  
  
• Similarly, add a filter to one of the other fields. For example, you could reduce the time frame of your report by adding a Create date is after 1/20/2022 filter.