Dynamic dimensions on your dashboard

Data Pipeline

Using the Pipeline, it is possible to have the ability to dynamically change between dimensions on your dashboard. This will allow dashboard viewers to select only the fields they are interested in viewing. For this example, we are using the Chartio Demo data source dataset based on Postgres.

  1. On the dashboard, click Add Control from the sidebar and choose Dropdown from the options.
  2. Switch to SQL Mode and type in the following and click Run Query:

    SQL codeblock

    If you have more than two dimensions, add additional UNIONs and SELECTs with the dimension names. The aliases you set will need to match the names exactly as they are in the table or chart.

  3. Below the SQL Editor, rename the variable to something unique like DIMENSION. Make sure that the Multi-select option is unselected. Add one of the dimension names as the Default Value. We will be using Rep Name in this example.

    Select dropdown settings

  4. If you already have your table created, chose to Edit it. If not, create a new chart and add both dimensions and both measures to the chart.
  5. Once the initial columns are added, add a step in the Pipeline. For this example, click Add Column and name it. Choose Custom formula and enter a case statement:

    CASE WHEN {DIMENSION} = ‘Rep Name’ THEN “Rep Name” ELSE “State” END

    Add Column in Pipeline

    If you have more dimensions, add additional WHEN and THENs clauses to your CASE statement. Click Apply and Close.

  6. Add a hide columns step in the pipeline and then select Rep Name and State to hide these columns in the final chart.
  7. Add a group step in the pipeline. Set your Dimension columns to use the group option and set your measures column to use the SUM aggregation.

    Group in Pipeline

  8. Reorder the new dimension column to the top of the list and rename the measures as wanted.

Related Data Pipeline Help Articles

See more