1 person found this helpful
For this you don't need to create a derived column - it would put unnecessary strain on the BI server. Better do it in the physical mapping of the logical column and push it to the database.
Here's an example whol will work on an SA406 RPD:
when "01 - Sample App Data (ORCL)".."BISAMPLE"."D30 Offices"."Department" = 'Equipment Dept.' then 'EQ'
when "01 - Sample App Data (ORCL)".."BISAMPLE"."D30 Offices"."Department" = 'Operations Dept.' then 'OP'
else 'Something' end
So let's forget the "Content" tab and the WHERE clause field (that's something else and you don't need it here).
If you open the properties of logical column, go in the "Column source" tab, you are supposed to have the "Derived from physical mappings" already enabled (because your column come from the DB).
There you click the source LTS and click on "Edit".
You now have in front of you all the columns mapping from the Logical Table source.
Select your column "University Code" and click on the "fx" icon (top right of the window).
There you can put your CASE WHEN.
If you are not happy with the result (no data or no look up), have a look at the generated physical SQL (your CASE WHEN is inside) and find out where is the issue (maybe your column in the DB has spaces before or after codes and things like that).
That was one of the two options I had tried earlier, but I must have forgotten to save my changes before uploading the RPD. When I tried again, it worked. Thanks to both of you.