Search code examples
reporting-servicesdynamics-crm

Get the first value of result of LookupSet if not null in SSRS for Dynamic 365

I'm writing a report for Microsoft Dynamics 365 Version 1612 (8.2.2.113) which uses the following expression:

=IIF(LookupSet("Test", Fields!new_product_name.Value, Fields!new_product_code.Value, "Test_DataSet").Length = 0,
     "",
     LookupSet("Test", Fields!new_product_name.Value, Fields!new_product_code.Value, "Test_DataSet")(0)
 )

The idea is to return the code of first product with name as "Test", and return blank if there is no match.

However, because IIF in SSRS does not short-circuited, even if there is no match, it will still try to get the first (index = 0) element from LookupSet and then throw an error.

How can I archive what I'm trying to do?

Solution

  • If all you need is the first result, then you should be able to just use Lookup. Have you tried that?

    Lookup returns the first result if there are any results, or Nothing if there are no results.