列转行

【问题】

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 脚本】。