Associated Dataset

Application Scenario

Common scenario one: Concatenate datasets from different data sources vertically, which is equivalent to the union all operation in SQL. For example: Datasets ds1 and ds2 originate from different sources but share the same structure. By using an associated dataset, you can concatenate them into a new table.

Common scenario two: Query different databases or tables via different parameters. For example: With parameters, you can choose whether to use ds1 or ds2 two different datasets created under a template to retrieve data from Oracle and SQL Server, respectively. For example, Branch A uses ds1, while Branch B uses ds2.

All the scenarios mentioned above can be implemented by using associated datasets, and in practical applications, the adopted methods are essentially the same.

Function Description

No parameter setting: You can achieve the simplest vertical concatenation function (union all).

Parameter setting: You can use different datasets or tables via different parameters.

Dataset Defining

Create two datasets ds1 and ds2 firstly, and create queries for ds1 and ds2 by using the FRdemo data source. The following figure shows the ds1 dataset.

ds1: SELECT DISTINCT Region,Salesperson FROM Sales_Volume

ds2: SELECT DISTINCT Region,Product FROM Sales_Volume

The following figure shows the preview data of ds1 and ds2.

Associated Dataset Creating

1. Click the + icon, and select Associated Dataset, as shown in the following figure.

2. Select ds1 and ds2, and click Preview, as shown in the following figure.

3. The following figure shows the previewed data.

The same field names are merged into one column, while different field names are displayed in separate columns This is equivalent to the union all operation in SQL.

Continue to use ds1 and ds2 in Example One.

Create Associated Dataset with Parameters

1. Create an Associated Dataset, and click ds1 and ds2. Set a parameter in Condition Formula. You can set the value of Dataset name freely. Set dataset name in the example as dsname.

Enter $dsname="ds1"、$dsname="ds2" in Condition Formula, as shown in the following figure.

2. Click OK. Preview the created Associated Dataset to bring up a dialog box. You can enter different parameter values  to preview different data table, as shown in the following figure

Vertical Concatenation Through Associated Dataset with Parameter

Set Condition Formula = $dsname="ds1", or($dsname="ds1",$dsname="ds2"), as shown in the following figure.

2. Preview the Associated Dataset.

Enter ds1 into the dialog box. As both tables meet the given condition, the ultimate outcome is a vertical concatenation of the two tables.

Enter ds2 into the dialog box. Only the ds2 table meets the condition, so ultimately only the ds2 table is displayed.