SSRS - Look up field in dataset that is not part of report
I have an ID column in a table in my Reporting Services report. I want to title each page of my report based on a corresponding name field.
When I try to create an expression for the group-level PageName property, I see that there is a Lookup() function in SSRS. The example given in the description looks like this:
=Lookup(Fields!SaleProdId.Value, Fields!ProductID.Value, Fields!Name.Value, "Product")
The problem is that these fields are presumably in the same dataset used to create the report table. In my case, however, the name field is in another dataset of my project.
Is there a way to span report datasets to lookup up a label not in the current table's dataset?
Answer
Yes, that's exactly what the Lookup(...) function is for. The last parameter is the name of the data set that you would like to look in for your value.
From: http://technet.microsoft.com/en-us/library/ee210531.aspx
Lookup(source_expression, destination_expression, result_expression, dataset)
Parameters
source_expression (Variant) An expression that is evaluated in the current scope and that specifies the name or key to look up. For example, =Fields!ProdID.Value.
destination_expression(Variant) An expression that is evaluated for each row in a dataset and that specifies the name or key to match on. For example, =Fields!ProductID.Value.
result_expression(Variant) An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve. For example, =Fields!ProductName.Value.
dataset A constant that specifies the name of a dataset in the report. For example, "Products".
Let me know if you need more explanation.