Solved

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

Posted on 2014-11-24
5
427 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 53

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 53

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 43

Accepted Solution

by:
Rob 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 43

Expert Comment

by:Rob
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
This article discusses how to create an extensible mechanism for linked drop downs.
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…

617 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