Scenario

In BIRT, I am working on creating a custom report. I have a database query with the information I want, and I need to join it with an outside CSV file. When I perform the join within BIRT and preview the results, it works fine. However, when I want to do something slightly more complex summary calculations, after using the Joint dataset in BIRT, there is no similar database window function or stored procedure capabilities to solve somewhat more complex user requirements.

Solution
You can use esProc as Data Source. The esProc SPL can join a Database with flat files and have the capabilities to implement complex requirements through a simple script.

For example,salestable is from a database:

ORDERID     SELLERID    AMOUNT
----        ----        ----
1           17          392.0
2           6           4802.0
3           16          13500.0
4           9           26100.0
5           11          4410.0
6           18          6174.0
7           2           17800.0
8           7           2156.0
9           14          17400.0
10          19          19200.0
...

employee table is from an employee.csv file:

EID         NAME        GENDER      BIRTHDAY    STATE
----        ----        ----        ----        ----
1           Rebecca     F           1974-11-20  California
2           Ashley      F           1980-07-19  New York
3           Rachel      F           1970-12-17  New Mexico
4           Emily       F           1985-03-07  Texas
5           Ashley      F           1975-05-13  Texas
6           Matthew     M           1984-07-07  California
7           Alexis      F           1972-08-16  Illinois
8           Megan       F           1979-04-19  California
9           Victoria    F           1983-12-07  Texas
10          Ryan        M           1976-03-12  Pennsylvania
...

Requirements:

1). Find out the sales of female sales under 40 years old.
2). Top three sales in each state.

Here is the SPL script.

A5:

A8:

You can perform the join within BIRT and get the same result of A5. But if you want to get A8 results, it is impossible to use the functions provided by BIRT internally.

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 many difficult calculations of Text Computing, you can refer to Structured Text Computing.

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