Hi,
I am using a view to build up a dynamic pie chart for each entry. The number of fields that are in that pie chart will change depending on if there are data for that field or not, as below:
CREATE VIEW elemental_chart_view AS
(SELECT Sample_ID as "Sample_ID","Carbon" as "constituent", CARBO as percentage from analytical WHERE Sample_ID IS NOT NULL AND CARBO IS NOT NULL)
UNION
(SELECT Sample_ID as "Sample_ID","Hydrogen" as "constituent", HYDRO as percentage from analytical WHERE Sample_ID IS NOT NULL AND HYDRO IS NOT NULL)
UNION
(SELECT Sample_ID as "Sample_ID","Nitrogen" as "constituent", NITRO as percentage from analytical WHERE Sample_ID IS NOT NULL AND NITRO IS NOT NULL)
UNION
(SELECT Sample_ID as "Sample_ID","Sulphur" as "constituent", SULPH as percentage from analytical WHERE Sample_ID IS NOT NULL AND SULPH IS NOT NULL)
So, for example if CARBO is null then that field will not display on the chart. However that means that the following fields have different colour sections in the pie chart than they would in a pie chart where there was data for the carbon field.
The only way I can get the colours to stay the same is using the following for each field:
(SELECT Sample_ID as "Sample_ID","Carbon" as "constituent", IFNULL(CARBO,0.00) as percentage from analytical WHERE Sample_ID IS NOT NULL)
However, that means that the legend gives a figure of 0 to Carbon in the chart, which is not correct.
Is there a way to manually assign pie chart colours to a field or, alternatively, to remove data labels from the chart when their value is 0?