Documentation Dashboards FAQs

Filter charts with custom Dropdowns

There may be times where you need to add values to your charts or Dashboard Controls that aren’t present in your database.

In some cases, creating a quick Google Sheet and connecting it to your Chartio account may be useful. In other cases, you may want to add a column or rows in Chartio directly.

Check out our step-by-step video showing how to add custom data to your Dropdown here:

In the example below, we have a Table chart listing out our overdue accounts and bucketing those accounts by the number of days overdue. We want to filter the chart by the custom date buckets in our chart that aren’t found in our database.

To do this, we’ll create a Dropdown with the same buckets we created in our chart then connect it to the chart following the steps below:

Create a custom Dropdown

  1. On your dashboard’s right sidebar, click Add Control > Dropdown.

    Click Add Control and select Dropdown

  2. In your Dropdown’s editor, switch to SQL Mode to manually enter your data. Since you’ll add data that isn’t present in your database, you can use any data source for this query. Just note you need to adjust the syntax depending on the data source type selected.

    Here, we’ll use one of our PostgreSQL demo data sources.
    Switch to SQL Mode

  3. Add a SELECT statement with the value you’re adding between single quotes. Then, add the name of the column you’d like to create between double-quotes using the format below:

    select 'value' as "Column_Name"  
    

    replacing value with your value and Column_Name with the name you’d like to give the new column.

    If you want to add multiple values within the same column, you can do so by adding a UNION clause. This stacks the created values as different rows within your new column. For example:

    select '0-29' as "Bucket"
    union
    select '30-59' as "Bucket"
    union
    select '60-89' as "Bucket"
    union
    select '90+' as "Bucket"
    

    Tip! You can also use the same query format to add rows and columns to your charts.

  4. Once you’ve added the desired values for your Dropdown, fill out the rest of your Dropdown settings and click Save Chart. Your Dropdown is saved to your dashboard, and you can arrange it where you’d like.

    Date Bucket Dropdown

Once you’ve created your custom Dropdown, you’ll need to link it to your chart. Depending on how your chart is set up, you can connect the Dropdown to your chart in one of two ways:

  • Add your Dropdown as a filter in the initial query
  • Add it using a Filter Rows step in the Data Pipeline.

In this example, since the buckets are created in the Pipeline, we’ll add the filter after the last Pipeline step.

  1. Open the chart editor by clicking on the ellipsis on the top right corner of your chart and click on Edit Chart Data from the menu.
  2. Click the + or +Add Transformation in the Data Pipeline and select the Filter Rows step.

    Filter Rows Pipeline step

  3. Fill out the Pipeline step information and add your Dropdown as a Custom Input in between curly brackets following the format below: {DROPDOWN_NAME}

    Filter Rows using the Date Bucket Dropdown

And there you have it, you can now use your custom Dropdown to filter your chart.