Pass field from one dataset to another dataset as parameter In SSRS 2008 R2

Question

I have a scenario where one of my data-set returns multiple records. Now I have implemented grouping in my report so as to display each record from this data set on a different page.

Now I have an another dataset which loads some data using fields from dataset1 as parameter. That means I need to assign the fields from dataset1 to dataset2 as parameter for each record.

For a single record, we can assign the values from dataset1 to a report parameter and then use it for our dataset.

But in this case, parameter always holds the value 1st record and passes it for every page or group.

How can I achieve this?

Answer 1

I may be misunderstanding your question, but I think what you're looking for here is the LookUp() function. This basically fetches data from another dataset, matching on some primary key that you define.

The function has four arguments; the first is the data to match on in the "destination" dataset (I.E. the one that's currently powering your matrix, the second is the data to match on in the "source" dataset (I.E. the one you're fetching the data from). The third is the data you want to fetch, and the fourth is the name of the "source" dataset.

So as an example:

=LookUp(Fields!PrimaryKey.Value, Fields!PrimaryKey.Value, Fields!Data.Value, "DataSet2")

The result of this expression would be for the Report to bring back the data from the field called "Data" in "DataSet2", matching on the values in the fields called "PrimaryKey" in each dataset

Hope that makes sense.

Answer 2

I had the same problem, where the value was always from the first record in the parent report dataset for each subreport record.

In my case, the resolution was to remove the hidden parameter that I was using in the parent report. The default value I was assigning for that parameter were results from a query.

I then went into the properties of the Subreport, selected Parameters and in the Value dropdown where I originally assigned my parent report parameter, I switched it to the field name of the parent report dataset field so that it would pass the value to my subreport parameter.

Hope this helps.

Answer 3

From your explanation, it seems like you want to use a for...next loop but only the first item in Dataset1 is always returned. Did you try to clear the parameter before the loop goes to the next item? Another alternative is to create a Dictionary and add all the items in Dataset1 to it, then loop the Dictionary to get the records for Dataset2, and always clear your parameter before the loop goes to the next item. There should be much better options, but this is what I have for now.

Regards.