Connect a control outside of a query in Visual SQL

In most cases, you’ll probably connect your controls to a chart by referencing it in a query. While this is the easiest way to do it, you can actually connect your controls in other Visual SQL steps too.

One advantage for connecting a control outside of a query is to improve performance on your data source. When the control is connected in the query itself, new queries are sent to your data source whenever a dashboard viewer updates the control’s values. By connecting the control outside of a query, new queries are not sent to your data source whenever the control’s values change because the calculations and transformations performed for non-query steps are applied to cached query results.

Connect a “Date bucket” control

To connect a “Date bucket” control in a non-query step:

1. Ensure that the original datetime column in your query uses the Second time bucket; otherwise, this process won’t work. Learn more about changing the time bucket using the aggregation menu.

2. Select the Apply formula step on your datetime column.

   1. Select Custom as the formulas type.

   2. Use the following CASE statement:

      1 2 3 4 5 6 7 8 9 10 case when {DATE_BUCKET_NAME}='minute' then strftime('%Y-%m-%d %H:%M', "Column_Name") when {DATE_BUCKET_NAME}='hour' then strftime('%Y-%m-%d %H:00', "Column_Name") when {DATE_BUCKET_NAME}='date' then strftime('%Y-%m-%d',"Column_Name") when {DATE_BUCKET_NAME}='week' then datepart("Column_Name", 'year')||'-W'||((strftime('%j', date("Column_Name", '-3 days', 'weekday 4')) - 1) / 7 + 1) when {DATE_BUCKET_NAME}='month' then strftime('%Y-%m',"Column_Name") when {DATE_BUCKET_NAME}='year' then strftime('%Y',"Column_Name") when {DATE_BUCKET_NAME}='quarter' then strftime('%Y', "Column_Name")||'-Q'||((datepart("Column_Name", 'month')+2)/3) else "Column_Name" end

   3. Select Save.

3. Select the Group & aggregate step.

   1. Select Group for the datetime column and the appropriate aggregations for the other columns.

   2. Select Save.

Connect a “Dropdown” control

If Multi-select is deselected for your “Dropdown” control, connect it outside of a query by doing the following:

1. Select the Filter step.

2. Add the following filter condition for your column:
   
   

   1 "Column_Name" = {DROPDOWN_NAME}

3. Select Save.

If Multi-select is selected for your “Dropdown” control, connect it outside of a query by doing the following:

1. Select the Filter step.

2. Add the following filter condition for your column:
   
   

   1 "Column_Name" is one of {DROPDOWN_NAME}

3. Select Save.

Connect a “Text input” control

If Multi-value is deselected for your “Text input” control, connect it outside of a query by doing the following:

1. Select the Filter step.

2. Add the following filter condition for your column:
   
   

   1 "Column_Name" = {TEXT_INPUT_NAME}

3. Select Save.

If Multi-value is selected for your “Text input” control, connect it outside of a query by doing the following:

1. Select the Filter step.

2. Add the following filter condition for your column:
   
   

   1 "Column_Name" is one of {TEXT_INPUT_NAME}

3. Select Save.

Connect a “Calendar” control

If the Data type of your “Calendar” control is Date, connect it outside of a query by doing the following:

1. Select the Filter step.

2. Add the following filter condition for your column:
   
   

   1 "Column_Name" = {CALENDAR_NAME}

3. Select Save.

If the Data type of your “Calendar” control is Date range, connect it outside of a query by doing the following:

1. Select the Filter step.

2. Add two filter conditions: one for the start date and one for the end date.

   1. For the start date:
      
      

      1 "Column_Name" >= {CALENDAR_NAME.START}

       

   2. For the end date:
      
      

      1 "Column_Name" <= {CALENDAR_NAME.END}

3. Select Save.