It’s easy to develop reports by connecting directly to the database using JDBC Datasource. REST is pretty popular today, Birt has option to create datasource from web services, it seems to only accept SOAP APIs. BIRT has no build in JSON data source. However there are some community JSON data source plugins, but all of them I have seen are very low level and not comfortable to use.

There are two ways to do restful APIs in Birt.

1). Use scripted datasource.

Here is Javascript program for scripted datasource.

Create a scripted datasource with open() method as follows:

logger = java.util.logging.Logger.getLogger("birt.report.logger");

importPackage(Packages.java.io);
importPackage(Packages.java.net);

//if you have a parameter
var param= params["industryname"].value;

var inStream = new 
URL("http://yourapi/endpoint/" + param).openStream();
var inStreamReader = new InputStreamReader(inStream);
var bufferedReader = new BufferedReader(inStreamReader);
var line;
var result = "";

while ((line = bufferedReader.readLine()) != null)
result += line;
inStream.close();

var json = JSON.parse(result);
vars["HTMLJSON"] = json;

logger.warning (result);
//logger.warning (json);

Then create a dataset with the following methods:
open()

recNum=0;

fetch()

len = vars["HTMLJSON"].length;

if (recNum >= len)
    return false;

row["name"] = vars["HTMLJSON"][recNum].name;
row["id"] = vars["HTMLJSON"][recNum].id;
row["active"] = vars["HTMLJSON"][recNum].active;

recNum++;

return true;

You may need Apache Commons IO included in your scriptlib folder.

2). Use esProc datasource.

Reading REST APIs with SPL scripts is very easy.

The following examples will show in detail how to write an esProc script for parsing the JSON strings and computing them.

In the case, the JSON string is transferred from Birt through a parameter.

Suppose there is a JSON string of employee information that includes the following fields – EID, NAME, SURNAME, GENDER, STATE, BIRTHDAY, HIREDATE and DEPT. Now parse the string and find out the female employees born after January 1, 1981 inclusive. The string is as follows:

[{EID:1,NAME:"Rebecca",SURNAME:"Moore",GENDER:"F",STATE:"California ",BIRTHDAY:1974-11-20,HIREDATE:2005-03-11,DEPT:"R&D",SALARY:7000},

{EID:2,NAME:"Ashley",SURNAME:"Wilson",GENDER:"F",STATE:"New York",BIRTHDAY:1980-07-19,HIREDATE:2008-03-16,DEPT:"Finance",SALARY:11000},

{EID:3,NAME:"Rachel",SURNAME:"Johnson",GENDER:"F",STATE:"New Mexico",BIRTHDAY:1970-12-17,HIREDATE:2010-12-01,DEPT:"Sales",SALARY:9000},…\]

To select female employees born after January 1, 1981 inclusive, esProc script inputs two parameters – “jsonstr” and “where” – from Birt, as shown below:

Solution: Call the esProc script in Birt and meanwhile input the JSON string; esProc parses and filters the JSON data and then returns the result in the form of JSON string to Birt.

“where” is a string, its value is BIRTHDAY>=date(1981,1,1) && GENDER=="F".

Note: Parameter jsonstr is a string, its entered value needs to be preceded by a single quote.

Here’s the esProc script for doing this task:

A1: Parse the JSON data into a table sequence. The result can be displayed in esProc’s Integrated Development Environment (IDE), as shown in the right part of the figure above.

A2: Filter the data of the table sequence. A macro is used to dynamically parse the expression, in which “where” is the input parameter. esProc will compute the expression in ${…} to get the macro string value and, replace ${…} with it, and then interpret and execute the generated expression. The final code for execution is =A1.select(BIRTHDAY>=date(1981,1,1) && GENDER=="F").

A3: Return the desired result set to Birt.

The report can be designed in the same way as you would if you were retrieving the data from a database. For detail SPL integration with BIRT, see How to Call an SPL Script in BIRT.

For more examples, procedure JSON files refer to the following JSON data calculation and importing into database .

If you have any questions or comments, please leave them below.