Interactive Dashboards in Apache Superset

What if you want to make your dashboard more interactive? What if you have a complex SQL query for your dataset and you have to implement an algorithm that will depend on filters you set in the dashboard or even based on parameters from a link for that particular dashboard? The answer to all of the questions is Jinja templates.

Photo by Kelly Sikkema on Unsplash

First of all, you need to activate Jinja templating if you have not done this before. To do this, you should add another element to the FEATURE_FLAGS dictionary in your superset_config.py. Element’s key will be ENABLE_TEMPLATE_PROCESSING, and the value will be True. Here is how it will look like:

FEATURE_FLAGS = {
    ...  # your existing flags
    "ENABLE_TEMPLATE_PROCESSING": True,
}

After adding the parameter you should rebuild your Docker image if you use Superset in a container. Here are two of my articles that could help you Run Apache Superset Locally in 10 Minutes and change settings via superset_config.py.

Now, it’s time to use Jinja macros in our SQL query! You may start with from_dttm or to_dttm standard Superset macros, for example. If you go to edit your virtual dataset, you may use {{to_dttm}} placeholder for the Jinja processor. Jinja will replace that placeholder with the end bound of the time range filter you set. Let’s start with an example query for MariaDB:

SELECT DATE_SUB(NOW(), INTERVAL 1 DAY) AS XDATE

We will use it to create a new virtual dataset in our system by clicking EXPLORE button and save. Then, we will save a table chart with the name test_table_chart. Now, we see the result of the query like this:

Result of the query execution

Now let’s change our query in the virtual dataset by replacing NOW() function with {{to_dttm}} Jinja macro this way and save:

Changed virtual dataset with Jinja macro

If you try to run your chart now, it will return N/A, because the template processor does not see any time range filter associated with this chart. To fix this, you have to put together your chart with a filter on a new dashboard. If you open your dashboard now, you will see the result of the query as we expected — the date that represents one day before the end bound of the time range filter:

Chart based on a dataset that uses to_dttm macro

Wow, it is cool, isn’t it? Play with the filter to check how it affects test_table_chart.

There are many built-in macros available for templating. You know that HTML is rendered in the Table visualization type, right? For example, you could use {{ url_param('your_custom_parameter') }} for using custom GET request parameters in your dashboards and make them really interactive. You could construct links via <a> tags in your SQL like this:

SELECT <a href="http://superset?page=777">Page #777</a> AS MY_LINK

That link will lead a user to the detailed dashboard where the dataset is based on {{ url_param('page') }} which filled via GET parameter from your link!

Additional information on other built-in Jinja macros could be found in the jinja_context.py source code. Moreover, you could define your own macro and make it available for templating in the dataset via TEMPLATE PARAMETERS in dataset properties. Fantastic!

I hope, you have enjoyed the article and this piece of information will help you to build charming useful visualizations. If so, please, follow me on Medium, GitHub, Twitter, and LinkedIn.