1. 

   ajdjackson, Member

   Hi

   I'm trying to create a line chart with 2 series. The first series is cumulative sales value and the second series is cumulative number of sales.

   The code I'm using is:

   select 'dbr.chart', 'msline', 'Cumulative Contribution'; select 'dbr.colstyle',2,'%0.0F'; select 'dbr.running_total',2; select 'dbr.chart.options','showvalues',0; select 'dbr.chart.options','axis','Year','Contribution, £,000s'; select 'dbr.chart.options','imagechart'; select 'dbr.chart.options','rotatenames',90; select 'dbr.chart.options','title_font_size',20; select 'dbr.chart.options','title_color','blue';

   select

   t.Period_ID as 'Year', sum(t.Sales_QTY) as '#Sales', sum((t.Sales_QTY*u.TL_Contrib)/1000) as 'Contrib ,000s' from salestimeline_tmp t join hilmark.tblsitesjake u where t.Site_ID = u.Site_ID group by t.Period_ID;

   This is not giving me what I want. It works fine if I want to show only a single line by commenting out one of the sum statement lines.

   I've looked at the demo charts but can't quite follow the queries.

   Thanks for your help.

   Cheers

   Jake
2. 

   myDBR Team, Key Master

   Jake,
   you are combining two separate series into a a chart, so you ust combine the queries.

   The chart query for for multiseries charts is:

   select Category, SeriesName, SeriesValue
   
   from mydb.Data;

   So in your query combining two query sets into one is:

   
   
   select 'dbr.running_total', 'value';
   
   select
   
     t.Period_ID as 'Year',
   
     'Sales',
   
     sum(t.Sales_QTY) as 'value'
   
   from salestimeline_tmp t
   
     join hilmark.tblsitesjake u
   
   where t.Site_ID = u.Site_ID
   
   group by t.Period_ID
   
   union
   
   select
   
     t.Period_ID,
   
     'Contrib ,000s',
   
     sum((t.Sales_QTY*u.TL_Contrib)/1000)
   
   from salestimeline_tmp t
   
     join hilmark.tblsitesjake u
   
   where t.Site_ID = u.Site_ID
   
   group by t.Period_ID;
   
   

   --
   myDBR Team
3. 

   ajdjackson, Member

   Hi

   Thanks for getting back.

   I've tried the above but I get an error: Can't reopen table: 't'

   Any thoughts

   Cheers

   Jake
4. 

   myDBR Team, Key Master

   OK,
   did not recognize that you were using temporaty table. Problem is the silly limit in MySQL where a temporary table cannot be used twice in same query. Now that you have two parts of the union both accessing salestimeline_tmp-temporary table, MySQL is unable to process it.

   The (somewhat boring) solution is is to create a clone of the temporary table (copy the content from the original) and use that in the second part of the query.

   --
   myDBR Team
5. 

   ajdjackson, Member

   Thanks for that.

   I duplicated that temp table and the query worked great.

   Now if I could only find a way of freezing the leftmost 'n' columns of a crosstab.....

   Jake

Reply

You must log in to post.