Now that we know how to retrieve the value of a custom field in an insight, lets see how to create an insightboard parameter containing its all possible values.
You have two options:
Insightboard parameter with predefined values - if you know the exact values of the custom field and it is not likely the available values to change, you can simply list all the available options
Insightboard parameter with dynamic values - when you do not have all available values of the custom field or the values are likely to change often.
Lets learn more about the second option. The information in Quantive Results entities is kept in a json string and if we create an insightboard parameter using custom_fields field as a title/value of the parameter definition, the result will not be readable for the end user. Following these steps will lead us to the desired result:
1. First step is to find a way to extract the field values from the json string. Virtual data sources are the perfect solution for this case. Here is an example code of the VDS extracting custom fields values:
SELECT DISTINCT
CASE
WHEN customfields <> ''
THEN (customfields::json ->> '<field_name>')
ELSE ''
END AS <field_name>
FROM <quantive_results_entity>
where <field_name> should be replaced with the name of the custom field and <quantive_results_entity> with the entity the fields is part of.
2. Next, use the virtual data source for creating the insightboard parameter. When creating the parameter, select dynamic values option. For entity type select the created in step 1. Virtual Data Source and for title/values - the available option from the drop-down menu.
3. Last step would be to add the parameter in the SQL code for your insight. Here we have a detailed guide on writing an insight with insightboard parameter.
Example
Let's review a real case. We have a custom field named region on user level. As an administrator of the platform I would like to be able to find which are the employees for every region.
1. Navigate to Insights > Data Sources and click on "Add new virtual data source" button:
Give a name of the data source: user_region
Build the entity using this SQL code:
SELECT DISTINCT
CASE
WHEN customfields <> ''THEN (customfields::json ->> 'region')
ELSE 'No Region'
END AS region
FROM quantiveresultsusers
Execute to verify the information:
2. Navigate to Settings > Insightboard parameters and click on "Add new parameter":
Title: Region
Key: region
Selector: Multi-select drop-down
Dynamic values: enabled
Entity type: user_region
Title field: region
Value field: region
3. Navigate to Insights > Insightboards and click on Manage Insights menu. Click on "Create new insight" and select "Use SQL expect editor" option.
Title: Users and Regions
SQL code for this Insight:
SELECT * FROM (
SELECT
CASE
WHEN customfields <> ''
THEN (customfields::json ->> 'region')
ELSE 'No Region'
END AS region,
id,
firstname
FROM quantiveresultsusers
) all_users
WHERE CASE
WHEN '%%region%%' <> ''
THEN region = ANY(string_to_array('%%region%%', ','))
ELSE true
END
ORDER BY firstname
HTML code:
<div id="users-per-region" ng-init="users = data == undefined ? [] : data.default;">
<div class="bigger-150 bold">{{insight.title}}</div>
<table mat-table class="table mt-2">
<tr>
<th ng-click="orderKey = 'firstname'; reverse = !reverse;">Employee</th>
<th ng-click="orderKey = 'region'; reverse = !reverse;">Region</th>
</tr>
<tr ng-repeat="user in users | orderBy : orderKey : reverse">
<td>
<assignee id="{{user.id}}"></assignee>
</td>
<td>{{user.region}}</td>
</tr>
</table>
</div>
And this is how our insight looks like:
