列转行

【问题】

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 个开始返回,结果如下:

imagepng

A3:创建序表

A4-B6:循环 A1,将对应值写入 A3 结果序表中

Jasper 可以通过 JDBC 连接集算器,调用脚本方法和调用存储过程一样,详情参考【JasperReport 调用 SPL 脚本】。