Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Tree and table view for grouped fields tables

tutorial table tree grouping view

  • Please log in to reply
No replies to this topic

#1 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 03 August 2012 - 11:18 PM

How many times did we found ourselves in the situation of needing to display a table which has more header information than real data ?
Haven't you then wondered : is it a regular table the way to go ?

Listings like this are the very example of boring data sheets:

Year month unit money
---------------------------------------
2011 Jan IT 100
2011 Feb IT 120
2011 Mar IT 85
2011 Apr IT 12
2012 Jan Sales 70
2012 Feb Sales 90
2012 Mar Sales 170
2012 Mar Sales 170

what about those lists ? boring, neverending, unlikely to be paid any attention, repetitive, if our work talks about us, then we for sure don't want to show any work like this, do we ?

so, what ideas do we get ?

- it would be nice to navigate the data in a tree structure
- it also would be nice if could easily group the cells so it does not look so granulated

well, that two ideas we are about to develop in this javascript tutorial, for what we will need those two images attached ( see below the open.gif and closed .gif )


We will work with the following :
(click on the items to go to w3schools manual for each subject)

- array object

- array push() method

- array join() method

- HTML table row span

so, we will assume you have a program, php maybe which generates large listings based on very grouped queries ( via group by sql statement ) and that you don't like how those results looks like in your output html table, so we will experiment displaying the data as a tree, just like the next illustration shows :

Posted Image

And as a well row-spamed table like this :


Posted Image

Looks nice right ? even without coloring the backgrounds it looks nice to the eye, year expands over its months, months covers the involved dept's statistics, and in case all that grouping has more than one record it shows independently, so how do we do this ?

before talking specifics let me tell you this tutorial deals in economy too, so we are going to use some kind of indexed array-tables likes this :

- An array with the column names
- One array per each grouped column
- One array for the records

Lets take a look to the following code which contains the table's data as discussed before, lets see how the arrays interact themselves and how that saves us memory/space/etc.


// - - - - - - - - - -- - -
// column configuration
// - - - - - - - - - -- - -

// column names
col_name = [ 'Year', 'Month', 'Department', 'Scores' ];

// grouped column values indexes
years  = [ '2011'    , '2012' ];  
dept   = [ 'IT', 'Sales' ];
month  = [ 'January'  , 'February','March' ] ;

// column index definition
col_defs = [ years, month , dept, 'Scores' ] ;

// last grouped column ( 0, 1, 2 )
var groupedColumns = 2;

// - - - - - - - - - -- - -
// - - - - - - - - - -- - -


// the recordset
with( records )
{
  push( [ 0, 0, 0, 144 ] );
  push( [ 0, 0, 0, 131 ] );
  push( [ 0, 0, 1, 119 ] );
  push( [ 0, 1, 0, 125 ] );
  push( [ 0, 1, 1, 171 ] );
  push( [ 0, 2, 1, 143 ] );
  push( [ 1, 0, 0, 180 ] );
  push( [ 1, 0, 1, 131 ] );
  push( [ 1, 0, 1, 154 ] );
  push( [ 1, 1, 0, 111 ] );
  push( [ 1, 1, 1, 149 ] );
  push( [ 1, 2, 0, 129 ] );
  push( [ 1, 2, 0, 125 ] );
  push( [ 1, 2, 1, 123 ] );
}
 

in detail, we are talking to the program that our table contains 4 columns as described in col_names

col_name = [ 'Year', 'Month', 'Department', 'Scores' ];

and we make one array per each grouped column with its unique values, note that scores is not a grouped column so it will not be indexed.

// grouped column values indexes
years  = [ '2011'    , '2012' ];  
dept   = [ 'IT', 'Sales' ];
month  = [ 'January'  , 'February','March' ] ;

as we can see, our year column contains only 2 values : 2011 and 2012, which occupies the positions 0 and 1 on the years array, keep that in mind.

Then we associate actual columns with indexes :

// column index definition
col_defs = [ years, month , dept, 'Scores' ] ;

this means that for solving the value for column 0 we are going to use the years array.

finally the records and the whole explanation :

// the recordset
with( records )
{
  push( [ 0, 0, 0, 144 ] );
  push( [ 0, 0, 0, 131 ] );
....
...


this is our first record : push( [ 0, 0, 0, 144 ] );
when 1st column's value index is 0, second's is 0 so is 3rd, and 4th is 144,

now lets solve the values, col_defs says 1st column values are in years array, so first value for that record will the one in INDEX 0 of array years, which equals years[0] which is '2011',
following the same logic, 2nd is months[0] (' January' ) , 3rd is depts[0] ('IT') and 4th is column index 3, wich is > than groupedColumns, so its not an indexed value so 144 is the true content of that cell.

it all ends up with you not needing to write '2011' or 'January' or 'Sales' in every record, one index to a values's table is enough, so imagine those tables with real large annoying large strings which are repeated in hundreds of records ? as lets say ... company branches and departments like : East side Lousiana | Insurance and Finances regional direction; it changes your perspective does not it ?

in order to make this happen of course, you will have to generate the javascript data with your php/asp/etc page (probably in a separa javascript file like : data.js)
so you will have a little extra job there, but not difficultt at all since your query comes already grouped and the only algorigthm you need there for generating the column indexes is the good old control-break.


So, how do we use that data and indexing strategy to actually draw either a tree or a row-spamed table ?, well, here is the code :

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
    <head>
   
        <title>Codecall's javascript tree and table view tutorial</title>
   
        <style>
       
            body { font: 10pt Verdana,sans-serif;  color: navy; }
           
            <!-- Styles for tree display -->
            .branch { cursor: pointer;  cursor: hand;  display: block; }
            .leaf {  display: none;  margin-left: 16px; }
           
            a{  text-decoration: none; }
            a:hover{   text-decoration: underline; }
        </style>
</head>

<body>

<script language="Javascript">

// Arrays definition

// Array for the years column values
var years	 = new Array();
// array for the department column values
var dept    = new Array();
// array for the month values
var month    = new Array();
//var calls    = new Array();
var col_defs = new Array();
// the actual records
var records = new Array();


// - - - - - - - - - - - - - - - - - - - - - - - - -
// Data definition
// this would be written by your php/asp/etc page
// - - - - - - - - - - - - - - - - - - - - - - - - - 

// - - - - - - - - - -- - -
// column configuration
// - - - - - - - - - -- - -

// column names
col_name = [ 'Year', 'Month', 'Department', 'Scores' ];

// grouped column values indexes
years  = [ '2011'    , '2012' ];  
dept   = [ 'IT', 'Sales' ];
month  = [ 'January'  , 'February','March' ] ;

// column index definition
col_defs = [ years, month , dept, 'Scores' ] ;

// last grouped column ( 0, 1, 2 )
var groupedColumns = 2;


// - - - - - - - - - -- - -
// - - - - - - - - - -- - -


// the recordset
with( records )
{
  push( [ 0, 0, 0, 144 ] );
  push( [ 0, 0, 0, 131 ] );
  push( [ 0, 0, 1, 119 ] );
  push( [ 0, 1, 0, 125 ] );
  push( [ 0, 1, 1, 171 ] );
  push( [ 0, 2, 1, 143 ] );
  push( [ 1, 0, 0, 180 ] );
  push( [ 1, 0, 1, 131 ] );
  push( [ 1, 0, 1, 154 ] );
  push( [ 1, 1, 0, 111 ] );
  push( [ 1, 1, 1, 149 ] );
  push( [ 1, 2, 0, 129 ] );
  push( [ 1, 2, 0, 125 ] );
  push( [ 1, 2, 1, 123 ] );
}


// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// entry branch function
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
function make_branch( depth, y )
{
    var level = depth;
    var father = col_defs[level][ records[y][level] ] ;
    var res = '';
    var son = '' ;
    var data = false;
    var nada = '';
   
    nada = father + depth + y;
                   
    res += '\n<span class="branch" onclick="showBranch(\''+nada+'\')">';
    res += '<img src="closed.gif" id="I'+nada+'">'+father+'</img>';
    res += '</span>';
   
    res += '\n<span class="leaf" id="'+nada+'">';
   
    for( i=y; i < records.length; i++ )
    {   
        // check up if its our aimed branch
        if ( father != col_defs[level  ][ records[i][level  ] ] )     { break; }
       
        // when am here i am working on parents branch
        data = false;   
        if ( level >= groupedColumns  ) { data=true; };
               
        if ( data )
        {           
            res += '\n<span class="leaf" id=hoja style="display:block;">' ;
            res += '<p>'+records[i][col_defs.length-1]+'</p>' ;
            res += '</span>' ;
            continue ;
        }

        if ( son  == col_defs[level+1][ records[i][level+1] ] ) { continue; }        

        son = col_defs[level+1][ records[i][level+1] ];
        res += make_branch(level+1, i);               
    }
   
    res += '</span>';

    return res;
}

// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// Here the function for drawing the well col-spamed table
// according to table grouping
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
function draw_tbl()
{
    var col_stat = new Array();
    for(i=0;i<col_defs.length; i++) { col_stat.push( -1 ); };

    var table = new Array();

    // some space after the tree
  table.push( '<BR><br>' );
    table.push( '<table border=1>' );
    table.push( '<tr>' )
   
    // first we draw the column names
    for( j=0; j<col_defs.length; j++ )
    {
        table.push( '<td>'+col_name[j]+'</td>' );
    }
    table.push( '</tr>' );       
   
    // records loop, i index stands for rows
    for( i = 0; i<records.length; i++)
    {
        table.push( '<tr>' );
       
        // column loops, j index stands for columns
        for( j=0; j<col_defs.length; j++ )
        {
          // if it is not a grouped column we just draw the data and get
            // back to the loop       
            if( j>groupedColumns )
            {
                table.push( '<td>' + records[i][j] + '</td>' );
                continue;
            }
   
            // if we are  here it is a grouped column, we
            // need to precess values in order to know the right cells rowspam
            if ( col_stat[j] == -1 || records[i][j] != col_stat[j] )
            {
                	   // we calculate how many times (rows) a grouped column value is repeated
                        // and then we draw it once only, with the proper rowspan, so the table
                        // display's granulation does look nicer
                	 	 cnt=0;
                        for( m=i; m<records.length && records[i][j]==records[m][j]; m++,cnt++ );

                        table.push( '<td rowspan='+cnt+'>'+col_defs[j][ records[i][j] ]+'</td>' );
                                        col_stat[j] = records[i][j] ;
            }
        }

        table.push( '</tr>' );
    }

    table.push( '</table>' );
   
    // we put the table together for html printing
    return table.join( '\n' );
}

// - - - - - - - - - - - - - - - - - - - - - -
// Tree branches display and management
// - - - - - - - - - - - - - - - - - - - - - -

// image for opened folder tree branch
var openImg = new Image();
openImg.src = "open.gif";

// image for closed folder tree branch
var closedImg = new Image();
closedImg.src = "closed.gif";

// Expand/contract/show branches
function showBranch(branch)
{
   var objBranch = document.getElementById(branch).style;

   if (objBranch.display=="block")
	  objBranch.display="none";
   else
	  objBranch.display="block";

   swapFolder('I' + branch);
}

// swap open/closed images
function swapFolder(img)
{
   objImg = document.getElementById(img);

   if (objImg.src.indexOf('closed.gif')>-1)
	  objImg.src = openImg.src;
   else
	  objImg.src = closedImg.src;
}

// lets draw the tree
function draw_tree()
{
    // first branch
        document.write( make_branch(0,0) );
       
	     for( idx=1;idx<records.length;idx++ )
        {       
        	      if ( records[idx][0] !=  records[idx-1][0] )
                 {        
                	         document.write( make_branch(0,idx) );
                    }                 
        }
}
// - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 

// lets draw the tree display
draw_tree();

// now the row-spamed table
document.write( draw_tbl() );
//-->
</script>

</body>
</html>
 


a very small piece of javascript, mostly comments as you can see up there ( or you can download it from the attachment ) and very useful if you want to play around how to display boring listings in a better looking way, or to stick to tree navigation practicity.

Short, easy, nice to the eye, what do you think ?
its up to you know to give some coloring and art to the table and you got yourself a better way to show your query jobs.

see you in next tutorial

BlackRabbit

Attached Thumbnails

  • closed.gif
  • open.gif

Attached Files


  • 0





Also tagged with one or more of these keywords: tutorial, table, tree, grouping, view

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download