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;

If you do not want to use the built-in Flash chart functionality, the Google Chart API is a simple alternative.