Multilevel Drilldown in Jquery Datatables

Jquery datatables is powerful plugin to create html tables with numerous features like sorting, pagination, editing, deleting etc. For more information refer the following link.
https://datatables.net/
Implementing single level drilldown on this plugin is quite straightforwards. But when it comes to multilevel drilldown it becomes more complex to make it work with paginations and multiple levels of drilldown within it. The normal jquery bind and live methods some times fail to work as expected. So I have used ‘on’ instead of the bind/live. To understand the usage of bind vs live vs on, please refer http://www.elijahmanor.com/differences-between-jquery-bind-vs-live-vs-delegate-vs-on/. We also need to pass the element position while creating the drilldown tables. This will avoid the drilldown bug where in, it will not confuse between the element of two different level when opening and closing them.
So here is a working code of jquery datatable multilevel drilldown.

Datatable drilldown JSBin Example

The javascript works as follows.

var anOpen = [];
var anOpen1 = [];
$(document).ready(function () 
{  
	//We create the html string for the 2nd level and 3rd level tables. In this case 2nd level is same as 1st level<br />
	var itable = $('#example').html();
	var jtable = $('#example1').html();
	//Initialize the 1st level table
    var oTable = $('#example').dataTable({
        "bRetrieve": true,
        "bJQueryUI": true,
        "iDisplayLength": 5,
        "sPaginationType": "full_numbers",
        "sDom": '<"F"lfT>t<"F"p>'
    });
    $('#example').on("click", "td.control",function() 
	{
        var nTr = this.parentNode;
        var i = $.inArray(nTr, anOpen);
        if (i === -1) 
		{
			var iPos = oTable.fnGetPosition(nTr);
            $('img', this).attr('alt', "-");
            var nDetailsRow = oTable.fnOpen(nTr, fnFormatDetails(itable, iPos), 'details');
            $('div.innerDetails', nDetailsRow).slideDown();
            anOpen.push(nTr);
			//Initialize 2nd level datatable
            var oInnerTable = $('#inner' + iPos).dataTable({
                "bRetrieve": true,
                "bJQueryUI": true,
                "iDisplayLength": 5,
                "sPaginationType": "full_numbers",
                "sDom": '<"F"lfT>t<"F"p>'
            });
            //2nd Level drilldown click function                                                   
            $('#inner' + iPos).on("click","td.control",function()
			{
                var nTr1 = this.parentNode;
                var j = $.inArray(nTr1, anOpen1);
                if (j === -1) 
				{
                    var jPos = oInnerTable.fnGetPosition(nTr1);
                    $('img', this).attr('alt', "-");
                    var nDetailsRow1 = oInnerTable.fnOpen(nTr1, fnFormatDetails1(jtable, iPos, jPos), 'details');
                    $('div.innerDetails', nDetailsRow1).slideDown();
                    anOpen1.push(nTr1);
					//initialize 3rd level table
                    var oInnerTable1 = $('#doubleinner' + iPos + '_' + jPos).dataTable({
                        "bRetrieve": true,
                        "bJQueryUI": true,
                        "iDisplayLength": 5,
                        "sPaginationType": "full_numbers",
                        "sDom": '<"F"lfT>t<"F"p>'
                    });
                } 
				else 
				{
                    $('img', this).attr('alt', "+");
                    oInnerTable.fnClose(this.parentNode);
                    anOpen1.splice(j, 1);
                }
				event.stopPropagation();
            });
            //2nd Level Drilldown ends here           
        }
		else 
		{
            $('img', this).attr('alt', "+");
            oTable.fnClose(this.parentNode);
            anOpen.splice(i, 1);
			event.stopPropagation();
        }
    });
});
function fnFormatDetails(itable, pos) {
    var sOut = "<table id=\"inner" + pos + "\">";
    sOut += itable;
    sOut += "</table>";
    return sOut;
}

function fnFormatDetails1(jtable, pos1, pos2) {
    var sOut = "<table id=\"doubleinner" + pos1 + '_' + pos2 + "\">";
    sOut += jtable;
    sOut += "</table>";
    return sOut;
}

Service-Now data pull – python vs perl

Service-now is a IT service management software that hosts a wide ranging list of reports from different modules of IT services that are organized into tables. ServiceNow publishes its underlying table structures and associated data that can be pulled via SOAP query. This can be achieved through any scripting language. Although, python is my default goto scripting language, the soap client in python are not very well maintained. The most pythonic of those is the SUDS which does not seem to work well with service now api. While it is pretty slick and easy to get the basic data output using python/suds, as you move to more complex queries python fails. This is a known issue.

https://fedorahosted.org/suds/ticket/330
http://lists.fedoraproject.org/pipermail/suds/2011-October/001526.html
http://stackoverflow.com/questions/11850275/parameters-with-leading-underscores-in-python-suds

The __limit and encoded query does not seem to have the desired effect on the output as SUDS never passes these. So after few trials I ended up using Perl for the data pull and calling Perl from my Django framework. This worked like a charm. So here goes a snippet that can be used to get multiple records from an incident with max limit 10000 records and pulling single records from incident.

#!/usr/bin/perl -w
use SOAP::Lite;

# basic auth
sub SOAP::Transport::HTTP::Client::get_basic_credentials {
return 'user' => 'password';
}

# specify the endpoint to connect. This is the first incident that pulls say two values field1, field2 and field3 is used as a filter for records.
my $soap1 = SOAP::Lite -> proxy('https://instance.service-now.com/incident1.do?displayvalue=all&SOAP');
my $method1 = SOAP::Data->name("getRecords")-> attr({xmlns => "http://www.service-now.com/"});
my @params1 = ( SOAP::Data->name(field3=> "Your Value") );
push(@params1, SOAP::Data->name(__limit => "10000") );
my $som1 = $soap1->call($method1 => @params1);
my @serverData1 = @{$som1->body->{getRecordsResponse}->{getRecordsResult}};
foreach my $serverRec (@serverData1) {
	$f1 = $serverRec->{field1};
	$f2 = $serverRec->{field2};
	print $f1,',',$f2,"\n";
}
#incident 2 match  field4 and then retrieve field5 and 6 for single record
my $soap2 = SOAP::Lite -> proxy('https://instance.service-now.com/incident2.do?displayvalue=all&SOAP');
my @params2 = ( SOAP::Data->name(field4 => "Your Value") );
push(@params2, SOAP::Data->name(__limit => "1") );
$f5 = $soap2->call($method1 => @params2)->body->{getRecordsResponse}->{getRecordsResult}->{field5} || "None";
$f6 = $soap2->call($method1 => @params2)->body->{getRecordsResponse}->{getRecordsResult}->{field6} || "None";
print  $f5,',',$f6,"\n";

And then from python function we can easily call this perl script:

import subprocess
ret = subprocess.call(["perl","/path/name/only/servicenow.pl"])