Apex substitution strings in reports, not always replaced with their values, guess why?

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

It's amazing to you find out how easy is to take for granted certain features of Apex for the simple fact that you have been using them for a long time without paying too much attention to certain specific details.

This morning I ran a report that I rarely use in an application and to my surprise there were some missing icons. How can it be, did I forgot to copy over the images during the last upgrade?
Did I miss some flag or inadvertently change some report attribute?
Did something change in Apex 4.2 that I was not aware of?

I quickly assessed not only that the missing icons were not missing at all, but for some reason the #IMAGE_PREFIX# substitution string was not replaced by its run-time value only in this specific report.
But why if the report has been cloned from a perfectly working one with only minor changes in the column values?

As it always happens in these cases, the first thing that comes off the top of your mind is to blame the software, no matter how improbable that could be, so I started comparing the two reports with the help of the APEX dictionary views trying to understand why one was working perfectly and the other one was failing miserably, may be some little flag makes the difference, but it turned out very quickly that Apex 4.2 wasn't the culprit here :-)

Before revealing the name of the killer, let's see how Apex deals with the substitution strings.

First of all you could define a simple classic report as follows, changing the IMG column definition from "Display text (does not save state)" to "Standard Report Column" and setting the report attribute "Strip HTML" to "No".

select htf.img('#'||'IMAGE_PREFIX#/copy.gif') img from dual

union all 

select htf.img('#IMAGE_PREFIX#/copy.gif') img from dual

The report will then display like this (I am using Firefox, on different browser it could be seen differently):

So, why does the first icon appear to be broken, while the second one displays just fine?
The reason is in that Apex report engine as part of its job is replacing #IMAGE_PREFIX# in the source SQL at some stage before executing the query. This explains why the first row is broken, it's because the string #IMAGE_PREFIX# results from the concatenation of the strings that occurs only at run time, while the second row gets its value before run-time.
Of course the same applies to queries containing functions that return values containing substitution strings at run-time or even functions returning a SQL query containing functions that return values containing substitution strings at run-time (sorry for the recursive definition, but Apex gives you also this powerful possibility).

So, what to do in these cases where the #IMAGE_PREFIX# or other substitution values are generated only at the last second?
The solution is very simple and is what I initially overlooked, but it emerged very clearly when I ran the comparison query between the attributes of the two reports, because the bad one was missing one important attribute.


By specifying #IMG# in the "HTML expression" attribute, we can indeed force Apex to perform the substitution also at run-time but be sure to have set "Strip HTML" to "No" in the report definition otherwise you won't see anything.
This powerful attribute allows for the customization of the HTML code of each individual report column and gives also the possibility of referring to the current values of other columns which can be combined with HTML tags for building snippets of HTML of arbitrary complexity.

Interestingly enough the on-line help for "HTML Expression" doesn't mention the fact that Apex will perform the evaluation of built-in substitution strings as part of the processing of this piece of data, which is a life saver if you are generating HTML on-the-fly.


Another example of how useful Apex dictionary views can be when you need to find out what's wrong between two apparently identical report definitions...