Thursday, September 27, 2007

BI Publisher: Converting Report6i reports - Pitfalls and Suggestions

Yes there are pitfalls! I have seen some questions in the bi publisher forum about converting all of a companies "existing report6i code" to BI Publisher. Don’t get me wrong the tool is a great, but like any tool it has some issues.

Here are some common elements as far as time is concerned for report development in general. These estimates are based my experience writing reports in the 11i e-business suite.

New Reports – From dev -->test -->production typically development time is about 40 hours.
Fixing an Existing report – If you’re lucky, 2 minutes but on average 5 about hours.
Converting/Copying an existing report 10-15 hours
Converting reports6i to BI Publisher
Small report 10 hours
Large report 20-40 hours, it can get ugly!


You may be asking yourself, what in the duce? How is that possible that converting a large report could take upto 40 hours? There are some bugs in the conversion tool. As example:

Reports6i:
Function get_item_no return char
Is
l_item_no varchar2(30);
Begin
Select item_no
Into l_item_no
From ic_item_mst
Where item_id = :item_id;

Return l_item_no;
End;

Conversion to PL/SQL:
Function get_item_no (item_id in number) return char
Is
l_item_no varchar2(30);
Begin
Select item_no
Into l_item_no
From ic_item_mst
Where item_id = item_id;

Return l_item_no;
End;

Data Template Issues
In the converted PL/SQL example you can see the problem in grey. Sometimes during the conversion the parameters end up having the same name to a joining column. Ouch, get_item_no will actually compile too (note: it really shouldn’t in "theory" since item_id is “technically” ambiguous). Now were left with a situation where that function will blow up 100% no matter where it’s called from. Some reports can have over 50+ functions. When this issue occurs you have to fix each query and narrow it down in the data template.

Format Template Issues
Another kind of nasty issue is caused by sloppy development in reports6i with srw.set_field(….). This "was" a way of changing the data on a field in the report format trigger. It’s a big no, no. Well, guess what, set_field never gets converted to the format template so we have to fix the issue manually. Something like this is really easy to slip through the cracks. Also, did I mention that all the logic in your format triggers to suppress fields or groups, change colors, fonts, etc doesn’t get converted as well? Now that will take some dev time!

Conclusion
The one thing to come away from this article is this: Don’t fix things that aren’t broken (it’s easy to say, hard to practice). It’s a lot of work to convert a large report and troubleshooting the conversion issues will cost you time and money. Also, expect to feel a majority of the pain in the format template conversion. Formatting is the number one challenge for any report!

When the conversion tool becomes a more mature product we can expect the conversion to be better and it “might” make sense to convert more of your organizations reports. The only justification for this would be either lower cost of support or a disappearing reports6i skill set in your organization/development community. With that in mind, if you don’t have a choice and you have to convert a report, the tool does a pretty good job and it’s better than nothing.

6 comments:

Gareth said...

Hi Ike,
Great post ... nice to hear someone using the tool and giving real world experience.
To me it sounds like you almost need to convert the reports from scratch . Alternatively some tool to say, parse an rex version of the report and alert you to common issues might come in real handy (such as your note on items/bind variable in functions, format triggers, etc etc) - have you found anything like this?
Gareth

Ike Wiggins said...

Garth,

Sorry, for taking so long to respond to your questions....

Here goes....

Unfortunatly, it's not a simple yes or no when it comes to converting a reports6i report. Depends on how complex the report is you may or may not have issues. I've had some reports convert with "zero issues" in the format template. As for the pl/sql/data template, there is almost always issues.

When you convert the report, there is a log-file that spits out all of the format trigger info. If there's nothing in the log-file thats a good thing, your report is going to convert very easily.

Based on the fact it takes relatively seconds to do the conversion, it makes sense to do it (conversion), to see whether or not you should scrap the whole report. Regardless, it really is better than nothing and if all else fails, at least it's a good starting point.

Ike Wiggins

Tiger said...

Hello Ike,
I am wondering if you can help me with an issue I am having. I have a procedure that I use to populate a table, then generate the XML. I run this from the APPS and it works great if my output < 32767. If it exceeds this amount, then the tag gets chopped off and the output is not valid.
For example a tag like
item1234/item
becomes
item1234
/item because it is cut off right at 32767. I cannot use the actual tag so I excluded the <>. I use the following code

SELECT DBMS_XMLGEN.getxml('SELECT
MATCH_TYPE
,SUPPLIER
,PAY_ON_RECEIPT
,COUNTRY
,APPROVERS
,CURRENCY
,CHECKNO
,NO_OF_INVOICES
,CHECK_AMOUNT
,TWO_WAY_AMT
,THREE_WAY_AMT
FROM APPS.CHECK_AUDIT') INTO lv_1 FROM DUAL;

len := DBMS_LOB.getlength (lv_1);

LOOP EXIT WHEN len = retrieved;
IF (len - retrieved) < 32767 THEN
SELECT SUBSTR (lv_1, retrieved + 1) INTO xmlstr FROM DUAL;
retrieved := len;

fnd_file.put_line (fnd_file.output, xmlstr);

ELSE
SELECT SUBSTR (lv_1, retrieved + 1, offset) INTO xmlstr FROM DUAL;

retrieved := retrieved + offset;

fnd_file.put_line (fnd_file.output, xmlstr);

END IF;

END LOOP;

END;


Do you know how I can get around this issue. I have tried to figure out offset dynamically, but I have had little luck.

Any help is very much appreciated.

Thanks

Ike Wiggins said...

Hi Darren,

Use fnd_file.put instead of fnd_file.put_line.

Ike

Stephen Campbell said...

Hi Ike,
I'm converting some reports from Oracle Reports6i to BI Publisher, (which I'm new to).

In Oracle Reports6i, the Before Parameter Form trigger obviously fires before the parameter form is displayed to the user.

But, in BI Publisher is there an equivalent trigger?
When clicking on View, the user parameters appear at the top of the screen. However, I can't figure out where the processing that pre-populates the user parameters, (ie. before parameter form trigger), takes place.

Also, I'm wondering how to display messages to the user if they enter an invalid parameter value.

I'm fairly new to BI Publisher, so I may be missing something fundamental.

(I can't seem to find any detailed documentation on the XML data template).

Thanks in advance,
Stephen

Ike Wiggins said...

"In Oracle Reports6i, the Before Parameter Form trigger obviously fires before the parameter form is displayed to the user."

Thats kind of a true statement, remember though in the EBS that form trigger is really not applicable....It's the before report trigger that is.

If your report resides in the EBS, then I would just setup a value set. That would stop users from entering in the wrong parameter. Value sets can always be daisy chained to insure all parameters are entered in correctly.

Just so you know, I've put some free utilities out there to convert report6i reports.

http://bipublisher.blogspot.com/2009/05/bi-publisher-reports6i-to-bip.html