Use SAQL Bindings for Convenient Text Filters

Using the “asEquality” function within bindings works in use cases where your data contains the fields you want to filter by.  This is great in many but not all cases.  I have recently ran across a number of scenarios where the request is to filter by some complex logic – multiple fields, aggregations, the result of another step (only pull the top 5% of performers by the date range selected) but only if the selection to filter at all is made by the user.

In these cases you want nothing (additional) to be filtered in the query unless the proper selection is made by the user interacting with the dashboard.  In these cases I have resorted to text/string based filters.  If no selection is made or the default is used we simply add two dashes “–” to comment out the line.  If the filter is to be used, we simply pass the appropriate text string from the static step or the new term, “custom query.”

This has been very handy in a pinch when either the filter criteria is not something that is contained within the dataset already or in cases where it would not be possible to add them to the necessary dataflow to include them in the dataset.

How It Works – String SAQL Filter Bindings

This is a basic use case that does not require a string binding to filter, but I wanted a simple and clear example to explain the concept.  In my case, I am starting with a pillbox toggle widget to control the filtering.  Then I go to create my custom query:

 I simply add my three values I want to display visually to the user in the “Display” column, and then add a hidden “Text2” column that will be the corresponding filter values.  When the user selects “All” I don’t want any filter so I just add two dashes to comment out the line “–” and the two filter values that I would write manually within the SAQL if the user selects the other options.

This creates a query or step that looks like this in the JSON:

I change this query to “Single Selection (required)” and set the initial selection to “All.”

Then I need to place the binding within my original query/step.  I do that in the highlighted section here:

That’s it.  Now when I toggle between the options, the binding either passes in two dashes to comment out the line or the necessary filter line:

When I “Show Details” to view the “Final Query” when running the dashboard and “All” is selected, it passes the two dashes “–” as expected:

When I do the same with one of the other values selected, in this case “Event” – it passes the appropriate filter:

Wrap

I believe that too often the ‘asString’ binding function is glossed over and use cases that are only possible with SAQL and passing the binding as a string are missed.  You can use this almost anywhere necessary within a SAQL query.  I’ve even used it to toggle between time zones and used a string binding within every filter, grouping, select/generate, order, etc. statement within the final query.  This allows the user to change the query entirely with a single click.  Going back to the filter example, I’ve had use cases where I need a 300 character filter statement within the query most of the time, so I add it to the original query.  But in some cases the user wants to disable that complex filter.  In these cases, I create a custom query and a binding that would return either the two dashes “–” to comment out the filter or nothing/empty string “” to keep the filter in place.

Is this helpful, silly, crazy?  There are so many use cases for this and it really does come in handy while working real world scenarios.  Would love to hear your thoughts in the comments!