Input Parameter Validation in BIP

BI publisher is really simple-easy-to-use tool but with its own limitations. One of which is validation of the input parameter befor report execution. Yeah you might fire back stating we do have LOV concept there but what about Email and Phone formatting.

Keep all your worries at bay as there is always an alternate solution. We can't validate the parameters while keying but we can validate the same in the template. That's how much powerful BIP is. BI Publisher has extended a set of SQL and XSL functions for use in RTF templates. The syntax for these extended functions is

<?xdofx:expression?> for extended SQL functions

<?xdoxslt:expression?> for extended XSL functions

The key here is syntax. You might struggle in the begining if you are new to this part of world. However there are certain functions (read Instr as one) which are supported by xdof while some are supported by xdoxslt. Keep in mind you cannot mix xdofx statements with XSL expressions in the same context. Consider a simple scenario of validating email address parameter in template, whether it have '@' symbol or not. The syntax can be real tricky if you are using the same with conditional region in rtf. Below steps can be used to perform the same.

1 - Define input parameter in rtf template

<?param@begin:Email;u_rock@gmail.com?>

2- Define xdofx condition which checks for @ in the parameter. This will use extension function for the same.

<?xdofx:if Instr($Email,'@',1) <= 1?>

Invalid Email Address

<end if>

<?xdofx:if Instr($Email,'@',1) > 1?>

Your Report Data

<end if>


With the help of extension functions you can define your own conditional region and conditional formatting.  Do share your experience with the extension functions as the syntax can be quite tricky at times for these functions. In my next post i will try to take a dig on xdoxslt function for validation.

Happy Reporting!!

Blogger Blog to Custom Domain

This has been interesting week for this tech blog! I finally moved it from blogspot subdomain to a custom domain. I have been wanting to do this since a long time (ever since this blog started getting some attention) , I somehow couldn't manage to do so but finally it has happened and feeling is as good as moving from rented apartment to owning your own villa. 

Thanks to all fellow readers and bloggers. You have been inspirational.

Thanks!!

The State of Siebel 2014 - Webinar 23/09/14

Renowned speaker and chief editor of the Siebel Observer, Mr Bruce Daley, will present a webinar on the State of Siebel 2014 on September 23rd 2014 at 11:00 AM EST.

Building on last year's successful State of Siebel address, this year's presentation helps Siebel customers and partners determine what different directions they can take their implementation and their careers in the light of Siebel Open UI. Is a Siebel Renaissance really taking place? Find out where you really stand in your career.

Register now before clock ticks over.

You wouldn't want to miss this for anything.

NOTE Datatype in OBIEE 10g

As entire world is ready to embrace Iphone 6, i am still stuck with Iphone 3s and as fate would have it OBIEE 10g. For those who have moved on with OBIEE upgrade there will be new challenges but currently we were hand-tied-in-hole sort of issue. It was not known fact to me that OBIEE 10g doesn't support columns with data type NOTE from siebel. So when we decided to import S_NOTE_SR table in OBIEE rpd we were in for surprise when dashboad reports bombed where we had to display NOTE column. NOTE column came perfectly into OBIEE rpd with type LONGVARCHAR and size as 32768 characters but when it was used in queries it was failing with error as below:

"State: HY000. Code: 25. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 1722, message: ORA-01722: at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000"

Friends at Oracle were not kind in reponse when they told LONG datatype is not supported in OBIEE 10g. To do overnight upgrade was bigger challenge then problem at hand. Another option was to convert NOTE column into VarChar Column and bring it into OBIEE repository. Then friend-colleague-DBA-
Mastermind, Mr Shuaib, came to rescue when he was able to write function which converted NOTE datatype in VarChar and we created database View in SIEBEL schema to store curtailed-Notes.

Function: 
create or replace function f_long2Char (r rowid) return varchar2 is
  v varchar2(4000);
begin
    SELECT NOTE INTO V FROM SIEBEL.S_NOTE_SR WHERE ROWID=R;
      return substr(v,1,4000);
END;

View:
CREATE OR REPLACE VIEW VNOTE AS
SELECT ROW_ID, SRC_ROW_ID, f_long2Char(ROWID) AS NEWNOTE FROM SIEBEL.S_NOTE_SR;

Once db view was created things become simple. We were able to import this view into OBIEE rpd and reports started working like charm. However the catch here is in our case we knew the maximum length of data going into LONG column is upto 4000 characters. I might get Iphone 6 soon and upgrade sooner but if you are on OBIEE 10g and facing such issue. This might just help you.  Sometimes big is not that beautiful.

Happy Reporting!!

Error in Contact Recipient Group

Recently we stumbled upon strange issue of mutiple emails going to same contact record. To make matter worse it was not happening for all contact records. On further investigation we found if you set primary suspect id in multiple Case records for same contact record you will get this behavior. Multiple emails will go to the same Contact id. This issue is also reproducible in OOB Siebel Public Sector flavor. One can always use "Administration - Communications -> Outbound Request Overview" to verify this.

As devil lies in details and issue lies in SQL. On enabling all the logging parameter of Outbound Communication Manager, we found the join between S_CONTACT and S_CASE was causing this issue. The query was returning more than one record per Contact recipient Id. On Disabling the Join with Alias Name, Case Primary Contact in Contact BC, it worked perfectly.

We have raised SR with Oracle and same has been made as public Knowledge item by good guys at Oracle. Below is the link for same. Hope it helps many.

Multiple Emails Are Going When Using Outbound Communication Manager Business Service (Doc ID 1672680.1).

Happy Configuration!!

Escaping Characters in SOAP UI

It is very important and tricky to escape special characters whatever you are doing. In Siebel eScripts we do this escaping by using "\" . Same holds true while invoking any request from SOAP UI. Recently we hit an issue with one of our webservices as it was working fine for all accounts data but failed for couple of records.

While invoking the request from SOAP UI, we were getting response as "Supplied input in not well formed". Closer look of input exposed usage of '&' was giving the error. Yes you thought it right we need to escape special characters in request message.

Characters like "<",">","&" are criminals in XML.Once escaping is done the result was as expected. If you need to pass heavy duty XML message as request then the only gotcha is explicit CDATA sections where the special meaning doesn't hold.


So better Know Escaping else No Escaping.

Project and Resource Management in Siebel

Every now and then Siebel mesmerizes with its power and flexibility. Recently while doing an implementation, we stumbled upon where to do project/resource management activities as there was no major tool in place except good-old-excel. Siebel as good it is, comes with wonderful solution in form of Project and Resource Management module. It is power packed with options for creating Projects, Individual Tasks, Project Team and workbook, Timesheet module to keep track of each task/resource and many many more features.

Mother of all siebel documents (Bookshelf) suggests this module was designed in consultation with leading professional services organizations specifically to meet the needs of consulting and service organizations. It is built in such a way that it can compete and beat any current Resource management tool available in market. If you are implementing Siebel and your client doesn't have good resource management tool don't miss out to demonstrate this module of Siebel.

The path to Project Summary view is something you can learn a trick or so.

PS: Do check your Siebel flavors before proposing the solution.

Happy Crunching!!