列转行
【问题】
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 |
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+")") |
A2：取 A1 结果集中的列名称，从第 2 个开始返回，结果如下：
A3：创建序表
A4-B6：循环 A1，将对应值写入 A3 结果序表中
Jasper 可以通过 JDBC 连接集算器，调用脚本方法和调用存储过程一样，详情参考【JasperReport 调用 SPL 脚本】。
