Use BIRT to generate reports from Excel

 

You can use the esProc with the BIRT (Business Intelligence Reporting Tools) platform to create BIRT reports. esProc as a Birt Excel computing data source can does very complex calculations with simple scripts.

1.Install the esProc.

2.Develop SPL Script in esProc IDE. For example(Calculate the number of posts per month in 2014),

Embedded Simple SQL Script(test1.dfx)


A

1

=connect()

2

=A1.query("select * from (select month(Datetime) as months, amount from /Downloads/post.xlsx where year(Datetime)=2014)  group by months")

Using SPL Script(test2.dfx)


A

1

=file("/Downloads/post.xlsx").xlsimport@tx()

2

=A1.select(year(Datetime)==2014).groups(month(Datetime):month;amount)

Two Scripts get the same result, but using pure SPL syntax can calculate more complex logic. For more information, see SPL parsing and exporting Excel , An Easy Way to Merge and Summarize Excel Files.

3.Call SPL Script in BIRT. Integrate esProc JDBC with BIRT report designer - put the necessary jars and configuration files in BIRT’s corresponding directory. Create a new report and add esproc JDBC data source. Create a new data set on New Data Set window and select the esProc JDBC data source. The data set type is SQL Stored Procedure Query. The query is:{call test1.dfx()} or {call test2.dfx()}.

View the report in web viewer.

month

amount

1

1

2

32

3

60

4

91

5

121

6

152

7

182

8

213

9

244

10

274

11

305

12

335

For detail information, see How to Call an SPL Script in BIRT.

For more explanations and examples, please refer to SPL parsing and exporting Excel .