Solved

How do I feed my data to pivot.js to create a pivot view on my data?

Posted on 2014-11-24
5
320 Views
Last Modified: 2014-12-21
Hi Experts,

Advised by Scott Fell I did an effort to transform the data I retrieve from our active directory into Json data.
The goal is to feed the data to the script pivot.js so I will be able to create an output model like in the picture beneath this text.

desired output
The script to transform the AD data into JSON is this one:
<%
FuncADuser = "mydomain\myADSviewAccount"
FuncADpassword = "mypassword"
Group = "CN=Sales,OU=firmname,DC=subname,DC=anothersubname,DC=intra"
Set objRootDSE = GetObject("LDAP://RootDSE")
sDomain = objRootDSE.Get("defaultNamingContext")
Set oCmd = Server.CreateObject("ADODB.Command")
Set Conn = Server.CreateObject("ADODB.Connection") 
Set RS = Server.CreateObject("ADODB.Recordset") 
Conn.Provider = "ADsDSOObject" 
Conn.Properties("User ID") = FuncADuser
Conn.Properties("Password") = FuncADpassword
Conn.Properties("Encrypt Password") = True
strConn = "Active Directory Provider" 
Conn.Open strConn , FuncADuser, FuncADpassword
Set oCmd.ActiveConnection = Conn
sql = "SELECT sAMAccountName,department,memberof FROM 'LDAP://" & SDomain &"' WHERE memberof = '" & Group & "' ORDER by name "
oCmd.CommandText = sql
response.write sql
Set oRS = oCmd.Execute
if not oRS.EOF Then
	count =0
	oRS.MoveFirst
	myData = "[{" 
	Do Until oRS.EOF
		membership=oRS.Fields("memberof")
		For each group in membership
			newgroup=split(group,"=")
			GroupName= left(newgroup(1), len(newgroup(1))-3)
			GroupName = Replace (GroupName, "\", "\\")
			GroupName = Replace (GroupName, "'", "''")
			myData = myData & """sAMAccountName"":""" & oRS.Fields("sAMAccountName") & """,""GroupName"":""" & GroupName & """},"
		Next
		oRS.MoveNext
	Loop
	myData = left(myData,Len(myData)-1) 'remove the last comma
	myData = myData & "];" ' add bracked and semicolon to close the Json data format
	response.write myData
	oRS.Close
end if
%>

Open in new window


The (Json) output of this script is:

[{"sAMAccountName":"Bill","GroupName":"Domain users"},"sAMAccountName":"Bill","GroupName":"Application B"},"sAMAccountName":"Judy","GroupName":"Domain users"},"sAMAccountName":"Judy","GroupName":"Application A"},"sAMAccountName":"John","GroupName":"Domain users"},"sAMAccountName":"John","GroupName":"Application B"},"sAMAccountName":"John","GroupName":"Printer 45"}];

My questions are:

 

1.

is my format right?
 

2.

and how do I feed this to the pivot.js script to get the desired output?
0
Comment
Question by:Steynsk
  • 2
  • 2
5 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40462317
See if the example makes sense to you https://github.com/rwjblue/pivot.js/wiki/Integrating-with-jQuery where you just need to plug in your json data for var data =
<head>
  <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
  <!-- Set src to location of pivot.js and jquery_pivot.js relative to this file! -->
  <script type="text/javascript" src=".location/to/pivot.js"></script>
  <!-- Must be loaded after pivot.js & jQuery -->
  <script type="text/javascript" src="./location/to/jquery_pivot.js"></script>
</head>>
<div id="pivot-table">
</div>
<div id="results">
</div>

<script type="text/javascript">
  $(document).ready(function() {
    var data = "" // Your CSV or JSON data as a string

    // default fields are labelable, non-sumarrizable, non-filterable
    // so be sure define the fields you expect to be able to filter by
    var fields = []

    $('#pivot-demo').pivot_display('process', {csv: data, fields: fields})
  });
</script>

Open in new window

0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40462344
Also, viewing the source of the demo view-source:http://rwjblue.github.io/pivot.js/

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en-us">
<head>
  <title>Pivot.js</title>
  <!-- Twitter Bootstrap -->
  <link rel="stylesheet" href="./lib/css/bootstrap.min.css" type="text/css" />
  <link rel="stylesheet" href="./lib/css/subnav.css" type="text/css" />
  <link rel="stylesheet" href="./lib/css/pivot.css" type="text/css" />

  <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
  <script type="text/javascript" src="./lib/javascripts/subnav.js"></script>
  <script type="text/javascript" src="./lib/javascripts/accounting.min.js"></script>
  <script type="text/javascript" src="./lib/javascripts/jquery.dataTables.min.js"></script>
  <script type="text/javascript" src="./lib/javascripts/dataTables.bootstrap.js"></script>


  
  <!-- jquery_pivot must be loaded after pivot.js and jQuery -->
  <script type="text/javascript" src="./pivot.js"></script>
  <script type="text/javascript" src="./jquery_pivot.js"></script>
</head>
<body>
    <div class="navbar navbar-fixed-top">
      <div class="navbar-inner">
        <div class="container">
          <ul class="nav">
            <li class="active">
              <a class="brand" href="https://github.com/rjackson/pivot.js">Pivot.js</a>
            </li>
            <li><a href="https://github.com/rjackson/pivot.js">Fork On Github</a></li>
            <li><a href="./spec/runner.html">Run Spec</a></li>
            <li><a href="./docs/index.html#!/api/Pivot">Docs</a></li>
          </ul>
        </div>
      </div>
    </div>
  <div class="container">
    <h1>Pivot.js</h1>
    <p>Pivot.js is a simple way to summarize large data sets on the fly. On this page we are using the pivot jQuery plugin which offers a nice entry point to Pivot.js.
    <br/><br/>
    The data for the chart below is based on a CSV file of about 5000 rows.  It is generated by a <a href="https://github.com/rjackson/pivot.js/blob/master/demo_csv_builder.rb">script</a> included in the repository.  It is designed to have many fields that have at least some significant relationships between one another.  This only just scratches the surface of what Pivot.js can do.  Check out the <a href="https://github.com/rjackson/pivot.js/blob/master/README.md">README</a> for more information.  Also feel free to run the spec (found in the link up top), and if you spot any problems let us know so we can fix them.
    <br/><br/>
      <b>To begin:</b>
      <ul>
        <li>Select the fields you would like to display from the Label Fields/Summary Fields drop down.</li>
        <li>Then filter your data by selecting from the filters drop down.</li>
        <li>Ta-da, you have yourself a custom report!</li>
      </ul>
    </p>
    <div class="subnav">
      <ul class="nav nav-pills">
        <li class="dropdown">
          <a class="dropdown-toggle" data-toggle="dropdown" href="#">
            Filter Fields
            <b class="caret"></b>
          </a>
          <ul class="dropdown-menu stop-propagation" style="overflow:auto;max-height:450px;padding:10px;">
            <div id="filter-list"></div>
          </ul>
        </li>
        <li class="dropdown">
          <a class="dropdown-toggle" data-toggle="dropdown" href="#">
            Row Label Fields
            <b class="caret"></b>
          </a>
          <ul class="dropdown-menu stop-propagation" style="overflow:auto;max-height:450px;padding:10px;">
            <div id="row-label-fields"></div>
          </ul>
        </li>
        <li class="dropdown">
          <a class="dropdown-toggle" data-toggle="dropdown" href="#">
            Column Label Fields
            <b class="caret"></b>
          </a>
          <ul class="dropdown-menu stop-propagation" style="overflow:auto;max-height:450px;padding:10px;">
            <div id="column-label-fields"></div>
          </ul>
        </li>
        <li class="dropdown">
          <a class="dropdown-toggle" data-toggle="dropdown" href="#">
            Summary Fields
            <b class="caret"></b>
          </a>
          <ul class="dropdown-menu stop-propagation" style="overflow:auto;max-height:450px;padding:10px;">
            <div id="summary-fields"></div>
          </ul>
        </li>
        <li class="dropdown pull-right">
          <a class="dropdown-toggle" data-toggle="dropdown" href="#">
            Canned Reports
            <b class="caret"></b>
          </a>
          <ul class="dropdown-menu">
           <li><a id="ar-aged-balance" href="#">AR Aged Balance</a></li>
           <li><a id="acme-detail-report" href="#">Acme Corp Detail</a></li>
           <li><a id="miami-invoice-detail" href="#">Miami Invoice Detail</a></li>
          </ul>
        </li>
      </ul>
    </div>
    <hr/>

    <h1>Results</h1>
    <span id="pivot-detail"></span>

    <hr/>
    <div id="results"></div>
  </div>
</body>

<script type="text/javascript">

function ageBucket(row, field){
  var age = Math.abs(((new Date().getTime()) - row[field.dataSource])/1000/60/60/24);
  switch (true){
    case (age < 31):
      return '000 - 030'
    case (age < 61):
      return '031 - 060'
    case (age < 91):
      return '061 - 090'
    case (age < 121):
      return '091 - 120'
    default:
      return '121+'
  }
};

// Define the structure of fields, if this is not defined then all fields will be assumed
// to be strings.  Name must match csv header row (which must exist) in order to parse correctly.
var fields = [
    // filterable fields
    {name: 'last_name',         type: 'string', filterable: true, filterType: 'regexp'},
    {name: 'first_name',        type: 'string', filterable: true},
    {name: 'state',             type: 'string', filterable: true},
    {name: 'employer',          type: 'string', filterable: true},
    {name: 'city',              type: 'string', filterable: true},
    {name: 'invoice_date',      type: 'date',   filterable: true},

    // psuedo fields
    {name: 'invoice_mm', type: 'string', filterable: true, pseudo: true,
      pseudoFunction: function(row){
          var date = new Date(row.invoice_date);
          return pivot.utils().padLeft((date.getMonth() + 1),2,'0')}
    },
    {name: 'invoice_yyyy_mm', type: 'string', filterable: true, pseudo: true,
      pseudoFunction: function(row){
        var date = new Date(row.invoice_date);
        return date.getFullYear() + '_' + pivot.utils().padLeft((date.getMonth() + 1),2,'0')}
    },
    {name: 'invoice_yyyy', type: 'string', filterable: true, pseudo: true, columnLabelable: true,
      pseudoFunction: function(row){ return new Date(row.invoice_date).getFullYear() }},
    {name: 'age_bucket', type: 'string', filterable: true, columnLabelable: true, pseudo: true, dataSource: 'last_payment_date', pseudoFunction: ageBucket},


    // summary fields
    {name: 'billed_amount',     type: 'float',  rowLabelable: false, summarizable: 'sum', displayFunction: function(value){ return accounting.formatMoney(value)}},
    {name: 'payment_amount',    type: 'float',  rowLabelable: false, summarizable: 'sum', displayFunction: function(value){ return accounting.formatMoney(value)}},
    {name: 'balance', type: 'float', rowLabelable: false, pseudo: true,
      pseudoFunction: function(row){ return row.billed_amount - row.payment_amount },
      summarizable: 'sum', displayFunction: function(value){ return accounting.formatMoney(value)}},
    {name: 'last_payment_date',  type: 'date',  filterable: true}
]

  function setupPivot(input){
    input.callbacks = {afterUpdateResults: function(){
      $('#results > table').dataTable({
        "sDom": "<'row'<'span6'l><'span6'f>>t<'row'<'span6'i><'span6'p>>",
        "iDisplayLength": 50,
        "aLengthMenu": [[25, 50, 100, -1], [25, 50, 100, "All"]],
        "sPaginationType": "bootstrap",
        "oLanguage": {
          "sLengthMenu": "_MENU_ records per page"
        }
      });
    }};
    $('#pivot-demo').pivot_display('setup', input);
  };

  $(document).ready(function() {

    setupPivot({url:'./lib/csv/demo.csv', fields: fields, filters: {employer: 'Acme Corp'}, rowLabels:["city"], summaries:["billed_amount", "payment_amount"]})

    // prevent dropdown from closing after selection
    $('.stop-propagation').click(function(event){
      event.stopPropagation();
    });

    // **Sexy** In your console type pivot.config() to view your current internal structure (the full initialize object).  Pass it to setup and you have a canned report.
    $('#ar-aged-balance').click(function(event){
      $('#pivot-demo').pivot_display('reprocess_display', {rowLabels:["employer"], columnLabels:["age_bucket"], summaries:["balance"]})
    });

    $('#acme-detail-report').click(function(event){
      $('#pivot-demo').pivot_display('reprocess_display', {filters:{"employer":"Acme Corp"},rowLabels:["city","last_name","first_name","state","invoice_date"]})
    });

    $('#miami-invoice-detail').click(function(event){
      $('#pivot-demo').pivot_display('reprocess_display', {"filters":{"city":"Miami"},"rowLabels":["last_name","first_name","employer","invoice_date"],"summaries":["payment_amount"]})
    });
  });
</script>

Open in new window

0
 
LVL 42

Accepted Solution

by:
Rob Jurd, EE MVE earned 500 total points
ID: 40462931
Scott has asked me to jump in here with the JS stuff.

Your JSON is not quite correct.  There are unmatched braces { }.  You need to encapsulate each record with curly braces { } as they will be transformed to objects in javascript that make it easy to reference:

This is your current JSON:
[{"sAMAccountName":"Bill","GroupName":"Domain users"},"sAMAccountName":"Bill","GroupName":"Application B"},"sAMAccountName":"Judy","GroupName":"Domain users"},"sAMAccountName":"Judy","GroupName":"Application A"},"sAMAccountName":"John","GroupName":"Domain users"},"sAMAccountName":"John","GroupName":"Application B"},"sAMAccountName":"John","GroupName":"Printer 45"}];

This is what it should look like (I've added the curly braces that were missing - they're in bold below):
[
{"sAMAccountName":"Bill","GroupName":"Domain users"},{"sAMAccountName":"Bill","GroupName":"Application B"},{"sAMAccountName":"Judy","GroupName":"Domain users"},{"sAMAccountName":"Judy","GroupName":"Application A"},{"sAMAccountName":"John","GroupName":"Domain users"},{"sAMAccountName":"John","GroupName":"Application B"},{"sAMAccountName":"John","GroupName":"Printer 45"}
];

Working on a demo for you as well.
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 40463114
http://jsbin.com/sixowi/2/edit?js,output

I've added a "value: 1" to each record.  This is just making it easier to do the pivoting and is working within the design of the plugin.  It should be simple for you to add this at the end of each record.

JS
var jsondata = {
	dataid: 'Your sample data',
	columns: [
		{ colvalue:"sAMAccountName", coltext:"sAMAccountName", header:"Name", sortbycol:"sAMAccountName", groupbyrank: null, pivot: true, result: false},
		{ colvalue:"GroupName", coltext:"GroupName", header:"GroupName", sortbycol:"GroupName", groupbyrank: 1, pivot: false, result: false},
		{ colvalue:"value", coltext:"GroupName", header:"GroupName", sortbycol:"GroupName", groupbyrank: null, pivot: false, result: true}
	],
	rows: [
	{"sAMAccountName":"Bill","GroupName":"Domain users", "value": 1},
	{"sAMAccountName":"Bill","GroupName":"Application B", "value": 1},
	{"sAMAccountName":"Judy","GroupName":"Domain users", "value": 1},
	{"sAMAccountName":"Judy","GroupName":"Application A", "value": 1},
	{"sAMAccountName":"John","GroupName":"Domain users", "value": 1},
	{"sAMAccountName":"John","GroupName":"Application B", "value": 1},
	{"sAMAccountName":"John","GroupName":"Printer 45", "value": 1}
]
};

$(function() {
	$('#res').pivot({
		source: jsondata,
    	bTotals: false,
		parseNumFunc: null,
		formatFunc: function (n) { 
			var rtn = "";
			if (n === 1) {
				rtn = "x";
			}
			return rtn;
			//return jQuery.fn.pivot.formatUK(n, 0); 
			//console.log(n);
		},
		sortPivotColumnHeaders:false //we want months non sorted to get them in the right order.
	});
});

Open in new window


html
<!DOCTYPE html>
<html>
<head>
<script src="//code.jquery.com/jquery-2.1.1.min.js"></script>
	    <script src="http://metalogic.dk/jquery.pivot/js/src/lib.js"></script>
    <script src="http://metalogic.dk/jquery.pivot/js/src/adapter.js"></script>

	<script src="http://metalogic.dk/jquery.pivot/js/src/jquery.pivot.js"></script>
  <meta charset="utf-8">
  <title>Pivot Example</title>
</head>
<body>
  <div id='res'></div>
</body>
</html>

Open in new window


Some CSS to format the table:
table, table * {
	border: 1px solid black;
	border-collapse: collapse;
}

Open in new window

0
 
LVL 1

Author Closing Comment

by:Steynsk
ID: 40511759
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This article will give core knowledge of JavaScript and will head in to your first JavaScript program. I am Durvesh Naik and I am here to deal with this series of JavaScript. I will teach you JavaScript in part wise , as its quite boring to read big…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now