How to filter a dashboard based on user input

[federicoquaranta40]

Hello,

I'd like to filter a dashboard based on a selected time range. How can I do this in Apache Superset? I've read about Jinja Templating but I don't know how to put it into practice.

Thanks,

Federico

[dosubot[bot]]

To filter a dashboard based on a selected time range in Apache Superset, you can use Jinja templating. Here’s how you can do it:

1. Using Jinja for Time Range Filtering: You can use Jinja templating to dynamically filter data based on time ranges. Here's an example of how you can implement this in your SQL query:

   {% set time_filter = get_time_filter("dttm", remove_filter=True) %}
   {% set from_expr = time_filter.from_expr %}
   {% set to_expr = time_filter.to_expr %}
   {% set time_range = time_filter.time_range %}
   SELECT
     *,
     '{{ time_range }}' as time_range
   FROM logs
   {% if from_expr or to_expr %}WHERE 1 = 1
   {% if from_expr %}AND dttm >= {{ from_expr }}{% endif %}
   {% if to_expr %}AND dttm < {{ to_expr }}{% endif %}
   {% endif %}

   This example uses the get_time_filter macro to handle time filters, allowing you to filter data based on the selected time range in your dashboard ^[1].

2. Dynamic Filtering with URL Parameters: You can also use the url_param macro to pass user input through URL parameters. For example:

   SELECT count(*)
   FROM ORDERS
   WHERE country_code = '{{ url_param('countrycode') }}'

   This allows you to filter data dynamically based on user input provided through the URL, such as www.example.com/superset/sqllab?countrycode=ES ^[1].

These methods enable you to create dynamic and user-responsive dashboards in Superset.

To continue talking to Dosu, mention @dosu.

[Keveen-ghori]

@dosu How parameter looks like for below query?

      {% set time_filter = get_time_filter("dttm", remove_filter=True) %}
      {% set from_expr = time_filter.from_expr %}
      {% set to_expr = time_filter.to_expr %}
      {% set time_range = time_filter.time_range %}
      SELECT
        *,
        '{{ time_range }}' as time_range
      FROM logs
      {% if from_expr or to_expr %}WHERE 1 = 1
      {% if from_expr %}AND dttm >= {{ from_expr }}{% endif %}
      {% if to_expr %}AND dttm < {{ to_expr }}{% endif %}
      {% endif %}

[dosubot[bot]]

In the context of the SQL query using Jinja templating in Apache Superset, "dttm" represents a column that is marked as a datetime column. This is specified by setting is_dttm=1 in the TableColumn definition, indicating that the column is used for datetime operations ^[1].

For the query you provided, the parameters from_expr and to_expr are part of the TimeFilter object returned by the get_time_filter macro. These parameters can be str or None, and they represent the expressions used to filter the datetime column based on the selected time range. Examples of these expressions include formatted timestamps or dates, such as TO_TIMESTAMP('2024-08-27 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US') or DATE '2024-08-03' ^[2][1].

Make sure to replace dttm with the actual name of your datetime column in the logs table to apply these filters effectively.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[Keveen-ghori]

{
"_filters": [
{
"col": "bucket",
"op": "=",
"val": ["2024-11-01"]
}
]
}

@dosu Is dttm looks like above parameter? if not give new parameter with proper date range in superset's sql lab.

[federicoquaranta40]

What is dttm?