In Navicat BI, data sources reference data from tables or files in your connection/ODBC source and can select data from different server types. The fields in the dataset can be used to construct charts. In fact, when building a chart, you need to specify the data source used to fill the chart.
As we have seen throughout the series, the data source supports custom field types. These types include type changes, connections, mappings, custom sorting, and calculated fields. The previous blog post introduced how to use custom sorting fields to sort chart data based on reference fields. This week, we will learn how to set an explicit sorting order. For this purpose, we will create a vertical bar chart for the free 'DVD rental' sample database, displaying the total monthly movie rental revenue.
Configure Data Source
As mentioned earlier, our chart requires a data source that can obtain relevant data, so let's create a new data source called 'Rentals by Month'.
This is the query I created in Navicat for PostgreSQL:
Now, we can click the 'Import Query' button to import it into the data source:
After refreshing the data, we can see the query fields and results:
Design the 'Sales by Month' chart
We are now starting to design our charts. Firstly, let's take a look at what happens when sorting by month name:
As you can see, this is sorting the bar chart alphabetically by month name rather than chronological order. To achieve this, we need to add a custom sorting field in the data source by right clicking on the month in the field list (holding down the Control key on MacOS), and then selecting New Custom Field ->New Custom Sorting Field from the pop-up menu...:
In the "New Custom Sorting Field" dialog box, we can now confirm that the "Custom" radio button has been selected, and then use the arrow button (highlighted in red in the figure below) to move each month in the suggested value list to the sorting value:
If there is an error, don't worry! You just need to select the item and use the up and down arrows to change its position in the list.
After being satisfied with the sorting order, click the "OK" button to close the dialog box.
Now you should be able to see the new custom sorting field in the query results:
Please note that this will not affect the sorting order in the data source, but when we add new fields to the chart and apply sorting to them, it will affect the sorting order.
If we now set the custom sorting field as the chart axis and sort in ascending order, the bar chart will now follow the sorting order we specified in the New Custom Sorting Field dialog box:
