Have you ever wanted to automatically drill into a lower level of data in your charts when you select a filter option? With some binding magic, you can! Let’s look at an example of how we can select a product family filter and automatically drill into the product on charts which are grouped by the product family by default.
Here we have a doughnut chart grouped by our product families.
We also have a standard list selector with product family.
To make it so we can change our groupings based on whether we have a product family selected or not, we’ll need an intermediate step. This step can be anything because we’re going to change the query to only return our grouping field.
We’ll create a new step from the dashboard edit mode, select a dataset (in this case we grabbed the opportunity dataset, but what we choose does not matter), and then we’ll go to SAQL mode. In SAQL mode, we remove the limit line and hit ‘run query’ to change this into a SAQL step. Then we hit done and the step is saved.
[code]
q = load "opportunity";
q = group q by all;
q = foreach q generate count() as ‘count’;
[/code]The next thing we need to do is get our list selection step name. For us, that is Product_Family_1. We’re going to use a results binding to change the output of our newly created SAQL step (lens_2 in this case).
The way a list selector displays the selection binding is a bit misleading. When “All” is showing because there is no selection, it doesn’t actually return “All,” it returns null. When using a results binding a list selector doesn’t return null, it returns an array of every value that exists in the list. For our purpose, we want to use the “null” selection to return which field we should be grouping on. To do that we need the following binding:
[code]{{cell(Product_Family_1.selection, 0, "Product.Family").asObject()}}
[/code]
Normally, when using a binding in SAQL we would use ‘asString()’ but in this case, the null return behavior makes it tricky. By wrapping our binding in quotes, using the asObject() and equating it to empty quotes, we’re able to handle both null values and when there is a value selected.
We need to adjust the other step we created earlier to use this binding. In our case, that was “lens_2.”
[code]
"lens_2":{
"type": "saql",
"query": "q = load "opportunity"; q = group q by all; q = foreach q generate count() as ‘count’;",
"useGlobal": true,
"numbers": [],
"groups": [],
"strings":[],
…[/code]
To get this step to return our field names for grouping we’ll use a case statement:
[code]case when "{{cell(Product_Family_1.selection, 0, "Product.Family").asObject()}}" == "" then "Product.Family" else "Product.Name" end
[/code]
We can just replace everything after the ‘generate’ keyword with this case statement like so (don’t forget to cast it with ‘as’):
[code]
"query": "q = load "opportunity"; q = group q by all; q = foreach q generate (case when "{{cell(Product_Family_1.selection, 0, "Product.Family").asObject()}}" == "" then "Product.Family" else "Product.Name" end) as ‘GroupBy’",[/code]
We can drop this step into our dashboard to confirm our bindings are working correctly:
Looks good! By default, it returns Product.Family and when we select a Family from the Product Family list, it changes to Product.Name.
We’re not done yet though. Now we need to use the results from this step to dynamically change the grouping on our main chart (we’ll remove the step we dragged on since we don’t need it displayed on the dashboard to work).
Now we need to go back into the dashboard JSON edit mode and find our doughnut chart (in this case Similar_Account_Prod_2).
[code]
"Similar_Account_Prod_2":{
"type": "aggregateflex",
"query":{
"measures": [
[
"sum",
"Opportunity.Amount"
]
],
"groups": [
"Product.Family"
]
}
…
[/code]
We’re going to adjust the “groups” section of this step to use the results from our other step (lens_2) with this binding:
“{{cell(lens_2.result, 0, “GroupBy”).asString()}}”
[code]
"Similar_Account_Prod_2":{
"type": "aggregateflex",
"query":{
"measures": [
[
"sum",
"Opportunity.Amount"
]
],
"groups": [
"{{cell(lens_2.result, 0, ”GroupBy”).asString()}}"
]
}
…
[/code]
The last thing we need to do is update the column maps for this step and widget to null.
[code]
"columnMap": null,
[/code]
That allows us to have dynamic bindings by not forcing the step or widget to look for specific return field names. Now let’s test it out!
There is no product family selected, so the doughnut chart is displaying all product families.
When the Apparel product family is selected, the doughnut displays all the products for that specific product family!
