IIf statement to combine 2 date fields in report builder

Question

I have two date fields in my dataset. They are labled OperativeA and OperativeB and are populated based on who a job has been assigned to. Users will only be able to add a date to one of the fields so only 1 of the date fields will be used meaning when I run the report, the date field that is not used shows blank.

I am trying to write a calculated field so that if both date fields are blank then it shows as "unassigned" else show the operative it has been assigned to. OperativeA or OperativeB.

So far, I have created 2 calculated fieds:

=IIF(IsNothing(Fields!OperativeA.Value), "Unassigned", Fields!OperativeA.Value)

=IIF(IsNothing(Fields!OperativeB.Value), "Unassigned", Fields!OperativeB.Value)

These work but I haven't really achieved anything as I still have 2 fields!

I'm strugginling 2 combine these so that I only have 1 field that either shows "unassigned" or either one of the operatives.

Has anyone got any advice that would help me to achieve the 1 field?

Many thanks

Alan Schofield Alan Schofield · Accepted Answer · 2017-09-07T23:01:39

Personally I would do this on the server if possible (I'm assuming SQL Server here). It would be as simple as

SELECT 
    COALESCE(OperativeA, OperativeB, 'Unassigned') AS myCalcField
    , myOtherFields 
    FROM myTable

If you really have to do it in SSRS then you'll need something like

=SWITCH (
        Len(Fields!OperativeA.Value) >0 , Fields!OperativeA.Value,
        Len(Fields!OperativeB.Value) >0 , Fields!OperativeB.Value,
        True, "Unassigned"
        )

You could use a nested IIF statement to get the same result but SWITCH usually is cleaner and easier to read. SWITCH statements stop at the first expression that returns True. So we check if A contains anything, if so we return A, if not check B and finally if all else fails, the final expression True will obviously return true so the last value is returned (The last expression/value pair act as an 'else').

IIf statement to combine 2 date fields in report builder

1 Answers