You have multiple csv files like this:

The directory is
├─UserA
│ january_2013_sales.csv
│ february_2013_sales.csv
│ march_2013_sales.csv
│ …
│ january_2015_sales.csv
│ february_2015_sales.csv
│ march_2015_sales.csv
│ …
│
├─UserB
│ january_2013_sales.csv
│ february_2013_sales.csv
│ march_2013_sales.csv
│ …
│ january_2015_sales.csv
│ february_2015_sales.csv
│ march_2015_sales.csv
│ …

All the files in the same format.

You want to generate summary reports of each user yearly, but You find that the dataset can only contain one CSV file in Flat file Data Source.

So, is it possible that one dataset contains multiple CVS files?

Solution

Using esProc as Data Source:

Call the esProc script with parameters. esProc combines data from multiple CSV files that belong to a specific user and queries the result set according to conditions.

Here is the SPL Script:

A1:

esProc script inputs two parameters – “user” and “when” (Assuming that the parameters passed over from BIRT) as shown below :

Result of A1

A2:
Combine data from multiple CSV files in a result set.

A3:
Group the result set of A2 by ‘Customer ID’ and ‘Year’, calculate total and average for each group.

A4:
Filter data with Year.

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.

The sample data can be downloaded UserA.zip. If you have any questions or comments, please leave them below.