How to use DataSet Joins in ActiveReports 12

The Lookup function was introduced in ActiveReports 11 to strengthen the support for multiple data sources/datasets in a data region. Continuing with this, ActiveReports 12 introduces the concept of DataSet Joins, which gives further flexibility to retrieve data stored in different data systems/data sources.

In this blog, we'll look at what a dataset join is, how to use dataset joins, and understand their benefits with the help of a business scenario in ActiveReports.

Download .NET reports and data files covered in this blog

Conceptually, "Dataset Joins" can be compared to SQL joins with a crucial difference: writing complex SQL join queries is not involved in this, and dataset joins can be used with any data source – file-based like XML, JSON, CSV etc., or even relational databases like SQL or Oracle.

You may want to use DataSet Joins:

• When the data is stored in multiple data sources including file-based sources – CSV, JSON, XML etc.
• To avoid time-consuming and error-prone manual aggregation of data using SQL joins when the data comes from multiple relational data sources
• When the data has 1:N cardinality. For data with 1:1 cardinality, Lookup function is more suitable to use. While "Lookup" returns only the first occurrence of the record from the related dataset, "Dataset Joins" fetches all related records like "Left Outer SQL Join".

To retrieve the data from multiple data systems in reports designed using ActiveReports, you need to have datasets based on different data sources and nest data regions such as Table, Tablix, List, BandedList, Chart, Bullet, Sparkline within Page, and RDL reports. Thereafter, an internal mapping (termed as DataSet joins) needs to be created between these data regions bound to these datasets.

Step 1: Locate your different data sources

Suppose at the end of the financial year, the Director of Human Resources needs to prepare a payroll report of its employees, listing the payroll information over the years along with employee's profile, as shown below:

The data to be shown in this report comes from different data sources.

Employee details are maintained by HR department in an online system and the data is generated/available in JSON format for use.

Payroll details are maintained by the finance department and stored in a SQL server database.

Step 2: Design the report

The required report layout can be conveniently designed in ActiveReports using the Table data region. We'll nest a Table data region within a List data region, where List displays the employee's personal information and Table summarizes the payroll information, as shown below:

Note: For the scope of this blog, we will not be going into the details of report design (appearance part) but will be primarily focused on the data retrieval aspect. For more information on designing reports using ActiveReports, you might want to refer to "Single Layout Reports" walkthrough.

Step 3: Add the data sources and bind the report

Next, we will add two DataSources (Employees and Payroll) and their respective datasets (EmployeeInfo and EmployeePayroll) for retrieving data:

Bind the List to EmployeeInfo dataset and Table to EmployeePayroll dataset, then group the List on EmployeeID field to categorize the payroll information of an employee on single page of the report.

Step 4: Map the data regions

Now that the data regions are bound to datasets, they're acting independently and a mapping - a join - is required between them so we can display the data from different data sources. To display the payroll data for the respective employee, we need to define this mapping between Table and the List data regions.

There can be two ways to create this mapping in ActiveReports 12 - by specifying filters or parameters on the child table.

Map the data regions using filters

Add a filter on EmployeeID field in the child table that is bound to the EmployeePayroll dataset:

Here, EmployeeID in Expression is evaluated in context of the EmployeePayroll dataset and EmployeeID in Value is evaluated in context of the EmployeeInfo dataset, thus retrieving the payroll records of employees to be displayed in the child table whose employee ID matches the employee ID displayed in the parent list.

Map the data regions using parameters

Create the required mapping by adding a parameter, empID, in DataSetParameters collection of the child table:

Add a parameter in the EmployeePayroll dataset:

Then update the EmployeePayroll dataset's query:

Here, the parameter empID added in the DataSetParameter collection of the child table is evaluated in context of the EmployeePayroll dataset, and the value is evaluated in the context of the "EmployeeInfo" dataset, thus retrieving the payroll information of respective employees to be displayed in the child table.

Step 5: Run the report

Once the required mapping has been done using either of the above approaches, here's how the report looks with the payroll information for respective employee over the years. You can download the complete reports here.

Pros and cons of parameter vs. filter

While the parameters approach is good for showing larger records in the child table, the filters approach is more suitable for displaying limited records in the child table and can be used when child datasets does not support parameters - CSV, XML, JSON and other non-SQL data providers.

Although parameters are slow to process, they consume less memory, whereas filters are fast to process but consume high memory when working with the dataset joins. So, depending upon how your data is organized and your business needs, you may choose either of the above approaches for retrieving data from different datasets in the nested data regions.

For more information on dataset joins, see topic "Nested data regions bound to different datasets" in the help document.

Download .NET reports and data files

Try ActiveReports Developer