RaqForum 19 No.
1 Reply  •  552 View  • 
     ...

How Reporting Tools Access Webservice/Restful Data

 

Many reporting tools do not provide Webservice/RESTful interface. They hard code the access through the user-defined data set. A few reporting tools do have the retrieval interface, but they are not good at data computing at all (they are only intended for data presentation). On most occasions, they resort to their own way, such as the old tool user-defined data set or an intermediate RDB like SQLite, to try to accomplish computations. The hard code is complex and lengthy. SQLite is low real-time and has a complicated framework. Both are not good choices. Moreover, WebService/Restful contains multilevel data rather than simple structured data. Both ways are not good at handling multilevel data.

The Java-based, open-source class library SPL is the ideal method. It offers convenient WebService/Restful retrieval functions, has remarkable multilevel data processing capabilities, and the JDBC driver that is easy to be integrated by reporting tools.

SPL has the built-in WebService/Restful interface, through which data can be conveniently retrieved. There is a URL that returns Restful JSON storing employee records and orders of each employee, and we are trying to get all employee records in SPL:

A

1

=httpfile("http://....../api/getData").read()

2

=json(A1)

Or get all orders records:

A

1

…//Same as above

2

=json(A1).conj()

Similar, we can use SPL to retrieve data from WebSerivce XML:

A

1

=ws_call(ws_client("http://....../entityWS.asmx?wsdl"),"entityWS":"entityWSSoap":"getData")

2

=A1.conj(Orders)

SPL can also access servers that have privilege controls. But we just skip them here.

 

SPL has a wealth of built-in functions for processing multilevel data using simple and intuitive syntax, without the need of any complicated hardcoding. To perform a query on orders data according to interval-style conditions, for instance, we have the following SPL code, where p_start and p_end are parameters:

A

2

…// Get orders records

3

=A2.select(Amount>p_start && Amount<=p_end)

SPL provides general data types to compute data from any source. Computing data WebService and RESTful only differ in retrieval processes but share the rest of the code. So, we won’t explain the processes separately.

As code written in an RDB, SPL code can be integrated into a reporting tool through its JDBC driver. Take the above SPL code as an example, we can first save it as a script file (intervalQuery.splx) and then invoke it in the reporting tool as we do with a stored procedure. Suppose we are using JasperReport and the code is as follows:

call intervalQuery($P{paramStart}, $P{paramEnd})

More examples:

A

B

2

…//Get employee records

3

=A2.conj(Orders).groups(Client;sum(Amount))

/Multilevel data summarization

4

=A2.groups(State,Gender;avg(Salary),count(1))

/Single-level data summarization

5

=A2.sort(Salary)

/Sort

6

=A2.id(State)

/Distinct

7

=A2.new(Name,Gender,Dept,Orders.OrderID,Orders.Client,Orders.Client,Orders.SellerId,Orders.Amount,Orders.OrderDate)

/Join

 

SPL also supports standard SQL syntax in order to help beginners adapt. The syntax is suitable for handling computing scenarios involving short and simple code. To accomplish the above query based on interval-style conditions, we can create a SQL data set in the reporting tool and write the following SQL statement:

$select * from {json(httpfile("http://....../api/getData").read()).conj()}
where Amount>$P{paramStart} and Amount<=$P{paramEnd}

 

SPL’s agile syntax can simplify computations with complex logic, including stepwise computations, order-based computations, and post-grouping computations. SPL can deal with many computations effortlessly that are hard to handle in SQL and stored procedures. Here is an example. We are trying to find the first n big customers whose sales amount occupies at least half of the total and sort them by amount in descending order.

A

B

1

…

/Retrieve data 

2

=A1.sort(amount:-1)

/Sort data by amount in descending order

3

=A2.cumulate(amount)

/Get the sequence of cumulative totals

4

=A3.m(-1)/2

/The last cumulative total is the final total

5

=A3.pselect(~>=A4)

/Get position of record where the cumulative total reaches at least half of the total

6

=A2(to(A5))

/ Get values by position

 

SPL has a professional IDE equipped with all-around debugging functionalities and lets users observe the result of each step with grid-style coding, making it particularly suited to developing algorithms with complex logic.

undefined 

SPL also supports many more data sources, including files, such as CSV, XLS, JSON, and XML, and various NoSQL sources, such as MongoDB, Hadoop, Redis, ElasticSearch, SalesForce, and Cassandra, and the mixed computations between any types of data sources or databases.


 
 •  552 View  •