Filters based on dataset from SQL

Two tables -

  1. containing exclude list
  2. the other containing full list of servers.

Develop a filter widgets whereby selection on filter widget from table 1 will automatically exclude values from the list of items in filter widget from table 2.

We will use two features of Arcadia - dataset from SQL and parameters.

Setup

To establish the correct SQL let’s setup a dashboard using the the two tables.

Dataset 1 contains the exclude list. A filter from dataset 1 is used to make selections of the exclude list.

That will then drive the list of servers which will be excluded from Dataset 2.

Dataset from SQL

The look more closely into the tabular visual from Dataset 2 on the right. It contains the following SQL

SELECT TA_0.`main_servername` as `main_servername`, TA_0.`day` as `day`
  FROM `shaun`.`main_list_v1` TA_0 
  WHERE TA_0.`main_servername` not in 
  (
        SELECT TA_1.`servername` as `servername`
        FROM `shaun`.`exclude_list_v1` TA_1 
        WHERE TA_1.`exclude_list_filter` in (<<exclude_param.data:''>>)
  )
  LIMIT 100

image

We now want to save this SQL as a dataset using the “Save Table” feature:

Once you have created the dataset, reload the page and add server filter from the new dataset built from SQL.

This will automatically update based on selection from the exclude filter based on dataset

End to End

2 Likes