There are many instances in which you may be required to build an SSRS report using two or more datasets. You may also be required to show information from both datasets in the same table in your report. However, in the case of SSRS reports, once you’ve chosen a dataset for your table, you can choose fields only from that dataset – not from the second one. So, how can you bring values from the other dataset into this table? In today’s post, we’ll answer that question!

Fortunately, there is a way to do it. Using the example below, let’s see how it’s done…

Let's say we have the following two datasets:

Note that we are storing State values in dataset 1 and Region (new region) values in dataset 2. In our example, we must build a report with a table that show Name, Country, State, and Region, which means we need to combine both datasets. We’ll do it like this:

1. Add a new Tablix and start adding values from Dataset 1. Let's add Name, Country, and State. Our Tablix will look like this:

2. To add the fourth column, Region, from dataset 2 in this Tablix, add a new expression, as shown:

3. In our expression, we will use the "Lookup" formula to find the corresponding Region values in dataset 2.

The Lookup function is set up like so: Lookup(source_expression, destination_expression, result_expression, dataset)

In our example, source_expression is new name from dataset 1, destination_expression is new name from dataset 2, result_expression is new region from dataset 2, and dataset is the second dataset from where we want to fetch the Region values, which is DataSet2.

So, our expression looks like this:

Once you have this expression set, you will get Region values in the same table!

In conclusion, just note that we need to make sure the dataset we are "looking up to" has unique values. Then we will be able to simply use the Lookup function and fetch values from that dataset and combine it with data from our original dataset we chose for our table.

Don't forget to subscribe to our blog for more tips and tricks.

As always, happy Dynamics 365’ing!