Skip navigation
mrmichael1981
Currently Being Moderated

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

Aug 16, 2010 7:07 AM

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(replac e(replace(replace(replace(replace(

 

 

replace(replace(replace(replace(replace(replace(replace(replace(replac e(replace(replace(replace(replace

 

 

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

 

 

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

 

 

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

 

 

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

 

 

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

 

 

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

 

 

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(replace(replace(replace(replace

 

 

(replace(replace(replace(replace(replace(replace(replace(replace(repla ce(

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/>

 
Replies

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points