Multiple Datasets with Like or Contains Keyword

Question

I'm trying to filter an expression using Dataset Value along with Like or Contains Statement. I have 7 Datasets(Outline_info, Product_info, Materials_Info, Wiring_info, etc) and each dataset has handful Calculated Fields. For example., "Product_info" dataset has fields like productID, qty, unitofmeasure etc., which are getting populated through stored procedures

I have added the following value in the textbox on the report:

=IIF((Fields!productID.Value,"Product_Info") = "J*", ("Jacket: " & First(Fields!productID.Value,"Product_Info")), ("Tops: " & First(Fields!productID.Value,"Product_Info")))

In the above expression, I'm checking, if the productID field starts with "J" i.e., ((Fields!productID.Value,"Product_Info") = "J*"), then I want the Textbox starts with "Jacket: ProductID Value" i.e., ("Jacket: " & First(Fields!productID.Value,"Product_Info")), otherwise "Tops: ProductID Value" i.e., ("Tops: " & First(Fields!productID.Value,"Product_Info")).

But I'm getting the following Errors while trying to process the report: the value expression for the text box "ProductID" refers to the field "ProductID" without specifying a dataset aggregate. when the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.

Alan Schofield Alan Schofield · Accepted Answer · 2018-08-01T21:14:17

You can't use a wildcard with "=" so you will have to use the LEFT function like this.

=IIF(
    LEFT((Fields!productID.Value,"Product_Info"),1) = "J"
        , "Jacket: " & First(Fields!productID.Value,"Product_Info")
        , "Tops: " & First(Fields!productID.Value,"Product_Info")
)

Also make sure that the fields names are spelled correctly, they are case-sensitive.

Multiple Datasets with Like or Contains Keyword

1 Answers