Table of Contents
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.
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.
For this example, a new report titled "Custom Field test" belonging to the "Accounts" report category will be created:
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:
Within the "Accounts" explore category, there are several listings that control Custom Field Data for each entity and they do the following:
- 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.
- CustomFieldData > CustomFields
- 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.
- CustomFieldDataX (X being a number 1 through 5)
- 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.
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.
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.
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.