• Products
  • Documentation
  • Resources

Atlassian Analytics is in an early access program

Only people who are part of this invite-only early access program have access to Atlassian Analytics.

Connect a variable control outside of a query in Visual SQL

In most cases, you’ll probably connect your variable 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.

Unlike filtering in a query, the column that you want to connect your control to must exist in your result set. If you no longer need that column in your result set after connecting your control, you can always hide it. Learn more the “Hide column” step.

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.

Keep in mind that connecting a multi-select “Dropdown” control in a “Filter” step does not handle the Show all setting.

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.

Just like multi-select “Dropdown” controls, connecting a multi-value “Text input” control in a “Filter” step does not handle the Show all setting.

If your “Text input” controls uses wildcards, connecting them outside of a query requires a little more work. Learn how to connect “Text input” controls that use wildcards.

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.

 

Additional Help