Thursday, 10 February 2011

Google Visualization API (Org Chart)

Wanted to have a go at making a family tree, and came across this API. It is worth noting this particular chart doesn't allow you to specify more than one node for the parent, so not exactly perfect for a family tree. See: http://code.google.com/p/google-visualization-api-issues/issues/detail?id=162.

I believe it worthwhile to read the getting started guide for google visualizations: http://code.google.com/apis/visualization/documentation/using_overview.html and the actual guide for using the organizational chart: http://code.google.com/apis/visualization/documentation/gallery/orgchart.html.

Why use this over the charts provided in APEX? For one thing, there is no organizational chart available, to my knowledge. For another, it is not flash ;-) That can only be a good thing!

Alright, down to business. Firstly create a table to store the data:


CREATE TABLE "PERSON"
  (
    "PERSON_ID" NUMBER NOT NULL ENABLE,
    "NAME"      VARCHAR2(200 BYTE),
    "PARENT_ID" NUMBER,
    CONSTRAINT "PERSON_PK" PRIMARY KEY ("PERSON_ID") ENABLE,
    CONSTRAINT "PERSON_PERSON_FK1" FOREIGN KEY ("PARENT_ID") REFERENCES "PERSON" ("PERSON_ID") ENABLE
  );
  /

  create sequence seq_person start with 1 increment by 1;
  /

CREATE OR REPLACE TRIGGER "BI_PERSON" before
  INSERT ON "PERSON" FOR EACH row BEGIN IF inserting THEN IF :NEW."PERSON_ID" IS NULL THEN
  SELECT seq_person.nextval INTO :NEW."PERSON_ID" FROM dual;
END IF;
END IF;
END;
/
ALTER TRIGGER "BI_PERSON" ENABLE;
/

Create an on demand process, to output an Array - which will be later used to draw the chart. I called mine getFamilyData:


declare
  v_out varchar2(4000);

BEGIN
  v_out := v_out || '[';

  FOR i IN (SELECT person_id, name, parent_id FROM person) LOOP
      v_out := v_out || '[';
      v_out := v_out || '{"v":"' || i.person_id || '", "f":"' || i.name || '"},';
      v_out := v_out || '"'||i.parent_id||'",';
      v_out := v_out || '"'||i.name||'"';
      v_out := v_out || '],';
  END LOOP;

  v_out := rtrim(v_out, ',');
  v_out := v_out || ']';
  htp.p(v_out);
END;


Create a form with report on the table you just created.

Create a HTML region - I placed mine below the report, as this seemed as convenient a place as anywhere.

In this region, add the following code (or adapt to suit your needs):


<div id="my_chart_div"></div>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
// <![CDATA[
    
    // Load the Visualization API and the piechart package.   
    google.load('visualization', '1', {packages: ['orgchart']});   
    // Set a callback to run when the Google Visualization API is loaded.   
    google.setOnLoadCallback(drawChart);   
    
    function drawChart() {  
        var data = new google.visualization.DataTable();         
        data.addColumn('string', 'Name');          
        data.addColumn('string', 'parent');         
        data.addColumn('string', 'tooltip');  
        $.post('f?p=&APP_ID.:0:&APP_SESSION.:APPLICATION_PROCESS=getFamilyData::::', 
            function(output){    
                data.addRows(JSON.parse(output));   
                var chart = new google.visualization.OrgChart(document.getElementById('my_chart_div'));   
                chart.draw(data);  
            });    
        }
// ]]>
</script>

Basically, you first need a div for which the chart will live.

You need to load the AJAX API, and load the orgchart package so we can draw it in the div. The way all of the charting works is to first create a DataTable object, specify the columns you want/need. Each chart has different specs - as far as column requirements go, so you need to look at what the chart you are using requires - for the org chart, three columns are required (you need not follow the names I gave mine, but they made sense to me):



  • Text - The Name which will appear on the chart node
  • Parent - A unique value which represents a nodes parent
  • Tool Tip - for when you hover over, the text that will be displayed
You can also have each value as an Object where you want the a return value and a display value. I have used this for the first column so if there are two person(s) with the same name, they will definitely be unique from their ID.

So I used AJAX to get the Array data of the person(s) in the database, which calls the on demand process created earlier. It is then necessary to parse it as JSON before passing it into the DataTables addRow function.

Finally the chart is drawn and voila, you have your family tree outputted on the screen.


Side note: Roel Hartman has already created a plugin for this chart type (amongst others). See http://www.apex-plugin.com/oracle-apex-plugins/dynamic-action-plugin/google-visualizations_29.html.

2 comments:

  1. This is all presuming we're asexual beasts though, right? Have you played with a multi-parent model?

    ReplyDelete
    Replies
    1. Wow, blast from the past! Haven't really come across a nice easy to use multi-parent visualization API.

      Delete