Pentaho 简明教程

Pentaho - Data Sources & Queries

在本章中，我们将通过一个示例来了解如何使用 Pentaho Reporting Designer。我们将创建一个员工数据库报表，以便对每位员工进行快速概览。我们将通过添加数据源和向 Pentaho Designer 传递查询来创建我们的第一个报表。

In this chapter, we will learn to use the Pentaho Reporting Designer by taking an example. We will create a report on the employee database to produce a quick overview of every employee. We will create our first report by adding a data source and passing queries to the Pentaho Designer.

在使用 Pentaho Report Designer 之前，创建一个名为 employeedb 的数据库，并在该数据库中使用以下查询创建一个名为 employee 的表。

Before using Pentaho Report Designer, create a database named employeedb and in that database, create a table named employee using the following query.

CREATE TABLE 'employee' (
   'id' integer NOT NULL,
   'name' varchar(20),
   'designation' varchar(20),
   'department' varchar(20),
   'age' integer,
   PRIMARY KEY ('id')
)

将以下记录插入表中。

Insert the following records into the table.

Name

Designation

Department

age

1201

'satish'

'writer'

'Tuto_Write'

1202

'krishna'

'writer'

'Tuto_Write'

1203

'prasanth'

'php developer'

'Tuto_develop'

1204

'khaleel'

'php developer'

'Tuto_develop'

1205

'raju'

'HTML developer'

'Tuto_develop'

1206

'javed'

'HTML developer'

'Tuto_develop'

1207

'kiran'

'Proof Reader'

'Tuto_Reader'

1208

'pravenya'

'Proof Reader'

'Tuto_Reader'

1209

'mukesh'

'Proof Reader'

'Tuto_Reader'

1210

'sai'

'writer'

'Tuto_writer'

1211

'sathish'

'graphics designer'

'Tuto_designer'

1212

'viswani'

'graphics designer'

'Tuto_designer'

1213

'gopal'

'manager'

'Tuto_develop'

1214

'omer'

'manager'

'Tuto_writer'

1215

'shirjeel'

'manager'

'Tuto_Reader'

如果你想操作表中包含的数据，最好的选择是使用 SQL。但如果你想根据数据创建报表，那么 Pentaho Reporting 是最佳选择。我们的任务是向 Pentaho Reporting 设计器工具传递一个 SQL 查询，并选择相应的字段（它们显示在报表中），然后在报表页的“详细信息”中显示它。

If you want to manipulate the data contained inside the table, the best choice is to use SQL. But if you want to create a report based on the data, Pentaho Reporting is the best option. Our task is to pass an SQL query to the Pentaho Reporting designer tool and select respective fields (which are presented in the report) and present it on the Details of the report sheet.

在继续之前，请确保你精通 Pentaho 中提供的所有导航选项（在上一章中进行了说明）。既然我们有一个数据源，那么让我们进一步了解如何使用 Pentaho 生成专业报表。

Before moving further, make sure you are well versed with all the navigation options available in Pentaho (explained in the previous chapter). Now that we have a data source, let us proceed further and try to understand how to use Pentaho to generate a professional report.

Steps to Generate a Report using Pentaho

按照以下步骤从头开始创建报表，无需使用报表设计向导。

Follow the steps given below to create a report from scratch without using Report Design Wizard.

Step 1 : Create a New Report

你可以通过单击欢迎窗格上的“新建报表”或转到“文件 → 新建”来创建新的报表定义文件。

You can create a new report definition file by clicking "new report" on the welcome pane or go to "File → new".

Step 2 : Add a Data Source

右侧的结构窗格提供报表的可视元素的视图。数据源的定义将在 Data 标签上；它允许定义报表数据来自何处以及在报表处理期间如何处理此数据。

The Structure Pane on the right-hand side provides a view of the visual elements of a report. The definition of the data source will be on the Data tab; it allows to define where the report data comes from and how this data is processed during the report processing.

报表通常以表格的形式显示由数据源提供的的数据，而报表定义定义如何对报表进行格式化或打印。如以下截图所示，从 structure pane 中选择 Data tab 。

A report generally displays the data that is supplied by a data source in the form of a table, whereas a report definition defines how the report is to be formatted or printed. As shown in the following screenshot, select the Data tab from structure pane.

在数据标签中，右键单击数据集并选择 JDBC 以添加数据源。通常，在选项列表中，你可以根据要求选择任何其他选项。这意味着，如果你有 XML 文件作为你的数据源，那么从列表中选择 XML 选项。看看以下截图。在这里，我们正在选择 JDBC 选项以将数据库添加为数据源。

In the Data tab, right-click on the Data Sets and select JDBC to add a data source. Generally, in the list of options, you can select any other option based on the requirement. It means, if you have an XML file as your data source, then choose XML option from the list. Take a look at the following screenshot. Here we are selecting the JDBC option to add a database as data source.

在选择 JDBC 选项作为数据源后，你将找到一个对话框，如以下截图所示。

After having selected the JDBC option as the data source, you will find a dialog box as shown in the following screenshot.

我们已经为数据源选择了 MySQL 数据库，因此我们必须选择对话框左侧面板中的 SampleData (MySQL) 选项（在给定的截图中标记为指针“1”）。指针“2”用于编辑连接语句和用于与数据库交互的 URL。

We have already chosen MySQL database for the data source, therefore we have to select the SampleData (MySQL) option in the left-side panel of the dialog box (marked as pointer "1") in the given screenshot. Pointer "2" is meant for editing the connection statement and URL to interact with the database.

以下截图显示了一个对话框，其中您可以定义连接语句和用于数据库的 URL。我们需要在以下截图中执行四个操作（使用指针突出显示）。

The following screenshot shows a dialog box where you can define your connection statement and the URL for the database. We need to carry out four operations on the following screen (which are highlighted using pointers).

In the connection type list, select MySQL − We have already chosen MySQL as the database (data source).
In the Access list, select Native (JDBC) − Through JDBC connection, we can access the database.
In the Settings section, we must mention the Host Name (localhost), Database name (employeedb), port number (3306), username (root), and the password (as per you system).
Test the connection statement by clicking the Test button.

最后，单击 OK 按钮确认数据库连接。

Finally, click the OK button to confirm the database connection.

Step 3 : Add a Query

查看以下截图。该对话框展示了通过数据库连接可用的已保存查询。

Take a look at the following screenshot. The dialog box presents the available saved queries available through the database connection.

The Available Queries block on the right side of the dialog box displays a list of all the available queries.
The Query Name block displays the selected query name which is selected in the above available queries list.
The Query block displays the query statement. If no queries are available or if you want to create a new query, click the “+” button which is highlighted as pointer “1” in the following screenshot.

在单击 “ + ” 按钮时，可以通过在 Query Name 块上编辑名称（如 select_all_records ），在 Query 块中使用以下查询语句来创建查询。

While clicking the “+” button, you can create a query by editing a name on the Query Name block as select_all_records and use the following query statement in the Query block.

SELECT
   employee.id,
   employee.name,
   employee.designation,
   employee.department,
   employee.age
FROM
   employee
LIMIT
   15

添加查询后，您应该获得以下对话框。单击预览按钮。

After adding the query, you should get the following dialogue box. Click the preview button.

单击预览按钮后，您将在一个单独的对话框中找到所有 employee 表记录，如下截图所示。单击 close 按钮。

After clicking the preview button, you will find all the employee table records in a separate dialog box as shown in the following screenshot. Click the close button.

然后，单击 OK 按钮提交查询。提交查询后，您将在右侧结构窗格中的查询名称下方找到所有表字段名称及其数据类型，如下截图所示。此处，最大化的框是位于屏幕右侧的结构窗格。

Then, click the OK button to submit the query. After submitting the query, you will find all the table field names and their datatypes under the query name on the right-side structure pane, as shown in the following screenshot. Here, the maximized box is the structure pane which is placed on the right side of the screen.

到目前为止，我们已向 Pentaho 报表设计器添加了一个数据源和一个查询。现在，我们必须向工作空间添加元素以创建报表。同样的示例在下一章 " Reporting Elements " 中得到扩展。

We have so far added a data source and a query to the Pentaho Reporting Designer. Now, we have to add elements into the workspace to create a report. The same example is extended to the next chapter "Reporting Elements".