Creating Custom Fields In Navicat BI: Custom Sort Orders

Aug 23, 2024 by Robert Gravelle

In Navicat BI, data sources reference tables in your connections or data in files/ODBC sources, and can select data from tables on different server types. The fields in the dataset can be used to construct a chart. In fact, when building a chart, you will need to specify the data source that’s used to populate the chart.

As we’ve seen throughout this series, data sources support custom field types. These include: Type-Changed, Concatenated, Mapped, Custom-Sorted, and Calculated. The last blog covered how to use Custom-Sorted Fields to sort chart data according to a reference field. This week, we’ll be learning how to set an explicit sort order. In order to do so, we will create a Vertical Bar Chart for the free “dvdrental” sample database that shows a sum of movie rental proceeds by month.

As mentioned earlier, our chart will require a data source that fetches the relevant data, so let’s create a new data source named “Rentals by Month”.

Here’s a query that I created in Navicat for PostgreSQL:

We can now import it into our data source by clicking the Import Query button:

After refreshing the data, we can see the query fields and results:

Time to design our chart. First, let’s see what happens when we sort by month name:

As you can see, this sorts the bars alphabetically according to the month name, and not in chronological order. To do that, we’ll need to add a Custom-Sorted field to the data source by right-clicking the month (Control-click on macOS) in the field list and selecting New Custom Field -> New Custom-Sorted Field… from the context menu:

In the New Custom-Sorted Field dialog, we can now verify that the “Custom” radio button is selected, and proceed to move each month from the Suggested Values list into the Sorted Values using the arrow button (highlighted in red below):

If you ever make a mistake, no need to worry! You can just select the item and use the up and down arrows to change its position in the list.

Once you’re satisfied with the sort order, click the OK button to close the dialog.

You should now see the new Custom-Sorted field in the query results:

Note that this will not affect the sort order in the data source, but it will once we add our new field to the chart and apply a sort to it.

If we now set the Custom-Sorted field as the chart Axis and sort it in ascending order, the bars will now follow the sort order that we assigned in the New Custom-Sorted Field dialog:

This blog covered how to use Custom-Sorted Fields to sort chart data according to an explicit sort order. Next week, we’ll be moving on the final custom field type of the series: Calculated Fields.

You can download Navicat BI for a 14-day fully functional FREE trial. It’s available for Windows, macOS, and Linux operating systems. You’ll also find Navicat BI bundled with Navicat Premium and Enterprise Editions of Navicat for MySQL, Oracle, PostgreSQL, SQLite, SQL Server and MariaDB.