Lakeview dashboard dynamically change filter values

[EDDatabricks]

Greetings everyone,

We are trying to implement a series of visualizations. All these visualizations have queries assigned to them in the form of “ Select * from test table where timestamp between :Days start and :Days end”. The is also a filter applied to each one of those parameters so we can check the time period we want. We would like to have a schedule that refreshes once a day and dynamically uses the filters (for example the start time should be the current timestamp - 7 days and the end time should be the current timestamp). Is there any way to do that? Moreover is there anyway to setup a pre set warehouse that this dashboard will run on after without choosing it manually in the first time?

Also during the development of the lakeview dashboards we noticed 2 inconsistencies with the dashboard. The first one is that the filter wouldn’t let us choose a different date (we don't know how to replicate this one).
The other one is that half of the visualizations were displaying data and some of them were not with the error “Missing selections for parameters”.

To replicate this do the following:

1. Create queries in the data tab and have them to have parameters (2 of them)  in the form of “:parameter” with the type of “ Date and Time “.
2. Leave those queries without a Default value for the parameters as shown in the screenshot below.
   
3. Create 2 filters. One of them has one parameter and the other one has the other.
4. If you try to set the value of the filters and refresh the dashboard some visualizations return this error message.
   

Also we noticed that if you don't have a default value in the query parameter associated with the visualization you can’t edit the visualization. But if you add a default value in the query parameter, then edit and then remove the default value the dashboard remains as edited. Is there a way to not have a default parameter and edit the visualization without going through this?

[Kaniz]

Hi @EDDatabricks, Thank you for sharing your requirements and observations regarding the Lakeview dashboards. Let’s address each of your points:

1. Dynamic Filters Based on Current Timestamp: To achieve dynamic filtering based on the current timestamp, you can use parameters in your queries. Specifically, you mentioned that your queries have the form: "SELECT * FROM test_table WHERE timestamp BETWEEN :Days_start AND :Days_end". To make these parameters dynamic, you can set their default values to be calculated expressions.

   For example:

   • Set the default value for :Days_start to be the current timestamp minus 7 days.
   • Set the default value for :Days_end to be the current timestamp.

   This way, whenever the dashboard is refreshed, the parameters will automatically adjust to the desired time range. You can achieve this by using the appropriate expressions in your query parameters.

2. Setting Up a Pre-Set Warehouse: To ensure that your dashboard runs on a specific warehouse without manual selection, you can follow these steps:

   • In your Databricks workspace, navigate to the cluster configuration.
   • Create a dedicated cluster with the desired configuration (e.g., specific instance types, number of workers, etc.).
   • Save this cluster configuration as a preset or template.
   • When creating your Lakeview dashboard, specify this preset cluster as the target for execution. This way, every time the dashboard runs, it will use the predefined warehouse.

3. Inconsistencies with Filters and Visualization Parameters: The issues you mentioned regarding filter selection and visualization parameters are likely related to how you’ve set up your queries and filters. Let’s address them individually:

   • Filter Selection Issue:

     • Ensure that the filter parameters are correctly mapped to the query parameters.
     • Double-check that the filter values are being passed to the query correctly.
     • If you encounter issues with date selection, verify that the filter type (Date and Time) matches the query parameter type.
     • If you’re unable to choose a different date, it might be related to the filter configuration or interaction with the dashboard UI. Investigate the filter settings and any customizations you’ve made.

   • “Missing Selections for Parameters” Error:

     • This error occurs when a visualization expects certain parameters to be selected but they are not provided.
     • Make sure that all required parameters (including the date range parameters) are correctly set in the filter.
     • Check if any visualization-specific parameters are missing or not properly configured.
     • If you encounter this issue, review the visualization settings and ensure that all necessary parameters are selected or have default values.

   • Editing Visualizations without Default Parameters:

     • Unfortunately, Databricks currently requires default values for query parameters to allow visualization editing.
     • As a workaround, you can set temporary default values during the editing process and then remove them afterward.
     • Alternatively, consider using Databricks REST APIs or automation scripts to programmatically update visualization parameters without relying on manual edits.

If you need further assistance, feel free to ask! 😊