Since SSRS 2008 R2 multiple datasets can be joined in a single data region on the report. As a result of the expression field we can display the first matching value for the specified name from secondary dataset that contains name/value pairs.
SQL Reporting Services comes with three lookup functions having four parameters as given below
- Lookup (source_expression, destination_expression, result_expression, dataset)
- LookupSet (source_expression, destination_expression, result_expression, dataset)
- MultilookUp (source_expression, destination_expression, result_expression, dataset)
LookUp Functions Parameters
- source_expression. Evaluated in the current scope and that specifies the name or key to look up. For example, =Fields!AgentID.Value.
- destination_expression. Evaluated for each row in a dataset and that specifies the name or key to match on. For example, =Fields!Agent_ID.Value.
- result_expression. Evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve. For example, =Fields!TeamLeader.Value.
- dataset. Name of a dataset in the report. For example, “Agents”
| Lookup | LookupSet | MultiLookup |
|---|---|---|
| Retrieves the value from the specified dataset for a name/value pair where there is a 1-to-1 relationship. | Retrieves multiple values for a single name or key field where there is a 1-to-many relationship | Retrieves a set of values from a dataset for name-value pairs where each pair has a 1-to-1 relationship |
| For example, for an ID field in a table, you can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region | For example, for a customer identifier in a table, you can use LookupSet to retrieve all the associated phone numbers for that customer from a dataset that is not bound to the data region | For example, for a multivalue parameter that is based on primary key identifiers, you can use Multilookup in an expression in a text box in a table to retrieve associated values from a dataset that is not bound to the parameter or to the table. |
| Returns the result expression value | Returns the set of result expression values
Because LookupSet/MultiLookup returns a collection of objects, you cannot display the result expression directly in a text box. You can concatenate the value of each object in the collection as a string Use the Visual Basic function Join create a delimited string from a set of objects. Use a comma as a separator to combine the objects in a single line. In some renderers, you might use a Visual Basic line feed (vbCrLF) as a separator to list each value on a new line.
|
|
|
||
SSRS Lookup Example
Dataset1 includes an Agent Name identifier (agent_name_tpi) field. Dataset2 contains the corresponding AgentName and total calls answered AnsCalls.
In the following expression, Lookup compares the value of agent_name_tpi to AgentName in each row of the dataset2 and, when a match is found, returns the value of the AnsCalls field for that row.