Show Columns from differents Datasets

Question

I am designing a report and have some doubts.

Actually i have 2 dataset (A (MainDataSet) and B)

In the report I am showing columns from A and I would like to show columns from B (as a group). I have used LookUp function but it is only showing me only 1 column from B.

I would like to show every column. For that I tried to use LookUpSet but I dont want to join the result. I would like to get as a single column.

There is a relation between A to B, 1:m.

I hope you had understand it.

Many thanks.

Answer 1

In your table properties set the DataSetName as DataSet B, the one with your Player data. Then use a Lookup expression to get a single record from MainDataSet A, i.e. the team and country for that player that year.

The expression here joins the datasets by year and position, but you may need an extra field to make sure each player appears in the correct team.

=Lookup(Fields!YEAR.Value + Fields!PLAYER_POSITION.Value,
  Fields!YEAR.Value + Fields!POSITION.Value 
  Fields!COUNTRY.Value, "MainDataSet")

(This assumes that your two datasets come from different Data Sources. If they have the same source, it's typically easier to include all your data in a single dataset.)