SSRS dynamic data retrieval from another dataset

Question

I want to get data(dynamic) according to the data in another dataset and put the result in textbox of the different dataset.

Right now, the data I get is static and I tried to use many expressions but no avail.

Problem lies in editing textbox expression. Since I am getting data from different dataset, it always ask me to have dataset aggregate function in my expression. If I specified the dataset from where I am getting the data, then, I cannot make it dynamic - and I get the undesired result.

And another way out that I attempted is merging the data sets into one rather than having multiples and use it only but this was unsuccessful.

How can I make it work to get the result that I want:

By the way, the textbox and the tables are in different scope. I have tried to combine them in one scope too.

Answer 1

Getting data from another dataset requires an aggregate function because it can't tell what data you want from that dataset so it forces you to aggregate it so it can return a specific value rather than an entire list of values.

I think what you are after is the Lookup function. Lookup allows you to look up a value from another dataset based on a value in the table's dataset. To give a simplistic example, let's say your table was of employees and you had a department id on that table and another dataset called "Departments" which had an id and a name. You could look up the department's name using the following expression:

=Lookup(Fields!DeptId.Value, Fields!Id.Value, Fields!Name.Value, "Departments")

This uses the current table's dataset's DeptId field to look up the Departments dataset by the Id field and return the Name field.