One selection to control them all: Driving multiple queries with one widget selection.

A common way of comparing data is to use dates. Whether it's by years, by weeks, or sometimes for particualar key dates.

As this is a pretty rich topic, I'm going to break it down into separate articles. In this article we'll deal with the general apporach and preparing the ground. This will include setting up the dashboard and datasets.

The second article will look at how we build out the selection dataset to meet our requirements using some of the date functions available in SAQL.

The third article will look at the bindings we need to create to service our SAQL queries in the dashboard JSON.

The fourth will look at how we can nest bindings to group our data dynamically depending on a selection.

Our example is quite specific, but elements of the approach might have wider applications and certainly it's another strategy for enhancing user experience, with the user making fewer clicks to achieve powerful results. It might also save some work for the developer too.

Part One - Analysing requirements and setting up the data

The client wants a dashboard that will show them sales data for their orders.

  • Revenue by Product, Key date this year vs Key date last year, Total last Year.
  • Revenue by Product Key date this year vs 7 days previous vs 1 month previous.
  • Revenue for Accounts from Jan of key date year to key date month vs following months to December.
  • Performance this year vs the same date last year vs the year before, in terms of billed revenue only

As orders are only invoiced after the delivery date, it's important for the client to view orders in terms of those eligible for billing. So although an order has been placed in January, it may not be delivered until February. Therefore the revenue can only be counted after the delivery date has passed.

Here's the kicker(s)

  • Because orders change from time to time our customer is only interested in seeing the exact value of an order on an exact date using snapshots of the order. Not just the final value of an order as it is now.
  • To make it easy for the users we want to show the data in a single dashboard and make it easy to filter by a particular date.
  • The key date must be flexible, they may want to look at data from the past as well as the present.

So here's what we aim to do in part one:

  • Build a dataset to drive multiple queries
  • Set up a list widget so the user can select a key date
  • A couple of example bindings to demo the output

You can try this out in your own org by downloading the sample data or just read the parts that interest you and try it with your own data.

As we have already mentioned, when several queries are required in a dashboard that are related to each other, wouldn't it be nice if we could deliver all those parameters from one user selection? We aren't able to set up these variables on the fly like you would in Apex or Javascript for example, but we can use a dataset instead.

Datasets can be used for producing tables and charts, but they can also be leveraged to boost the power of our dashboard bindings. Just think of them as you would objects that store values we can bind to our widgets and that are queried by a selected value.

In the context of our example, when we query orders based on a key date for this year, we also want to return data for the previous year, and a total of the previous year. Our SAQL would require parameters to filter for only orders with a delivery date in the selected year and the we only want the revenue values up to the key date.

baseYear = filter q by 'DeliveryDate_Year' in ["2018"] && 'SnapshotDate' in [.."2018-02-02"];

Then if we take into account the other two datastreams for previous year and previous year total -

baseYearYtd = filter q by 'DeliveryDate_Year' in ["2018"] && SnapshotDate in [.."2018-02-02"];

previousYearYtd = filter q by 'DeliveryDate_Year' in ["2017"] && SnapshotDate in [.."2017-02-02"];

previousYearTot = filter q by DeliveryDate_Year in ["2017"] && SnapshotDate in [.."2017-12-31"];

That's six dates we need, in different formats for one chart.

After some experiementing with the standard date widgets, it just seemed a bit unwieldy to have dates controlled by several widgets, it takes up a lot of space and feels clunky. So let's try using a list widget populated with a Key Date dataset.

The Key Date dataset is what I call a utility dataset. It doesn't contain any actual CRM data, it's purely there to drive functionality and help enhance the power of our dashboard.

Everything we are doing in this example is date driven. So I built the basic CSV Key date file using a spreadsheet editor. They all have the ability to create a column of dates quickly and easily. Which is great for providing our key date values.

To save time you can download the Keydate Selection Matrix Dataset and then upload it to your org. If you do, then make sure that you set the 'Nice_Date' column as a dimension, this is so the dates are presented in a user friendly way in the UI widget.

Create a new blank dashboard, and call it 'Wormingford Wholesale Dashboard'.

Screen-Shot-2018-02-12-at-15.35.07

Drag and drop a List widget onto the Dashboard click 'List'

Choose the data, our Key Date Selection Matrix dataset.

Screen-Shot-2018-01-30-at-20.21.21

And select the 'Nice_Date' dimension.

Screen-Shot-2018-02-10-at-19.17.22

This populates our list widget with our 'Nice_Date' values. Rename the list widget as 'Select Key Date'.

In order to be able to access our utility dataset's values, just like in a chart or table widget, the fields must be made available in the step.

We can do this by clicking on the selection menu for the step, and selecting edit. A pop up will warn us about changes affecting other steps, but we're brave so we can click continue.

Screen-Shot-2018-02-12-at-15.47.17

In the step/lens view switch to SAQL Editor mode. And add the basic columns we want as per the csv file using the SAQL code below:

q = load "Key_Date_Selection_Matrix_2016_18";
q = foreach q generate 'Nice_Date' as 'Nice_Date', 'Base_Year_Key_Date' as 'Base_Year_Key_Date', 'Previous_Year_Key_Date' as 'Previous_Year_Key_Date', 'Previous_Year_1_Key_Date' as 'Previous_Year_1_Key_Date','Previous_Year_2_Key_Date' as 'Previous_Year_2_Key_Date';
q = limit q 10000;

In some cases this could be done in the UI values table edit mode, but it enforces a default order on the 'Nice_Date' column which doesn't look good in the UI. And we increase the limit to cover the number of options we want to display in the widget.

Screen-Shot-2018-02-12-at-16.07.08

Run the query and click update to go back to the dashboard.

Drag a text widget onto the layout. This will be our logging widget to test our dataset. We'll bind a selection query to this to test that our dataset is properly set up.

Screen-Shot-2018-02-12-at-15.39.08

If you aren't familiar with the different types of dashboard bindings yet, then please refer to the documentation. Different use cases will require the binding to output data in a specific format. E.g Sometimes you'll want a string or sometimes an object, if you were looking at matching a range of values for example.

For now we'll use the cell() and selection binding syntax, and return a value from our dataset as a string.

{{cell(Nice_Date_1.selection,0, \"Base_Year_Key_Date\").asString()}}

This states that based on the value the user has selected from the list widget (Nice_Date_1), we want to return the corresponding field value for that cell in field column "Base_Year_Key_Date" as a string.

We could also extend this statement to use the coalesce() function to provide a value if our selection returns null.

{{coalesce(cell(Nice_Date_1.selection,0, \"Base_Year_Key_Date\"), \"~Selected Base Range~\").asString()}}

Copy the code from the snippet above and Open the dashboard JSON editor using cmd+E.

Use find (cmd+f) to find the name of your text widget, in our case it's "Text_1".

Screen-Shot-2018-02-12-at-15.27.06

Locate the "text" parameter in the parameters section of the widget object and paste the code snippet between the double quotes as above. Click done. If all is well, then you should go back to the dashboard and see your first text widget is populated with our placeholder string.

Screen-Shot-2018-02-12-at-15.41.02

Click on the eye icon to enter Preview mode. And type or select a date in the list widget. If a date is selected from the list widget we should see the corresponding key date value in the text widget.

widget_test

You could set up a utility dataset for different kinds of selections, not just dates. Hopefully this has provided you with some fuel for your own creations.

Coming soon in part 2 - how to build out the selection dataset to meet our requirements using SAQL date functions.

Show Comments

Get the latest posts delivered right to your inbox.