Friday 27 April 2012

Using custom transformations in BI Publisher 10G

Out of all my posts on BI publisher, this one is my personal favorite. The article describes a mechanism to use any custom function in BI publisher reports. This opens a whole box of opportunities. The custom functions have to be written in java which means that you can code anything between the sun and the earth and then use it in your BI Publisher reports.
Let me 1st chalk out the steps for this
  1. Write your code and then compile it
  2. Encapsulate your functions in a java archive (jar)
  3. Put your archive on your webserver under the following directory <OracleBI_Home>\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib
  4. Restart your BI server
  5. Use this function in your rtf template
  6. Bingo!!
Let me use an example to present the process. Follow the steps to prefix ‘Hello’ to a list of names. Now, there are easier ways to concat strings in BI Publisher but the objective of this article is to present the process and not concat strings
  1. Create a java file and name it customFunctions.java. Put the following code in it. Save it in C:
package customFunctions;
public class customFunctions
{
    public customFunctions()
    {
    }
    public static String helloFunction(String s)
    {
        StringBuffer stringbuffer = new StringBuffer("Hello ");
        stringbuffer.append(s);
        return stringbuffer.toString();
    }
}
  1. Open command prompt and navigate to C:\. Compile this file using the following command                              javac customFunctions.java
  2. customFunctions.class will be created. Create ‘customFunctions’ folder in C: and put customFunctions.class in customFunctions folder
  3. Run the following command
  4. jar -cvf customFunctions.jar customFunctions
    -c  create new archive
    -v  generate verbose output on standard output
    -f  specify archive file name
    You should be getting this message
    added manifest
    adding: customFunctions/(in = 0) (out= 0)(stored 0%)
    adding: customFunctions/customFunctions.class(in = 532) (out= 317)(deflated 40%)
  5. Place this jar in <OracleBI_Home>\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib
  6. Now restart your webserver (OC4J in my case)
  7. Create a file called ‘customFunctions.xml’ and put it in <OracleBI_Home>\xmlp\XMLP\DemoFiles
<customFunctions>
  <listOfNames>
    <name>Vishal</name>
  </listOfNames>
  <listOfNames>
    <name>Shaili</name>
  </listOfNames>
</customFunctions>
  1. Now create a rtf template and put the following things in it
a.       <?namespace:cfns=http://www.oracle.com/XSL/Transform/java/customFunctions.customFunctions?>
b.      <?for-each:listOfNames?>
c.       <?cfns:helloFunction(name)?>
d.      <?end for-each?>
Extension function namespace should start with 'http://www.oracle.com/XSL/Transform/java/'
This string is followed by the package structure of your jar.
  1. Create a BI Publisher report. Select customFunctions.xml as the datasource and upload the newly created rtf in the templates section
  2. You should be able to see the following output when you run the report

21 comments:

JohnP said...

Very nice, Vishal!

Have you tried the same approach on BIP 11g ? Is it working ?

TIA,

JohnP

Vishal Pathak said...

Thanks John.

I should have done this experiment on 11G but right now I don't have it installed on my machine

Theoretically, it should work the same way.

I will try to find some time and test it in 11G as well

Regards,
Vishal

Anonymous said...

Hello. When I try to View this report, I see this error in the logs:


[050412_121819998][][EXCEPTION] java.lang.reflect.InvocationTargetException
...
Caused by: oracle.xdo.parser.v2.XPathException: Variable not defined: '_XDONFSEPARATORS'.
at oracle.xdo.parser.v2.XSLStylesheet.flushErrors(XSLStylesheet.java:1534)
at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:521)
at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:489)
at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:271)
at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:155)
at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:192)
... 50 more

What am I doing wrong? I created the rtf file using a text editor with the same 4 lines from your tutorial. Is that the proper way?

Vishal Pathak said...

The error which you got is an XPATH error. I reckon this error should be because of incorrect namespace.

Let me repeat a few important things about the namespace.

The string SHOULD begin with

http://www.oracle.com/XSL/Transform/java/

The string that follows this is your package structure. In my example, I have customFunctions.customFunctions. The reason for having this is that my customFunctions class is encapsulated in a package called customFunctions. Check out step 3. I have created a folder called customFunctions and placed my customFunctions class in it. I feel I should have used better names for the package and the class. Again check the definition of the java file. See the 1st line. I have declared the package customFunctions. I have done this because I knew that I would put it in customFunctions folder in step 3.

I will suggest that you copy the exact same code to get a feel of how this thing works.

Can you also tell me the version of BI Publisher on which you are working

Regards,
Vishal

Anonymous said...

Thank you,
I am working on BI Publisher 10.1.3.4.1


I followed your instructions exactly, yet still get these results.

Vishal Pathak said...

Ok, now this is interesting.

Do me a favor.

Below is a link to the jar compiled by me, the xml datasource file and the BI Publisher report.

https://www.box.com/s/7e1120463ed834be14f7

Please put these files in the required places and let me know the result.

Can you name the web server that hosts your BI Publisher

Regards,
Vishal

Anonymous said...

Vishal, thank you again.

I used the exact files you placed on box and it still has the same results.

I'm running Weblogic 10.3.5.

Vishal Pathak said...

Oh ok, there lies the problem

The process to deploy jars on weblogic is different from the process to deploy them on OC4J.

You will have to go through the documentation of weblogic to find the method. Just copying files on weblogic does not work

Regards,
Vishal

Anonymous said...

Hm, as long as it is on the classpath, I should be OK. I'll look into it. THANKS!

Anonymous said...

I finally got it to work. You have two RTF files on box, and one was simply the four lines from your tutorial, but the other one had tons of other stuff in it. On step 8, what is the process for turning those 4 lines into an RTF template? I think that's where I screwed up. I simply placed the four lines into an RTF file (minus the a.b.c.d) and saved it and uploaded it.

Thanks again!

Vishal Pathak said...

Well, those 4 lines have to be there in the form fields. But in any case, I gave you my rtf file and requested you to upload them and you said that even that didn't work.

Do you mind sharing the steps you took to get it working in weblogic

Regards,
Vishal

Anonymous said...

Ah so I have to use the BI Publisher desktop utility in MS Word to put those 4 lines - that's where I screwed up.

FYI you can place jar files in $DOMAIN_HOME/lib to add them to the classpath when the admin server starts.

Narayan said...

Hi Vishal,

I am working on one of the report (Siebel - BI Publisher) where siebel IO outputs xml file with HTML which has character entities (example: <P><STRONG><U>The customer:</U></STRONG></P>) and same is not being parsed by BI Publisher as it understand only XHTML.

Do you have any idea how to get this one working in a proper format when it outputs to PDF.

I am using Oracle BI Publisher Template Builder for Word 10.1.3.3.3.

Any suggestion or guidance will be much appreciated.

I can provide you the files if you give me the upload link.

email: nalawandi@yahoo.co.in

Cheers,
Narayan

Vishal Pathak said...

You can create a new IO that generates the right XML or you can parse in the rtf file i.e. have if conditions to find the required formatting for the node and use MS Word formatting to format the node
If you are on BI Publisher version 11.1.1.5.0 then you can apply 13554951 patch. This patch supports HTML embedded in XML. It has the following tag to retain formatting of HTML in output

Narayan said...

Thanks Vishal for your reply.

There are some good stuff in below blogs but nothing is of my help on BIP 10.1.3.4.2.

https://blogs.oracle.com/xmlpublisher/tags/xhtml

https://blogs.oracle.com/xmlpublisher/entry/formatting_html_with_templates

https://forums.oracle.com/forums/thread.jspa?threadID=703696&start=15&tstart=0

So many others are facing the same issue but no solution.

Cheers,
Narayan

Vishal Pathak said...

What is the problem in creating a new IO or parsing in the template?

Navin Khanna said...

Very interesting article.

Do you know how/where do we place the jar so that the desktop previewer can use the custom jar. Also, will this work in an eBusiness environment R12.1.3? We are on BI Pub version 5.6.3.

Vishal Pathak said...

I believe that you are talking about the preview using the MS word plugin. If you are then you just have to create a batch file with the following

echo %1

set _JAVA_OPTIONS=-Xbootclasspath/a:

"C:\Program Files\Microsoft Office\Office12\WINWORD.EXE" %1

You will obviously have to change the path of WINWORD.exe according to your MS office installation

Regards,
Vishal

Navin Khanna said...

I get the following error

Caused by: oracle.xdo.parser.v2.XPathException: Extension function error: Class not found 'customFunctions.customFunctions'

I was able fix it by repackaging RTF2PDF.jar by updating the classpath in the manifest file to include my custom jar

Thanks

Anonymous said...

Hi, is there any way that we can creare our own custom export function that should export to csv format.
Because when we exporting in csv the date format is not.comming properly.

Vishal Pathak said...

This blog shows the process to write your own function and use it