1. 

   john, Member

   I haven't been able to locate a working example of sort in a ms chart(but honeslty i could have looked a bit harder in the demo) and am having troubles implementing the Catagory sort by the sum of all the values, i can only get it to sort on the value in the first series. this is what i have tried
   select 'dbr.chart.options', 'category_sort', sum('value'), 'numeric' ;
   also without the sum, value is the name of the 3rd column
2. 

   myDBR Team, Key Master

   The option to use is 'category_order' (with 'category_sort' you can define the sorting method (string', 'numeric', 'natural)).

   If you want to sort based on sum of all the values, you need to calculate the sum and order the category_order parameter based on that.

   Assume you have a mscolumn chart:

   select 'dbr.chart', 'mscolumn', 'My chart';
   
   select name, quarter, sum(value)
   
   from data
   
   group by name, quarter
   
   order by name, quarter;

   In order to sort the quarters in each name based on sum of all values in a quarter you would do:

   select 'dbr.chart.options', 'category_order', quarter
   
   from (
   
     select quarter, sum(value)
   
     from data
   
     group by quarter
   
     order by 2 desc
   
   ) as q;
   
   

   i.e calculate the sort order of the quarters (descending order) and get the quarter and pass it to category_order as a parameter.

   --
   myDBR Team
3. 

   john, Member

   That is a wonderful answer, sql advice as well. might have taken me a while to get such a simple qry.
   but am having difficutly making it work
   I run the query in catagory_order on the report and it shows the correct order (ie seperate line and output the query), but the axis is not in that order(the y axis (by y i mean the vertical axis)). the catagory_order line does not affect the sort, comment it out and no difference.

   Here is the chart options line and the query for ms chart
   select 'dbr.chart.options', 'category_order', ShortName from (select c.`ShortName` , sum(c.`value`) from Tabletmphour c where c.Splicing = 1 group by c.`ShortName` order by 2 asc) as q;

   select c.`ShortName` as 'ShortName', c.`ProjectName` as 'Total',sum(c.`value`) as 'value' from Tabletmphour c where c.Splicing = 1 group by c.`ShortName` , c.`ProjectName` ;

   But now i am really confused as it looks like your post above is using a catagory sort on a series value. ie the second value im the select stmt for a ms chart is the series. from this page

   select Category, SeriesName, SeriesValue from mydb.Data;

   so in your example above quater is the series not the catagory and the command we are using is catagory_order ?

   or maybe i am wrong
4. 

   myDBR Team, Key Master

   John,
   the 'category_order'-option defines the data order within the category.

   The axis item order is defined by the chart query itself. The categories in the chart are shown in the order they are in the query result.

   So, if your Tabletmphour is a temporary table and you are using MySQL, you cannot use the same temporaty table twice in the query (a bug/design feature/limitation of MySQL). You need to calculate the order to another temp table first:

   create temporary table chart_order_tmp (
   
   ShortName varchar(255),
   
   value float
   
   );
   
   insert into chart_order_tmp
   
   select ShortName, sum(value)
   
   from Tabletmphour
   
   group by ShortName;
   
   select
   
     c.ShortName,
   
     c.ProjectName,
   
     sum(c.value)
   
   from Tabletmphour c
   
     join chart_order_tmp o on o.ShortName=c.ShortName
   
   where c.Splicing = 1
   
   group by c.ShortName , c.ProjectName
   
   order by o.value desc;
   
   

   --
   myDBR Team
5. 

   john, Member

   That is brialliant and yet more how to write select, thanks
   i used the code in your second post to choose custom order for series. using select 'dbr.chart.options', 'series' , [columnname] from ( select 1,2,3)as q;

   Can i do the following in StackedBar charts
   I want 1 to be same colour in both charts - easy
   I want 3 to be same colour in both charts (ie I don't want 3 in chart b to be the same colour as 2 in chart a)- maybe harder

   chart a bob 1 9 sue 2 8 jack 3 7

   Chart b sally 1 9 frank 3 6

   thoguht is use a procedure for chart colours (like in the docs) call this in report, and also series option like above - i guess the key here is if the colours would be applied to the chart data set or the value in the series order?
   reason is 6 charts on same report but all series don't show in all charts and would be nice if each item in series maintained colour accross charts.

   I could test the dataset and insert a 0 vlaue row for the series if it doesn't exist but then it would show in the legend as if it has data in the report.

   edit
   I suppose i could alternatly test the chart dataset against the series sort order and build the colour code on the fly skipping colours where series doesn't exist in dataset
6. 

   myDBR Team, Key Master

   John,
   If you want to use the same series color across multiple charts, you can define a helper procedure which uses the 'label_color' option:

   create procedure sp_default_chart_colors()
   
   begin
   
   select 'dbr.chart.options', 'label_color', series, color
   
   from (
   
     select 1 as 'series', '0xFF66CC' as 'color'
   
     union
   
     select 2, '0x9900FF'
   
     union
   
     select 3, '0x99FFCC'
   
   ) as q;
   
   end;
   
   

   
   Then just call the procedure before each chart:

   call sp_default_chart_colors();

   --
   myDBR Team

Reply

You must log in to post.