乾学院 23 号会员
1,594 浏览  • 
     ...

BIRT 中两表记录并列显示

 
report(8)

【问题】

table1：

|id | name| isActive|

|---|-----|---------|

| 1 | dd | 1 |

| 1 | cc | 1 |

| 1 | zz | 1 |

| 1 | yy | 1 |

table2：

|id | name| isActive|

|---|----|----------|

| 1 | ab | 0 |

| 1 | cd | 0 |

| 1 | ef | 0 |

| 1 | gh | 0 |

| 1 | wf | 0 |

| 1 | gp | 0 |

the requirements are to get count of both tables and print them in to two separate columns and print it into two separate columns in birt Report i have tried this

SELECT

 COUNT(table2.`name`) Table1Count,

 table1.`isActive` Table1IsActive ,

 COUNT(table2.`name`) Table2Count,

 table2.`isActive` Table2IsActive

FROM

 `table1`,

 `table2`

its out put is in two separate columns but with cross join

Table1Count isActive Table2Count Table2IsActive

 43316 0 3536 1

i can not use any join because there is no relationship in both of these table while its output is similar to the desired out put where i want two tables counts in separate columns

by trying this i get two separate rows

SELECT

 *

FROM

 (SELECT

 COUNT(*) Table1Count,

 table1.`isActive` Table1IsActive

 FROM

 `table1`

 UNION

 SELECT

 COUNT(*) Table2Count,

 table2.`isActive` Table2IsActive

 FROM

`table2 `) AS a

its output is in two separate rows while my requirements are separate columns

Table1Count Table1IsActive

 442 0

 98 1

【回答】

BIRT 脚本写这种运算有些复杂。SQL 不支持分步，写出来的代码不易读。这种运算用集算器的 SPL 脚本写更清晰，也很方便集成进 BIRT：

A

1

=myDB1.query("select isActive from table1")

2

=myDB1.query("select isActive from table2")

3

=create(Table1Count,Table1IsActive,Table2Count,Table2IsActive).record([A1.count(),A1.#3,A2.count(),A2.#3])

A1：读取 table1

1png

A2：读取 table2

2png

A3：创建序表，使用 record 填入 table1 和 table2 的表记录数

3png

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

report(8)