1. 

   mleary2001, Member

   Good Morning,

   I need to dynamically set the colors of segments of bars in a Stacked Bar Chart. My task is to display the elapsed times that different engines have on or off over the course of a day.

   I almost have what I need in a stacked bar chart using just the first three fields, (screen shot) However, I need the 'on' segments of a bar to be green, and the 'off' segments to be red, based on the values in a fourth field, 'Ignition'.

   I have a MySQL procedure that returns a result set with four fields, such as:

   Engine DateTime Elapsed Ignition
   111 2015-01-02 01:03:12 01:03:15 On

   I think I almost have the answer with the following code:

   select distinct 'dbr.chart.options', 'bar_segment???', Ignition, '0xFF0000'from resultset??? where Ignition = 'Off'

   select distinct 'dbr.chart.options', 'bar_segment???', Ignition, '0x00FF00'from resultset??? where Ignition = 'Off'

   Any help will be much appreciated,

   Mike
2. 

   myDBR Team, Key Master

   Mike,
   you can use label_color-option for it:

   select 'dbr.chart.options', 'label_color', DateTime_column, if (Ignition='On', '0x00FF00', '0xFF0000')
   
   from yourdata;

   --
   myDBR Team
3. 

   mleary2001, Member

   Wow! Sounds like we are getting close! I am just not sure what to put in the 'from' clause. Here is my code:

   /*myDBR chart code */
   select 'dbr.chart', 'StackedBar';
   select 'dbr.title', 'Elapsed Times Between Events - Chart';
   select 'dbr.chart.options', 'label_color', Date, if (Ignition='On', '0x00FF00', '0xFF0000') from ???;
   CALL gpsdata.elapsed_time_when_asset_changes_state(selected_date, 0);

   Thanks in advance,

   Mike
4. 

   myDBR Team, Key Master

   Mike,
   you basically just set the color by label. The label in your case is the 'DateTime' column (whatever you call it in database) in the same query as your actual data (elapsed_time_when_asset_changes_state).

   --
   myDBR Team
5. 

   mleary2001, Member

   I think I am getting closer. I can see how to statically change the color of each bar segment using lable_color, but dynamically changing the color still eludes me.

   Here is my code up to this point. I now have a temp table and a simple query to bring that data into myDBR:

   DROP PROCEDURE IF EXISTS sp_DBR_Asset_Date_Elapsed
   $$
   CREATE PROCEDURE sp_DBR_Asset_Date_Elapsed(IN selected_date DATE)
   BEGIN

   /* make the temp table in the database */
   CALL gpsdata.elapsed_time_when_asset_changes_state(selected_date, 0);

   This table looks like:temp table

   /*myDBR chart code */
   select 'dbr.chart', 'StackedBar';
   select 'dbr.title', 'Elapsed Times Between Events - Chart';
   select 'dbr.chart.options', 'label_color', ‘event_date’, if (Ignition='On', '0x00FF00', '0xFF0000') from gpsdata.asset_ignition_elapsed_time_temp_table;

   /* select data from the temp table created above */
   SELECT
   Asset,
   event_date,
   Elapsed,
   Ignition
   FROM
   gpsdata.asset_ignition_elapsed_time_temp_table;

   The chart looks like this:
   Stacked Bar Chart

   Thanks in advance,

   Mike
6. 

   myDBR Team, Key Master

   You might want to select the data in event_date instead of string 'event_date' as a label_color option.

   select 'dbr.chart.options', 'label_color', event_date, if (Ignition='On', '0x00FF00', '0xFF0000')
   
   from gpsdata.asset_ignition_elapsed_time_temp_table;

   When you select string 'event_date', none of your lables (dates) matches the 'event_date' string and no color is applied.

   You can also move the dbr.title before the dbr.chart as is is a separate command and not an option to dbr.chart.
   --
   myDBR Team
7. 

   mleary2001, Member

   Touchtown! Thank you very much for your patience and quick responses!
8. 

   mleary2001, Member

   Good Morning, again,

   I have the bars the way I need them, here. Now if I can just get the legend the way I need it, here. I have found that if I switch the ‘event_date’ and ‘Ignition’ fields in my query the legend appears the way I need it, but the elapsed times are summed.

   Is there a way to customize the legend colors, similar to the ‘label_color’, below?

   ————— This is the code to get the desired bar segments ———————————

   /* make the temp table in the database */
   CALL rkhydro_gpsdata.elapsed_time_when_asset_changes_state(selected_date, 0);

   /*myDBR chart code */
   select 'dbr.title', 'Elapsed Times Between Events - Chart';
   select 'dbr.chart', 'StackedBar';
   select 'dbr.chart.options', 'tooltip', '[Asset] [event_date] [Elapsed]';
   select 'dbr.chart.options', 'label_color', event_date, if (Ignition='On', '0x42B64A^', '0xE85854') from rkhydro_gpsdata.asset_ignition_elapsed_time_temp_table;

   SELECT
   Asset,
   event_date,
   Elapsed,
   Ignition
   FROM
   rkhydro_gpsdata.asset_ignition_elapsed_time_temp_table;

   ————————— Rearranging the Ignition and event_date fields produces the desired legend —————
   SELECT
   Asset,
   Ignition,
   Elapsed,
   event_date
   FROM
   rkhydro_gpsdata.asset_ignition_elapsed_time_temp_table;

   Thanks in advance,

   Mike

Reply

You must log in to post.