Gtmhub allows you to build insights which can be filtered by different values. Typically, those filters are date ranges or predefined values. 

Often times, though, one needs to filter by values which are not known in advance or can change over time. For example, let us imagine an insight which shows the number of sales activities. If we want to be able to filter the number of sales activities for one or more sales representatives, we'll need to add a filter.

Because sales representatives come and go and because the list of sales representatives already exists in a CRM, we can define a new filter which will dynamically load sales representatives - or in other words - we can define a dynamic filter.

How to define a dynamic filter?

To define a dynamic filter, you will need to have at least one data source connected. In this article, we will explain how to define a dynamic filter on entities available in our Demo masterplan.

  • Log in to Gtmhub
  • Navigate to Setup > Configuration
  • From the left menu, select Insightboard Parameters
  • Click on the Add new parameter button

In the Define new parameter dialog, set the values as follows:

  • Title: Sales rep
    This is what end-user will see in insightboards.
  • Key: sales_rep
    This is the programmatic name of the filter that will be used in algorithms (SQL or R).
  • Selector: List
    This means that we will filter only by one single value. If you want to be able to filter by more than one value, use Multi-select drop-down here.
  • Under Dynamic values, turn on Use connected entity values switch
  • From Entity type drop-down select employees
    This is the entity (or table) from which dynamic values will be populated.
  • Select Email for both Title and Key drop-downs
    The title is what end-user will see in the filter selectors. Key is the value that will be replaced in the algorithm. In this case, we are using Email for both; but often you will choose something like Name for Title and ID for the key.
  • Click on the Create parameter definition button

How to use dynamic filters in your insights?

When you develop insights, you can use placeholders which will be replaced by the values of filters when end-user filters.

To demonstrate how to use our Sales rep filter, we will create a new insight which will display the number of sales activities and will allow end-user to filter by the sales rep.

If you are not sure how to create a new insight, you can check out this article.

Insight markup:

<div class="title o-5 mb-2">Sales activity count</div>
<div class="title-xlg positive">
    <metric field-name="activities" name="Sales activity count">
      {{ data.activities | number }}
    </metric>
</div>

Insight algorithm (SQL):

SELECT COUNT(*) as activities FROM sales_activity
WHERE
    CASE WHEN '%%sales_rep%%' <> ''
        THEN (sales_rep_email = '%%sales_rep%%')
        ELSE true
    END

In the SQL algorithm, we are using CASE syntax to apply the filter only if user has selected a sales rep; otherwise, we want to show the count for all sales representatives.

Using the dynamic filters

Dynamic filters look the same to the end-user.

In the image above we can see our insight, which shows the number of sales activities performed without the sales rep filter applied.

Once the user selects the sales rep, the insight will automatically be recalculated taking into account the filter value.

Did this answer your question?