Table of Contents

How To Enhance Your Reporting With Custom Field Data

Mitchell Paul-Soumis Updated by Mitchell Paul-Soumis

Read Time: 5 mins

Custom Fields are a unique way of storing data on various entities within your Sonar instance, but extracting the information contained within them can sometimes be a challenge. Ordinarily, you would need to check each customer account for what was entered into the custom field, but with Sonar's Business Intelligence Reporting, you're able to create a custom report that shows you exactly what's contained in each custom field on an entity.

Before creating a custom report to extract custom field data, there are a few prerequisites. First, it's recommended you familiarize yourself with the reporting dashboard, and second, you'll need your user profile to have a Report License assigned to it.

Creating the Report

As with any custom report, the first step is to access the Reports dashboard then click on "Create Report":

This will open the creation modal, where you'll need to name your report and select the report category it belongs to.

This name is up to you but will be seen by other users in your instance if you choose to publish this report.

For this example, a new report titled "Custom Field test" belonging to the "Accounts" report category will be created:

In order for your custom report to access the Custom Field Data endpoints, the Report Category needs to be set to Accounts. The Financial category has a distinct set of data endpoints.

Configuring the Dashboard

Once you've created the report, you'll be redirected to the dashboard for this new report. Configuring the dashboard allows you to set which data points will appear in the report. In order to extract custom field data, specific data points need to be extracted. To get started, click on "Edit Dashboard":

A new bar will populate toward the top of the page. Once visible, select "Add" and then click on "Visualization".

This will open the "Explore" modal, where you need to select the explore category that best matches your needs in this report. Because Custom Fields exist across most entities in Sonar, the Custom Field Data Tables are available from multiple groups. For this first tile, the "Accounts" explore group will be selected for demonstration:

While there are numerous explore categories and tiles to select within them, this example will focus solely on the Custom Field tiles. For more information on creating Reports in general, have a look at the cast video | How to Create a Report - Sonar's Business Intelligence

Within the "Accounts" explore category, there are several listings that control Custom Field Data for each entity and they do the following:

  1. CustomFieldData
    1. This is the default Custom Field Data data table. It allows you to extract basic information from the information stored within Custom Fields on the entity, such as the field value, field ID, field type, its creation date, and when the field was last updated.
  2. CustomFieldData > CustomFields
    1. This data table will show information on the Custom Field itself, such as its name, its creation ID, whether or not it's required, and more.
  3. CustomFieldDataX (X being a number 1 through 5)
    1. This data table follows a similar format to the CustomFieldData data table, with an input filter added directly to the data table. With the addition of multiple identical tables and an input filter, you're able to display the results of multiple Custom Field Data queries side by side in a single table.
When extracting data from custom fields, please note that Custom Fields of the "Boolean" type will not be reported as containing a "Yes" or "No". Instead, the values in these custom fields will be reported as 1 (for "Yes") or 0 (for "No").

Report creation example using only the CustomFieldData data table

In this example, the tile will be generated as a table using only two data tables - the Accounts data table in order to extract the account ID and account name, and the CustomFieldData data table, in order to extract the information stored in Custom Fields, filtered to only show accounts with a custom field data value:

You can see that with this generated report, the extracted data is from every account and every custom field with data. While in small numbers this type of data is accessible for reporting purposes, as your organization grows and acquires more accounts necessitating custom fields, having this information concentrated in a single column is disadvantageous. While you could use the "Name" field from the "CustomFieldData > CustomFields" data table to provide more information, larger datasets will still be difficult to filter and compare. This is where the additional numbered CustomFieldData data tables come in handy.

Report creation example using numbered CustomFieldData data tables

In this example, we'll be creating a new tile that will be generated using multiple numbered Custom Field Data data tables. Each of these columns will be renamed to indicate the Custom Field it belongs to, and the "Custom Field ID Input" filter-only field will be used in order to show each custom field.

Because the filter-only field requires the Custom Field ID in order to correctly list the Custom Field, you'll need to make sure you have the desired IDs before creating this tile. You can do that through the user interface by going to Settings > Misc > Custom Fields and reviewing the IDs there, or through the GraphQL API.

The resulting table will allow you to view all accounts in your instance alongside all possible custom field values. While this example is limited to "Account" entity custom fields, using the numbered CustomFieldData data tables would allow you to extract the information stored in Custom Fields on other entities, such as Addresses and Jobs on the Account.

Overriding the column headers isn't required, however, it does simplify the report for viewing by those who are less familiar with the data endpoints and data tables in your Sonar instance.

While the numbered CustomFieldData data tables do generally provide an advantage over the standard one, there are still some limitations of this report. As a result of multiple columns containing distinct information, it's difficult to filter out accounts that contain no data within your instance. For these cases, exporting the report and filtering it in your preferred spreadsheet application is recommended.

The granularity offered by extracting the value of each custom field as a unique column allows you to more accurately report on data points for measuring business growth, such as the effectiveness of different advertisement strategies, monitoring referral data, and monitoring customer health through stored happiness scores and the like.

How did we do?

Enhanced Business Intelligence - Tips & Tricks for Advanced Users

Report Licenses