Skip to end of banner
Go to start of banner

Sub-query

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

With effect from 2022-03-24 dashboard release:

Sub-query introduces the ability to use charts as filter values, this means that charts are run as sub-queries to help build the data of the main chart.

What it is:

In the example below:

  • A chart called ‘Sales Overview’ has a filter

  • And the value of this filter is using a chart called ‘Cities with Outstanding Sales’

  • This would result in Sales Overview only showing data related to these cities

This works by running the SQL query of the sub-chart and using the values from the sub-chart to populate the filter for the ‘Sales Overview’ chart.

Depending on the Operator used on the filter

  • in/not in operator - the first column of the data from the sub-chart is used (multiple values, as described on the tooltip in the screenshot)

  • top/bottom operator - sub-query charts are not allowed, so you don’t have the option to put a chart here

  • single value operator - the first data point (first row of the first column) is used as the value

 

When clicking on the drop down to pick the value for a filter, not only data can be used, charts are now also allowed to be selected as below. The type of charts allowed to be used as sub-query are similar to the merged chart/table

  • if a chart is allowed to be used as a sub-chart of a merged chart or table, it can be used as sub-query, this means

  • lines, bars, tables are allowed

  • pies, special shapes, maps, merged charts/tables and so on are not allowed

 

Reasons to use sub-query:

Sub-query mainly enables the following capabilities:

  • it allows sub-chart data from another data connection, e.g.

    • chart 1 is from a Sales data connection

    • chart 2 is from a HR data connection

    • chart 1 can have a filter, and the value is chart 2

    • this means you can view the sales related to employees who take a lot of holidays

  • it allows sub-chart data from another chart, which is built with complex sql query structure

 

Performance about sub-query:

Since sub-query charts are building charts to be used by other charts, the time it takes to display a chart will be the total time it takes to build all the charts used. You can see a breakdown of these times by clicking Chart Tools>Chart Information and selecting the ‘Debug’ tab - add screenshot.

To reduce the length of time taken to draw your chart, try to avoid using lots of sub-query references in your charts.

 

Potential problems:

  • Cycle referencing: If Chart 1 uses Chart 2, Chart 2 uses Chart 3, Chart 3 uses either Chart 1 or Chart 2 as sub-query, the system shows an error

  • Sub-charts are changed: If a sub-chart is edited afterwards, you may get unexpected results on the main chart while the main chart is not likely to be broken, because the main chart simply just picks the first column or first data point of the sub-charts, it doesn’t care how the sub-charts are configured

  • Sub-charts not having data: again, the main chart is not likely to display data since there isn’t anything relevant

  • No labels