Documentation

Click To Filter For SQL Charts

Charts created using the Drag & Drop interface can always be used as quick filters on your dashboard. However, when you are using SQL queries in your charts there are some rules that you will need to stick to, in order to use the values from your chart as quick filters. Please have a look at the information below to enable click-to-filter for your SQL charts.

 

Avoid aliases in subqueries

 

Avoid the use of aliases when working with subqueries in your SQL charts. For demonstration purposes, we’ve created two very simple SQL queries below using subqueries. You can run both queries on our demo database. However, only the dimension ‘Channel’ from the query on the left may be used as Quick Filter, while the query on the right does not work due to the aliases used in the subquery for this field.

 

SQL working as Click-to-Filter SQL not working as Click-to-Filter
 

Select a.CustomerID, a.Channel

From

(Select count( `Customer`.`CustomerID`) as CustomerID, `Customer`.`Channel`

From `Customer`

Group by 2)a

 

 

Select a.CustomerID, a.Channel

From

(Select count( `Customer`.`CustomerID`) as CustomerID, `Customer`.`Channel` as ‘Marketing Channel’

From `Customer`

Group by 2)a

 

 

Specify the date format

 

In case you are using a custom date format as a dimension in your SQL query, you will be asked to specify this format on the dashboard before you can use the date as quick filter.

 

Let’s assume we have a chart on our dashboard with the following SQL query using a custom date format:

 

Select DATE_FORMAT( `Customer`.`Signup_date`, ‘%Y %m’), count(`Customer`.`CustomerID`)

From `Customer`

Group by 1

 

On the dashboard tab, enable click-to-filter for this chart and then click on one of the data series in this chart. In the Date Format popup, you will then be asked to define the format used in the SQL query.

 

  1. Simply specify the format used in the textbox next to Date Format.
  2. You can use the list of codes on the right to define the right format.
  3. Then Save your date format.

You can now use the date as filter. Please note, if you make changes to the SQL query you might need to redo this process.

select data format for click-to-filter for sql charts