Relation Dataset

1) Relation dataset, similar to the Union in SQL statements, can link 2 or more datasets coming from different databases, and merge 2 or more table structures.

2) For example, one template has defined two datasets ds1 and ds2, which were fetched from Oracle and SQL Server, and you plan to use a parameter to determine whether the report data are from ds1 or ds2.

3) Data sources in the Designer can be divided into two types according to their scope of application: Template dataset  and server dataset. We use template dataset as an example here. The operation method of server datasets is similar to that of template datasets, and will not be discussed here.

1. Create an original dataset.

1) Use FRDemo to create a DB query dataset ds1, for details, please refer to DB Query. The SQL statement is as follows:

SELECT DISTINCT Region, Salesperson FROM Sales_Volume

The data is shown below.

2) Use FRDemo to create a DB query dataset ds2. The SQL statement is as follows:

SELECT DISTINCT Region, Product FROM Sales_Volume

The data is shown below.

2. Create a relation dataset

1) Create a relation dataset.

2) Name the dataset and check the ds1 and ds2 that need to be linked.

3) Click  above to preview the result.

The merge rule is as follows: If several datasets with different field names are merged, merging begins from top to bottom from the first data set, and the same field name will be used for merging. If encountering different field names, the new field name will be added. If a field does not exist in the dataset before the merge, field values are null.

For example, the relation dataset displays all the columns of ds1 and ds2, and is linked according to the common Region field. There is no Product field in ds1, so in the relation dataset, the Product field for records coming from ds1 is empty. Similarly, there is no Salesperson field in the ds2, so in the relation dataset, the Salesperson field for records coming from ds2 is empty.

3. Set the parameters of a relation dataset

You can also set parameters in a relation dataset. For example, you can use parameters to control the results of a relation dataset.

1) Input parameter formulas in the Condition Formulas.

Input the following formula in the Condition Formulas field of ds1. $dsname="ds1”.

Input the following formula in the Condition Formulas field of ds2: $dsname="ds2”.

2）You can obtain the parameter dsname. When the parameter value is ds1, then the parameter dsname corresponds to the result of ds1 dataset; and when the parameter value is ds 2, then the parameter dsname corresponds to the result of ds3 dataset. Refresh the interface, and you will see the set parameters below.

2) Click Preview button, and enter ds1 to preview the data of ds1. Return to the Preview interface and enter ds2 to preview the data of ds2.