Tables with pages using SAQL offset

Last week while in London I was asked the question if it was possible to have pages on tables as this would be helpful for tables with large results. Out of the box, no this is not possible – at least not yet. But I did find a way to achieve this with a value dataset, hidden step, and bindings.

Note: this blog is leveraging SAQL and bindings. If you are new to CRM Analytics you may benefit from getting a basic understanding of both concepts.

The dashboard

My starting point for the dashboard is simple; one table widget and one bar chart to have an option to filter the table result using faceting. This would also show that the number of pages we are showing for the table is dynamic and depends on the number of returned rows. The dataset I’ve been using is just a basic opportunity dataset.

What you have in the chart and in the table is actually irrelevant, however, the one important thing to note is that my table is a SAQL query with a limit and an offset as you can see below.

q = load "opportunity1";
q = foreach q generate 'Amount' as 'Amount', 'CloseDate' as 'CloseDate', 'Name' as 'Name', 'Owner.Name' as 'Owner.Name', 'StageName' as 'StageName';
q = order q by 'Amount' desc;
q = offset q 0;
q = limit q 10;

Offset and limit

The offset is used to define what row number from the result we want to start from. If none is defined we will start from 0. However, in this use case, we want the offset to be dynamic depending on the page we have selected.

The limit is used to define how many rows we want to show of the results. In my case I want to split my table into pages with 10 results on each page, thus my limit is 10.

If I have more than 10 rows I want to go to the next 10 rows, which I can do by changing the offset from 0 to 10 and keeping my limit as 10.

Values table

With the combination of offset and limit we can mimic pages in a table. In order to leverage the offset and limit functions we do need to introduce a values table (dataset) that can store the page number and the matching offset. As I want my table to show 10 rows my offset will start at 0 and increase by 10 for each page. See below for an example.

Note: You want to make sure the values table have enough pages and offsets to match the result you are getting back in the table on the dashboard.

In my CSV file I’ve made sure to have up to 50 pages and uploaded the file as a dataset in CRM Analytics. Even though Pages looks to be a measure, you should upload it as a dimension, so we can group by it in our query.

Once the dataset has been uploaded, from your dashboard add a new query with this dataset; group by Page and show sum of Offset. I’ve also added a limit of 10, which we will make dynamic later. As you can see below I’ve added this to the dashboard leveraging the toggle widget.

Let’s tie it together

Now that we have all the components, let’s first look at how we can make the page toggle dynamic, and then how we can tie that to the table with all the details.

Hidden step

The first thing to do is to create a hidden step that calculates how many pages we should have. Currently, the pages just show all the values from our CSV file, but we want to look at the number of returned values from the table and divide that by 10 since we are expecting 10 results per page.

We will be using the same opportunity dataset that the chart and table are using. We don’t need to add any groupings, but we do need to calculate the number of rows with a function. To do this click the plus to add a new measure and in the top right choose “Add Formula”.

I’ll call the new column ‘pages’. In the formula editor choose the function (or just type it) ceil(). This function will take the value, round it up, and return a full number. Inside the function, we want to take the count of rows (column A) and divide it by 10 (the number of results per page which is also defined in your offset). The formula will look something like this: ceil(A/10).

Remember to hit “Apply”. You can then exit the formula editor and hide the Count of Rows column aka column A. The latter is not strictly necessary, I just find it a bit cleaner. Finally, just click done and leave the query sitting on the right hand side as we do not need to show this query on the dashboard, we simply just need to reference it when deciding how many pages to show in the toggle.

Page toggle

We will now use the hidden step from before to set a limit on the page toggle query. In the dashboard editor click once on the toggle widget and then select the advanced editor to get started on the binding. From the editor select:

• Source Query: totalPages_1 (or whatever you called your hidden query)
• Source Data:
  • Data Selection: Cell
  • Row Index: 0
  • Column: pages
• Interaction Type: Result
• Data Serialization Function: asObject

The result of the selections will look like this.

{{cell(totalPages_1.result, 0, \"pages\").asObject()}}

Note: If you want to know more about bindings check out the bindings blog series.

Now copy the binding and in the query tab replace the limit of the query. Don’t forget the double quotes.

"limit": "{{cell(totalPages_1.result, 0, \"pages\").asObject()}}"

You can save and exit the editor and see how selections in the chart will narrow down the number of pages shown in the toggle.

Dynamic offset

The last bit to make this solution work is to bind the offset from the selected page to the table query. Similar to before, select the table widget and hit the Advanced Editor button. Make the following selections to create your binding:

• Source Query: Pages_1 (or whatever you called your toggle query)
• Source Data:
  • Data Selection: Cell
  • Row Index: 0
  • Column: sum_Offset
• Interaction Type: Selection
• Data Serialization Function: asObject

In the more section, you can optionally select the default value 0. It’s optionally only if you have made “Single Selection Required” for the toggle query. The reason being we always need to have a value or the query will fail. In my example, I will add a default value in the case that nothing has been selected just yet.

The result of the binding will look like this:

{{coalesce(cell(Pages_1.selection, 0, \"sum_Offset\"), 0).asObject()}}

Next, add the binding to the query by replacing the 0 from the offset statement. Adding the binding can be a little difficult when it’s a SAQL query, but hopefully the image below illustrates it well enough.

At the end, your query should look like this:

q = load "opportunity1";
q = foreach q generate 'Amount' as 'Amount', 'CloseDate' as 'CloseDate', 'Name' as 'Name', 'Owner.Name' as 'Owner.Name', 'StageName' as 'StageName';
q = order q by 'Amount' desc;
q = offset q {{coalesce(cell(Pages_1.selection, 0, "sum_Offset"), 0).asObject()}};
q = limit q 10;

Save your changes and return to your dashboard.

Test it out

You can now test out the solution; notice how the number of pages changes depending on the selection from the chart. And notice how the table shows a new set of rows depending on the page you have selected.

Written by

Rikke Hovgaard

I have worked with CRM Analytics since 2015 (back then Wave). I started this blog to help make the journey to master CRM Analytics easy by documenting tips, tricks & cool solutions.

See author's posts