Hi All,

I am using BI Publisher to generate my reports but I stumbled on a big problem...

I have a table with a column TASK_DESCRIPTION. This column is a CLOB containing a simple HTML coded page. When a user works with the application he sees a nicely formatted page but when I generate a report I get all the html tags in it (< html >

...etc...)

Is it possible to somehow convert the source data from the table (which is html code) into readable formatted text when generating the report?

ANY ideas are very welcome!

Regards,
Pawel.

Any luck with this? Let me know as i have a similar problem.

Thanks

Yes you can do this with an XSL template converting HTML to Formatting objects.
There are a few catches however. First, you must be sure your markup-fragment is valid XHTML, that is all tags must be closed. Luckily the builtin standard HTML-editor does this.
I wasn't able to call BI-publisher from within Apex without the XHTML-fragment being escaped, but i managed to create a query on a table containing the fragment within BI-publisher itself. This leaves the fragment intact, producing a valid XML-document.
This is very important because the XSL attached to the RTF-template must be able to match and convert the XHTML-tags to FO.

Basic setup :

Create a datasource (query) within BIP.
Create the RTF-template and add an extra field (at the top) containing : <?import:file:///C:\xhtml-to-xslfo.xsl?> or whatever location the XSL-file resides.
Now, layout your report using the wizard and change the field containing the XHTML-fragment into <xsl:apply-templates select="TASK_DESRIPTION"/>

There are a few XSL-templates out there converting XHTML to FO. One I found particularly useful : http://www.ibm.com/developerworks/library/x-xslfo2app/xhtml-to-xslfo.xsl
You might want to change the .xsl so it matches tags case insensitive (eg. <xsl:template match="a|A"> )

Another caveat is the html-entities for special characters. When the XML from the datasource is parsed by BIP it doesn't recognise entities like &Agrave ; .
This can be solved by creating a pl/sql function converting these characters to numeric entities like &#192 ;. and use this function in the select statement of your datasource. see http://www.w3schools.com/tags/ref_entities.asp

Now you can call the report from Apex using an URL to the report definiton (see BIP docs for URL syntax and parameter passing)
Not a 'really' integrated solution but I had some nice results.

It would be nice though if we could specify a .dtd containing the references for HTML-entities and a sort of flag wheter the report column should be escaped or not, from within Apex. This would allow us to embed XML-fragments within the XML that Apex produces, resulting in a valid XML doc. Using XSL, you can then match the tags of the XML-fragment and convert it to FO.

Good luck !

Maarten

Meenu,

As I mentioned, this won't work directly integrated with Apex. Can you make your report work within BIP only ?
You might want to check the errorlog on the BIP-server on what caused your report to fail.

Maarten

Hi Meenu,

Let's take this one step at a time. Are you able to create a report using the BI-publisher interface (and desktop tools) ? see http://www.oracle.com/technology/oramag/oracle/07-jul/o47bi.html for a nice tutorial.
In the model-section you have to create a new dataset, a query containing the clob with the XHTML-fragment.
You can test the dataset bij "viewing" you report : choose "data" as the format and your dataset will be shown as XML (this XML should contain the fragment). If this fails you most likely have a non-valid XHTML-fragment in your clob.

If this works for you we'll have a look at the report template. Have fun...

Maarten

Ok, now create the RTF-template (use the export from the 'data'-view as the sample XML) using the Desktop tools. After that, upload the RTF-template to BIP and see if you can view your report in PDF-format. Leave out the XSL-import etc. I described earlier to see if this works In your previous attempt, did you by any chance import the XSL from a local directory : eg. file:///C:\xhtml-to-xslfo.xsl ?

Maarten

Meenu,

You have to place your XSL in a directory on the server running BIP (Ask your sysadmin). You must reference the file in the ímport-field That's why I asked you to leave out the import stuff to determine the problem. Guess we found it !

> 2. has anyone tried using text area with HTML editor and tried using BIP to display that data?
Guess you're making it happen right now !

>3. Does anybody has a XSL that does all the conversions that the HTML Editor generates?
See my first post with a link to the xhtml-to-xslfo.xsl. This one covers most html-tags.