Create 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.

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:

• In Visual SQL
• In Data Explorer

In the Visual SQL interface

1. Create a custom Dropdown

1. On your dashboard’s right sidebar, click Add Control > 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.

   

3. Add a SELECT statement, wrap the value you’d like to add in single quotes, and wrap your column name in double quotes. The SELECT statement should look like this:

   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, then save your Dropdown to your dashboard and arrange it where you’d like.

   

2. Link the Dropdown to your chart

Once you’ve created your custom Dropdown, you 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 Action.

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

1. Open the chart editor by clicking on the ellipsis on the top-right corner of your chart and click Edit Chart Data from the menu.

2. Click Filter above the Result Table to add a Filter Action to the Pipeline.

3. Enter your filter conditions, wrapping your custom Dropdown’s name in curly brackets (e.g., {DROPDOWN_NAME}).

   

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

In the Data Explorer interface

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

1. Create a custom Dropdown

1. On your dashboard’s right sidebar, click Add Control > 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.

   

3. Add a SELECT statement, wrap the value you’d like to add in single quotes, and wrap your column name in double quotes. The SELECT statement should look like this:

   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, then save your Dropdown to your dashboard and arrange it where you’d like.

   

2. Link the Dropdown to your chart

Once you’ve created your custom Dropdown, you 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 Edit Chart Data from the menu.

2. Click the + or +Add Transformation in the Data Pipeline and select the Filter Rows step.

   

3. Fill out the Pipeline step information and add your Dropdown as a Custom Input, wrapping the Dropdown’s name in curly brackets (e.g. {DROPDOWN_NAME}).

   

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