Einstein Analytics: Demystifying Bindings – Part 6

5
(3)

If you have been looking at some of my other blogs, you might know I love the compare table functionality, we get to do powerful calculations without writing code. Now I know that we of course also want our compare tables to be dynamic, so next in the binding agenda is compare table bindings.

Compare Table Group Binding

As always we need to do some prep work and in this case we need to have two things; the static step and the compare table step.

If we look at the static step I am planning a simple group selection allowing the user to switch between ‘Industry’ and ‘Billing State’. The step looks like this:

"values": [
{
"display": "Industry",
"value": "Account.Industry"
},
{
"display": "State",
"value": "Account.BillingState"
}
]

The compare table is pretty simple, it consists of the grouping ‘Industry’ (of course) and ‘sum of Amount’. To leverage the compare table functionality I did a simple rank within group function. The query looks like this

"query": {
"columns": [
{
"query": {
"measures": [
[
"sum",
"Amount"
]
],
"groups": [
"Account.Industry"
]
}
},
{
"query": {
"measures": [
[
"sum",
"Amount"
]
],
"groups": [
"Account.Industry"
],
"formula": "rank() over([..] partition by all order by A desc)",
"order": [
[
-1,
{
"ascending": true,
"inner": true
}
]
]
},
"header": "Rank"
}
],
"measures": [
[
"sum",
"Amount",
"A"
],
[
"sum",
"Amount",
"B"
]
],
"groups": [
"Account.Industry"
]
}

Now first of all your JSON might have a different order than me, that is alright. A compare table has different components and these can come in any order. We will, of course, look at these components in the next section.

The dashboard before the binding has been applied looks like this:

The Binding in Details

Looking at the JSON for the compare table you will notice that the query has three main components: “measures”, “groups” and “columns”. The measures section contain the two measures we added which in my case is two sum of Amount. You will also notice that they have A and B which refers to the column alias you see in the UI. The groups just contains the group and finally, we have columns. The column section is a little bit more complex, this is where we have the query for each column (measure added in the UI). For each of these, we will see a grouping and a measure plus possibly a few more parameters like the formula for our rank function.

With this in mind, it is not enough with a single binding, we need to change all the references to the grouping. In the sample above that will be three bindings, which you can see highlighted below.

"query": {
"columns": [
{
"query": {
"measures": [
[
"sum",
"Amount"
]
],
"groups": [
"Account.Industry"
]
}
},
{
"query": {
"measures": [
[
"sum",
"Amount"
]
],
"groups": [
"Account.Industry"
],
"formula": "rank() over([..] partition by all order by A desc)",
"order": [
[
-1,
{
"ascending": true,
"inner": true
}
]
]
},
"header": "Rank"
}
],
"measures": [
[
"sum",
"Amount",
"A"
],
[
"sum",
"Amount",
"B"
]
],
"groups": [
"Account.Industry"
]
}

The binding we have to use is pretty simple, it’s a column data selection referencing the static step and .asObject() for the data serialization.

"{{column(Static_1.selection, ["value"]).asObject()}}"

I can recommend going through the first part of this blog series if you need the binding described further.

In order to make the compare table dynamic we, of course, need to replace the three groupings with the binding like below.

"query": {
"columns": [
{
"query": {
"measures": [
[
"sum",
"Amount"
]
],
"groups": "{{column(Static_1.selection, ["value"]).asObject()}}"
}
},
{
"query": {
"measures": [
[
"sum",
"Amount"
]
],
"groups": "{{column(Static_1.selection, ["value"]).asObject()}}",
"formula": "rank() over([..] partition by all order by A desc)",
"order": [
[
-1,
{
"ascending": true,
"inner": true
}
]
]
},
"header": "Rank"
}
],
"measures": [
[
"sum",
"Amount",
"A"
],
[
"sum",
"Amount",
"B"
]
],
"groups": "{{column(Static_1.selection, ["value"]).asObject()}}"
}

And that’s all it takes. The binding is the same as we have learned, we just need to change all the references to the grouping.

Other Compare Table Bindings

If you have followed this blog series then you know that it’s not only groupings we can make dynamic. So if you want to make the measure or filter dynamic, that is possible as well. Just remember you need to replace all the references in the query with the binding.

In the next part, we will be looking at how to make bindings in values tables.

How useful was this post?

Click on a star to rate useful the post is!

Written by


10 thoughts on “Einstein Analytics: Demystifying Bindings – Part 6”

  • 1
    Callum Sheerin on March 17, 2020 Reply

    This is great, but when I toggle, my column seems to disappear!

    • 2
      Cameron on March 31, 2020 Reply

      I’m having the same issue! I think its because in the visualizationParameters section theres an item that looks like this:

      “columns”: [
      “B”,
      “G”,
      “I”,
      “A”,
      “{{columm(dimension_picker_1.selection,[\”display\”]).asObject()}}”
      ]

      On other charts it doesn’t seem like this “columns” section exists and the binding works fine. Any ideas??

  • 3
    Cameron on April 8, 2020 Reply

    So I thought I found the underlying issue to the disappearing columns issue, and was able to fix on some compare tables but not others…

    Here’s what I was able to figure out (and sorry if I’m not getting the nomenclature exactly right…):

    What seemed to be causing it was hiding columns in the compare table before adding the bindings.
    If you hide columns in a compare table step, in the underlying JSON a new section is added to the vlsualizaitonParameters{} section. This section is called “columns” : [ ] and has the column aliases of the metrics which are shown (eg. “A”, “C”, “E” – where B and D are hidden in the query) plus the columns that are grouped by (eg. “Account”). I was originally just replacing the “Account” line item with the same binding I was using in the “groups” section of the step. with no avail. I then found that the same “columns” : [ ] section was added at the top of the JSON for the table itself, (eg. table_1). Replacing the “Account” line with the same binding I had used in the step worked!

    However…..that was on a test table with only a few columns, I have another step with a bunch of hidden columns for calculations, and I can’t seem to do anything to make that one show up.

    Any thoughts or guidance would be majorly helpful!

    • 4
      Cameron on April 8, 2020 Reply

      Actually I think I got that wrong, I don’t think adding the binding to the “columns” : [ ] section worked. So I guess the question should the binding clause be different in the “columns” : [ ] section than the “groups” : [ ] section, or is it not possible to add a binding to a compare table with hidden columns. Thank you all in advance!

      (Going to post on the Trailblazer community and will post an answer if anyone else is having the same issue)

  • 7
    Pranit Bhisade on February 18, 2021 Reply

    Hi Rikke,
    Thank You for your amazing blogs,
    I have a question, can we bind the column headers too
    for eg- I have a list selector,if i select the value in it, the column header in compare table must change to the selected value from list selector.

    • 8
      Rikke on February 18, 2021 Reply

      Yes you can, you just need to bind to the alias.

      • 9
        Pouillon on April 18, 2024 Reply

        Hi! Thank you for your blog!
        I try to modify the column header, but it only catches fixed strings. In other words, it shows me the binding as a text, not as its value.
        I’m trying in ‘Rename Column’, where I only get the binding as a text. If y write the binding in the SAQL code, it gives me always an error. And I can’t find the label for the headers in the json.
        Where do I have to do it?

  • 10
    Manjit Singh on May 2, 2021 Reply

    Hi, thank you for this great blog. I was wondering if table widget supports selection binding.

    Use case: Parent Account => Contacts (Child) => Cases (Child of contact)

    I have following widgets:

    – A List selector (Show all accounts)
    – A tables shows all Contacts related to the account (not pivot, not compare – normal table)
    – A table to show all cases related to selected contact

    I am using selection binding to filter Contacts for an account (This is working fine, the issue is in cases table where I want to filter cases based on selected cell in Contacts table) Binding in contact table:

    // Contact Query
    “filters”: [
    [
    “AccountId”,
    [
    “{{coalesce(cell(All_Accounts.selection, 0, \”Id\”), \”\”).asString()}}”
    ],
    “in”
    ]
    ],
    Now when I select a contact cell in Contact table, I am trying to get cases filtered based on Contact selected (By selecting the cell which has the actual contact Id). I see that this selection is never broadcasted from contacts table. (Facet broadcast is on, Global filters is off, Selection – Single Mandatory). I have tried column selection as well. Any help would be really appreciated.

    // Cases Query
    “filters”: [
    [
    “ContactId”,
    [
    “{{coalesce(cell(All_Contacts.selection, 0, \”Id\”), \”\”).asString()}}”
    ],
    “in”
    ]
    ],

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.