Look Up and Look Up Set in SQL Server Reporting Service 2008, 2008 R2 and 2012...

LookUP and LookUP Set in SSRS 2008, 2008 R2 and 2012...

 

Look UP and LookUP Set properties are only available in SQL Server 2008, 2008 R2, 2012. it's used to compare to dataset data and provide the Matching data as done similar to Excel sheets.

 

SQL Server 2012 Version has both LookUP and LookUP Set

SQL Server 2005 Doesn't have Look UP or LookUP Set

Step 1: Create the respective table such as Department and Employee

Create table Department

(

        [Department ID] int,

           [Department Name] Varchar (100)

)

Insert into Department Values (1, 'Sales')

Insert into Department Values (2, 'Production')

Insert into Department Values (3, 'Marketing')

Create table Employees

(

                [Employee ID] int,

                [Employee Name] Varchar (100),

                [Department ID] int

)

 

Insert into Employees Values (10001, 'Albert', 1)

Insert into Employees Values (10002, 'Daniel',2)

Insert into Employees Values (10003, 'Jayaraj',3)

Insert into Employees Values (10004, 'Angeline',1)

Insert into Employees Values (10005, 'Pamela',1)

Insert into Employees Values (10006, 'Ethan',1)

Insert into Employees Values (10007, 'Eric',2)

Insert into Employees Values (10008, 'Shanthi',3)

Insert into Employees Values (10009, 'Sundar',3)

Insert into Employees Values (10010, 'Esther',2)

 

Step 2 : Create a report with Tablix Control present in SSRS and Create 2 set of Dataset as EmployeeSet and DepartmentSet

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Step 3: Create the Tablix Report as shown above and run the Report. It runs the Report properly as shown

Step 4: Instead of the Department ID, I want to provide the Department Name. Consider the Department data has been taken from Oracle and Employee data has been taken from SQL Server where data cannot be joined unless they have predefined Linked Server between them. How can we replace them? So LookUP and LookUP Set can be used to solve such problem in SSRS.

 

Step: 5 Run the Report now. it should display the Department Name instead of Department ID

 

Lesson Learnt

I learnt this lesson from an Interviewer who asked me such question where i answer the 2005 way of implementing these data. but this LookUP and LookUP Set where very useful and i want to share these technique with others who are learning every day. thanks to the Interviewer. 

 

Regards

Daniel J

Dataware and Data Mining Architect...