Report Builder function - IIF nesting

[yomero]

Please some help with these formula.

In a query I want to consolidate the values of multiple columns into a new column

My new column = UniqueID
Existing columns:
MDN
UUID
IMSI
IMEI
ICCID

Populate UNIQUEID column logic:
- If MDN ="NA" then UUID

- If UUID = "NA" then IMSI​

- If IMSI = "NA" OR is Null then IMEI​

- If IMEI is null then ICCID
​

I have been successful at populating nesting only 2:
UniqueID: IIf([msisdn]="NA",IIf([UUID]<>"NA",[UUID],[imsi]))

 

[HiTechCoach]

If I were doing this I would use a User Defined Function (UDF) in a VBA code module.

This would also allow you to reused ti in other places as needed. Depending on how you would reused it, you could pass only the primary key for the record. You may need to pass all 5 values. The Global function would return the desired value. This also makes maintaining the logic very easy since it is only in one place.

 

[yomero]

I figured it out, posting the solution to help others with the same question.

UniqueID: IIf([msisdn]="NA",IIf([UUID]<>"NA",[UUID],IIf([imsi] Is Not Null,[imsi],IIf([iccid] Is Not Null,[iccid],[imeisv]))),[msisdn])

 

[yomero]

Thanks, I will look into it.

 

[Micron]

FYI as long as imeisv and msisdn cannot ever be null, you should be OK, otherwise I think your expression will fail. You also have to be OK with the expression returning either msisdn or imeisv if the others are null.