Use JSON as a Scripted Data Set

I was doing some work with JSON this weekend and I thought it might be fun to build a scripted data set from a JSON…

OpenText  profile picture
OpenText

April 02, 20133 min read

QR code and smartphone, new technologies. Electronic digital technologies scanning, barcode
QR code and smartphone, new technologies. Electronic digital technologies scanning, barcode
images

I was doing some work with JSON this weekend and I thought it might be fun to build a scripted data set from a JSON file. With just a few lines of JavaScript I had it up and running without much work.The first thing you will need is some JSON data to work with. Here’s the sample data I created and saved to my desktop as test.json

{"bindings":[{"name":"Bob","title":"Manager","phone":"555-000-0012"},{"name":"Tom","title":"Sales Rep","phone":"555-000-0037"},{"name":"Larry","title":"Tech","phone":"555-000-0021"}]}

Once you have the JSON data saved open the designer and create a new report. Then you need to create a scripted data source. From the data explorer right click “Data Sources” > “New Data Source” > “Scripted Data Source” > “Finish”

Now we’re going to need a scripted data set to work with. Right click “Data Sets” > “New Data Set” > Select the data source you just created > “Next”. From here we need to add three output columns named “name”, “title”, and “phone”. Then click “finish”.

Attached Image

Now that the output columns have been created we need to read the JSON file from open().

First we need to import java.io and org.apache.commons.io. If you don’t have the apache commons library it can befound on their website.

importPackage(Packages.org.apache.commons.io);
importPackage(Packages.java.io);

Next we’ll use the apache commons library to open the JSON file and store it in a String.

// Grab the JSON file and place it in a string
fisTargetFile =newFileInputStream(newFile("C:/Users/kclark/Desktop/test.json"));
input =IOUtils.toString(fisTargetFile,"UTF-8");// Store the contents in a variable
jsonData = input;

After we have the contents of the JSON file we need to convert it to a JSON object.

// Convert the String to a JSON object
myJSONObject =eval('('+ jsonData +')');

Then we need to get the length of the object because we will be accessing the data similar to how we use arrays in JavaScript.

// Get the length of the object
len = myJSONObject.bindings.length;

count =0;// Counter used to step through each item in the JSON object.

Finally our last step is to look through the JSON object and assign the appropriate data to the output columns from fetch().

// Loop through the JSON object adding it to the scripted data sourceif(count < len){var name     = myJSONObject.bindings[count].name;var title    = myJSONObject.bindings[count].title;var phone    = myJSONObject.bindings[count].phone;
  
  row["name"]= name;
  row["title"]= title;
  row["phone"]= phone;
  count++;returntrue;}returnfalse;

After this is done you can save the rptdesign, right click the data set > “edit” > “Preview Results”. If successful it will look like this.

Attached Image

Once complete you can now use this data set like any other, creating tables, charts, and other report items.

The complete example can be downloaded from this devshare. If you have any questions or comments please leave them below.

Share this post

Share this post to x. Share to linkedin. Mail to
OpenText avatar image

OpenText

OpenText, The Information Company, enables organizations to gain insight through market-leading information management solutions, powered by OpenText Cloud Editions.

See all posts

More from the author

Can CIOs meet today’s challenges and plan for tomorrow?

Can CIOs meet today’s challenges and plan for tomorrow?

Read the new white paper

April 21, 2025

4 min read

From the Great North to global innovation: How OpenText powers the future of tech

From the Great North to global innovation: How OpenText powers the future of tech

The beginning of the year is the perfect time to reflect on one’s successful journey and set our sights on the future. At OpenText, our…

February 28, 2025

3 min read

Why SAST + SCA is the key to protecting your organization in 2025

Why SAST + SCA is the key to protecting your organization in 2025

Software supply chain risk continues to rise—just last year we witnessed a staggering 156% year-over-year increase in malicious software supply chain attacks.

January 17, 2025

4 min read

Stay in the loop!

Get our most popular content delivered monthly to your inbox.