13 Replies Latest reply on Jun 12, 2007 6:10 PM by knut

    Generate PDF report with HTML data

    Padmocho-Oracle
      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 > < h4 > ...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.
        • 1. Re: Generate PDF report with HTML data
          Padmocho-Oracle
          One idea is coming to mind... maybe this could be somehow achieved with XSL templates?

          Does anyone has any examples of such thing? Please....?
          • 2. Re: Generate PDF report with HTML data
            knut
            Any luck with this? Let me know as i have a similar problem.

            Thanks
            • 3. Re: Generate PDF report with HTML data
              Dimitri Gielis
              I think this is a question for Marc Sewtz... Marc?

              I didn't try this yet. I wonder if it's this the same in Cocoon. I'm trying to catch-up with the forum again as last week I had too many other things to do. I'm planning to test this out in the next weeks as I need to demo this integration (APEX - BI PUB) in two weeks.

              If I remember good, this is the same question: Re: HTML in the data
              and
              HTML tags in Report

              This thread was spotted to be included in the weekly APEX forum wrap-up.
              (in the interesting part)

              Thanks,
              Dimitri
              -- APEX Forum wrap-up --
              @ http://dgielis.blogspot.com/search/label/apex%20forum
              • 4. Re: Generate PDF report with HTML data
                212284
                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
                • 5. Re: Generate PDF report with HTML data
                  knut
                  Hi ,

                  I installed Oracle BI publisher desktop on my machine
                  and did try the steps that Maarten has mentioned
                  above in APEX (report query, word template with the import hints, uploaded the template, set that as the report layout in the report query and tried opening the report query using the url that was mentioned there) . But when I call the report adobe is not able to open the document. I get the following error message "adobe reader could not open the file because either it is not a supported file type or the file has been damaged...

                  Can somebody please help.

                  Thanks
                  Meenu
                  • 6. Re: Generate PDF report with HTML data
                    212284
                    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
                    • 7. Re: Generate PDF report with HTML data
                      knut
                      Thanks Maarten,

                      Can you tell me what log filename and what directory i need to look for?
                      We don't have access to the servers and I need to tell my Admin what to look for.
                      So this method that you have mentioned won't directly with APEX?
                      Can you please give me the steps how i can get this to work indirectly then?
                      Sorry ! for asking but since i am stuck with the reports and am new to the whole apex/bi stuff I am desperate to get this out.

                      Thanks
                      Meenu
                      • 8. Re: Generate PDF report with HTML data
                        212284
                        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
                        • 9. Re: Generate PDF report with HTML data
                          knut
                          Hi Maarten,

                          I am able to create a report using the BI publisher interface.
                          I am able to see the data when i choose 'data' as the format and View click.
                          if i chose any other format i get an error.

                          Thanks
                          Meenu
                          • 10. Re: Generate PDF report with HTML data
                            212284
                            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
                            • 11. Re: Generate PDF report with HTML data
                              knut
                              Hi Maarten,

                              Thanks a lot for being so patient and answering my questions. I really appreciate that.

                              I created a RTF file without xsl stuff and was able to view my report in pdf /html from BI publisher without any problem.
                              I tried using the import <?import:file:///C:/Temp/htmlmarkup.xsl?> and when i preview from the word document it works fine with all my formatting.
                              Now when i upload this rtf it throws an error as i think it is not able to locate this xsl file on the server. Where should i keep the xsl on the server? Do i need to upload it to BIP or on any other folder on that machine/server.
                              eg if the link for my bipublisher is say http://testbi1@oh.us:9704/xmlpserver/servlet/report then how do i set the import file link?

                              I have a few more questions regarding this topic.
                              1. I was using old english font in one of my rtfs. but for some reason BIP is not able to convert it, eventhough we have set that in the font mapping.
                              2. has anyone tried using text area with HTML editor and tried using BIP to display that data?
                              3. Does anybody has a XSL that does all the conversions that the HTML Editor generates?

                              Thanks
                              Meenu.
                              • 12. Re: Generate PDF report with HTML data
                                212284
                                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.
                                • 13. Re: Generate PDF report with HTML data
                                  knut
                                  Hi

                                  Thanks a lot for responding.

                                  Is there any specific folder where we need to keep the xsl file? (Our admin wants to know where exactly we have to place the xsl file.) we all are new to this stuff.

                                  eg our admin has given a mapped drive where he has put the xsl file.
                                  X:\Oracle\BI1_PUBLISHER\xmlp\XMLP\Users\swarrier\RejectLetter

                                  which if i specify in the imports tag i get an error.
                                  The report cannot be rendered because of an error, please contact the administrator.

                                  Where exactly will the log files be? How can we debug this and see or know what the problem is?

                                  Thanks
                                  Meenu