I need an SSRS expression to check if a value in one column exists anywhere in another column

Question

I have an employee database, which includes a "Names" column, and a managers column ("Mngr"). I need an SSRS expression to return True (or "Y") if a value in "Names" exists anywhere in "Mngr". This will show if a particular person is a manager.

I have tried
=IIF(InStr(Fields!Mngr.Value, Fields!Name.Value) > 0, "True", "False")
&
=IIF(InStr(Fields!Name.Value, Fields!Mngr.Value) > 0, "True", "False")
but neither work. Both return False, where it should be True (I swapped the cols around as I couldn't quite understand the syntax of the expression)

After researching, I have a 2nd DataSet that just holds managers ("Mngrs"), and tried this
=IIF(Not IsNothing(LookupSet(Fields!Name.Value, Fields!Mngr.Value, Fields!Mngrs.Value, "DataSet2").Length > 0, True, False)
which wouldn't even run.

So I'm a bit stuck. I'm sure there must be an expression that can do this - so any help would be appreciated.

Answer 1

You don't need a second dataset to do this, you need to use LOOKUP() but across your entire dataset, the first expressions you showed will only take into account the the rows that are in context, which will probably be just the row where the expression sits.

try something like this

=IIF(
    LOOKUP(Fields!Names.Value, Fields!Mngr.Value, Fields!Mngr.Value, "DataSet1") Is Nothing
    , "N"
    , "Y"
    )

This just looks for the Names field in the Mngr field and returns the Mngr field if it finds a match, across the whole of "DataSet1". If Nothing` is returned from the lookup, the expression returns "N" otherwise "Y".

As a quick test a generated some dummy data and used this as my dataset query

DECLARE @t TABLE (Names varchar(20), Mngr varchar(20))
INSERT INTO @t VALUES 
('Dave', 'Bob'), ('Mary', 'Bob'), ('Bob', 'John'), ('John', NULL), ('Anna', 'John')
SELECT * FROM @t

Then I just added a table and set the 3rd column to the LOOKUP() expression above

This gives the following result.