There may be times where you need to add values to your charts or dashboard variables which are not 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 bucketed them by days overdue. We want to be able to filter the chart by the custom date buckets in our chart which aren’t found in our database.
To do this, we’ll create a Dropdown with the same buckets we created in our chart and connect it to the chart following the steps below:
Create a custom Dropdown
1. On your dashboard’s right-side menu, click ‘Add Control’ and select Dropdown.
2. In your Dropdown’s editor, switch to SQL Mode to manually enter data. As you’ll be adding data which isn’t present in your database, you can use any data source for this query. Just note that the syntax will need to be adjusted depending on the data source type selected. Here, we’ll use our PostgreSQL Demo Data Source.
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 following the format below:
SELECT ‘value’ AS “ColumnName”
If you’d like to add multiple values within the same column, you can do so by adding a UNION statement. This will stack the created values as different rows. For example:
SELECT '0-29' AS "Bucket" UNION SELECT '30-59' AS "Bucket" UNION SELECT '60-89' AS "Bucket" UNION SELECT '90+' AS "Bucket"
4. Once you’ve added the values needed to the SQL Mode editor, fill out the rest of your Dropdown settings and click ‘Save Chart’. Your Dropdown will now be saved to your dashboard and can be placed where you’d like.
Link the Dropdown to your chart
Once you’ve set up your custom Dropdown, you’ll need to link it to your chart. Depending on how your chart is set up, you can either add your Dropdown as a filter in the initial query or add it using a Filter step in the Data Pipeline.
In this example, as 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 clicking on ‘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 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.
You can also try out the same query format to add rows and columns to your charts as well. Don’t hesitate to reach out to our support team with any questions.
Check out a video of how to add custom data to your Dropdown here: