troubleshooting Question

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

Avatar of Steynsk
SteynskFlag for Netherlands asked on
JavaScriptASP
5 Comments1 Solution1000 ViewsLast Modified:
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
%>

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?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros