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 wecan 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 -

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

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

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.

Wednesday, 27 July 2011

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.

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 wecan 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 -

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.

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().

Friday, 22 July 2011

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.

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 wecan 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.

Search This Blog

About Me

Experience of more than 11+ years in developing & designing Client-Server based applications, smart client applications, web portals and small utility applications using Microsoft technologies.
Strong in designing the Application Architecture, providing out of box solutions & a very good trouble shooter.