0

So I am using Report Builder in order to create a dashboard connecting the information between 2 lists in Sharepoint and fetching specific cases. Practically speaking, the cases in one lists are sales and the cases on the other list are tracking items (documents) for each sale. They are connected with a common ID (ID on the first list, SaleID on the tracking list) and there can be a relationship of one sale to many tracking items (at least one to one). There will be various tables on the dashboard but the concept is the same for all (meaning data misalignment).

Since the relationship is one to many, I want to compare the document status on the sales list with the document status on the last tracking item on the tracking list and then filter out all the cases that the tracking status is the same on the 2 lists.

Using the lookupset, I encounter the #Error when the array is empty.

I tried different variations like:

=iif(
(LookupSet(Fields!ID.Value, Cint(Fields!Sale_ID.Value), Fields!document_status.Value, "sales").length) >=  1
,((LookupSet(Fields!ID.Value, Cint(Fields!Sale_ID.Value), Fields!document_status.Value, "tracking"))((LookupSet(Fields!ID.Value, Cint(Fields!Sale_ID.Value), Fields!document_status.Value, "tracking")).Length - 1))
,"")

or

=IIF(
IsNothing(Lookup(Fields!ID.Value, Cint(Fields!Sale_ID.Value), Fields!document_status.Value, "tracking")),
 "",
((LookupSet(Fields!ID.Value, Cint(Fields!Sale_ID.Value), Fields!document_status.Value, "tracking"))
((LookupSet(Fields!ID.Value, Cint(Fields!Sale_ID.Value), Fields!document_status.Value, "tracking")).Length - 1))
)

There are other variations of the above too, but no matter how many things I tried, I still get the same error when the array is empty, but I want to just display blank cell in that case. Any help would be appreciated! Thanks in advance!

Dimitris P
  • 1
  • I'm not sure exactly what you are trying to return but the `True` part of your `IIF` has two values `((LookupSet(Fields!ID.Value, Cint(Fields!Sale_ID.Value), Fields!document_status.Value, "tracking"))` and `((LookupSet(Fields!ID.Value, Cint(Fields!Sale_ID.Value), Fields!document_status.Value, "tracking")).Length - 1))` which cannot be correct – Alan Schofield May 04 '22 at 12:29
  • check this answer https://stackoverflow.com/questions/52736733/error-in-ssrs-expression-for-false-condition/52739800#52739800 – RegBes May 04 '22 at 12:51
  • @AlanSchofield I am trying to return the last value of my array like here https://stackoverflow.com/questions/60192646/get-last-item-from-lookupset-in-ssrs-expression – Dimitris P May 05 '22 at 09:00
  • OK, but your `IIF` statement has two values in the "if true" part of the expression. Instead of `IIF( "lookupset is not empty", return "lookupset result", else "blank")` you have `IIF( "lookupset is not empty", return "lookupset result""lookupset result", else "blank")` – Alan Schofield May 05 '22 at 10:05

0 Answers0