Arranging series colour and legend or in Multiseries charts.

myDBR Forums » Charting

Arranging series colour and legend or in Multiseries charts.

(7 posts) (2 voices)

Started by jjr
Latest reply from jjr

Tags:

chart legend colours

jjr, Member
Hi,
From what I can tell, the chart options for setting a specific colour and a legend order don't work together.
I am using chart type 'stackedbar3d'.

I know that I can specify chart series order using (for example):
select 'dbr.chart.options','series', 'a','b','c';
select 'dbr.chart.color','0x00DCEFAE','0x0073ADEF','0x00EFB8B6';
However, if one of these series is missing from my data then the colour is not consistent to the series - i.e. if there is no "b" then "c" is given the second colour. However, I need it to show consistency in different reports and is surely more professional to have the same colour for the same values.

Therefore I tried to specify the colour specifically using:
select 'dbr.chart.options', 'label_color', 'a', '0x00DCEFAE';
etc
However, the order of the series listed in the legend appears in the order those series appear in my data, but I want them in a specific order (not necessarily alphabetical).

Therefore I thought I could use:
select 'dbr.chart.options','series', 'a','b','c';
to set the order.
But this does not work. It appears that the colours are being still set according to the order they are present in my data.

The same is true if I try to set the legend series order by ordering my data in my SQL query. This is because if my first data set does not have any "b" series, then again, "c" seems to be given "b"'s colour.

If this issue is not clear, here is a MyDBR test report to show the issue. In it I specify series "b" to be colour Red, but that is allocated to series "c" instead:
```
DROP PROCEDURE IF EXISTS sp_DBR_Test_10_Legend

$$

CREATE PROCEDURE `sp_DBR_Test_10_Legend`()

BEGIN

	select 'dbr.chart', 'stackedbar3d', 'test';

	select 'dbr.chart.options','series', 'a','b','c';

	select 'dbr.chart.options', 'label_color', 'a', '0x0095B3D7'; -- BLUE

	select 'dbr.chart.options', 'label_color', 'b', '0x00DA9694'; -- RED

	select 'dbr.chart.options', 'label_color', 'c', '0x00C4D79B'; -- GREEN

	SELECT '1' AS `category`,

		'a' AS `series`,

		1 AS `value`

	UNION

	SELECT '1' AS `category`,

		'c' AS `series`,

		1 AS `value`

	UNION

	SELECT '2' AS `category`,

		'a' AS `series`,

		1 AS `value`

	UNION

	SELECT '2' AS `category`,

		'b' AS `series`,

		1 AS `value`

	UNION

	SELECT '2' AS `category`,

		'c' AS `series`,

		1 AS `value`;

END

$$
```
Please can you advise me if there is a way around this (i.e. set the colour AND the order the series are listed in the legend).

Thanks,
Justin
myDBR Team, Key Master

Justin,
we were able to produce (and fix) the issue when 'series' and 'label_color' were used together when data was missing from a series. However, we were not able to reproduce the problem with 'dbr.chart.color' command. Mabye you have another example for that.

You can run the automatic updater to get the fix. See sample report.

--
myDBR Team
jjr, Member

Hi there,
Thanks so much for this. I'll install the updated version and also see if I can produce a demo of the other issue (although this is not a problem for me any more if I can use the other solution now) and I'll get back to you with feedback soon.
Thanks,
Justin.
jjr, Member

Hi,
I can confirm that the 'series/label_color' issue now seems fixed.
I can also confirm that I can't reproduce the error I thought I had with dbr.chart.color either. So either I made a mistake or there was some other combination of data I was using that caused it to behave unexpectedly but there doesn't seem to be the issue I initially thought.
So thanks, all seems fine and if I work out that there was another issue then I'll get back to you.
Thanks. Justin

jjr, Member

Hi,
I found the issue with dbr.chart.color.
It is actually with a different chart type - donut (and maybe others), which is why I couldn't reproduce it above with stackedbar3d. However, because it didn't work in donut I didn't want to rely on that method and probably didn't test it fully with stackedbar3d.
Anyway here is some code to reproduce the error. Being a donut chart it doesn't use series, but the principle is the same - "b" is given a green colour instead of red in the same way as the first error with stackedbar3d did. However, now you've fixed the first error, that fixed method does give the correct colours (by uncommenting the label_color lines):



DROP PROCEDURE IF EXISTS sp_DBR_Test_10_Legend

$$

CREATE PROCEDURE `sp_DBR_Test_10_Legend`()

BEGIN

	/*test of second issue*/

	select 'dbr.chart', 'donut', 'test';

	select 'dbr.chart.options','series', 'a','b','c';

	select 'dbr.chart.color', '0x0095B3D7', '0x00DA9694', '0x00C4D79B', '0x00B1A0C7', '0x0092CDDC';

	-- select 'dbr.chart.options', 'label_color', 'a', '0x0095B3D7'; -- BLUE

	-- select 'dbr.chart.options', 'label_color', 'b', '0x00DA9694'; -- RED

	-- select 'dbr.chart.options', 'label_color', 'c', '0x00C4D79B'; -- GREEN

	SELECT 'a' AS `category`,

		1 AS `value`

	UNION

	SELECT 'c' AS `category`,

		1 AS `value`

	UNION

	SELECT 'b' AS `category`,

		1 AS `value`;

END

$$

Regards,
Justin

myDBR Team, Key Master

Hi,
the 'series'-option is only for the multi-series charts. It will have no effect in single-series charts as there is no need for it. You can simply define the order in the data set. The example provided works as expected: the 'c'-sector of the donut will get the second color as it comes second in the data set.

The 'series'-option multi-series charts defines the second dimension which you cannot define in the data set as the primary one (category) may override it.

--
myDBR Team
jjr, Member

Hi,
Yes, fair enough. My initial instinct was that this was working as I would expect, but thought it worth pointing out this was where I found the similarity with the other issue in case there was another way of doing it or an issue. I will just use label_color option for the single series chart then.
Thanks.

Reply

You must log in to post.