Multiple result sets of diversified data sources for computing reports

Diverse data sources are becoming more and more common in report development. the effective support of the collection and computing reports for diverse data sources makes the development of such reports very simple, currently, in addition to supporting different types of data sources (RDB, TXT text, Excel, JSON, HTTP, hadoop, and MongoDB, you can also use multiple result sets returned for a report in a dataset. This effectively avoids repeated operations and improves report development efficiency and computing performance. The following example describes how to use multiple result sets.

Report description

Query the names and total scores of the top three and the last three students based on the student Score Table. The report style is as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4D/39/wKioL1RN53LCzdh6AACOHsA-GR8903.jpg "style =" float: none; "Title =" 2014-10-27_143057.jpg "alt =" wKioL1RN53LCzdh6AACOHsA-GR8903.jpg "/>

To display the names and scores of the top three students and the top three students respectively, the report is naturally divided into two parts. If a dataset needs to be hidden, it is relatively simple to use two datasets. a dataset is grouped and summarized based on the original data, sorted in descending order of the total score, and the top three are obtained, the second dataset takes the top three in ascending order. However, Repeated Computation (group aggregation and sorting) occurs in this process. This problem does not occur if multiple result sets are returned through the set calculator. The following is the implementation process.

Compile the computing script

Use the computing Script Editor to write computing scripts to complete data computing and data source preparation.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4D/38/wKiom1RN5x-TL1biAAJG73-vwzU571.jpg "style =" float: none; "Title =" 2014-10-27_143132.jpg "alt =" wKiom1RN5x-TL1biAAJG73-vwzU571.jpg "/>

A1: connect to the data source demo;

A2: Execute SQL to query the student renewal table data;

A3: scores are summarized by student group;

A4: Use the. Top () function to obtain the first three records based on the total score;

A5: Use the. Top () function to obtain the last three records based on the total score;

A6: returns the first three records in two result sets.

Edit a Report Template

Connect to the report data source demo.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/4D/39/wKioL1RN53TRweDOAAByIWFe_Z4378.jpg "style =" float: none; "Title =" 2014-10-27_143143.jpg "alt =" wkiol1rn53trwedoaabyiwfe_z4378.jpg "/>

Set Dataset

Use the report designer to create a report template, set the dataset, and call the edited script. Because two result sets are returned in the script, you must manually enter the dataset name "ds1, DS2" in dataset settings. Multiple dataset names are separated by commas.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/4D/38/wKiom1RN5yCxLoSmAADpg4LsU6g597.jpg "style =" float: none; "Title =" 2014-10-27_143152.jpg "alt =" wkiom1rn5ycxlosmaadpg4lsu6g597.jpg "/>

The DFX file path can be either an absolute or relative path, and the relative path is the DFX home directory configured in the relative options.

Dataset presentation form:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4D/39/wKioL1RN53TikpREAABbYdRT-2E532.jpg "style =" float: none; "Title =" 2014-10-27_143202.jpg "alt =" wKioL1RN53TikpREAABbYdRT-2E532.jpg "/>

Set a report expression

Edit the report expression, directly use the two result sets returned by the Set Computing script, and use a simple list expression to complete report creation.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/4D/38/wKiom1RN5yGSd8ejAACpSQiYHoc126.jpg "style =" float: none; "Title =" 2014-10-27_143213.jpg "alt =" wkiom1rn5ygsd8ejaacpsqiyhoc126.jpg "/>

As you can see, you can use the set calculator script to output multiple result sets for the report, and the external set computing script has a visual editing and debugging environment, the edited script can also be reused (called by other reports or programs ). However, if the script has been debugged and does not need to be reused, It is troublesome to maintain consistency between the two files (the Set Computing script and Report Template, in this case, it is easier to directly use the script dataset of the Set Computing report.

In the script dataset, you can write scripts to complete computing tasks step by step. The syntax is the same as that of the Set calculator. You can also directly use the data sources and parameters defined in the report. You can use the script dataset as follows:

1. Click "add" in the dataset settings window. The dataset Type dialog box is displayed. Select "script dataset ";

2. Compile the script in the pop-up script dataset editing window;

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/4D/39/wKioL1RN53aQKXxtAAGq_tA_PMU245.jpg "style =" float: none; "Title =" 2014-10-27_143222.jpg "alt =" wkiol1rn53aqkxxtaagq_ta_pmu245.jpg "/>

In the script dataset, you can directly use the demo of the data source defined in the report for query, instead of using the connect () function to connect to the data source like an independent set computing script. The report expression is the same as the dataset using the cube.

Multiple result sets of diversified data sources for computing reports