Thanks Mike! The easiest way would be to add an Order By clause to the query which populates the Genres dataset. Otherwise I think you're looking at a custom code solution like the one shown here stackoverflow.com/questions/60747732/how-to-sort-values-in-a-join-function-with-lookupset-in-ssrs I hope it helps!
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?
Hi Jordan! You can use the Split function to return an array from any type of delimited list. See the examples here for how to nest Split in the Multilookup function docs.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-multilookup-function?view=sql-server-ver16 I hope it helps!
Hi! You'll need to use a custom function to do this, you can see help here stackoverflow.com/questions/52208203/ssrs-how-to-sum-values-on-a-lookupset-expression I hope it helps!
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?
Hi Praveen! Yes you can nest an aggregate function inside a lookup function. Here's an example which returns a description based on the sum of Oscar wins: =Lookup( Sum(Fields!OscarWins.Value), Fields!Wins.Value, Fields!Description.Value, "LookupTable") I hope it helps!
Hi Johan! There isn't a way to do this in Report Builder unfortunately. Microsoft's suggestion is to use SQL Server Data Tools in Visual Studio to see more detailed error information docs.microsoft.com/en-us/sql/reporting-services/report-design/expressions-report-builder-and-ssrs?view=sql-server-ver15#Valid
Hi! You can't use wildcards but you can use functions to control which part of the text you're attempting to match. The example below matches the first three characters of the FullName fields between the Actor and Director datasets: =Lookup( Left(Fields!FullName.Value, 3), Left(Fields!FullName.Value, 3), Fields!FullName.Value, "Director" ) I hope it helps!
@@WiseOwlTutorials thanks! I was actually able to make it work yesterday following this same approach with the Left function, I had some doubts because it kept underlying the field in red in the editor but it actually works. Thanks for your response!
Hi Rajat! Do you mean database tables in your query or report item tables? Are you using query parameters or report parameters? We have many videos on using parameters in Report Builder starting at Part 10.1 in this playlist which might help you find the answer you need th-cam.com/play/PLNIs-AWhQzcmEFHyxCRwA_gb29WOz5SJU.html I hope that helps!
@@WiseOwlTutorials thanks for your reply 🙏.. actually from same database i have to fetch three tables in same report which should be filtered by fourth table column’s parameter
i have been stuck tring to use lookup with more than 1 key ex: Lookup(Fields!ldv_accountid.Value & 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?
Hi Sara! The syntax of your formula looks correct. When you say that it's not working what exactly happens? Do you see an error message when you run the report?
@@sos4790 Hi Sara, I'm sorry but I don't have enough information to help. All I can suggest is checking the names of your fields and datasets carefully. Try going back a few steps and check that you return the expected result from a simple formula like =Fields!ldv_accountid.Value & Fields!ldv_calendarid.Value Check that each of the smaller expressions returns the expected results before attempting to piece it all together. I hope that helps!
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")
@@WiseOwlTutorials Great observation. I double checked and no duplicates. only 1 record per location code. Any other suggestions? I know you can only say so much without seeing the actual report. Anything helps.
@@foxybark I don't have any other good suggestions I'm afraid but it seems to be a popular question on Stackoverflow, perhaps something here is useful? stackoverflow.com/search?q=ssrs+lookup+%23error&s=660fd4c0-349f-4691-be4b-73b0bba5221a
Hello, Nice work and well done for this tutorial. However, is it possible to have a row by result (multiples values part) For your exemple : ------------------------------------------------------------------ 20th Century Fox | 4 | Alien Convenant ------------------------------------------------------------------ 20th Century Fox | 4 | Deadpool 2 ------------------------------------------------------------------ And if yes, how ? Thank you for your help
Hi! I don't think that the multiple values part of the video helps here. As far as I know, you can't create new detail rows using an SSRS expression. In the Film - Studio example we can easily create a separate row for each film by populating the table with the Title field from the Films dataset, then use the Lookup function to return the corresponding studio name.
I really appreciate your videos, you have saved my days multiple time with your SSRS series.
Thanks Isumy! Happy to hear that you've found the videos useful, thank you for taking the time to leave a comment!
This solved my issue perfectly...thank you.
Happy to hear that it was useful, thanks for watching and taking the time to comment!
THANK YOU! was getting annoyed look upset didnt work, you showed me it was missing join. Your videos are super helpful. Much appreciated!
You're very welcome, happy to hear that the video helped! Thank you for watching and for taking the time to leave a comment!
Very easy to understand and follow. Great video, thank you so much!
Happy to hear that you enjoyed it, thank you for watching and for taking the time to write a comment!
Thank you for this video. I find it very intuitive and clear.
I'm happy that you liked it, thank you!
excellent video. Thank you for your work
Thank you for the kind comment and for watching!
Thank you very much for the explanation! Extremely didactic!!
You're very welcome, thanks for watching!
Great tutorial thank you!
You're very welcome, thanks for watching!
excellent Video
Thanks very much for watching!
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?
Thanks Mike! The easiest way would be to add an Order By clause to the query which populates the Genres dataset. Otherwise I think you're looking at a custom code solution like the one shown here stackoverflow.com/questions/60747732/how-to-sort-values-in-a-join-function-with-lookupset-in-ssrs
I hope it helps!
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?
Hi Jordan! You can use the Split function to return an array from any type of delimited list.
See the examples here for how to nest Split in the Multilookup function docs.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-multilookup-function?view=sql-server-ver16
I hope it helps!
should names match or the content of the fields only 8:09 Lookup()?
The field names do not need to match.
Thank you so much. what if I need to lookupset multiple decimal or currency values and sum them in one value?
Hi! You'll need to use a custom function to do this, you can see help here stackoverflow.com/questions/52208203/ssrs-how-to-sum-values-on-a-lookupset-expression
I hope it helps!
How to join two shared datasets in SSRS report?
When I do the lookup it only returns the first match. How do i get it to lookup all rows
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?
Hi! You might find videos 9.9 and 9.10 in this playlist useful th-cam.com/play/PLNIs-AWhQzcmEFHyxCRwA_gb29WOz5SJU.html&feature=shared
I hope it helps!
Yes, that helped me
What is the difference between Lookupset and Multilookup?
Can we use an aggregate function inside the lookup function?
Hi Praveen! Yes you can nest an aggregate function inside a lookup function. Here's an example which returns a description based on the sum of Oscar wins:
=Lookup(
Sum(Fields!OscarWins.Value),
Fields!Wins.Value,
Fields!Description.Value,
"LookupTable")
I hope it helps!
@@WiseOwlTutorials Thank you but I want to return the sum of an X value based on other matching values.
Hi, "director ID" is 3 different tables, how can we combine them in one line?
Thank you! How do you know the reason behind an "error"? It says nothing..
Hi Johan! There isn't a way to do this in Report Builder unfortunately. Microsoft's suggestion is to use SQL Server Data Tools in Visual Studio to see more detailed error information docs.microsoft.com/en-us/sql/reporting-services/report-design/expressions-report-builder-and-ssrs?view=sql-server-ver15#Valid
how to use wildcard in case I'm looking for non exact matches, like prefixes should match only?
Hi! You can't use wildcards but you can use functions to control which part of the text you're attempting to match. The example below matches the first three characters of the FullName fields between the Actor and Director datasets:
=Lookup(
Left(Fields!FullName.Value, 3),
Left(Fields!FullName.Value, 3),
Fields!FullName.Value,
"Director"
)
I hope it helps!
@@WiseOwlTutorials thanks! I was actually able to make it work yesterday following this same approach with the Left function, I had some doubts because it kept underlying the field in red in the editor but it actually works. Thanks for your response!
@@LHBCPF Excellent, happy to hear you got it working!
How can i apply one parameter to more thn 2 tables so it will show filtered data in same report?
Hi Rajat! Do you mean database tables in your query or report item tables? Are you using query parameters or report parameters?
We have many videos on using parameters in Report Builder starting at Part 10.1 in this playlist which might help you find the answer you need th-cam.com/play/PLNIs-AWhQzcmEFHyxCRwA_gb29WOz5SJU.html
I hope that helps!
@@WiseOwlTutorials thanks for your reply 🙏.. actually from same database i have to fetch three tables in same report which should be filtered by fourth table column’s parameter
@@Leelamay Hi Rajat! You can use the same report parameter in a filter on each of the three tables that you want to filter. I hope that helps!
@@WiseOwlTutorials thank you
i have been stuck tring to use lookup with more than 1 key ex:
Lookup(Fields!ldv_accountid.Value & 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?
Hi Sara! The syntax of your formula looks correct. When you say that it's not working what exactly happens? Do you see an error message when you run the report?
@@WiseOwlTutorials not sytax error the report runs but the column with the exp give me #error
@@sos4790 Hi Sara, I'm sorry but I don't have enough information to help. All I can suggest is checking the names of your fields and datasets carefully. Try going back a few steps and check that you return the expected result from a simple formula like =Fields!ldv_accountid.Value & Fields!ldv_calendarid.Value
Check that each of the smaller expressions returns the expected results before attempting to piece it all together.
I hope that helps!
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")
It looks good to me. Did the error message provide any details?
@@WiseOwlTutorials The report runs fine. Only the column with the above expression has '#error' for all rows.
@@foxybark is it possible that your lookup table contains multiple matches for the lookup value?
@@WiseOwlTutorials Great observation. I double checked and no duplicates. only 1 record per location code. Any other suggestions? I know you can only say so much without seeing the actual report. Anything helps.
@@foxybark I don't have any other good suggestions I'm afraid but it seems to be a popular question on Stackoverflow, perhaps something here is useful? stackoverflow.com/search?q=ssrs+lookup+%23error&s=660fd4c0-349f-4691-be4b-73b0bba5221a
Hello,
Nice work and well done for this tutorial.
However, is it possible to have a row by result (multiples values part)
For your exemple :
------------------------------------------------------------------
20th Century Fox | 4 | Alien Convenant
------------------------------------------------------------------
20th Century Fox | 4 | Deadpool 2
------------------------------------------------------------------
And if yes, how ?
Thank you for your help
Hi! I don't think that the multiple values part of the video helps here. As far as I know, you can't create new detail rows using an SSRS expression. In the Film - Studio example we can easily create a separate row for each film by populating the table with the Title field from the Films dataset, then use the Lookup function to return the corresponding studio name.