Einstein Analytics: Demystifying Bindings – Part 6
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.
Written by
This is great, but when I toggle, my column seems to disappear!
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??
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!
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)
Hi Cameron,
Did you get a solution to your above query?
I want to generate the column names in a Compare table dynamically. I have some hidden columns, and hence using the “columns [ ]” parameter to specify the column names that should be shown. But I’m not able to replace these column names with Binding to generate the names dynamically.
The Compare table is a SAQL step type.
Hey Sneha,
Unfortunately I haven’t had any success – I posted to the Trailblazer community as well, and there were a few ideas, but I was never able to figure it out myself. Here’s the link to post if you want to try it out – https://success.salesforce.com/_ui/core/chatter/groups/GroupProfilePage?g=0F9300000009MBP&fId=0D53A00004r4H6J&s1oid=00D300000000iTz&emkind=chatterCommentNotification&s1nid=0DB30000000072L&emtm=1586539530511&s1uid=0053A00000FJhKs&fromEmail=1&s1ext=0
Good luck and let me know if you figure it out!
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.
Yes you can, you just need to bind to the alias.
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”
]
],