Are you looking for a way to combine related data from different sources for your reports that doesn't involve a lot of tedious

• extracting, transforming, and loading (ETL) data from each data source,
• coding to relate the data, and
• aggregating the data to one source?

New Dataset Joins

In response to customer needs, we've added a new feature to ActiveReports 12: dataset joins.

Dataset joins work in nested data regions in RDL and Page reports.

You can use the following data regions with datset joins, as both parent and nested data regions.

• Tablix
• List
• BandedList
• Table

You can also use these two as nested data regions.

• Sparkline
• Chart

There are two ways to create dataset joins. You can use parameters or filters, depending on your needs.

Note: If you're new to ActiveReports, check out our Getting Started guide for more details on how to open a report, add a data source, etc.

Parameters

Data joins using parameters are great for when

• all of your data is coming from a data provider that supports parameters,
• your parent data set doesn't have a ton of rows, and
• most of the joined rows in the child data set connect to only one row in the parent data.

For example, let's say that your parent data set is Regions and your child data set is Stores, and you join the datasets on the RegionID field. In this case you probably don't have a whole lot of regions, and you probably don't have stores that have multiple regions. This keeps the number of requests relatively low.

This is because the data engine executes the child data set for each instance (every row, every column) of the nested region it's bound to and passes the evaluated parameters. The data engine uses DBMS data filtering indexes, which could end up sending a LOT of requests if you reverse the example above and use Stores for the parent data set, and Regions for the child.

Advantages

• It doesn't consume extra memory.
• You can use DBMS indexed searches.

Disadvantage

Multiple reqests for data can affect performance if you reverse the parent and child data sets so that the parent contains a lot of rows of data.

How to

Note: I'm using the Reels.mdb sample data installed with ActiveReports in the \Documents\GrapeCity Samples\ActiveReports 12\Data folder, plus a small Regions.csv data file that I made.

1. Add a data source and dataset, Region, from the CSV file. We'll use this one for the parent data region.
2. Add a data source, Reels, and a dataset, StoreSales, from the MDB.
3. Add a parameter that you want to join with a value in the parent dataset.
4. Drag a data region (Table, Tablix, List, or BandedList) onto your report. This is your parent data region.
5. Set the parent data region's DataSetName property to DataSet1.
6. Drag a data region to nest in a cell, band, or body of the parent data region.
7. Set the child data region's DataSetName property to DataSet2.
8. Set up the child data region's parameters to evaluate against the parent data set fields.

Filters

Data joins using filters are great for when

• your child data set uses a data provider that doesn't support parameters (like CSV, JSON, XAML), and
• the child data set doesn't contain a ton of data.

For example, let's say that your parent data set is Products and comes from an MDB data provider, your child data set is Inventory and comes from a CSV file, and you join the datasets on the ProductID field. In this case you probably don't have a whole lot of regions, and you probably don't have stores that have multiple regions. This keeps the amount of memory used to store the child data set relatively low.

This is because we load the child data set into memory to access its rows. Then we perform an indexed search in that memory for each row in the parent data set.

Advantages

• It doesn't send multiple requests.
• You can use non-SQL data providers for the child data set.

Disadvantage

Storing your child data set uses memory. If it contains a lot of values, it uses a lot of memory to store it and to perform the indexed search.

How to

Note: I'm using the sample data installed with ActiveReports in the \Documents\GrapeCity Samples\ActiveReports 12\Data folder.

1. Add two data sources from different providers to a new RDL report: NWIND.mdb and Products_header_tab.csv.
2. Create a parent dataset, here we'll use "Products" from the csv source.
3. Create a child dataset, for example, "Invoices" from the mdb source.
4. Add a Table to use as the parent data region, and in the Properties window, set its DataSetName property to Products. (You can also use a Tablix, List, or BandedList.) Create nested data region (inside cell or row body of parent data region) and attache it to the 'DataSet2' Setup nested data regions filter in the following way: Add a new filter row Select field of the child data set as the filter's 'Expression' Select field of the parent data set as the filter's value NOTE. As can be seen from #5, there is limitation: the left part of filter expression is always evaluated in child data set context, and the right one in parent data set context.