1 Reply Latest reply: Mar 19, 2012 1:44 AM by nghiact RSS

    Using html in cf report builder and escaping the & ampersand character

    mrmichael1981 Community Member

      Some of the data we collect is entered via a WYSIWYG editor.  Therefore formatting such as bold, italic etc is translated into HTML.

      The text could also contain special characters/accented characters, again these are translated into their html equivalents.

       

      In CF Report Builder, I have set the XHTML Text formatting to TRUE.  However the report won't render the text in these boxes.  From what I can see it doesn't like the '&' character (ampersand).

       

      I have read elsewhere that report builder is limited in the characters etc that it can read and that I should translate the values.  I ended up doing this in the database to try and save some time.

       

      CREATE

       

       

      OR REPLACE FUNCTION translatexhtmltoasci(var_text IN appendix3%TYPE)

       

       

      RETURN clob

      IS

      var_translated_text

       

      clob;

      BEGIN

       

      select

       

       

      replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(re place(replace(

       

       

      replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(re place(replace

       

       

      (replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(r eplace(replace

       

       

      (replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(r eplace(replace

       

       

      (replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(r eplace(replace

       

       

      (replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(r eplace(replace

       

       

      (replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(r eplace(replace

       

       

      (replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(r eplace(replace

       

       

      (replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(r eplace(replace

       

       

      (replace(replace(replace(replace(replace(replace(replace(replace(replace(

      var_text

       

      , chr(38) || 'lt;', chr(60))

       

       

      , chr(38) || 'gt;', chr(62))

       

       

      , chr(38) || 'euro;', chr(49792))

       

       

      , chr(38) || 'iexcl;', chr(49825))

       

       

      , chr(38) || 'cent;', chr(49826))

       

       

      , chr(38) || 'pound;', chr(49827))

       

       

      , chr(38) || 'curren;', chr(49828))

       

       

      , chr(38) || 'yen;', chr(49829))

       

       

      , chr(38) || 'brvbar;', chr(49830))

       

       

      , chr(38) || 'uml;', chr(49832))

       

       

      , chr(38) || 'copy;', chr(49833))

       

       

      , chr(38) || 'ordf;', chr(49834))

       

       

      , chr(38) || 'laquo;', chr(49835))

       

       

      , chr(38) || 'not;', chr(49836))

       

       

      , chr(38) || 'shy;', chr(49837))

       

       

      , chr(38) || 'reg;', chr(49838))

       

       

      , chr(38) || 'macr;', chr(49839))

       

       

      , chr(38) || 'deg;', chr(49840))

       

       

      , chr(38) || 'plusmn;', chr(49841))

       

       

      , chr(38) || 'sup2;', chr(49842))

       

       

      , chr(38) || 'sup3;', chr(49843))

       

       

      , chr(38) || 'acute;', chr(49844))

       

       

      , chr(38) || 'micro;', chr(49845))

       

       

      , chr(38) || 'para;', chr(49846))

       

       

      , chr(38) || 'middot;', chr(49847))

       

       

      , chr(38) || 'cedil;', chr(49848))

       

       

      , chr(38) || 'sup1;', chr(49849))

       

       

      , chr(38) || 'ordm;', chr(49850))

       

       

      , chr(38) || 'raquo;', chr(49851))

       

       

      , chr(38) || 'frac14;', chr(49852))

       

       

      , chr(38) || 'frac12;', chr(49853))

       

       

      , chr(38) || 'frac34;', chr(49854))

       

       

      , chr(38) || 'iquest;', chr(49855))

       

       

      , chr(38) || 'Agrave;', chr(50048))

       

       

      , chr(38) || 'Aacute;', chr(50049))

       

       

      , chr(38) || 'Acirc;', chr(50050))

       

       

      , chr(38) || 'Atilde;', chr(50051))

       

       

      , chr(38) || 'Auml;', chr(50052))

       

       

      , chr(38) || 'Aring;', chr(50053))

       

       

      , chr(38) || 'AElig;', chr(50054))

       

       

      , chr(38) || 'Ccedil;', chr(50055))

       

       

      , chr(38) || 'Egrave;', chr(50056))

       

       

      , chr(38) || 'Eacute;', chr(50057))

       

       

      , chr(38) || 'Ecirc;', chr(50058))

       

       

      , chr(38) || 'Euml;', chr(50059))

       

       

      , chr(38) || 'Igrave;', chr(50060))

       

       

      , chr(38) || 'Iacute;', chr(50061))

       

       

      , chr(38) || 'Icirc;', chr(50062))

       

       

      , chr(38) || 'Iuml;', chr(50063))

       

       

      , chr(38) || 'ETH;', chr(50064))

       

       

      , chr(38) || 'Ntilde;', chr(50065))

       

       

      , chr(38) || 'Ograve;', chr(50066))

       

       

      , chr(38) || 'Oacute;', chr(50067))

       

       

      , chr(38) || 'Ocirc;', chr(50068))

       

       

      , chr(38) || 'Otilde;', chr(50069))

       

       

      , chr(38) || 'Ouml;', chr(50070))

       

       

      , chr(38) || 'times;', chr(50071))

       

       

      , chr(38) || 'Oslash;', chr(50072))

       

       

      , chr(38) || 'Ugrave;', chr(50073))

       

       

      , chr(38) || 'Uacute;', chr(50074))

       

       

      , chr(38) || 'Ucirc;', chr(50075))

       

       

      , chr(38) || 'Uuml;', chr(50076))

       

       

      , chr(38) || 'THORN;', chr(50078))

       

       

      , chr(38) || 'szlig;', chr(50079))

       

       

      , chr(38) || 'agrave;', chr(50080))

       

       

      , chr(38) || 'aacute;', chr(50081))

       

       

      , chr(38) || 'acirc;', chr(50082))

       

       

      , chr(38) || 'atilde;', chr(50083))

       

       

      , chr(38) || 'auml;', chr(50084))

       

       

      , chr(38) || 'aring;', chr(50085))

       

       

      , chr(38) || 'aelig;', chr(50086))

       

       

      , chr(38) || 'ccedil;', chr(50087))

       

       

      , chr(38) || 'egrave;', chr(50088))

       

       

      , chr(38) || 'eacute;', chr(50089))

       

       

      , chr(38) || 'ecirc;', chr(50090))

       

       

      , chr(38) || 'euml;', chr(50091))

       

       

      , chr(38) || 'igrave;', chr(50092))

       

       

      , chr(38) || 'iacute;', chr(50093))

       

       

      , chr(38) || 'icirc;', chr(50094))

       

       

      , chr(38) || 'iuml;', chr(50095))

       

       

      , chr(38) || 'eth;', chr(50096))

       

       

      , chr(38) || 'ntilde;', chr(50097))

       

       

      , chr(38) || 'ograve;', chr(50098))

       

       

      , chr(38) || 'oacute;', chr(50099))

       

       

      , chr(38) || 'ocirc;', chr(50100))

       

       

      , chr(38) || 'otilde;', chr(50101))

       

       

      , chr(38) || 'ouml;', chr(50102))

       

       

      , chr(38) || 'divide;', chr(50103))

       

       

      , chr(38) || 'oslash;', chr(50104))

       

       

      , chr(38) || 'ugrave;', chr(50105))

       

       

      , chr(38) || 'uacute;', chr(50106))

       

       

      , chr(38) || 'ucirc;', chr(50107))

       

       

      , chr(38) || 'uuml;', chr(50108))

       

       

      , chr(38) || 'yacute;', chr(50109))

       

       

      , chr(38) || 'thorn;', chr(50110))

       

       

      , chr(38) || 'yuml;', chr(50111))

       

       

      , chr(38) || 'quot;', chr(34))

       

       

      , chr(38) || 'amp;', chr(38))

       

       

      , chr(38) || 'apos;', chr(39))

       

       

      , chr(38) || 'nbsp;', chr(32))

       

       

      , chr(38) || 'sect;', chr(49831))

       

       

      , chr(38) || 'Yacute;', chr(50077))

       

       

      , chr(38) || 'sbquo;', chr(49794))

       

       

      , chr(38) || 'fnof;', chr(49795))

       

       

      , chr(38) || 'bdquo;', chr(49796))

       

       

      , chr(38) || 'hellip;', chr(49797))

       

       

      , chr(38) || 'dagger;', chr(49798))

       

       

      , chr(38) || 'Dagger;', chr(49799))

       

       

      , chr(38) || 'circ;', chr(49800))

       

       

      , chr(38) || 'permil;', chr(49801))

       

       

      , chr(38) || 'Scaron;', chr(49802))

       

       

      , chr(38) || 'lsaquo;', chr(49803))

       

       

      , chr(38) || 'OElig;', chr(49804))

       

       

      , chr(38) || 'lsquo;', chr(49809))

       

       

      , chr(38) || 'rsquo;', chr(49810))

       

       

      , chr(38) || 'ldquo;', chr(49811))

       

       

      , chr(38) || 'rdquo;', chr(49812))

       

       

      , chr(38) || 'bull;', chr(49813))

       

       

      , chr(38) || 'ndash;', chr(49814))

       

       

      , chr(38) || 'mdash;', chr(49815))

       

       

      , chr(38) || 'tilde;', chr(49816))

       

       

      , chr(38) || 'trade;', chr(49817))

       

       

      , chr(38) || 'scaron;', chr(49818))

       

       

      , chr(38) || 'rsaquo;', chr(49819))

       

       

      , chr(38) || 'oelig;', chr(49820))

       

       

      , chr(38) || 'Yuml;', chr(49823))

      into

       

       

      var_translated_text

      from

       

       

      dual;

       

       

       

      RETURN var_translated_text;

      END

       

       

      translatexhtmltoasci;

      /

       

       

      However my question is, how can I output the ampersand character? I've tried replacing it for chr(38) but it just outputs the & and leaves the rest of the text unrendered.

       

      eg)  The output in report builder becomes something like this:

       

      ...

      E 300 L-Ascorbic acid in O.J. L 254 of 01/01/2009, p.68<p> </p><p> </p></li></p><BR/><b>Removal</b><br><p>The entries struck through in edition 98 are now

      removed.</p><BR/><b>Note</b><br><p>tests <sub>2 </sub>m<sup>2.</sup></p><p>1233456 <strong>bold </strong><u>underline </u><em>italic</em></p><ul><li>bullet

      1</li><li>bullet 2</li><li>bullet 3</li></ul><p>aeiou mù^$*)àç_è-(é</p><p>2 ù</p><p>Michael & sons.</p><BR/>