These beautiful features were introduced
with the release of SQL Server 2008R2 which brings a lot of satisfaction to
report writer and developers to marge values from two datasets.
(This is something that we had to do in SSRS 2005 with custom code and timing the dataset that loaded a custom code variable in memory just to use custom code to retrieve the info from that same cache in the report).
These functions play a very handy role to combine data from two datasets in a single data region which was not possible in earlier versions of Reporting Services in a very simple way. We cannot assume these functions as an alternate to JOINs but something similar to JOINs of SQL.
(This is something that we had to do in SSRS 2005 with custom code and timing the dataset that loaded a custom code variable in memory just to use custom code to retrieve the info from that same cache in the report).
These functions play a very handy role to combine data from two datasets in a single data region which was not possible in earlier versions of Reporting Services in a very simple way. We cannot assume these functions as an alternate to JOINs but something similar to JOINs of SQL.
These functions play an
effect role to link together data from two shared datasets in one data region. 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)
source_expression – (VariantArray) An expression
that is evaluated in the current scope and that specifies the set of names or
keys to look up. This will be evaluated in the current scope – generally
the dataSet with which the Tablix is bound.
destination_expression – (Variant) an expression that is
evaluated for each row in a dataset and that specifies the name or key to match
on. This field will belong to the dataset provided in the same function
as the last parameter.
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. The field we want to retrieve
from the destination dataset for the matching source_expression &
destination_expression.
Dataset – A constant that will specifies the name of a dataset in the
report. The dataset in which we want to look the values into.
How
does Lookup work?
To understand the lookup function, we
have the two dataset such as Employee and Country in our report where Employee
is our main dataset and country is our secondary dataset having the following information-
In the above datasets, you can see that Country
Code is common in both dataset (Employee and Country) and after combination of
the both datasets; country name is appearing with each employee by the helping
of lookup functionality.
To implement these functions in SSRS
or Report Builder by using the following steps –
1) We need to create a report called Employee Information having with two datasets dataset (Employee and Country) as shown below:
2) Now, set the Employee dataset as the main dataset for the report Tablix as shown below:
3) We need to call Country Name from the Country dataset. So, right click on the column and click on expressions as shown below:
4) In Expression window, we need to do the following setting by using the lookup function where first value (Key Value) comes from the main dataset (Employee), second value (Key value) from the requested dataset (Country), third value (returned value) will be from the requested dataset (Country) and forth value should be the name of the requested dataset (Country)–
= Lookup (Fields!CountryCode.Value,
Fields!CountryCode.Value,
Fields!CountryName.Value,
"Country")
|
5) Click on the OK button and view the report preview as shown below –
6) Now, we need to implement second LookupSet function to the report and need to add another table to represent the data for this function. In this example, we need to set the Country dataset the main table to the Tablix and will use Employee dataset as the secondary dataset as shown below-
7) Now, we need to call Employees names from the Employee dataset which are associated with the respective country. So, right click on the column and click on expressions as shown below:
8) In Expression window, we need to do the following setting by using the lookupSet function where first value (Key Value) comes from the main dataset (Country), second value (Key value) from the requested dataset (Employee), third value (returned value) will be from the requested dataset (Employee) and forth value should be the name of the requested dataset (Employee)–
=Join(LookUpSet(Fields!CountryCode.Value,
Fields!CountryCode.Value,
Fields!EmployeeName.Value,
"Employee"),"," )
|
9) Click on the OK button and view the report preview as shown below-
In the LookupSet section, you can see that there are two names are appearing just front of USA country due to LookupSet function.
10) Now, we need to implement second MultiLookUp function to the report and need to add another table to represent the data for this function. In this example, we need to set the Country dataset the main table to the Tablix and will use Employee dataset as the secondary dataset as shown below-
11) Now, we need to call Employees names from the Employee dataset which are associated with the respective country. So, right click on the column and click on expressions as shown below:
12) In Expression window, we need to do the following setting by using the Multilookup function where first value (Key Value) comes from the main dataset (Country), second value (Key value) from the requested dataset (Employee), third value (returned value) will be from the requested dataset (Employee) and forth value should be the name of the requested dataset (Employee)–
=Join(MultiLookup(Split(Fields!CountryCode.Value,","),
Fields!CountryCode.Value,
Fields!EmployeeName.Value,
"Employee"),"," )
|
13) Click on the OK button and view the report preview as shown below-
Now you can see that for USA country is showing the single employee name instead of two employees. To look up the names that correspond to the list of identifiers, use Multilookup. You must first split the list into a string array, call Multilookup to retrieve the employee’s names, and concatenate the results into a string.
Watch Video demo here-
Watch Video demo here-
Very Helpful post.. Thanks Mukesh :)
ReplyDelete~
Chander
Can you write more on MultiLookup.
ReplyDelete~
Chander
Sure! I'll post some more example on Multilookup
ReplyDeleteWhat if I have 3 Datasets and want to use the Lookupset function on Dataset1 and Dataset3. How do I do that?
ReplyDeleteThanks you very much for sharing these links. Will definitely check this out..
ReplyDeleteCPA in Key West
Hi Thanks for the nice information its very useful to read your blog. We provide best Cost And Management Accountancy (CMA)
ReplyDeleteI found that site very usefull and this survey is very cirious, I ' ve never seen a blog that demand a survey for this actions, very curious... odzyskiwanie skasowanych smsów
ReplyDeleteThanks for sharing us. camping mit kind und hund
ReplyDeleteThank You for providing us with such an insightful information through this blog.
ReplyDeleteData Science-Alteryx Training Course in Coimbatore | Online Data Science Course in Coimbatore | Data Science Training in Coimbatore | Best Data Science Training Institute | Data Science Course in Coimbatore Online Data Science Training in Coimbatore | Data Science with python Training Course in Coimbatore | Data Science Traning in saravanampatti
GOOD ARTICLE
ReplyDeletefinal year project
mini projects for cse
final year projects for cse
final year projects for cse students
final year projects for cse domains
final year projects for cse in data mining
final year projects for cse with source code
final year project for ece
final year project in mechanical engineering
final year project for eee
Thanks for sharing this post.It is very informative and helpful.
ReplyDeleteSydney Brooke Simpson
Jimmy Graham
Bhgwalmartoffer
Organic Chemistry tutor
ReplyDeletechennai to bangalore cab
hadoop
Drilling consultants
Buy Cheap Squirrel Onesie 10% OFF
ReplyDeletemake your point known to millions of people
ReplyDeleteentertainmentbee.com
themoviesbio.com
petrefine.com
thepetsabout.com
happylifestyletrends.com
People and businesses have grown from being virtual unknowns to worldwide phenoms
ReplyDeletefrasesparaenamorarz.com
fras-es.com
punaisesdelit.org
groupe-sanguine.fr
meilleur-gps.fr