Dynamic dimensions on your dashboard

Data Pipeline

Using the Data 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 PostgreSQL syntax.

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

    select 'Rep Name'
    union
    select 'State'
    

    SQL codeblock

    Note: If you have more than two dimensions, add additional UNION operators and SELECT statements with the additional 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 dashboard 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 (optional). We will be using Rep Name as our default value in this example. Click Save Chart when done.

    Select dropdown settings

  4. If you’ve already created your table, chose to Edit it to add your new dropdown as a filter. If you haven’t one yet, 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 give your new column a name. Then, choose Custom formula as the formula type and enter a case statement in the Formula field:

    case when {DIMENSION} = 'Rep Name' then "Rep Name" else "State" end
    

    Add a column in the Pipeline with a case statement

    Note: If you have more dimensions, add additional WHEN and THEN clauses to your CASE statement. Click Apply & 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 remaining measures column to use the SUM aggregation. Click Apply & Close.

    Group in Pipeline

  8. Add a Reorder Columns Pipeline step and reorder the new dimension column to the top of the list. Rename the measures as wanted.

Related Data Pipeline Help Articles

See more