Tuesday, January 26, 2010

Siebel’s Extended Function for RTF Template

Has anyone noticed some funky functions after you converted the BAS (Actuate) files to BI Publisher’s RTF template ? These functions look confusing especially for some of us coming from pure BI Publisher side. And even worse, they are most likely throwing errors at you when you try to preview with the MS-Word Add-in Template Builder!

So what are these? Well, these functions are there to help you to provide easier way to achieve some of the Siebel specific requirements. However, there are many functions that now we can just use BI Publisher’s out-of-box functions instead. And some functions just simply don’t work due to some defects with the conversion utility.

So it’s important to identify what functions we can use, when to use them, and how to use them.

What Functions Can We Use ?

There are about 30 or so of the Siebel extended functions in total. But most of them are actually either not really useful or duplicate of already existing BI Publisher’s functions. I couldn’t get an approval to share the list with public so I can’t list them here. But again, most of them are not really useful so I’ll list only those that are useful for the Siebel reports development at the next section.

When to Use Them ?

Among all the Siebel’s extended functions here is a list of the functions that I personally find useful.

Function Name Description Example
DivIntZero(int div, int divisor) Method returns a 0 if the divisor is a zero. <?psfn:DivIntZero(10, 0)?>
now() Method to return the current time in the native time format. <?psfn:now()?>
phoneFormat(java.lang.String inp) Method to returns a String, which is the usual way to display the phone or fax number i.e. (XXX)AAA-BBBB <?psfn:phoneFormat(ssPhone)?>
totext(java.lang.String d,java.lang.String f,java.lang.String e) Method to return the date "d" the format "f" of the input date, which is in format " e ". format yyyy-MM-dd. <?psfn:totext(ssCreated,"yyyy-MM-dd","MM/dd/yyyy")?>
Val (java.lang.String input) Method to Returns the numeric value of a string expression. <?psfn:Val(ssAccountName)?>

Now, you might have the following two questions.

  • Ok, those look useful and I want to use them. But is there any pre-configuration or pre-requisite steps ?
  • I see bunch of functions in my RTF template that was generated by the conversion utility, what should I do with them ?

So let’s talk about what pre-requisite steps required to use the Siebel extended functions. Then later I’ll discuss about what to do with the automatically generated functions in the RTF template.

What are the Pre-Requisite Steps to Use Siebel Extended Functions ?

There are three steps to ensure in order for you to use the Siebel extended functions.

  • Configuration at Siebel Server
  • Configuration at BI Publisher Enterprise Server
  • Configuration at Client (BI Publisher MS-Word Add-in)
  • Declare the Siebel Extended Function in each RTF template

Configuration at BI Publisher Enterprise Server

There are a couple of JAR files that come with the Siebel server installation. And you need to make these JAR files available to your BI Publisher server instance so that at the runtime the BI Publisher Server can use the JAR files to execute the Siebel’s extended functions. So make sure you have those JAR files, which are listed below, under the BI Publisher’s Server’s Java library folder. (e.g. OracleAS_HOME/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/lib)

  • XSLFunctions.JAR
  • SiebelCustomXMLP.JAR
  • SiebelCustomXMLP_SIA.JAR

Then you need to enable ‘External File Reference’ from the BI Publisher Server UI.

  1. Log in to the Oracle BI Publisher Server with administrator privileges.
  2. Click the Admin tab, and then select Runtime Configuration and Properties.
  3. Change the default value for the Disable External Reference attribute to FALSE, click Apply, and then verify the changes were made.

The detail configuration for BI Publisher Enterprise Server is documented here.

Configuration at Siebel Server Side - Adding an Explicit Reference to JAR Files for the Oracle BI Publisher Server

This requires for the scheduling functionality to work with the functions. You can find the detail setup from here.

Configuration at BI Publisher client (MS-Word Add-in Template Builder) side

If you want to use the Siebel’s extended functions in your RTF template and preview with the MS-Word Template Builder you will get an error without the steps I’m going to talk about here. There are two things to do. You need to configure your Template Builder environment, which requires only for the first time, and need to declare the Siebel function name space in the RTF template for these functions to work.

Create a Batch File to Launch MS-Word Associating with the Siebel’s JAR files

Basically, you need to add a set of Siebel jar files to ‘_JAVA_OPTIONS’ variable and call MS-Word for these functions to work in your Template Builder instance. Here is an example script that you can use to launch MS-Word with the variable setting. 

echo %1

set _JAVA_OPTIONS=-Xbootclasspath/a:C:\81DQSSIA\client\classes\SiebelXMLP.jar;C:\81DQSSIA\client\classes\XMLP.jar;C:\81DQSSIA\client\classes\siebel.jar;C:\81DQSSIA\client\classes\XSLFunctions.jar;C:\81DQSSIA\client\classes\SiebelCustomXMLP.jar;C:\81DQSSIA\client\classes\SiebelCustomXMLP_SIA.jar

"C:\Program Files\microsoft office\Office\Winword.exe" %1

Note that the above ‘set _JAVA_OPTIONS’ line must be a single line, no return key. You can find the detail of this configuration here from the configuration guide.

Once you have created the above script as a .bat file then you can double click this .bat file to start MS-Word and open your RTF template. In order for this configuration to work you need to shutdown all the MS-Word documents first and start MS-Word by clicking this bat file.

Declare Siebel’s Extended Function Name Space in RTF Template

Before you start using the extended function, first you need to declare a name space called ‘psfn’ as below in your RTF template.

<?namespace:psfn=http://www.oracle.com/XSL/Transform/java/com.siebel.xmlpublisher.reports.XSLFunctions?>

You need to declare this in every single RTF template file where you want to use the extended functions.

Now you’re ready to start using the Siebel’s extended functions!

How to Use Siebel Extended Functions?

You can use the Siebel’s extended functions as the same way you use other BI Publisher’s functions. Here is some example.

<?psfn:DivIntZero(10, 0)?>

<?psfn:now()?>

<?psfn:phoneFormat(ssPhone)?>

<?psfn:totext(‘ssCreated’,"yyyy-MM-dd","MM/dd/yyyy")?>

You just need to start with ‘psfn:’ name space before the function. And you can preview the report output with a sample data that you have downloaded from the Siebel server UI (Web Client).

When you preview it you should be able to see the values from your sample XML data being calculated or formatted by the functions.

Troubleshooting:

1. I’m getting an error with ‘Namespace prefix 'psfn' used but not declared.’

Caused by: oracle.xdo.parser.v2.XPathException: Namespace prefix 'psfn' used but not declared.

As mentioned above you need to make sure that you have declared the Siebel’s extended function’s namespace in the RTF template. 2. I’m seeing an error with ‘method not found’ when trying to preview

2. I’m getting an error with ‘method not found’ when trying to preview.

If you are still seeing an error saying ‘method not found’ then you should make sure that you close all the MS-Word document completely then double click the custom MS-Word launch program (.bat) and open the RTF template file.

Conclusion

Once your RTF template is ready now you can upload it to the server and run it from the Web client. But you want to make sure that the server side configuration, which was mentioned above, has been done properly for these functions to work.

Hopefully now you understand how to use the Siebel’s extended functions within both the runtime and the development sides.

I still haven’t answered to one of the questions I raised above.

‘I see bunch of functions in my RTF template that was generated by the conversion utility, what should I do with them ?’

The Actuate to BI Publisher Conversion utility generate the RTF templates with many of the Siebel’s extended functions. So I will discuss about what to do with them tomorrow. Stay tuned!

13 comments:

  1. Excellent article, thank you! I've been wrestling with the BIP date formatting for ages, trying to figure out why it's not working. Cheers! :)

    ReplyDelete
  2. Hello Kanichiro,

    I am working on Siebel 8.0.0.8 with BIP 10.1.3.4. Over the last 3 months, I have encountered various issues which I think are bugs in the product / integration. I have also submitted these issues via Metalink. I thought it would be a good idea to share these with you to see if you have any further insight into these issues.

    a) French LOVs: We have a multilingual deployment with English and French. The french report does not recognize French LOVs. In French reports, you will see hardcoded columns headings appaear in French, but fields which are translated such as LOVs, will show up in English. Also, if you query for records in the French application and the query involves LOV fields, the report will not return any records.

    b) All vs My Visibility filter: If you run a master detail report such as Account Opportunities from the My Accounts view, it shows only those Opportunities where you are part of the sales team. It applies the team visibility filter for both objects Account and Opportunities.

    c) Case sensitivity: If you query for the account name such as ibm, Siebel will show all ibm and IBMs. But when the report requeries the data, it runs on a case sensitive mode and returns only those records which match the case of the query.

    d) Current Record Only: Actuate reports had a property “Current Record Only”. This will ensure that the report is run for only that one record that has been selected. But BI Publisher does not seem to have it.

    e) Logos in HTML files: If you run a report and select the output type to be HTML, the logo does not show up. However, it works well for PDF and other output types.

    Let me know if you have any helpful tips on these issues.

    Thanks

    Subbu

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. a)Are you talking about the translation for the parameter in the report submit applet box ? Which LOV are you talking about ?

    b)I'm not clear what is the problem. Are you saying it should not display any Opportunities other than your Opportunities when you run from My Account view but it is displaying all the Opportunities ?

    c)I don't know what you mean by 'Siebel' and 'the report requeries'. Are you saying that the ones you're seeing in the Siebel form is different from the result in the report ?

    d)For example with Account List, when you select a particular account in the Account List view page then submit a report then you should see only the data for the account. Are you seeing something different ?

    e)this sounds familiar, I remember there was an issue before and it's been fixed in one of the patchset. I will check and get back to you on this.

    ReplyDelete
  5. e) actually the issue we had before was Excel, not html format. Is the image embedded into the RTF template or a pointer with HTTP address ?

    ReplyDelete
  6. E) user MHTML insted of HTML in LOV screen.

    ReplyDelete
  7. a)I am referring to the LOVs in the UI e.g. Contact Status or Sales Stage etc


    b)The kind of reports I am referring to is a Master Details reports such as Account - Opportunities i.e the master section has Account information and the child section contains the Opportunity information for that account. When such a report is run from the MY Accounts view, the opportunities that show up under the Account are also only My Opportunities. However, in the UI I am able to view All Opportunities.


    c)That's right. WE have Case Insensitivity and Accent Insensitivity (CIAI) turned on. And when you query on those fields which are case insensitive in the UI, we see all the records in the UI. However, the report applies case sensitivity for the query and returns only those records which are case sensitive.

    d)Let us say my query returns 10 records. I want to be able to configure the report to run for the highlighted record alone - not for all the 10 records.

    e)I believe oracle has a patchset for this as well.

    ReplyDelete
  8. Hello Kanishiro,

    I have a very specific doubt.
    I am working in a report template using BI publisher desktop.
    I am trying to display a list of values in tree format and I need to bold the fathers or indent the sons. In order to do that I am thinking to take the numbers and choose the .s and then tab or bold the values.

    My main question is: can I use a function into an if clause?

    Something like this: bla bla bla


    Thanks in advance for your help.

    ReplyDelete
  9. I mean something like this: a function into the if clause, I was not able to write it here

    thanks

    ReplyDelete
  10. How do I add a namespace to the RTF template?

    ReplyDelete
  11. hi ,

    i have the foll req!

    i need the display the selected record in activities and also the next subsequent activity's date and day in the report

    can anyone pl help me in tis

    ReplyDelete
  12. How can I show only unique values (as distinct) in BIP desktop in MS-Word ?

    ReplyDelete