Bind to ColumnMap

If you are new to bindings aka advanced interactions in CRM Analytics I recognize this title might be slightly confusing, but if you have tried to do measure or group bindings in compact form you will know that the columnMap parameter is an annoying little thing you must consider. The approach has now changed, so let’s have a look.

What has changed?

The product team has been working on the dashboard JSON and has slowly converted the aggregateflex queries to be using compact form 2.0 instead of 1.0 for efficiency purposes. You can read more about that in the Spring 23 release notes. With this change they have now also made it possible for you to bind to the columnMap parameter in the widget (and widget section of your query).

Why is this interesting? Well, until now whenever we created group and measure bindings we had to find the columnMap section in the dashboard JSON and set it to null. I wrote about this in a previous blog, if you want a refresher on how this is done. The impact of doing this is great when we have multiple measures or groupings, because we as dashboard authors lost control of the order they were selected and portrayed in a chart. In fact, it depends on which order a user is selecting the multiple groupings or measures.

Now that you understand why this is such a great enhancement, let’s have a look at how it’s done.

Note: I will not explain how bindings work or how to create custom queries in this blog. If you want to learn more about bindings I’d suggest you check out one of the many blogs I’ve written on the subject.

ColumnMap Binding

I’ve created a simple dashboard with a custom (static) query and an aggregateflex query. The custom query allows you to either select ‘Industry’ or ‘Country’. The aggregateflex query shows ‘Sum of Amount’, grouped by first ‘Industry’ and second ‘Opportunity Type’ and ordered by the ‘Sum of Amount’.

The way the two queries should interact is to have the user select a value from the custom query which then should be the first grouping and used in the X axis. We will do this with bindings in the Advanced Editor view.

In the query tab and the "groups" parameter of the JSON we see this:

"groups": [
    "Account.Industry",
    "Type"
],

The approach to this binding remains the same – check out my binding blogs if in doubt about why this or that. Simply add your binding as below:

"groups": [
    "{{cell(SelectGroup_1.selection, 0, \"Dim\").asObject()}}",
    "Type"
],

Now, what about the columnMap? Well, it’s still something to consider, because without it we get the warning we are used to.

Normally we just set columnMap to null to get rid of the warning but with the loss of control over how the groupings are being displayed in the chart, but now we can use bindings. Before we look at the columnMap, note you would have to do the following in both the ‘Query’ and ‘Widget’ tab of the advanced editor.

Find the columnMap section, which looks something like the below:

"columnMap": {
    "split": [
       "Type"
    ],
    "trellis": [],
    "dimensionAxis": [
        "Account.Industry"
    ],
    "plots": [
        "A"
    ]
},

All we have to do is replace Account.Industry with our binding as seen below.

"columnMap": {
    "split": [
       "Type"
    ],
    "trellis": [],
    "dimensionAxis": [
        "{{cell(SelectGroup_1.selection, 0, \"Dim\").asObject()}}"
    ],
    "plots": [
        "A"
    ]
},

As mentioned remember to add the binding to the columnMap section in both the query and widget.

Now you can save your changes and preview your dashboard with columnMap bindings.

Note: I experienced that I did need to save my dashboard and refresh the page in order for the binding to kick in.

That’s all. I know this is going to be an enhancement many of you will benefit from – I have at least heard it requested often.

Written by

Rikke Hovgaard

I have worked with CRM Analytics since 2015 (back then Wave). I started this blog to help make the journey to master CRM Analytics easy by documenting tips, tricks & cool solutions.

See author's posts