1. 

   Selvi, Member

   I would like a chart to be the format of "Column". I want the chart to pick up the sum of the second column from the select statement for 'y' and the first column for 'x'. My select statements are as follows: (As the main query is complicated, I would like to avoid the "sum" function to be in the second query. I was wondering if the chart of myDBR can do this "sum" function similar to crosstab/reports in myDBR if possible.)

   Can I accomplish the following three steps in some form in myDBR?

   select 'dbr.chart', 'Column';
   select 'dbr.sum', 2;

   select DATE(h.some_date) as DATE1, max(h.field1)-min(h.field1) as SOME_NUMERIC from mydb.table1 h join mydb.table2 m on h.t1_id=12.id where DATE(h.some_date) >= '2009-06-13' and DATE(h.some_date) <= DATE_ADD('2009-06-13', INTERVAL 6 DAY) and t2.some_field='XYZ' group by t2.ID, DATE1 order by DATE1;

   My objective is to plot DATELL versus sum(max(h.field1)-min(h.field1)).
   Query explanation: Getting (max-min) value for field1 pertaining to each ID for different dates. As "some_field" is in a different table, I had to join on an ID field.

   Please post any suggestions as to if the aggregate functions can be carried out under "charts" similar to "reports".

   Thanks.
2. 

   myDBR Team, Key Master

   The 'dbr.sum' is for the table reports. Chart reports expect the data to be ready.

   If understood your query correctly you are looking something like this:

   select 'dbr.chart', 'Column';
   
   select date1 as 'DATE1', sum(minmax) as 'SOME_NUMERIC'
   
   from (
   
   	select t2.id, DATE(h.some_date) as 'date1', max(h.field1)-min(h.field1) as 'minmax'
   
   	from mydb.table1 h join mydb.table2 m on h.t1_id=12.id
   
   	where DATE(h.some_date) >= '2009-06-13' and DATE(h.some_date) <= DATE_ADD('2009-06-13', INTERVAL 6 DAY) and t2.some_field='XYZ'
   
   	group by t2.id, DATE(h.some_date)
   
   ) as q
   
   group by date1;
   
   
3. 

   Selvi, Member

   Thanks for your response/help!

Reply

You must log in to post.