SSRS Report Builder Part 7.12 - Lookup, LookupSet and MultiLookup Functions

By Andrew Gould

Chapters
00:00 Topic List
01:04 The Basic Report
03:22 Creating a Lookup Table Dataset
05:01 The Lookup Function
08:58 Converting Data Types
10:38 Looking Up Director Names
13:34 Checking if a Value was Returned
16:02 Returning Multiple Values
17:24 The LookupSet Function
19:43 Joining the Contents of an Array
22:08 Testing the Length of an Array
25:27 Looking up Multiple Values
26:55 The MultiLookup Function

[isumyfine]

I really appreciate your videos, you have saved my days multiple time with your SSRS series.

[abcxyz]

THANK YOU! was getting annoyed look upset didnt work, you showed me it was missing join. Your videos are super helpful. Much appreciated!

[CozBlueOcean]

Very easy to understand and follow. Great video, thank you so much!

[taitu]

Thank you for this video. I find it very intuitive and clear.

[henriquedosprazeresmarceli]

Thank you very much for the explanation! Extremely didactic!!

[ItsForUsio]

excellent video. Thank you for your work

[Mike]

Great videos, have been making use of them quite a bit recently! On the final example, is there a way to sort the genres returned alphabetically?

[jordanshefman]

Thanks so much for the great video! For the MultiLookup function, is there a way to have an input array that is delimited by a character other than a comma; maybe by spaces or semicolons?

[borisgarkun]

There is a row group in .rdl file(paginated report) which calculates some aggregate function for a certain period. So grouping is performed by this period. Dataset also returns data for the same period but 1 year ago.

How can I add some column to the right side of the report which will perform the same aggregate function but for the same period 1 year ago? Which function should I use for it? So I should see in the row for this month(period) the aggregate value of this month and the month 1 year ago. Is there CTE functions in report builder?

[vahdettiryakiogluotomotiva]

Hi, "director ID" is 3 different tables, how can we combine them in one line?

[TheIanhackett]

When I do the lookup it only returns the first match. How do i get it to lookup all rows

[haniomar]

Thank you so much. what if I need to lookupset multiple decimal or currency values and sum them in one value?

[superfreiheit]

What is the difference between Lookupset and Multilookup?

[ChandrasekharChandu-jn]

How to join two shared datasets in SSRS report?

[johan]

Thank you! How do you know the reason behind an "error"? It says nothing..

[programit]

should names match or the content of the fields only 8:09 Lookup()?

[LHBCPF]

how to use wildcard in case I'm looking for non exact matches, like prefixes should match only?

[sos]

i have been stuck tring to use lookup with more than 1 key ex:

& Fields!ldv_calendarid.Value,
Fields!ldv_accountid.Value & Fields!ldv_calendarid.Value,
Fields!brandNum.Value, "DataSetBrandNumber")

thats not working not with & not with + can you help me please?

[foxybark]

HOw does this look to you? I wrapped both keys in a string function just to be safe. But I'm still getting an error.

=Lookup(
Str(Fields!PH_Location_Code.Value),

Str(Fields!Location_Code.Value),
Fields!ID2021_YearTotal_NetIncome.Value,
"Excel_DS")

[PraveenKumar-ldbm]

Can we use an aggregate function inside the lookup function?