【Question】

I have a long report which shows a lot of KPIs with columns for today, yesterday, last month, year to date etc.

Each is a sub report which returns one row with all the required KPIs for the given period, e.g.

There are about 30 of these KPIs (i.e. 30 column headers in the sub reports)

The report will look like this:

The problem is in the sub report. I have to explicitly create a field for each KPI, and then explicitly create a text box going down the page for each field.

Is there no way to simply list the data which comes back from the query going down the page? I know there is a table component, but this only works if you have a single query which returns multiple rows, I have multiple queries which return a single row each.

【Answer】

The source data format isn’t one required by the reporting tool. That makes your reporting difficult. We can make some changes. Union “rows with all the required KPIs” as a table with the following format in SQL:

The transposition of the source data makes the displaying much easier. You can transpose a SQL result set of any structure in SPL in JasperReports. Here’s the SPL code:

A1: Retrieve data in SQL;

A2: Get field names from A1’s result set and return them beginning from the second one;

A3: Create a new table sequence;

A4-B6: Loop through A1 to populate the corresponding values to A3’s table sequence.

You can connect to esProc in JasperReport via JDBC. Calling an SPL script is the same as calling a stored procedure. Details are explained in How to Call an SPL Script in JasperReport.