ba6.us - Dave's Database Related Stuff

Here is some code to update the previous widget to show data in a green/yellow/red bar graph like an audio meter: The code is here:

CREATE OR REPLACE FUNCTION GetGraph (p_value IN NUMBER, p_total IN NUMBER DEFAULT 100) 
RETURN VARCHAR2
IS 

  -- David Mann
  -- 05/26/2009
  -- Create HTML bar graph widget for use in Application Express reports. 
  -- Has thresholds for yellow and red. 
  -- Parameters: p_value - Top of ratio fraction or percentage as whole number (0-100)
  --             p_total - Bottom of ratio fraction or default to 100

  my_retval VARCHAR2(1024);
  my_usedpercentage NUMBER := 0;
  topgreen  NUMBER := 80; -- Top of green region
  topyellow NUMBER := 95; -- Top of yellow region
BEGIN

    if (p_total > p_value) then
        my_usedpercentage := 100 - ROUND( (p_value / p_total) * 100 );
    end if;

    my_retval := my_retval||'<table width=120 border=0 style=single bgcolor=#AAAAAA><tr><td>';
    my_retval := my_retval||'<table><tr>';
    
    
    if (my_usedpercentage >= 0 and my_usedpercentage <= topgreen) then 
      my_retval := my_retval||'<td width='||my_usedpercentage||' height=15 border=0 bgcolor=#00FF00></td>';
    end if;
 
    if (my_usedpercentage > topgreen and my_usedpercentage <= topyellow) then 
      my_retval := my_retval||'<td width='||topgreen||' height=15 border=0 bgcolor=#00FF00></td>';
      my_retval := my_retval||'<td width='||(my_usedpercentage-topgreen)||
          ' height=15 border=0 bgcolor=yellow></td>';
    end if;
 
    if (my_usedpercentage > topyellow) then 
      my_retval := my_retval||'<td width='||topgreen||' height=15 border=0 bgcolor=#00FF00></td>';
      my_retval := my_retval||'<td width='||(topyellow-topgreen)||
          ' height=15 border=0 bgcolor=yellow></td>';
      my_retval := my_retval||'<td width='||(my_usedpercentage-topyellow)||
          ' height=15 border=0 bgcolor=red></td>';
    end if;

  my_retval := my_retval||'</tr></table>';
    my_retval := my_retval||'</td></tr></table>';

    RETURN my_retval;

END;
/

-Dave