Wednesday, July 22, 2015

SSRS – LookUp, MultiLookUp and LookupSet Functions

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.

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)
Parameters definitions
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-


60 comments:

  1. Very Helpful post.. Thanks Mukesh :)
    ~
    Chander

    ReplyDelete
  2. Can you write more on MultiLookup.
    ~
    Chander

    ReplyDelete
  3. Sure! I'll post some more example on Multilookup

    ReplyDelete
  4. What if I have 3 Datasets and want to use the Lookupset function on Dataset1 and Dataset3. How do I do that?

    ReplyDelete
  5. Thanks you very much for sharing these links. Will definitely check this out..
    CPA in Key West

    ReplyDelete
  6. Hi Thanks for the nice information its very useful to read your blog. We provide best Cost And Management Accountancy (CMA)

    ReplyDelete
  7. I 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

    ReplyDelete
  8. Thanks for sharing this post.It is very informative and helpful.

    Sydney Brooke Simpson
    Jimmy Graham
    Bhgwalmartoffer

    ReplyDelete
  9. Order Methadone overnight delivery in USA

    Methadone changes the way your brain and nervous system respond to pain so that you feel relief. Its effects are slower than those of other strong painkillers like morphine. Your doctor may prescribe methadone if you’re in a lot of pain from an injury, surgery, or long-term illness.

    Visit - Tramadol100mg.org

    Order - Methadone Overnight delivery in USA

    ReplyDelete
  10. “Interesting  information, thanks for making these contributions.
    Great  information Glad to find your article.!This blog is really very informative.
    Thanks for sharing it with us
    bangalore escorts service 
     bangalore call girls 
    bangalore russian escorts 
    bangalore cheap escorts 
    bangalore escorts 

    ReplyDelete
  11. Thank you for the good writeup. It in fact was a amusement account it. Look advanced to 먹튀사이트

    ReplyDelete
  12. Thank you for some other informative blog. Where else could I get that type of information written in such an ideal means? I have a mission that I’m just now working on, and I have been at the look out for such information. https://makingwayforpeople.wordpress.com/2021/06/21/why-do-business-in-cambodia-9-reasons-for-it/

    ReplyDelete
  13. Hi, This is a nice article you shared great information I have read it thanks for giving such a wonderful Blog for the reader. website management

    ReplyDelete
  14. Its as if you had a great grasp on the subject matter, but you forgot to include your readers. Perhaps you should think about this from more than one angle. Prime Cuppa

    ReplyDelete

  15. Hello
    Please i just took up LABRADOR PUPPIES breeding as a hobby after my mom passed away because they were her favorite PUPPIES. Despite the fact that they are very intelligent, am finding it very difficult getting them to mate.
    For any information CLICK HERE LABRADOR PUPPIES FOR SALE. THANKS

    ReplyDelete

  16. Hello
    Please i just took up macaw breeding as a hobby after my mom passed away because they were her favorite birds. Despite the fact that they are very intelligent, am finding it very difficult getting them to mate.
    For any information CLICK HERE http://supremeparrotfarm.company.com/ we shall get back to you. contact-about-us. THANKS

    ReplyDelete
  17. Welcome to the party of my life here you will learn everything about me. tafsir ahlam

    ReplyDelete
  18. Easily, the article is actually the best topic on this registry related issue. I fit in with your conclusions and will eagerly look forward to your next updates. business finder

    ReplyDelete
  19. I visit your blog regularly and recommend it to all of those who wanted to enhance their knowledge with ease. The style of writing is excellent and also the content is top-notch. Thanks for that shrewdness you provide the readers! IT support company

    ReplyDelete
  20. Ucuz, kaliteli ve organik sosyal medya hizmetleri satın almak için Ravje Medyayı tercih edebilir ve sosyal medya hesaplarını hızla büyütebilirsin. Ravje Medya ile sosyal medya hesaplarını organik ve gerçek kişiler ile geliştirebilir, kişisel ya da ticari hesapların için Ravje Medyayı tercih edebilirsin. Ravje Medya internet sitesine giriş yapmak için hemen tıkla: www.ravje.com

    İnstagram takipçi satın almak için Ravje Medya hizmetlerini tercih edebilir, güvenilir ve gerçek takipçilere Ravje Medya ile ulaşabilirsin. İnstagram takipçi satın almak artık Ravje Medya ile oldukça güvenilir. Hemen instagram takipçi satın almak için Ravje Medyanın ilgili sayfasını ziyaret et: instagram takipçi satın al

    Tiktok takipçi satın al istiyorsan tercihini Ravje Medya yap! Ravje Medya uzman kadrosu ve profesyonel ekibi ile sizlere Tiktok takipçi satın alma hizmetide sunmaktadır. Tiktok takipçi satın almak için hemen tıkla: tiktok takipçi satın al

    İnstagram beğeni satın almak için Ravje medya instagram beğeni satın al sayfasına giriş yap, hızlı ve kaliteli instagram beğeni satın al: instagram beğeni satın al

    Youtube izlenme satın al sayfası ile hemen youtube izlenme satın al! Ravje medya kalitesi ile hemen youtube izlenme satın almak için tıklayın: youtube izlenme satın al

    Twitter takipçi satın almak istiyorsan Ravje medya twitter takipçi satın al sayfasına tıkla, Ravje medya güvencesi ile organik twitter takipçi satın al: twitter takipçi satın al

    ReplyDelete
  21. This is also a very good post which I really
    enjoyed reading. It is not every day that I have the possibility to see
    something like this.. Anime Characters

    ReplyDelete
  22. This is also a very good post which I really
    enjoyed reading. It is not every day that I have the possibility to see
    something like this.. Khwab Ki Tabeer

    ReplyDelete
  23. This is very appealing, however , it is very important that will mouse click on the connection: backlinks

    ReplyDelete
  24. I was impressed by your writing. Your writing is impressive. I want to write like you.안전놀이터 I hope you can read my post and let me know what to modify. My writing is in I would like you to visit my blog.

    ReplyDelete
  25. Pretty useful article. I merely stumbled upon your internet site and wanted to say that I’ve very favored learning your weblog posts. Any signifies I’ll be subscribing with your feed and I hope you publish once additional soon. 메이저사이트

    ReplyDelete
  26. What a nice post! I'm so happy to read this. 안전놀이터모음 What you wrote was very helpful to me. Thank you. Actually, I run a site similar to you. If you have time, could you visit my site? Please leave your comments after reading what I wrote. If you do so, I will actively reflect your opinion. I think it will be a great help to run my site. Have a good day.

    ReplyDelete
  27. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!. Hire a Copywriter

    ReplyDelete
  28. Conversely, a significant new mall improvement might lessen the allure of existing serene, private properties. savoy little tokyo

    ReplyDelete
  29. You know your projects stand out of the herd. There is something special about them. It seems to me all of them are really brilliant! sweet business name ideas

    ReplyDelete
  30. I also wrote an article on a similar subject will find it at write what you think. niche

    ReplyDelete
  31. Also, when you think about making improvements, remember the worth of different homes nearby. ix tools

    ReplyDelete
  32. You should mainly superior together with well-performing material, which means that see it: backlinks

    ReplyDelete
  33. Erstellen Sie Ihre eigenen Filmen mit Windows Movie Maker. Mit dem Programm werden Ihre Fotos und Videos zum flüssigen und wunderschönen Film gemacht. Mit Hilfe von speziellen Effekten, Übergänge, Ton, Untertitel und so weiter können Sie Ihre Geschichte vollständig zum Ausdruck bringen. Kostenloser Download von Windows Movie Maker 2021

    ReplyDelete
  34. How are Sphynx cats with other pets?
    This is hard to answer. Generally, as a breed and given how affectionate they are I would say you have a better chance of a Sphynx cat getting along with other pets than a different breed of cat. But then each one is different and may or may not like any given pet or person. https://www.royaltykitten.com/
    hairless cat for sale
    They definitely do not like to be alone, that was one of the reasons we got two. So it is always good for them to have a buddy if you are going to be away at work all day.
    https://www.chihuahuapuppiesforsale1.com/

    ReplyDelete
  35. Totally loved your article. Looking forward to see more more from you. sphynx cat for sale Meanwhile feel free to surf through my website while i give your blog a read. chihuahua puppies for sale near me Are you also lokking to adopt chihuahua puppies sphynx kitten for sale, teacup chihuahua for sale

    ReplyDelete

  36. Usually I never comment on article but your article is so convincing that I never stop myself to Real Leather. chihuahua puppy for sale near me best chihuahua puppies for sale hairless cats for sale Despite the fact that they are very intelligent, am finding it very difficult getting them to mate chihuahua for sale
    chihuahua for sale near me


    ReplyDelete
  37. I absolutely agree with the above. In addition, I would encourage using a service like sphynx kittens for sale, chihuahua puppy for sale breeding as a hobby after my mom passed away because they were her favorite pets. chihuahua puppies for sale

    ReplyDelete
  38. How are Sphynx cats with other pets?
    This is hard to answer. Generally, as a breed and given how affectionate they are I would say you have a better chance of a Sphynx cat getting along with other pets than a different breed of cat. But then each one is different and may or may not like any given pet or person. https://www.royaltykitten.com/
    sphynx cats for sale
    They definitely do not like to be alone, that was one of the reasons we got two. So it is always good for them to have a buddy if you are going to be away at work all day.
    https://www.chihuahuapuppiesforsale1.com/
    Sphynx kitten for sale

    ReplyDelete
  39. I absolutely agree with the above. In addition, I would encourage using a service like chihuahua puppies for sale, teacup chihuahua for sale, sphynx cats for sale , sphynx kitten for sale

    ReplyDelete
  40. I found Hubwit as a transparent s ite, a social hub which is a conglomerate of Buyers and Sellers who are ready to offer online digital consultancy at decent cost. Microsoft365

    ReplyDelete
  41. Business landlines are soon becoming a thing of the past. But are small businesses really willing to put them into oblivion and welcome VOIP technology and business mobile phones for their small businesses? Black Hair Colour

    ReplyDelete
  42. I have bookmarked your website because this site contains valuable information in it. I am really happy with articles quality and presentation. Thanks a lot for keeping great stuff. I am very much thankful for this site. 알파벳 토토

    ReplyDelete
  43. thank you this good artivle in my reading journey tafsir ahlam

    ReplyDelete

Popular Posts