SQL 如何基于多个数据集创建 BIRT 报告

How to create BIRT report based on multiple data sets

提问by Montaro

I need help in creating a BIRT report; the situation is that I have multiple queries but the report all columns should be grouped by One column for example (Operator) Like :

我需要帮助创建 BIRT 报告；情况是我有多个查询，但报告所有列都应按一列分组，例如 (Operator) Like ：

Operator | Expr1 | Expr2 | Expr3 | Expr4 |

操作员 | 表达式1 | 表达式2 | 表达式3 | 表达式4 |

op1 | ## | ## | ## | ## |

op1 | ## | ## | ## | ## |

op2 | ## | ## | ## | ## |

op2 | ## | ## | ## | ## |

op3 | ## | ## | ## | ## |

op3 | ## | ## | ## | ## |

---

---

How can i accomplish that ? I 'm trying to make the Operator separate data set and put it in the first column and add a parameter in all quires i.e (where (operator = ?)) to get the Operator from the current row of operators column..But I can not attach the parameter value in each dataset to a the Operator dataset data set. Is this strategy right ? If yes .. how can I do that. If no, what is the correct strategy? Million thanks in advance,

我怎样才能做到这一点？我正在尝试使 Operator 分离数据集并将其放在第一列中，并在所有 quires 中添加一个参数，即 (where (operator = ?)) 以从操作符列的当前行中获取 Operator ..但我可以不将每个数据集中的参数值附加到 Operator 数据集数据集。这个策略对吗？如果是..我怎么能做到这一点。如果不是，正确的策略是什么？万万提前致谢，

回答by

If your multiple datasets are coming from the same datasource, I recommend combining the queries.

如果您的多个数据集来自同一个数据源，我建议合并查询。

If the multiple datasets are coming from different datasources, you can link them together using the Joint Data Set functionality in BIRT.

如果多个数据集来自不同的数据源，您可以使用 BIRT 中的联合数据集功能将它们链接在一起。

To link two separate datasets together:

要将两个单独的数据集链接在一起：

1. Right-click on Data Sets in the Data Explorer pane within the BIRT Report Designer, and select New Joint Data Set.
2. On the New Joint Data Set dialog, select the data set and field (Operator, here) to be joined from on the left-hand list.
3. Select the join type (Inner, Left Outer, Right Outer, Full Outer) from the options between the two lists.
4. Select the data set and field (Operator again, here) to be joined to on the right-hand list.
5. Give the resultant new dataset a meaningful name, in the option at the bottom of the dialog.
6. Click Finish.
7. Edit the resultant dataset to produce output as required, from the Edit Dataset dialog.

1. 在 BIRT Report Designer 的 Data Explorer 窗格中右键单击 Data Sets，然后选择 New Joint Data Set。
2. 在 New Joint Data Set 对话框中，从左侧列表中选择要连接的数据集和字段（Operator，此处）。
3. 从两个列表之间的选项中选择连接类型（内、左外、右外、全外）。
4. 在右侧列表中选择要加入的数据集和字段（再次是运算符，此处）。
5. 在对话框底部的选项中，为生成的新数据集指定一个有意义的名称。
6. 单击完成。
7. 从“编辑数据集”对话框编辑结果数据集以根据需要生成输出。

You can only produce joint datasets from no more than two existing datasets. However, joint datasets can themselves be included as input datasets for other joint datasets - so if you need to join three datasets together, this can be achieved by linking two of them into one joint dataset, then creating a new joint dataset based on the first joint dataset and the third original dataset.

您只能从不超过两个现有数据集生成联合数据集。但是，关节数据集本身可以作为其他关节数据集的输入数据集包含在内 - 因此，如果您需要将三个数据集连接在一起，可以通过将其中两个数据集链接到一个关节数据集，然后基于第一个创建新的关节数据集来实现联合数据集和第三个原始数据集。

It is not necessary to paramterise any of the existing queries to join datasets. A parameter condition on Operator in the query should only be included if the intention is to restrict the Operators returned in that dataset (eg. to return operators 100-121 only).

无需参数化任何现有查询来连接数据集。仅当意图限制在该数据集中返回的运算符（例如，仅返回运算符 100-121）时，才应包括查询中运算符的参数条件。

回答by MystikSpiral

You can certainly create as many data sets on a BIRT report as you need. From there you can create joint data sets that create a unified view of the data. DO you have a common field amongst your data sets that can allow you to create the join?

您当然可以根据需要在 BIRT 报告上创建任意数量的数据集。从那里您可以创建联合数据集，以创建统一的数据视图。您的数据集中是否有一个公共字段可以让您创建连接？

If you cannot create the joint data set, I would recommend setting up a scripted data set. The scripted set relies on a POJO to aggregate the data and apply the business logic necessary to produce the unified view you need. From there you read the data into the report from the POJO and you are all set.

如果您无法创建联合数据集，我建议您设置一个脚本化数据集。脚本集依赖于 POJO 来聚合数据并应用必要的业务逻辑来生成您需要的统一视图。从那里，您将数据从 POJO 读入报告中，一切就绪。