Bind Parts of a Query

You can dynamically set parts of a query based on the selection or results of another query. For example, you can set the grouping in a query based on the grouping selected in a chart.

Before we discuss how to bind the different parts of the query, let’s look at a comprehensive example. This example illustrates the interactions for different parts of a query. The chart is bound based on selections for grouping, measure, filter, order, and limit. When you make a selection in one of the toggle widgets, the chart changes to show the results of the modified query.

The dashboard shows toggle widgets that dynamically modify the chart's query based on selections of groupings, measures, filters, orders, and limits.

Here’s the JSON for the queries that power this dashboard. The Account_BillingCount_1 query is the underlying query for the chart widget. This query contains multiple interactions based on other queries.

1"steps": {
2    "Account_BillingCount_1": {
3        "datasets": [
4            {
5                "id": "0FbB00000000oEkKAI",
6                "label": "Opportunities",
7                "name": "opportunity",
8                "url": "/services/data/v38.0/wave/datasets/0FbB00000000oEkKAI"
9            }
10        ],
11        "isFacet": true,
12        "isGlobal": false,
13        "query": {
14            "measures": "{{column(StaticMeasureNames.selection, [\\"value\\"]).asObject()}}",
15            "limit": "{{column(StaticLimits.selection, [\\"value\\"]).asObject()}}",
16            "groups": "{{column(StaticGroupingNames.selection, [\\"value\\"]).asObject()}}",
17            "filters": "{{column(StaticFilters.selection, [\\"value\\"]).asObject()}}",
18            "order": "{{column(StaticOrdering.selection, [\\"value\\"]).asObject()}}"
19        },
20        "selectMode": "single",
21        "type": "aggregateflex",
22        "useGlobal": true,
23        "visualizationParameters": {
24            "visualizationType": "hbar",
25            "options": {}
26        }
27    },
28    "StaticGroupingNames": {
29        "datasets": [],
30        "dimensions": [],
31        "isFacet": true,
32        "isGlobal": false,
33        "selectMode": "single",
34        "start": {
35            "display": [
36                "Country"
37            ]
38        },
39        "type": "staticflex",
40        "useGlobal": true,
41        "values": [
42            {
43                "display": "Country",
44                "value": "Account.BillingCountry"
45            },
46            {
47                "display": "Industry",
48                "value": "Account.Industry"
49            },
50            {
51                "display": "Product",
52                "value": "Product.Product.Family"
53            },
54            {
55                "display": "Source",
56                "value": "Account.AccountSource"
57            }
58        ],
59        "visualizationParameters": {
60            "options": {}
61        }
62    },
63    "StaticFilters": {
64        "datasets": [],
65        "dimensions": [],
66        "isFacet": true,
67        "isGlobal": false,
68        "selectMode": "single",
69        "start": {
70            "display": "Ads Only"
71        },
72        "type": "staticflex",
73        "useGlobal": true,
74        "values": [
75            {
76                "display": "Ads Only",
77                "value": [
78                    "LeadSource",
79                    [
80                        "Advertisement"
81                    ],
82                    "in"
83                ]
84            },
85            {
86                "display": "Partners Only",
87                "value": [
88                    "Account.Type",
89                    [
90                        "Partner"
91                    ],
92                    "in"
93                ]
94            },
95            {
96                "display": "$1M+ Only",
97                "value": [
98                    "Amount",
99                    [
100                        [
101                            1000000,
102                            11921896
103                        ]
104                    ],
105                    ">=<="
106                ]
107            }
108        ],
109        "visualizationParameters": {
110            "options": {}
111        }
112    },
113    "StaticOrdering": {
114        "datasets": [],
115        "dimensions": [],
116        "isFacet": true,
117        "isGlobal": false,
118        "selectMode": "single",
119        "start": {
120            "display": "Ads Only"
121        },
122        "type": "staticflex",
123        "useGlobal": true,
124        "values": [
125            {
126                "display": "Ascending",
127                "value": [
128                    -1,
129                    {
130                        "ascending": true
131                    }
132                ]
133            },
134            {
135                "display": "Descending",
136                "value": [
137                    -1,
138                    {
139                        "ascending": false
140                    }
141                ]
142            }
143        ],
144        "visualizationParameters": {
145            "options": {}
146        }
147    },
148    "StaticLimits": {
149        "datasets": [],
150        "dimensions": [],
151        "isFacet": true,
152        "isGlobal": false,
153        "selectMode": "single",
154        "start": {
155            "display": [
156                "5"
157            ]
158        },
159        "type": "staticflex",
160        "useGlobal": true,
161        "values": [
162            {
163                "display": "5",
164                "value": 5
165            },
166            {
167                "display": "10",
168                "value": 10
169            },
170            {
171                "display": "25",
172                "value": 25
173            }
174        ],
175        "visualizationParameters": {
176            "options": {}
177        }
178    },
179    "StaticMeasureNames": {
180        "datasets": [],
181        "dimensions": [],
182        "isFacet": true,
183        "isGlobal": false,
184        "selectMode": "singlerequired",
185        "start": {
186            "display": [
187                "Total Amount"
188            ]
189        },
190        "type": "staticflex",
191        "useGlobal": true,
192        "values": [
193            {
194                "display": "Max Employees",
195                "value": [
196                    "max",
197                    "Account.NumberOfEmployees"
198                ]
199            },
200            {
201                "display": "Total Amount",
202                "value": [
203                    "sum",
204                    "Amount"
205                ]
206            },
207            {
208                "display": "Avg Amount",
209                "value": [
210                    "avg",
211                    "Amount"
212                ]
213            }
214        ],
215        "visualizationParameters": {
216            "options": {}
217        }
218    },
219    "Account_AccountSourc_1": {
220        "datasets": [
221            {
222                "id": "0FbB00000000oEkKAI",
223                "label": "Opportunities",
224                "name": "opportunity",
225                "url": "/services/data/v38.0/wave/datasets/0FbB00000000oEkKAI"
226            }
227        ],
228        "isFacet": true,
229        "isGlobal": false,
230        "query": {
231            "measures": [
232                [
233                    "count",
234                    "*"
235                ]
236            ],
237            "groups": [
238                "Account.AccountSource"
239            ],
240            "order": [
241                [
242                    -1,
243                    {
244                        "ascending": false
245                    }
246                ]
247            ]
248        },
249        "type": "aggregateflex",
250        "useGlobal": true,
251        "visualizationParameters": {
252            "visualizationType": "hbar",
253            "options": {}
254        }
255    }
256},
257"widgetStyle": {
258    "backgroundColor": "#FFFFFF",
259    "borderColor": "#E6ECF2",
260    "borderEdges": [],
261    "borderRadius": 0,
262    "borderWidth": 1
263}

If you bind a measure or grouping in a query used for a chart, you must also replace the columnMap section in the widget-level chart JSON with an empty columns array. For more information, see Measure Interactions and Group Interactions.

Note