Create custom queries faster than ever

In the latest release, Winter 20, we got a lot of help in creating static steps – or as we will be calling them from now on, custom queries – but it can perhaps be a little hard to navigate how it works. So let’s have a look at these custom queries.

First of all, when is this relevant to use? Sometimes you want to give some flexibility to the user in allowing them to select a certain grouping, time period etc. and let the chart change based on the selection. This is where we will need a set of static or custom values we can pass from one step (or query) to another using bindings. If you are new to bindings I can recommend checking out my very extensive blog series on bindings.

The pre-work

To illustrate how to create and use custom queries I have created a new black dashboard with a simple query that groups by industry and shows sum of amount.

"query": {
"measures": [
[
"sum",
"Amount"
]
],
"groups": [
"AccountId.Industry"
]
},

Create a custom query

To create the custom query the process is the same as always, however, the naming is new. Go ahead and click on the blue “Create Query” button in the top right of your dashboard in edit mode.

You will now have to select a data source, but since our query will be a custom one we have to manually add the values we want to use. Notice in the bottom of the dialog box there is a “Create Custom Query” link, which we need to click.

By default, we do get the first column in our query or table which is called “Display”. The rows in this column are what the end-user will see when you add this step to your widget. However, you can see we do not have any values yet.

Let’s now add a few values. First of all, I want to be able to change the grouping from Industry to Opportunity Stage. Hence I need one additional column that will contain the API name of the field to be used in the grouping and I need two rows; one for Industry and one for Opportunity Stage. In the “Display” column I click the “+Add Text” to be able to type “Industry” and repeat the step for “Opportunity Stage”.

In order to add the API value, which is what we need for the binding to work, click on the “+Add Column”. Previously we had to memorize the API name and type it in hoping we remembered upper and lower case the right places. With the new UI all you have to do is click “+Add Column”, you’ll see you can choose a value from a dataset or add a manual value, additional if you want to add a dimension or measure. As we are using this for a grouping we will choose the dimension. We will go ahead and select the same dataset we used for the query in the pre-work and go ahead and search for the value – in my case Industry. Repeat for “Opportunity Stage”.

You might have noticed the name of this column, Dimension2. This is the value you will have to use in your binding. You can, of course, rename the label to anything you want by clicking the down arrow at the top of the column. You can also move the column to the first or delete it.

Let’s say I want to show “sum of Amount” when the Industry is used as a grouping and “count of rows” when Stage is being used as a grouping. I can do similarly as before but, of course, pick the measure instead of the dimension however I still want to use a dataset to make sure I get the correct values.

Finally, you can create columns with manual entry; text or a number. Let’s say you want to also control the order and limit parameter of a query. But for now let’s keep the custom query with a dimension and a measure, so go ahead and click done plus add the new custom query to the dashboard.

Finally, we need to make sure that one of the custom values is being selected by choosing “Single selection (required)” in the selection type of the query tab.

One thing to note is that this tool does not support arrays, let’s say you want to add a date filter or a custom measure, you would still have to maneuver to the dashboard JSON. With that being said, let’s have a look at the dashboard JSON.

Behind the dashboard in the JSON

So if you have seen the static steps prior to Winter 20 you will probably notice that things have changed a bit. The static step or custom query as they are now called has a lot more details after the last release. That being said from a binding perspective – which we will get to later in this blog – we can disregard most of these changes. The custom query that we just created looks like below.

"static_1": {
"broadcastFacet": true,
"columns": {
"Group": {
"dataset": {
"id": "0FbB0000000HRbMKAW",
"name": "OppOwnerAccnt",
"url": "/services/data/v47.0/wave/datasets/0FbB0000000HRbMKAW"
},
"type": "grouping"
},
"Measure": {
"aggregateFunction": "Measure3__func",
"dataset": {
"id": "0FbB0000000HRbMKAW",
"name": "OppOwnerAccnt",
"url": "/services/data/v47.0/wave/datasets/0FbB0000000HRbMKAW"
},
"field": "Measure3__field",
"type": "aggregate"
},
"Display": {
"type": "string"
}
},
"label": "",
"selectMode": "single",
"type": "staticflex",
"values": [
{
"Display": "Industry",
"Group": "AccountId.Industry",
"Measure3__field": "Amount",
"Measure3__func": "sum"
},
{
"Display": "Opportunity Stage",
"Group": "StageName",
"Measure3__field": "*",
"Measure3__func": "count"
}
],
"numbers": [],
"strings": [],
"groups": []
}

The first thing I would highlight is that there has not been introduced a new step type, it’s still “staticflex”. The second thing is we still have the values parameter, which contains all the values we want to bind.

So what has changed? Well, the introduction of the “column” parameter, which as you can see have references to the dataset we will be using, but we can disregard this. What we cannot disregard is when it comes to binding the measure.

"values": [
{
"Display": "Industry",
"Group": "AccountId.Industry",
"Measure3__field": "Amount",
"Measure3__func": "sum"
},
{
"Display": "Opportunity Stage",
"Group": "StageName",
"Measure3__field": "*",
"Measure3__func": "count"
}
],

Above you can see the values again. If you look at the measure, it has been split into two; the field and the function. Now you could modify this manually and combine the field and the function in one value. But you can also leave it as it and do a different type of binding. Before going on to the binding, if you are interested in modifying the measure value please check out the blog I previously wrote on static steps.

On to the bindings

Alright, let’s look at the bindings; one grouping and one measure binding. If you are unfamiliar with bindings, please check out the binding blog series I wrote as I won’t be that detailed in this blog.

For the group binding, we can do as we normally would do, a simple selection binding using column and .asObject().

"groups": "{{column(static_1.selection, ["Group"]).asObject()}}"

Now to the measure binding. Since we have the field and the function separately we ultimately have two columns for a single measure and we need both of them. With that said let’s look at a row binding as this will allow us to take multiple columns from the same row. We will still create a selection binding and we want the output of the value to be an array, which is why we will choose .asObject().

"measures": "{{row(static_1.selection, [], ["Measure3__func", "Measure3__field"]).asObject()}}"

Before you go an review the result, remember that thing called columnMap, which you can read all about here. But basically, find it in the step and the chart and set it to “null”.

How about SAQL?

The new custom query actually makes it easy enough to work with SAQL bindings. Meaning you don’t have to go to the dashboard JSON to add more custom values. I’ve previously looked at group and measure bindings in SAQL in part 3 of demystifying bindings blog. As mentioned above the custom query allow you to manually enter a text or number, we would need to do this for SAQL bindings.

So I’ve created a new dashboard with the same step as in the pre-work above: sum of Amount and group by Industry. In this example since we are working with a SAQL binding, I’ve switched the SAQL in the explorer to have a SAQL step instead of the default aggregate flex. Check out the gif below if you are in doubt of how this is done.

Let’s now look to the custom query. Since we are binding a measure in SAQL we need two things; an expression (below in blue) and an alias (below in green).

sum('Amount') as 'Amount'

We, of course, need a row for each selection so I have chosen sum of Amount and count of rows, which I call count of Oppty. Besides the default display column, I create one for the expression and alias which both will be a manual text value. In the expression column, I have sum(‘Amount’) and count(). In the alias column, I have sum_Amount and count. And that’s it, once you have these three columns and two rows you can click on done.

As we did in the previous example, make sure to add your custom query to the canvas and make the query’s selection type “Single selection (required)”, to make sure you are not passing null values.

As you can read in my binding series on measure (and group) bindings in SAQL we need to use a row data selection and .asProjection() as data serialization. So switch to the dashboard JSON and find the query we are modifying. In the foreach statement where you have sum(‘Amount’) as ‘sum_Amount’ we will delete that and replace it with the binding.

{{row(static_1.selection, [], ["expression", "alias"]).asProjection()}}

The whole query should now look similar to below.

q = load "OppOwnerAccnt";
q = group q by 'AccountId.Industry';
q = foreach q generate 'AccountId.Industry' as 'AccountId.Industry', {{row(static_1.selection, [], ["expression", "alias"]).asProjection()}};
q = order q by 'AccountId.Industry' asc;
q = limit q 2000;

Once you have the binding, you can go back to the dashboard and see your SAQL binding work with the new custom query.

Written by