Wednesday, January 27, 2010

What to Do with these Siebel Extended Functions?

After you run the Actuate to BI Publisher conversion utility and open the generated RTF template file you would find something like below in the text forms, though it depends what you had in your old Actuate report.

<?value-of:concat(concat(ssActiveFirstName," "),ssActiveLastName)?>

<?value-of:psfn:Format(psfn:CInt(TotalCommit1),"$#,##0")?>

<?value-of:concat("Printed on: ",psfn:Format(psfn:currentdate(),"mm/dd/yyyy"))?>

And when you try to preview with your MS-Word with BI Publisher Add-in, you’ll get an error.

First of all, when you try to preview the report after loading a sample XML data by using the MS-Word add-in (Template Builder) you need to follow some steps that I discussed yesterday.

Second, the ‘value-of’ just doesn’t work. I don’t even know why they are there. But the good news is you can just get rid of them!

And now, assuming that your MS-Word Add-in has already been setup correctly and you have declared the ‘psfn’ name space in your RTF template, the next step is to figure out ‘what the hell are these functions doing?’

The conversion utility generated those codes with the Siebel’s extended functions doesn’t mean that you need to use them as they are. Most of the functions are covered and can be replaced by the BI Publisher’s existing functions so no need to use the Siebel’s. I would recommend to use the BI Publisher’s ones over the Siebel’s ones due to a long term maintenance advantage. However, some of the functions are not provided by BI Publisher, then you want to stick with the Siebel’s extended functions like the ones I recommended yesterday.

So What to Do with These Syntax?

Well, this really depends on each case but here is a list of the steps that you can take to reach to the answer.

  1. Understand what it’s trying to do
  2. See if there is any BI Publisher’s native function that can be used to provide the same functionality
  3. If yes to the #2 question then replace and test
  4. If no to the #2 question then see if it works without any modification
  5. If no to the #4 question then come up with a logic by using BI Publisher’s native function or developing a new custom Java function

1. Understand What It’s Trying to Do

You can guess what it’s trying to do just by looking at the generated code. But it’s always better to see the original logic in the Actuate report definition. And it’s even better to review the original business requirement. Often that the requirement has been changed over the years and some of the logic might not be relevant anymore. If that’s the case you definitely don’t want to spend any of your time on something that is not meeting today’s requirements.

2. See if there is any BI Publisher’s Native Function

Once you understand the requirement and what the syntax is trying to do then review the BI Publisher’s function list to see if any of the functions can replace it without compromising the requirement.

3. Replace It and Test It

No much to talk about here, but go ahead replacing it and make sure the result is meeting the expectation.

4. Leave It As It Is and Test It

If you can’t find any BI Publisher’s function that can replace it then try to use it and see if it works. If it works then great, but I had some functions that didn’t work (e.g. CnvDate), and later I was told that they were not even supported! So it’s important to setup your MS-Word and test it on your local environment before uploading it to the Siebel server.

5. Come up with a New Way

If you can’t find any BI Publisher’s function that can replace and the Siebel’s extended function doesn’t work then you need to come up with a way to make it work to meet the requirements. With BI Publisher’s flexible development framework usually you should be able to find a way easily to achieve this by combining the existing functions or XSL/XPath functions and creating a logic.

As a long term solution though, if you see this function or logic can be used in many other reports, then you might want to think about developing your own Java custom function. BI Publisher supports such custom function being developed by Java and being used in the RTF template. I will cover this later this week.

Now let’s see what we can do with those in the above example.

What Can We Do With the Examples

Example 1:

Here is the first one.


<?value-of:concat(concat(ssActiveFirstName," "),ssActiveLastName)?>

This one is simply trying to print First Name and Last Name in this order and have one space between so the result would be something like below.

Barak Obama

As most of you know this is where it becomes so easy to develop and design with BI Publisher. What you need to do is just this.


<?ssActiveFirstName?> <?ssActiveLastName?>

You can either use the two text forms (the gray square box ones) or just type the above, and adjust the space between in the MS-Word.

Example 2:

Here is the second one.


<?value-of:psfn:Format(psfn:CInt(TotalCommit),"$#,##0")?>

This one is trying to do the number formatting in this ‘$9,990’ format. And CInt is trying to ensure that the data is always passed as number. You can simply replace this by get rid of all but the ‘TotalCommit’ data element like below.


<?TotalCommit?>

Then use the BI Publisher’s property UI where you can specify your desired number format.

Example 3

And here is the last one.


<?value-of:concat("Printed on: ",psfn:Format(psfn:currentdate(),"mm/dd/yyyy"))?>

This is trying to print the current date as a ‘Printed’ date so that the users know when this report is generated. There are actually many ways to print the ‘Current date’ with BI Publisher. And here are some examples.

<?xdofx:sysdate()?>

<?xdoxslt:current_date($_XDOLOCALE, $_XDOTIMEZONE)?>

<?xdoxslt:sysdate_as_xsdformat()?>

The date formatting itself is a whole another topic and I leave the detail to another post later. But you can use one of the above to return the current date and use the BI Publisher’s property UI, the one pop up when you double click the gray box, and specify your desired date format. And again, you don’t need the ‘concat’ function to concatenate the two strings. You can simply type the ‘Pritned on: ‘ and have the text form box next which includes one of the above syntax.

Conclusion

The chances are you will have more and various different requirements and can have the RTF templates generated with more various and can be funky syntax. Hope this post help you to get started and give you a starting point and make your reports development more enjoyable.

6 comments:

  1. Hello Kanichiro,

    I find the tips you have provided are extremely helpful esp for the one regarding Siebel Extended functions ( where can I get the complete list of Extended functions ?)

    Anyway, my main question is, how can I design my report to run only for the selected record instead of the entire set that is returned by the query. How do I reproduce the "Current Record Only" property that we could set for Actuate Reports.

    Can you please let me know.

    Thanks

    Subbu

    ReplyDelete
  2. Hi Subbu,

    What is 'Current Record Only' ? Can you explain it a bit more about ?

    ReplyDelete
  3. Hi Subbu,

    Which version of Siebel are you using?

    ReplyDelete
  4. The latest fixpack 8.1.1.2 supports the current record only.

    ReplyDelete
  5. Hi Kanichiro,

    Can you tell me the code for the 'current record only'? I know you are referring to the converted reports but What should I add for a new report to achieve the 'current record only' functionality?

    ReplyDelete
  6. Hi,
    I am having an issue with generating a paramterized report. Following are the steps performed.
    1. Defined a parameter in RTF file. Tested the parameter by displaying it using . This works fine.
    But how should I use this $param value to restrict data on the report. Requirement is to have 5 parameter filters which the user will enter before generating the reports. I have tried multiple things but failing.
    I tried using . If you can give me different strategy to filter the data on report using the parameter. Or if you have any suggestion

    ZK

    ReplyDelete