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
On your dashboard’s right sidebar, click Add Control > Dropdown.
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.
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"
valuewith your value and
Column_Namewith 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.
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.
Link the Dropdown to your chart
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.
- 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.
Click the + or +Add Transformation in the Data Pipeline and select the Filter Rows step.
Fill out the Pipeline step information and add your Dropdown as a Custom Input in between curly brackets following the format below:
And there you have it, you can now use your custom Dropdown to filter your chart.