As early as the preview version of Navicat 17, we introduced some new business intelligence (BI) features, namely chart interaction and calculated fields. It should be noted that calculated fields are not the only available custom field types in Navicat BI. In fact, there are five types: type change, concatenation, mapping, custom sorting, and of course, computation. This blog will start with type change fields, laying the foundation for adding custom fields to charts
Change field type
The following is a SELECT statement to obtain sales revenue for each movie category:
It is similar to the query we saw in the previous chart tutorial, but with two important differences:
The field list contains rents_date
This query does not aggregate sales by category
We can see that the rents_date field contains the date and time:
Now, suppose we want to delete the time part in the date. We can edit the underlying query or add a new type change field to the existing data source. To do this, we will click on the rents_date title to select it, then click on the "New Custom Field" button and choose "Type Change Field..." from the pop-up menu:
Before clicking the "New Custom Field" button, select the rents_date column, and Navicat will know to copy the field. We will name the new field 'rents_date_no-time' and set it to date type:
In this way, we can break down sales by date in the chart.
After clicking the 'OK' button, we can see the new fields in the field list and data table:
Quick tip: If you need to convert a DATE field to a timestamp, you can select "Number" from the "Target Field Type" drop-down menu in the "New Type Change Field" dialog box:
Now we can use the new fields in the chart. Here is a vertically stacked bar chart displaying the daily sales revenue for each movie category:
Customize dates in the chart
It is worth noting that we can further customize the format of date and time fields in the chart. For example, we can select the "DD MMM YYYY" format from the "Date Format" section of the data attribute and change the date to the "DD MMM YYYY" format:
The new format will be immediately reflected in the chart:
