Jornica: 12.2007

13.12.07

Integrating the Google Chart API in APEX

A few days ago, Google released the Google Chart API. By calling a special crafted URL Google returns a image containing a chart derived from the parameters included in the URL. For instance

http://chart.apis.google.com/chart?cht=p3&chd=t:3,5,6,0&chs=200x100&chl=10|20|30|40
&chco=ff0000,00ff00,0000ff,000000

will give the image shown left. At the right the Flash equivalent generated by APEX is shown.

To include this dynamic Google chart based on a query on a page, define a PL/SQL dynamic content region with the following PL/SQL source:

DECLARE
l_url VARCHAR2(2000) := 'http://chart.apis.google.com/chart?cht=p3&chs=200x100&chco=ff0000,00ff00,0000ff,000000';
l_chd VARCHAR2(2000);
l_chl VARCHAR2(2000);
BEGIN
FOR rec IN
(SELECT d.deptno LABEL,
   COUNT(e.empno) VALUE
 FROM dept d,
   emp e
 WHERE d.deptno = e.deptno(+)
 GROUP BY d.deptno)
LOOP
  l_chd := l_chd || ',' || rec.VALUE;
  l_chl := l_chl || '|' || rec.LABEL;
END LOOP;
l_url := l_url || 'chd=t:' || SUBSTR(l_chd,   2) || '&chl=' || SUBSTR(l_chl,   2);
htp.p('<img src="' || l_url || '"/>');
END;