I have a report definition fetching certain columns of data table. For one of the column, the requirement is to replace the value to another value on display. Example if Value = 4, display as ABC and if Value = 5 , display as XYZ.
How do I configure the Replace function in Query? It works for just one replacement but doesnot handle two value replacement. Attaching the screenshot.
I don't believe you can pass a list of values and replacement values to a function like this. It will end up looking for the string "4;5" to replace with string "ABC;XYZ".
If these are the only 2 replacements you are doing, I believe you can nest 2 versions of the Replace function, one inside the other. One will be defined to do the "4" to "ABC" replacement, the other will do the "5" to "XYZ" replacement.
Presumably only one of the original values (4 or 5) will be in the property, so only one of the functions will actually change the string. The other will be a no-op.
If you are doing a large number of replacements, you will need to define your own SQL function alias, and code the SQL fragment to do all the replacements.
Create a new Function Alias and use that in your Report Definition.While creating Function alias you might search for any existing SQL function Alias and Save as from records explorer. You have to then write your own SQL code there.