Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications

|

How to Use Multiple Datasets in a Single Tablix Region in SSRS

Post Author: Joe D365 |

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:

Machine generated alternative text:

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

Machine generated alternative text:

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!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

One comment on “How to Use Multiple Datasets in a Single Tablix Region in SSRS”

PowerObjects Recommends