Pages

Monday, October 31, 2011

Lookup Function in SSRS 2008 R2









Today, I would like to give the information and an example of using the Lookup feature in SSRS 2008 R2.

Definition:

Lookup function returns the first matching value for the specified name from a dataset that contains name/value pairs.

Syntax:
Lookup(source_expression, destination_expression, result_expression, dataset)

Example:
I am going to just create a simple Reporting Services project with a shared data source to the database.


However, please note that I have two separate data sources pointing to two totally different databases.

So now let’s create a report with a couple of datasets in it. We are going to print the Sales information in one database while we would have all of our Customer information in another database. So I will create one dataset for Sales based off of the SalesLT.SalesOrderHeader table and the other one of Customer details off of the SalesLT.Customer and associated tables.

So now the problem is that since the data for the customers is in a totally separate dataset .How do we relate the two in order to remove CustomerID from our table and replace it with say the customer’s company name?

That’s where the Lookup function comes in. I will add a column on the left of my report and set the header to Company. Next I will delete my CustomerID column from the report since I won’t need that anymore. Lastly, I add a lookup function as an expression for the field under the Company column.

=Lookup(Fields!CustomerID.Value,Fields!CustomerID.Value,
Fields!CompanyName.Value,"Customers")

So in order to use the Lookup function I need to pass it four things.


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!CustomerID.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!CustomerID.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!CompanyName.Value.
dataset
A constant that specifies the name of a dataset in the report. For example, "Customers".


Now if we run our report we can see that everything turned out exactly as it should have.
Now it should be stated that I could have used an expression for any of the values passed as parameters to the function. This allows us to do matching on things like complex keys(ie more than one column to denote a match) or bringing back complex values like the full name of the customer.



No comments: