Lookup functions in SSRS 2008 R2 - III

Introduction

This is the final post to discuss the last Lookup function Multilookup(). The other two – Lookup() and LookupSet() have already been discussed in my previous posts.

Syntax

Multilookup(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound. The only difference from the previous lookup functions is that, here this is a VariantArray.

destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter. 

result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row. 

Note, If multiple matches are found, the value from the first matching row will be returned for all the values in the source expression. And we can not use any aggregate functions in the result_expression.

Dataset – The Dataset in which we want to look the values into.

Returns – A VariantArray, or Nothing if there is no match.

Example

Continuing with the same example from the previous posts of Employees & Departments, suppose we want to display the name of the departments to which the Employee is related to, we can use the following expression -

Table Structure of Employees Table

ID	EmployeeID	DepartmentIDs
1	1	1,2,3
2	2	2,3

Table Structure of Departments Table

ID	Name
1	D1
2	D2
3	D3

Required Output

ID	EmployeeID	Departments
1	1	D1,D2,D3
2	2	D2,D3

To get the Departments, we can use the following expression in SSRS -

=Join(MultiLookup(Split(Fields!DepartmentIDs.Value,","),

   Fields!ID.Value,Fields!Name.Value,"Departments")),

   ",")

Here, we have used 3 functions -

1. Split() – To convert the comma separated DepartmentIDs into a value array.


2. Multilookup() – To find the Name of departments for the matching ID.


3. Join() – Prepare the comma separated string for the names returned by the Multilookup() as array.

Conclusion

So, with this we have covered all the 3 lookup functions provided in SSRS 2008 R2 and have learned how to make best use of them to fetch data from multiple datasets.

Lookup functions in SSRS 2008 R2 - II

 

Introduction

In my last post on Lookup functions related to SSRS 2008 R2, I had explained Lookup(). It is used to fetch the first matching value from the other DataSet. Now, what if we want all the matching values from the other DataSet. Here, the LookupSet() function comes handy. Let’s try to understand this.

Syntax

LookupSet(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound.

destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter. 

result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row. 

Note, If multiple matches are found, all the values from the matching rows will be returned. And we can not use any aggregate functions in the result_expression.

Dataset – The Dataset in which we want to look the values into.

Returns – A VariantArray, or Nothing if there is no match.

Example

Continuing with the same example from the last post of Employees & Departments, suppose we want to display the name of all the departments to which the Employee is related to, we can use the following expression -

=JOIN(LookupSet(Fields!DepartmentID.Value, Fields!ID.Value, Fields!Name.Value, "Departments"),",")

Here, we have used 2 functions of SSRS,

1. LookupSet() – To get an Variant array of the matching values. Here, the DepartmentID field will be matched with the ID field of the Departments Dataset and the Name from all the matching rows will be returned.


2. JOIN() – To join all the elements of the array as a comma separated string.

Hope, I have explained clearly the use of LookupSet() funciton. Next, I will blog about the last Lookup function – MultiLookup().

Lookup functions in SSRS 2008 R2 - I

 

Introduction

Most of us who are regularly working with SSRS have always felt the need of some way through which multiple DataSets could be joined – something similar to JOINs of SQL. But there was no straight forward way to get it done in SSRS until the release of SSRS 2008 R2. SSRS 2008 R2 came up with Lookup Functions which plays a very handy role in getting the things done in a very simple way. However, these functions can not be taken as a alternate to JOINs. As their name suggests, they are just lookups helping us to retrieve values from a dataset that has name/value pairs and that is not bound to the data region.

Different Flavors

We are provided with 3 different lookup functions -

1. Lookup(source_expression, destination_expression, result_expression, dataset)
2. LookupSet(source_expression, destination_expression, result_expression, dataset)
3. Multilookup(source_expression, destination_expression, result_expression, dataset)

Let’s take up them one by one

1. Lookup(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound.

destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter.

result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression. 

Note, If multiple matches are found, the value from the first matching row will be returned. And we can not use any aggregate functions in the result_expression.

Dataset – The Dataset in which we want to look the values into.

E.g. Suppose, we are having 2 Datasets – EmployeeDetails & Departments in SSRS. The tablix is bound to EmployeeDetails and in one of the columns we need to display the DepartmentName to which the Employee in each row belongs to. The below expression will help us to fetch the Name of the department from the Departments Dataset.

=Lookup(Fields!DepartmentID.Value, Fields!ID.Value, Fields!Name.Value, "Departments")

Here, the DepartmentID field from the Employees Dataset will be matched with the ID field of the Departments Dataset and the Name for the matching row will be returned.

So, we have seen how we can make use of Lookup() and get the matching value. Will continue with the other two functions in my next blog.