【问题】
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,
registrations | deposits | games |
3 | 1 | 23 |
There are about 30 of these KPIs (i.e. 30 column headers in the sub reports)
The report will look like this:
kpi | today | yesterday | this month | last month | this year |
registrations | 3 | 4 | 19 | 42 | 333 |
deposits | 1 | 1 | 12 | 13 | 111 |
games | 23 | 24 | 29 | 22 | 23 |
etc |
The problem is In the sub report. i have to explicitly create a field for each KPI, 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 ahve a single query which returns multiple rows, I have mutliple queries whcih return a single row each.
【回答】
这张报表之所以复杂,是因为源数据不符合报表要求。按照要求的业务逻辑,可以将不同的“one row with all the required KPIs”在 SQL 中 union 成如下格式:
range | registrations | deposits | games |
today | 3 | 1 | 23 |
yesterday | 4 | 1 | 24 |
thisMonth | 19 | 12 | 29 |
lastMonth | 42 | 13 | 22 |
thisYear | 333 | 111 | 23 |
将上面的数据转置后再呈现,难度就会大幅降低。使用 SPL 可以辅助 Jasper 转置任意结构的 SQL 结果集,代码如下:
A | B | |
1 | =myDB1.query("select * from KPISubtoal") | |
2 | =A1.fname().to(2,) | |
3 | =create(KPI).record(A2) | |
4 | for A1 | =columnName=A4.#1 |
5 | =A4.array().to(2,) | |
6 | =A3=eval("A3.derive(B5(#):"+columnName+")") |
A1:执行 SQL 取数
A2:取 A1 结果集中的列名称,从第 2 个开始返回,结果如下:
A3:创建序表
A4-B6:循环 A1,将对应值写入 A3 结果序表中
Jasper 可以通过 JDBC 连接集算器,调用脚本方法和调用存储过程一样,详情参考【JasperReport 调用 SPL 脚本】。