Oracle E-Business Suite Blog

When you embed a URL in an HTML <img> tag, take care to use the character entity reference &amp; in place of an ampersand (&).

There are many more types of charts available (Bars, Pie, Scatter etc)

To try it out, we took a simple requirement: Make a pie chart grouping all the employee by departments.

Lets create a simple anonymous block to illustrate the same. This PL/SQL block will get us a simple URL which we can copy/paste in browser and get a chart. Or better, use as an tag for a report!

Here's the PL/SQL block:

---------------------------------

DECLARE 
  chd  VARCHAR2(4000);
  chdl VARCHAR2(4000);
  chl  VARCHAR2(4000);
  CURSOR c IS
  SELECT  ROUND(cnt / SUM(cnt) OVER () * 100, 2) pct, cnt, dname
  FROM
  (
     SELECT department_name dname, COUNT(*) cnt
     FROM employees, departments
     WHERE employees.department_id= departments.department_id
     GROUP BY departments.department_name
  )
  WHERE cnt>1
  ORDER BY 3 DESC;
BEGIN
  FOR usage IN c
  LOOP
    IF chd IS NULL THEN
      chd  := usage.pct;
    ELSE
      chd := chd || ',' || usage.pct;
    END IF;
   
    IF chdl IS NULL THEN
      chdl  := usage.dname;
    ELSE
      chdl := chdl || '|' || usage.dname;
    END IF;
   
    IF chl IS NULL THEN
      chl  := usage.cnt;
    ELSE
      chl := chl || '|' || usage.cnt;
    END IF;
  END LOOP;
  dbms_output.put_line('http://chart.apis.google.com/chart?cht=p&chs=300x200&&chco=FF0000'
    || '&chtt=Employees per department'
    || '&chl=' || chl || '&chd=t:' || chd || '&chdl=' || chdl);
END;

---------------------------------

The generated URL is:

http://chart.apis.google.com/chart?cht=p&chs=300x200&&chco=FF0000&chtt=Employees per department&chl=45|34|6|2|5|6|3|2&chd=t:43.69,33.01,5.83,1.94,4.85,5.83,2.91,1.94&chdl=Shipping|Sales|Purchasing|Marketing|IT|Finance|Executive|Accounting

And the image generated by Google Chart API for this URL is:

Isn't that easy? Try it!

Just replace the SQL with yours and ensure the cursor logic works for you. And you're done.