Let's Reconnect, thank you for making 2022 our best year yet.

Look Back
OTBI formula needed for multiple IF THEN — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

OTBI formula needed for multiple IF THEN

Received Response


I have a simple report that uses the subject area Workforce Management - Worker Assignment Real Time. The report contains Person Name, Job Code, Job Name, Department, etc.. I need to add a column with a formula that does this example:

If job code is in 3527,3115,3267,3992,3592,700,5361, THEN "Category Physician",

ELSE if job code is in 700,6937,4207,489,9489,530,3316,5400,4300,3315,623,8623,621,8621,173,1172,1173,4243,9173 THEN "Category Registered Nurse",

ELSE if job code is in 43,9173,9175,9174,9176,3774,541,4707,4101,3892,4092,4093,711,1709,1710,5251,6251,8251,7158,7007,7013,3255,101,189,6252,5252,8252,3937,3986,3938,3987,3939,3985,728,3877,997,3082,3100,3148,9148,9149, THEN "Category Lab Technician"

ELSE if job code is in 5250,7173,3580,158,8158,9158,569,5253,7569,3582,8569,9569,970,3069,3443,3419,3143,712,3442,3578,9581,9582,531,1530,1531,9531,9530,9532,1167,4182,1712,3673,3918,4918,999,5369,8371,3581,4581,4582,4115,4117,4118,4119,4116,3983,4191,3001,2998,998,9998,9001,3002,9002,3099,2099,5254,4227,5414,3977,543,3109,896,1896,1897,1898,5265,5269,5270,3297,3298,5209,3009,5365,161,9161,1161,9162,5208,5207,1152,9153,5206,694,3618,341,9341,186,191,5342 THEN "Category LPN"

ELSE "Category All Other Personnel".

Each category could have anywhere from 3 job codes up to 300 job codes. Is a formula like this even possible? If not, are there any other suggestions for how this can be accomplished. Thanks


Howdy, Stranger!

Log In

To view full details, sign in.


Don't have an account? Click here to get started!