How to create a Treetable (parent-child hierarchies) on CDE

Introduction

In this blog post I will be showing how to create a treetable using Pentaho CDE as shown in the bellow picture using the jQuery treetable plugin.

How to use it

First, you should add a unique data-tt-id attribute to each of the rows in your table, for example 'node-x'. Then you add a data-tt-parent-id attribute to each child of a node, give this class a value of 'node-x'. The node-x part should be the same as the data-tt-id of its parent. Do you still follow me? Let me show you an example of a very simple tree: a single parent with a single child.

<table>
  <tr data-tt-id="1">
    <td>Parent</td>
  </tr>
  <tr data-tt-id="2" data-tt-parent-id="1">
    <td>Child</td>
  </tr>
</table>

Please note that the plugin expects the rows in the HTML table to be in the same order in which they should be displayed in the tree. For example, suppose you have three nodes: A, B (child of node A) and C (child of node B). If you create rows for these nodes in your HTML table in the following order A - C - B, then the tree will not display correctly. You have to make sure that the rows are in the order A - B - C.

How to do in the CDE

Download the jQuery treetable plugin at http://plugins.jquery.com/treetable/.

Unzip the file and make a copy of the jquery.treetable.css, jquery.treetable.theme.default.css and jquery.treetable.js files to your solution project folder.

Create a new dashboard and add those files as a Resources.

Define your CDE layout, add a Table Component and a DataSource.

On the Draw Function of the Table Component, write a function to add correctly the attributes data-tt-id and data-tt-parent-id for every row.

On the Post Execution Function of the Table Component, apply the jQuery treetable plugin at the table using the code:

function() {
    $('#' + this.htmlObject + ' table').treetable({ expandable: true });
}

If everything are correct, you will get a nice treetable.

Additionally, you can apply some CSS to customize the table.

Example

Now, I'm going to show a real example!

For this sample works, the resultset from the Datasource MUST have in the first column the data in following format:

    1 - AAAAA
    2 - BBBBB
    2.1 - CCCCC
    2.1.1 - DDDDD
    2.2 - EEEEE
    3 - FFFFF

Do the steps defined in the before section, but on the Draw Function, use the following code:

function f() 
{
    var table = $('#' + this.htmlObject + ' table');
    var rows = $('tbody tr', table);
    var oTable = table.dataTable();

    rows.each(function(i) {

        var rowData = oTable.fnGetData(this);

        // Get ID and Parent_ID
        var res = rowData[0].split("-");
        var node = res[0].trim();
        $(this).attr('data-tt-id', node);   
        
        var nodeMatch = node.match(/^[0-9\.]+\./g);
        if (nodeMatch != null) {
            var aux_node_pai = nodeMatch[0];
            var node_pai = aux_node_pai.substr(0, aux_node_pai.length-1);        
            $(this).attr('data-tt-parent-id', node_pai);   
        }

    });
}

The code above, will identify the ID and Parent_ID and set correctly the attributes data-tt-id and data-tt-parent-id for every row.

Download the sample files here.

More Information

For more parameters and additional information about the plugin access http://ludo.cubicphuse.nl/jquery-treetable/.



That's it !!!!