Avatar of Steynsk
Flag for Netherlands asked on

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

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
	myData = "[{" 
	Do Until oRS.EOF
		For each group in membership
			GroupName= left(newgroup(1), len(newgroup(1))-3)
			GroupName = Replace (GroupName, "\", "\\")
			GroupName = Replace (GroupName, "'", "''")
			myData = myData & """sAMAccountName"":""" & oRS.Fields("sAMAccountName") & """,""GroupName"":""" & GroupName & """},"
	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
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:



is my format right?


and how do I feed this to the pivot.js script to get the desired output?

Avatar of undefined
Last Comment

8/22/2022 - Mon
Scott Fell

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 =
  <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>
<div id="pivot-table">
<div id="results">

<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})

Open in new window

Scott Fell

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">
  <!-- 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>
    <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><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>
  <div class="container">
    <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.
    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.
      <b>To begin:</b>
        <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>
    <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>
          <ul class="dropdown-menu stop-propagation" style="overflow:auto;max-height:450px;padding:10px;">
            <div id="filter-list"></div>
        <li class="dropdown">
          <a class="dropdown-toggle" data-toggle="dropdown" href="#">
            Row Label Fields
            <b class="caret"></b>
          <ul class="dropdown-menu stop-propagation" style="overflow:auto;max-height:450px;padding:10px;">
            <div id="row-label-fields"></div>
        <li class="dropdown">
          <a class="dropdown-toggle" data-toggle="dropdown" href="#">
            Column Label Fields
            <b class="caret"></b>
          <ul class="dropdown-menu stop-propagation" style="overflow:auto;max-height:450px;padding:10px;">
            <div id="column-label-fields"></div>
        <li class="dropdown">
          <a class="dropdown-toggle" data-toggle="dropdown" href="#">
            Summary Fields
            <b class="caret"></b>
          <ul class="dropdown-menu stop-propagation" style="overflow:auto;max-height:450px;padding:10px;">
            <div id="summary-fields"></div>
        <li class="dropdown pull-right">
          <a class="dropdown-toggle" data-toggle="dropdown" href="#">
            Canned Reports
            <b class="caret"></b>
          <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>

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

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

<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'
      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

    // **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.
      $('#pivot-demo').pivot_display('reprocess_display', {rowLabels:["employer"], columnLabels:["age_bucket"], summaries:["balance"]})

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

      $('#pivot-demo').pivot_display('reprocess_display', {"filters":{"city":"Miami"},"rowLabels":["last_name","first_name","employer","invoice_date"],"summaries":["payment_amount"]})

Open in new window


Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question


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.

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() {
		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); 
		sortPivotColumnHeaders:false //we want months non sorted to get them in the right order.

Open in new window

<!DOCTYPE html>
<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>
  <div id='res'></div>

Open in new window

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

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes