BI Publisher: Bursting Designer Version 1.2

Whats new in the Bursting Designer 1.2?

Version 1.2 is here and it includes the following enhancements:

1. Excel Output
   
2. Templates tab fix. If you enter more than one template row it may have potentially caused the application to crash. Also, the templates could potentially be assigned to all the deliveries.

Whats next?

The Bursting Designer 1.3 will include enhanced printer options that are undocumented by Oracle. The following attributes will be added to the Printer delivery tab:

1. attributes-charset
2. attributes-natural-language
3. document-format
4. job-name
5. media
6. orientation-requested
7. requesting-user-name
8. sides

The Delivery Designer is also another skunk works project that I'm working on. The tentative release will be sometime in December or late January. This tool will allow a developer to test the delivery APIs without writing a lick of code. There is no need to speculate, whether or not a delivery API is capable of a particular integration point.

Ever been asked, "Can we send a document to an EDI system using as2 Document Delivery APIs?" I know I have and I really didn't have a confident answer of yes or no and I had no way of finding out without writing some code. So, the Delivery Designer will let you kick the tires so speak before making a coding commitment. You'll be able to tell your manager confidently, yes this will work!

More details to follow....

POST A QUESTION
If you have questions about the Bursting Designer or if you find a bug please post a question on this blog. Please no cross posting and make sure the question matches the topic.

BI Publisher: Troubleshooting Bursting

How to Troubleshoot the Bursting Designer & Bursting

Since the bursting designer has come out I been getting quite a few questions lately. This post tackles some common issues that most developers run into when using the Bursting Designer or using Oracles BI Publisher Bursting Engine. If you haven't used the Bursting Designer yet, go on ahead and start kickin' the tires!

Issue: Bursting Designer Web application crashes when the application is downloading. Something like this might occur, " Java 6 splash screen shows, then "Warning - Security" window blips by, then nothing."
Resolution: There can be a wide variety of reasons why this might occur. The first thing to do is make sure your an administrator or windows power user. Because your installing software you need to make sure you have the appropriate rights.

The second thing you should do is check that java 1.6 is installed.

To find out what version your running go to
start->run
type: cmd, hit return
type: java -version

If java 1.6 is installed that can be eliminated. The next step would be to verify the amount of memory you have on your machine.

By default the Bursting Designer does not support dated hardware. The minimum amount of memory required is 512 megs. If you do not have 512 megs installed on your machine the Bursting Designer will not open. In order to fix this, modify the JNLP file and replace the memory options with this line:

<j2se args="-Xms128m -Xmx256m -Xdebug" version="1.5+"/>

Then manually open the JNLP file through IE or Firefox locally on your PC. Do not try and use the download link because it will use the standard memory options. I don't advice against modifying the memory settings, but I would recommend that you talk to your hardware guy in your organization and either request a new pc or ram upgrade. It's the 21st century and you should have a computer that has 21st century power. What are we in russia? I kid for all the Russians who read my blog :-)

Now, if the Bursting Designer is still crashing. Turn on the java debugger. Go to the control panel in windows and you should see an icon for java. Double click on the app. Select the show console option in Java Control Panel in the Advanced tab. This will let you see an errors that are occurring in the application or general debug info.

See the link below and go to the debug section for the java console:

http://java.sun.com/j2se/1.5.0/docs/guide/deployment/deployment-guide/jcp.html
http://java.sun.com/j2se/1.5.0/docs/guide/deployment/deployment-guide/console.html

If you cannot figure-out what the errors means in the debug console, post a comment here with the error message or find my email address and I'll do my best to help resolve the issue.

Issue: JNLP downloads but doesn't open the Bursting Designer
Resolution: The JNLP file is not associated correctly in windows or it's pointed at the wrong java runtime environment.

I ran into this issue myself when I first deployed and tested the Bursting Designer in windows. That's why I set up the JNLP associations on the download page. I have instructions for most browsers and I have tested the Bursting Designer on Firefox, IE & Safari. http://bethwphoto.com/ike/bipublisher/ReportConversion/ReportConversion.zip/ike/bipublisher/BurstDesignerDist/launch.html

For IE and windows users follow these steps:

To set up the association between *.jnlp files and javaws.exe in Windows which also handles IE:
1. In Windows, not IE, click Start | Settings | Control Panel | Folder Options | File Types.
2. Check to see if there is already an association for *.jnlp files.
3. If there is no entry for *.jnlp files already then click new | advanced | select "jnlp file"
4. If there is an entry, select it and click advanced.
5. Then edit the open application used to perform action field and make sure it is set to "C:\Program Files\java\jre...\bin\javaws.exe ""%1". Uncheck DDE.
6. Click OK.
7. Click Set As Default to make Open the default action. Likely Open will be the only action.
8. Click OK | Close.

Issue: Bursting Designer crashes and the web app log file says it's pointing to this directory: file:/Users/ike/Code/NetBeansProjects/Bursting%20Designer/dist/
Resolution: Contact me, I need to fix the JNLP file. If your in a bind you can do it yourself by changing the code base node to:

codebase="http://web.me.com/beth.wiggins/ike/bipublisher/BurstDesignerDist/"

Issue: Bursting Designer crashes when I click the run button.
Resolution: Follow the steps above to turn on java debug console. If you can not identify the issue or a workaround post the error on my blog or send me an e-mail. There is a known issue on the templates tab. If you enter more than one template row it may potentially cause the application to crash. This will be fixed in Bursting Designer Version 1.2.

Issue: Templates are shown across document deliveries.
Resolution: This will be fixed in Bursting Designer Version 1.2. I had a bug in my code that allowed a user to circumvent document to template associations.

Question: Printer node not found when when bursting. Example: ipp://localhost:631/printers/${CF_PRINTER_NAME}.
Answer:
1. XML is case sensitive, is the printer xml node upper case?
2. Is the printer node a child of the of the parent node selected in the xpath expression?
3. Are you using a document key? It's important, you need this to burst the file correctly. This is new for EBS users, it was not a feature in the original 5.6.3 version of BI Publisher. If you don't have a document key, it will not work.
4. Finally, it could be a pruning issue. See: http://bipublisher.blogspot.com/2007/10/bi-publisher-bursting-how-to-resolve.html

Question: How do I identify a printing issue.
Answer: Use the Bursting Designer and review the log file. In the example below we can see that the issues is related to the printer name.

IPP version: 10
operation id: 02
charset: utf-8
request id: 1
-- operation attrs --
[1]attributes-charset:utf-8
[1]attributes-natural-language:en
[1]printer-uri:ipp://localhost:631/printers/null
[1]document-format:application/pdf
-- printer attrs --
-- job attrs --
[1]copies:1
After adding the document key:
IPP version: 10
operation id: 02
charset: utf-8
request id: 1
-- operation attrs --
[1]attributes-charset:utf-8
[1]attributes-natural-language:en
[1]printer-uri:ipp://localhost:631/printers/HP_TEST_PRINTER
[1]document-format:application/octet-stream
-- printer attrs --
-- job attrs --
[1]copies:1

Question: Printer not found when when bursting.
Answer: You will see a message in the logfile that looks something like this:

IPP version: 10
operation id: 46
charset: utf-8
request id: 20
-- operation attrs --
[1]attributes-charset:utf-8
[1]attributes-natural-language:en
[1]status-message:The printer or class was not found.
-- printer attrs --
-- job attrs --

During upgrades sometimes printers that are setup can get wiped out. I know this true on a Mac. So the first step would be to verify the printer actually exists in the setup. If it doesn't add it. Now it's important to note, if you can't bring up the printer in your browser: http://localhost:631/printers then you may have a CUPS issue depending on how your system is setup.

Question: FTP files fail
Answer: Before starting a ftp control file, I would suggest you initial create file delivery instead. This will allow you to work out the kinks and make sure your control file is first class as well as your format template. Once you know there are no issues, then I would try setting up the FTP delivery. To troubleshoot FTP issues do the following:

1. Check the permissions. Suggest that you open a session in telnet and attempt to manually FTP. If you can't do it in telnet, you can't do it with BIP!
2. Verify the user name and password is correct. Remember it's case sensitive.
3. Verify the files are actually being generated. If there not, than obviously there is nothing to FTP. Also, verify the files aren't being generated with just an extension, in which case there is an issue with the document key.
4. Related to number 3, Verify the document key exists and is correct.

Question: Delivery Fails to show in the logfile or the delivery does not get processed.
Answer: BI Publisher Bursting Engine has some undocumented issues for multiple deliveries, below is a breakdown

1. Multiple deliveries for the same request are supported. If you assign the deliveries to one document you should be okay. See example 1.
2. Multiple distinct deliveries, that are individually assigned to a document will only process the last delivery (LIFO) in the document list. This is a very common mistake new developers make regardless if there using the Bursting Designer or not. Should you design your control file this way? No.

Example 1 is okay, Example 2 is wrong and it needs to be put in another request. The Bursting Designer application will allow you to setup a control file like example 2. Could this be a feature someday? Who knows.....Basically, if oracle allows you to do something and doesn't throw an error the Bursting Designer does the same.

Example 1: One Request - Multiple deliveries assigned to a document.
document key="${TRX_NUMBER}" output-type="pdf" delivery="ftp2, print"
Example 2: One Request - Multiple distinct deliveries assigned to a document.
document key="${TRX_NUMBER}" output-type="pdf" delivery="ftp2"
...some template assigned...
document key="${TRX_NUMBER}" output-type="pdf" delivery="print"
...some template assigned...

Also, putting a filter on each template will not resolve the issue with example 2. You will still have the same issue. Word of advice, when in doubt just create two requests and you use the template filters.

Question: Can there be multiple templates assigned for a delivery?
Answer: Yes. If a filter does not exist, all templates will be applied to the output. When all templates are applied to the output file it will occur in LIFO order.

Example:
xapi:document key="${TRX_NUMBER}" output-type="pdf" delivery="file"
xapi:template type="rtf"location="/Users/temp.rtf" filter=""
xapi:template type="rtf"location="/Users/temp2.rtf" filter=""
xapi:document

Template temp2.rtf will be applied first, and then temp.rtf. Also, there will only be one output file. I do not know if this desired functionality by Oracle or not, so you can use this at your own risk.

Question: Are document keys required for all document elements?
Answer: Absolutely, if a key node is left blank or doesn't exist your requested delivery may process but no results will be generated.

Question: Is there an alternative to creating template filters
Answer: Predicates are allowed for request xpath expressions. Now, don't think that because they are you can start getting fancy. There are very limited.

Is it better or worse than a template filter? No, the two are the same. Personally, I think it's easier to understand by having it in the request itself.
Question: Is CSV supported for bursting?
Answer: Yes, you will need to use etext templates.
Question: Is xdo:// uri syntax supported for Bursting?
Answer: Yes.
Question: Is xdo:// uri syntax supported for Bursting Designer?
Answer: No.Jan-15-2010: XDO Protocol is supported in the Bursting Designer

Question: I want to use bursting as a way to apply one or multiple format templates indiscriminately to the output file and I only want a single file(s) to be generated, I actually don't want it bursted.
Answer: Seems like an oxymoron, but it's really not. You may very well run into this situation, I know I have. In your request, just use slash "/" and for the key field generate a something in the data template that is below the root node and use that. As an example, lets say user requested that they need have 3 separate csv files. You could create 3 etext and burst the xml file. Make sense?
Question: I want to use excel as an output format in the Bursting Designer
Answer: That will be available in version 1.1 of the application.
Question: Are Data Expressions supported in Bursting?
Answer: Absolutely. It's supported for e-mail, ftp and webdav. Bursting uses the delivery manager APIs so by default that functionality works for bursting. For more info data expressions see Tim D. blog

http://blogs.oracle.com/xmlpublisher/2009/12/dynamic_delivery_file_naming.html

Example:

xapi:ftp id="ftp" server="localhost" user="hidden"
password="hidden"
remote-directory="/Users/ike/Desktop/Mallika/"
remote-file="ftp_${TRX_NUMBER}_%y.%m.%d.%S" secure-ftp="false"
xapi:ftp

[ASYNC_TIMEOUT:Integer] [86400000]
[HOST:String] [localhost]
[RETRY:Integer] [0]
[ASYNC_CHECK_INTERVAL:Integer] [60000]
[TEMP_FILE_SUFFIX:String] [.tmp]
[USERNAME:String] [hidden]
[PASSWORD:String] [*******]
[TEMP_FILE_PREFIX:String] [dlvr]
[FTP_REMOTE_DIRECTORY:String] [/Users/ike/Desktop/Mallika/]
[FTP_REMOTE_FILENAME:String] [ftp_1537394_%y.%m.%d.%S]
[RETRY_INTERVAL:Integer] [60000]

[121909_020451288][oracle.apps.xdo.delivery.ftp.FTPDeliveryRequestHandler][STATEMENT] submitRequest(): Opening connection : [localhost][21][hidden][****][/Users/ike/Desktop/Mallika/][ftp_1537384_2009.12.19.51][true]

Question: Is xdodelivery.cfg supported for Bursting?
Answer: Although, I have never tried it, I suspect it is. If you do try it let me know.
Jan-15-2010: xdodelivery.cfg is supported
Question: Is xdodelivery.cfg supported for the Bursting Designer?
Answer: No. If I ever decide to support it, I will create a property on the properties tab.Jan-15-2010: xdodelivery.cfg is supported in the Bursting Designer

Question: Does Bursting support custom delivery channels?
Answer: No. Would it be possible for oracle to do this? Absolutely, but not many organizations need this functionality.


POST A QUESTIONIf you have questions about the Bursting Designer or if you find a bug please post a question on this blog. Please no cross posting and make sure the question matches the topic.

BI Publisher: Bursting Designer Version 1.0

Whats new in the Bursting Designer?

The Bursting Designer has moved out of the beta phase! Version 1.0 is here and includes the following enhancements:

1. FTP and Secure FTP (depends on the patch level)
   
2. Webdav
3. Key support
   
4. Latest Version of BIP Libraries
   
5. Memory Adjustment
   

So if you have written a Bursting Listener to FTP files you no longer have to do that anymore. You now can FTP reports in seconds with Oracle's Bursting Engine and the Bursting Designer! A lot of developers are not aware of the fact that BI Publisher Bursting supports FTP. I can't confirm it but I think FTP is an undocumented feature for burst control files. Will have to ask Leslie Studdard from oracle if that's the case or not, she's got people and the answers.

What about Webdav? Thats another option in the Bursting Designer. Webadv is really not a utilized feature but a powerful one. A developer could use a sharepoint portal to create a report repository or pseudo reporting dashboard. I've never had the request for it but when I do I know the Bursting Designer should do the trick!

Key support is available, finally! The beta version was working off of the 5.6.2 version. Version 1.0 works off of BIP 5.6.3 (patch 6241631) . Developers don't have to rollback to old version when creating control files. Also, developers are working with the latest APIs for BI Publisher (11.5.10.2). Should be close enough for R12 developers too!

Run time memory options have also changed for the Bursting Designer. Most developers didn't experience issues with the with web start application but some did :-( The developers that had problems had out-dated hardware. If you don't have less than 256 megs of ram that is so 1999 and you need to get with the program (literally) and get a modern machine. I've changed the app to use -Xms256m -Xmx512m instead of -Xms512m -Xmx1024m. Funny story, back in 1999 I begged my father to give me some coin so I could pick up some ram to make my machine have 128 megs of ram. Back then, 128 megs was the shizzle and I was ridding high on the hog.

Future Functionality
There are some features that are still being developed below is a list:

• XDO uri support
• Request Removal *Manually edit the burst control file to remove a request
  
• File Browsing for File Delivery and Template Fields

If you would like to request an enhancement please feel free to post a question on this blog. Also, If you would like to participate in the development of the Bursting Designer contact me via e-mail. To date I have had no developers volunteer. If you do help out, you'll be elite like a level 3 black sage*

*I went to go get a hair-cut at a salon once (it was proximity thing). The stylist told me that they had there "level 2" people on and it would cost 40.00. I'm a dude, dudes don't need a $40.00 hair cut. Anyways, I responded with "Can you ask your level 2 people to see if they can cut me a deal. I'm a level 3 black sage and I can guarantee safe passage through the mushroom forest." Needless to say the stylist did not responsed to my request. It's easy to understand why, if I were in her shoes looking a level 3 black sage I would be spooked or maybe I would just blew a funny fuse.

Bursting Designer Download Site
*Desupported 04.22.2010 - please use the BI Publisher Toolbox.
Bursting Designer - By clicking on the download link you will be redirected to a different site. The bursting designer is java webstart application. Java 6 is required to run this application. It's recommended to bookmark this particular blog and the download site. This is because you will need to use these links every time you wish to run the Bursting Designer or find news about updates. It's technically not a requirement to use the webstart application for the java savvy developers but in doing so you will miss out on future updates and releases. It's important to note the Bursting Designer jar files will be loaded on your machine. This allows for a significant increase in performance. If you need to remove this application for some reason, go to add/remove applications.

IMPORTANT: If you have questions about the Bursting Designer or if you find a bug please post a question on this blog. If you do not post your comment on this post, I will reject your comment. I've had some complaints in recent months that it's hard to find answers on my blog because of cross post Q&A. If you e-mail me a question about the Bursting Designer be forewarned, I may ask you to post your question on this post.

Also, The source code is available for the Bursting Designer upon request. Note: You cannot sell, copyright, distribute or reproduce the software without the express written consent of Ike Wiggins.

BI Publisher ToolBox

Ever wished that using the Oracle BI Publisher APIs could be easier to use? Ever wanted a single-click template load application for EBS Template Builder? Ever wanted to be able to test your reports with a single click? Look no further! These features and many more are provided in the BI Publisher ToolBox, a free configuration management tool I created for Oracle BI Publisher .

The BI Publisher ToolBox is the predecessor to the BIPublisherIDE. It's another application, I've written to help supplement BI Publisher Development. To learn more about how this free utility can make running Oracle BI Publisher reporting components incredibly easy click the link below. OTN Tech Article: Getting Started with BI Publisher ToolBox

Like the other applications I've written, the Toolbox is a 100% java. If you don't have JRE 1.6 installed you need to do so (click here).

Oracle Open World

Open World is a blast!
It has been great putting names with faces! Met up with fellow bippers yesterday: Cory Keogh (from Gallup and fellow presenter), Pradeep Sharma, Noelle Bartlam, Klaus Fabian, Leslie, Osama Elkady and Mike Donahue. Fun crowd!

A little known fact about the infamous Klaus. Ever heard of a pink elephant gift? Klaus has taken this to a whole new level with the pink iPhone. Rumor has it Klaus got a very good deal on this phone, from a disgruntled husband who got his wife’s pink phone. Classic example of checking your pride at the door or a possible lack of shame; regardless Klaus mantra, it’s all about the technology, pink it’s the color of passion! Correction: It's a pink razor.

Also, met up briefly with Gareth Roberts. He's working the press scene here at Open World and answering BIP questions. If you get a chance, say hi but I warn you everything is on the record :-)

Wondering what’s new with Oracle Business Intelligence Publisher for Oracle Applications? If you get a chance check out Noelle Bartlam session, she’ll give you the low down on how to use BIP across platforms. Check it out! The reporting platform for Applications: Oracle Business Intelligence Publisher (S309970). The session covers implementations of Oracle Business Intelligence Publisher in Oracle E-Business Suite; Oracle’s Siebel Customer Relationship Management, PeopleSoft, and JD Edwards products; and other Oracle Applications.

What’s hot? How about the HTTP Analyzer! Don’t know about this hidden gem in JDeveloper11g? It’s a great tool that allows developers to easily create and test existing web services. If you ever wanted to test out the web service or build one from scratch this is the ticket! Went to the "Hands-on Lab: Web Services Development and Consumption with Oracle JDeveloper" and did three different exercises in 45 min. I walked away with a really good understanding of the tool. If you’re at open world, I would highly recommend this session or similar sessions. If you not at open world or would like to take a quick tutorial click here

BI Publisher: Bursting Designer

What is the Bursting Designer?

The bursting designer is a new application that allows developers to build, run and test burst control files (see Figure 1). Developers of all skill levels can now implement bursting easily!

Why use the Bursting Designer?

The Bursting Designer will enhance your productivity significantly and make your bursting development easier. Below is a high-level list of benefits the tool provides:

• Minimal knowledge is needed to build a configuration
• Build a burst control file in minutes
• Preview a burst control file as it's being built (See Figure 2)
  
• Test the burst control file (See Figure 3)
• Review/Analyze your delivery channels
• View errors or troubleshoot misconfiguration
• It's free!

Getting Started

Below is a list of video tutorials. It's recommend that developers of all skill levels review each video before trying to create a control file. This is especially true for rookie's. The video's are fast paced so you may need to pause or replay them to get the full value. This is especially true for the Bursting Designer Features.

Video Tutorials - Quicktime Required
Bursting Designer Features
Bursting Designer Lesson 1 - What is Bursting
Bursting Designer Lesson 2 - Creating a Burst Control File

Bursting Designer Download Site
Bursting Designer - By clicking on the download link you will be redirected to a different site. The bursting designer is java webstart application. Java 6 is required to run this application. It's recommended to bookmark this particular blog and the download site. This is because you will need to use these links every time you wish to run the Bursting Designer or find news about updates. It's technically not a requirement to use the webstart application for the java savvy developers but in doing so you will miss out on future updates and releases. It's important to note the Bursting Designer jar files will be loaded on your machine. This allows for a significant increase in performance. If you need to remove this application for some reason, go to add/remove applications.

This is a beta release of the Bursting Designer. There are some features that are still being developed below is a list:

• XDO uri support
• Request Removal *Manually edit the burst control file to remove a request
  
• File Browsing for File Delivery and Template Fields

If you would like to request an enhancement please feel free to post a question on this blog. Also, If you would like to participate in the development of the Bursting Designer contact me via e-mail.

Updates/Bug Fixes

• Request Removal is now fixed. Before removing a request make sure it's the selected one, the selected one gets removed.
  

Figure 1:


Figure 2:


Figure 3:



IMPORTANT: If you have questions about the Bursting Designer or if you find a bug please post a question on this blog. If you do not post your comment on this post, I will reject your comment. I've had some complaints in recent months that it's hard to find answers on my blog because of cross post Q&A. If you e-mail me a question about the Bursting Designer be forewarned, I may ask you to post your question on this post.

Also, The source code is available for the Bursting Designer upon request. Note: You cannot sell, copyright, distribute or reproduce the software without the express written consent of Ike Wiggins.

BI Publisher: Advanced Line Wrapping

Over the years, programmatically controlling line wrapping has been a very popular topic! There have been many requests in the BI Publisher forum, this blog, or via e-mail for a solution. Unfortunately, I wasn't able to provide a relatively simple solution until now.

If your new to this issue let me run down some technical scenarios on why line wrapping causes so many headaches!

Examples
My company's bill of ladding only has seven lines for comments. The comment field is stored as one text field in the database. The text field in the database allows for 70 character but the cell on the BOL can only have 50 characters. I need to only print what will fit on 7 lines maximum.

or

I expanded my cell to support 35 characters of text without wrapping but in some rare instances it wraps with 35 character because of the font I'm using.

or

My Swiss Declarations report can only print two lines on page 1 and there are several lines thereafter on the next page I can use. The text when added needs to create a new row (Cell) in the table and simply not add a carriage return in the one cell.

It's difficult to solve any of these examples in a format template. This is because BI Publisher does not have a string tokenizer api or something like a character array. However we do have ways of creating similar frameworks.

In order to solve the line wrapping problem were going to need to be able to do the following:

1. Have a controlled for-loop
2. Know how many times to loop to break-up a string (iterations) into a specified length
3. Storing the string that's going to get broken up

Controlled for-loop

The for loop down below cannot be directly ported to xsl syntax or BI Publisher.

for-loop java

for(int i=1; i<11; i++){
System.out.println("Count is: " + i);
}

We do have some workarounds though for controlled looping. There are some quietly documented features in the development guide and some other examples that can be found on Tim Dexter blog.

foreach_number can be used to control how many times a for-each statement can loop. It's may seem counter intuitive but for-loops in BI Publisher do not require an xml nodes to loop. The following statement is valid in BI Publisher:

for-each:xdoxslt:foreach_number($_XDOCTX,1,3,1)
Note: This will loop 3 times

Iterations

The math for figuring-out how many iterations are needed to break-up a string is pretty easy. Take the string below, if I have a cell length of 20, I can deduce the number of likely characters to fit in that cell by taking the length of the string, divided by the length of the cell. In the text below the length is 107. When we take 107 (text) divided by 20 (cell) equals 5 rounded iterations.

"Ike likes to walk the walk and talk the talk all of the time. End result is very long sentence of text."

Storage

Although it's not commonly used in the BIP world, xsl variables can be created in BI Publisher. XSL variables can be used globally in a template and or they can be used locally in an xsl template. Variables can also be re-assigned. If you were assuming that assignment can happen multiple times, all of the time, your mistaken. Here's the syntax break-down for creating an xsl variable:

Example 1: Create a variable with one value that's reset in a loop
creating a variable: variable@incontext:first_line_text; substring(//LONG_STRING,1,20)
getting the value: $first_line_text (should be "Ike likes to walk")

Misc Example 2: Create a variable with one value that is the length of a field.
creating a variable: variable:field_length; round(string-length(string(//LONG_STRING)))
getting the value: $field_length (should be 170)

Misc Example 3: Create two variables and summing both values
creating a variable: variable: bill_only; //ORDER_LINES[QTY > 10 and LINE_TYPE ='Bill Only' ]/QTY)
creating a variable: variable: ship_only; //ORDER_LINES[QTY > 10 and LINE_TYPE ='Ship Only' ]/QTY)
getting the value: sum($bill_only | $bill_only)

Solution

We now have the basic elements to create a parser utility. The xml text that were going to breaking up is below.
XML Doc

<WRAPPING>
<LONG_STRING>Ike likes to walk the walk and talk the talk all of the time.
End result is very long sentence of text.</LONG_STRING>
<LONG_STRING>This is some other text that will have to get parsed.<LONG_STRING>
<WRAPPING>

Here's the template that will break it up. Below is the code from the format template. This template will be breaking up the text in lengths of 20.

BIP Code:

<?param@begin:cell_length;20?>

<?for-each:WRAPPING/LONG_STRING?>
 <?call-template: string_variables?>
 <?variable@incontext:iterations; ceiling(string-length(string(.)) div $cell_length)?>
 <?$iterations?>
 <?for-each:xdoxslt:foreach_number($_XDOCTX,1,$iterations,1)?>
   <?call-inline-template:string_parser?>
 <?end for-each?>
<?end for-each?>


<?template:string_variables?>
<?xdoxslt:set_variable($_XDOCTX, 'text_string',.)?>
<?end template?>

<?template:string_parser?>
<?variable@incontext:start; ((position()-1)*20)+1?>
<?variable@incontext:first_line_text; substring(xdoxslt:get_variable($_XDOCTX,  'text_string'),1, $cell_length)?>
<?variable@incontext:line_text; substring(xdoxslt:get_variable($_XDOCTX, 'text_string'),$start, $cell_length)?>
<?variable@incontext:last_line_text; substring(xdoxslt:get_variable($_XDOCTX,  'text_string'), $start, $cell_length)?>
<?choose:?>
<? When: position() = 1?>
<?$first_line_text?>(FIRST LINE)
<?end when?>
<? When: position() = last()?>
<?$last_line_text?>(LAST  LINE)
<?end when?>
<?otherwise:?>
<?$line_text?>(REGULAR  LINE)
<?end otherwise?>
<?end choose:?>
<?end template?>

This is the best solution that I could come up. It doesn't account for the fact that specific characters may be bigger or smaller for a particular font (basically characters are not uniform). When implementing this solution be very conservative about how many characters can fit in a cell. I would suggest using the largest character in caps to figure-out the maximum length.

I'll throw this out there too, if anyone has a better or different way of doing it, please send me an e-mail with your solution on I'll post it on my blog with your name.

Also, it's important to note that a data template with a pipelined function can be used to solve line wrapping issues.....

Is there topic you would like me to cover? Post a comment and I'll see what I can do!

BI Publisher: Font Setup & Troubleshooting

Recently, Tim Dexter at oracle has been writing up a storm about barcode fonts implementations. This is an overview on how to do a relatively simple installation of fonts for BI Publisher on a PC. There are really only three steps to setting up a font for BI Publisher. The first step is to install it in windows, the second step is configuring the font for BI Publisher (java) and the third step is troubleshooting the setup font setup.

The following posts from Tim Dexter are useful as well for font setup or config:
How to install a font using XML Publisher Administrator?
I want a default font!
PS Font Support?
Barcoding 101

Installing the new fonts in word
1. Close any open Windows applications, such as Microsoft Word or Microsoft Outlook®.
2. In Control Panel, click Appearance and Themes.
Note: If you are using Control Panel Classic view or Microsoft Windows 2000, double-click Fonts, and then go to step 4.
3. In the task pane, under See Also, click Fonts.
Note: If a folder tree appears instead of the task pane, click Folders on the toolbar, and then under See Also, click Fonts.
4. On the File menu, click Install New Font.
5. In the Drives list, click the drive you want.
6. In the Folders list, double-click the folder that contains the fonts you want to add.
7. Under List of fonts, click the font you want to add, and then click OK.

Installing the new fonts in BI Publisher
1. Locate the JRE for BI Publisher (JAVA_HOME/lib). This can be done by going to Tools -> Options, then click the preview tab.


xdo.cfg

<?xml version="1.0" encoding="UTF-8"?>
<config version="1.0.0" xmlns="http://xmlns.oracle.com/oxp/config/"><!-- Properties -->
<properties>
<!-- System level properties    -->
<property name="system-temp-dir">c:\tmp</property>
<!-- PDF compression
<property name="pdf-compression">true</property>     -->
<!-- PDF Security
<property name="pdf-security">true</property>
<property name="pdf-open-password">user</property>
<property name="pdf-permissions-password">owner</property>
<property name="pdf-no-printing">true</property>
<property name="pdf-no-changing-the-document">true</property>     -->
</properties>
<!-- Font setting -->
<fonts>
<!-- Font setting (for FO to PDF etc...) -->
<font family="Arial" style="normal" weight="normal">
<truetype path="/fonts/Arial.ttf"/>
</font>
<font family="Default" style="normal" weight="normal">
<truetype path="/fonts/ALBANWTJ.ttf"/>
</font>

<font family="BC 3of9" style="normal" weight="normal">
<truetype path="C:\Program Files\Java\jre6\lib\fonts\B39R00.ttf"/>
</font>

<!--Font substitute setting (for PDFForm filling etc...) -->
<font-substitute name="MSGothic">
<truetype path="/fonts/msgothic.ttc" ttcno="0"/>
</font-substitute>
</fonts>
</config>

3. Configure the xdodebug.cfg. This is the standard debug configuration file for BI Publisher. xdodebug.cfg allows you to set the BI Publisher debugging level and to specify the directory where the log file should be written.

This is an example of what should be in the xdodebug.cfg
LogLevel=STATEMENT
LogDir=c:\tmp

If you would like to know more about xdodebug.cfg file it’s available in the Oracle BI Publisher documentation or see the following metalink Note:364547.1. Also, the following posts are good:

BI Publisher: xdodebug.cfg troubleshooting
Get Logging

Troubleshooting the font setup
Step 1: When you run your template and you export to rtf, the specified font should show up correctly. If the font does not show up correctly it's because of the following issues:

A. You may need to do barcode implementation
B. The font is not configured *Most likely the issue
Step 2: Run the template again, but select the output format for PDF. If the barcode font does not show up it could be caused by the followings issues:

A. Verify the font is the fonts directory under the JAVA_HOME/lib/fonts
B. Verify the path is correct. I always use the absolute path to the font.
Example: C:\Program Files\Java\jre6\lib\fonts\B39R00.ttf
C. Verify the font is being found by BI Publisher. In the example above for the xdodebug.cfg we set the log directory to C:\tmp. Navigate to the tmp directory and open the xdo.log file. Locate any errors that might be present.

Example error:
[072909_090808562][oracle.apps.xdo.common.font.FontFactory$FontDef][ERROR] Failed to open TrueType font: /fonts/B39R00.ttf(0)

Example fix:
[072909_091211224][oracle.apps.xdo.common.font.FontFactory$FontDef][STATEMENT] TrueType font created: C:\Program Files\Java\jre6\lib\fonts\B39R00.ttf(0)

BI Publisher: Conditionally Limiting Rows on a Page

Recently, I've been getting quite a few questions about row manipulation. Besides the solution posted here there are two really good examples for developers to look at that Tim Dexter at oracle created.

Tim D. Examples

• Across not Down!
• Multi Column Row Woes

Question

Hello Iceman,

I came across your posts on internet. I have trouble with a BI publisher report presentation and was hoping to get a quick answer from you.

I have a query result (4 simple columns) that could result records anywhere between 10 to 70. There is no grouping in this query and it is flat. I have a requirement to show 15 records in each page. Since, I have used syntax, it is showing about 22 records per page in the pdf report.

Is there a parameter in for-each element to limit the number of rows per page in the report?

Thanks in advance,
Srinath Sura

The question that Sirnath posed is quite common. It's not atypical for a business to specify the amount of lines they want to be displayed on a customer facing document (invoice, packing slip, etc). Fortunately, Srinath is not being asked to account for the number of printed lines. That gets tricky because we would have to account for line wrapping. The solution here does not account for line wrapping. If their is interest in seeing how one would go about doing that, post a comment.

In order to tackle Srinath problem were going to have to use two bip functions: position() and mod.

For those of you new to xpath or xsl, position() is the current position in a for-each loop. For each iteration the position is incremented by one automatically. Typically, position does not need to be used in most Xpath operations.

Modulus is a basic mathematical function that divides a number and returns the reminder. It's usefullness is not strictly limited to mathematical applications. As an example, if you have ever written program that creates a Gregorian calendar you have used modulus. Anyways, it's also a valuable function in bip as we will see shortly.

Pseudo Code Solution

If position() mod 15 = 0 then
  Use bip section break
End if

BIP Solution

<?for-each@section:DEPT?> <?//NAME?>
<?../DEPT_NAME?>
<?for-each:EMPLOYEE?>
  Name:  <?ENAME?>   Salary: <?SAL?>
  <?if: position() mod 3 = 0?>
      PAGE BREAK APPLIED
      <?split-by-page-break:?>
  <?end if?>
<?end for-each?>
<?end for-each?>

Pretty simple solution huh! All we have to do is test if the position is divisible by 15, if it's not then we do nothing, otherwise, we do a page-break.

Download the limiting 3 rows per page example

Up and coming posts: Webservices Mayhem

BI Publisher: Reports6i to BIP Conversion Utilities

Currently, many organizations and developers would really like to make the leap to BI Publisher. One potential option to ease into the BIP tool set is to convert some relatively simple reports6i reports.

There are two ways to do the conversion. Manually do it (see links below) or use the BIP Conversion Utilities.

Oracle OBIEE Documentation
RDFtoBIP.zipped

Doing it manually without a GUI is a good option but it's more time consuming because there is less automation and it doesn't really tell you where your at in the conversion process. Using the BIP conversion utilities is really the way to go though because of it's simplicity. It reminds me of quote by Walter Chrysler

"Whenever there is a hard job to be done, I assign it to a lazy man; he is sure to find an easy way of doing it."

It's refreshingly easy to do the conversion using the BIP Conversion Utilities and extremely fast. Here are the main advantages of using them:

1. BIP technical expertise is not required for the conversion
2. Complete automation from start to finish

Download the tutorial video (Quicktime)

Download the code

*By downloading the software you agree that you will not sell or license the BIP conversion Utilities. It's free software for everyone to use capiche.

InstructionsStep 1 : Extract the zip
Step 2: Navigate to directory to the bin directory (ReportConversion->bin)
Step 3: Copy the batch the dos batch file to the directory where you will be converting your reports6i binary to xml. Double click to run.
Step 4: Run the ReportConversion.exe (ReportsConversion->bin, do not move the executable)
Step 5. Click the folder icon
Step 6. Navigate to your conversion directory. Select the files named .rdf.xml
Step 7. Click process button.

Report Conversion FAQ

Q. I need to have reports9i installed on the apps server in order to do the conversion.
A. This is the number one misconception about the conversion process. I have heard a lot of developers say they don't have 10g setup in there apps server environment, so they will have to wait until release 12. In reality all a developer needs to do is install forms and reports 10g locally on there PC. Also, it's possible to have multiple versions oracle forms and reports installed on a PC, just be sure to create a new home for each installation.

Q. The conversion has to take place in unix; it must be done from the command line.
A. Because BIP is written in java it allows the conversion APIs to run anywhere there is a JVM. To resolve this issue you need to grab the BIP jar files. The BIP jar files are included with the conversion utility so there is no need to worry.

Q. The reports6i Binary to XML conversion is a manual process and I have to manually key everything into rwconverter.exe.
A. A dos batch or shell script can be written to accomplish this task. A dos batch script is included with the code.

Q. The conversion is time consuming.
A. It's remarkable fast. The conversion utility is running so fast (30 seconds) that it was very difficult to add an voice over for each clip step in the process. As a matter of fact, the video was extended by nearly a minute to allow for a voice over.

Q. The conversion is flawless.
A. Definitely not. The conversion has some known issues: format triggers, bind variables, etc that don't port (there is a log that can be reviewed after the conversion)! You can find more about those issues on this blog, Tim Dexter blog at oracle or if your feeling lucky you can convert your reports and see for yourself. The utility does a really good job but there is no magic bullet.

BI Publisher: Charts tips and tricks

The purpose of this post is a tips and tricks guide that explains effective and efficient ways of designing custom charts with BIP and BI Beans. When possible it's always preferable to use Oracle Charting Wizard in the Word plug-in. However if their is a requirement to do conditional logic or dynamic logic, the charting wizard was not designed for this. In this case you would have to manually manipulate the chart xml.

Before getting started on manually manipulating a chart xml file your going to needs some tools and resources.

Tools and Resources:

1. Use an XML Editor. Do not use Microsoft word to edit the chart xml. When you edit it in MSword your much more likely to make syntax errors, and there is no validation that your xml is syntactically correct. I highly recommend XML Pad as editor: http://www.wmhelp.com/

2. Get the chart DTD from oracle. While an XSD is eaiser to use than DTD, it's still better than the alternative which is nothing. The charting DTD is an excellent resource that gets passed over by most developers. Below are two links which should be helpful in your charting endeavors. The first one is Oracle Reports Graphing Faq and the second is the DTD. Note: You will need to copy the dtd and paste it in a file to use it with XML Pad.

I highly recommend reading the Oracle Reports Technical FAQ for graphs. This document is a hidden gem for setting and creating particular properties of a graph. As an example, I was able to find an obscure information like getting a reference line in a graph. I wouldn't have even been able to guess that this was an option by looking at the graph.dtd

http://www.oracle.com/technology/products/reports/htdocs/faq/Graph_FAQ_with_style.html
http://www.oracle.com/technology/products/reports/htdocs/getstart/whitepapers/graphdtd/graph_dtd_technote_2.html

When developers look at the graphing DTD, it seems like it's an impossible document to read and understand. In reality their is no need to understand the DTD. The real purpose of DTD is to assign it to an XML document. When it's assigned your xml editor will validate to make sure your creating a valid structure. The editor will also highlight available attributes for the applicable xml elements. This is one of the most important features of assigning the DTD because the heavy lifting is done automatically for you ie: the editor determines the required elements and attributes.

To assign a DTD in XML Pad go to the menu: XML->Assign Schema/DTD. See that wasn't to bad now was it! Now you should be good to go. Below is a screen shot (sorry for the blurriness, blame blogger, click it to view the whole image) . Notice to the left side of the screen there is a list of properties available. This is the benefit of assigning a DTD. When I clicked on a series line I was able to see all of the available attributes. It gets better though, when I clicked on the markerShape field, a drop down was provided with all the available shapes. Nice...





3. Use Oracle’s template viewer. This tool is another hidden gem and it’s extremely useful for debugging your format template charting issues. The tool can be found in the bi publisher directory in your program files. When running the template viewer be sure to change the “log (debug) level” on the Settings Tab to “STATEMENT” before running the template. As an example, I was able to figure-out a string comparison was being done to a number and the xml element could not be found because it was being treated as a literal string.

Caused by: java.lang.NumberFormatException: For input string: ".//P_ACTIVE_ENGAGEMENT_RATE"

Also, did you know that the template viewer is capable of running in any environment that has the jre? Below is an example the template viewer on my mac. All one would need to do is use the Mac OS X jar bundler and you can have this baby up and running in no time....perhaps a topic for another post :-)



Questions and Answers

This section contains some of the most common questions I have been asked. I tried to put in actual examples so it makes more sense. If you would like to ask a charting question please leave a comment and I'll see what I can do.

Q. Can I do if statements in charts.
A. Yes, remember this is xsl and you have to use that syntax over bip syntax.

Q. Can I do choose statements in charts.
A. Yes, remember this is xsl and you have to use that syntax over bip syntax. (See Example 1)

Q. Can I use xsl variables.
A. Yes (See Example 1)

Q. Do I have to use for-each-group for row data.
A. No . This is a common mistake that people think they have to group the data always. The only reason to group the data would be if you need to aggregate it. If your data is aggregated or you can use xpath with the sum function then the answer is always no. (See Example 1)

Example 1: Get the current month sales, using an xsl variable and default it to zero if there is no data for the month.
<RowData> <xsl:for-each select="MONTHS"> <xsl:sort select="current()/MONTH_ORDER" data-type="number" order="descending"> <Cell> <xsl:choose> <xsl:when test="sum($SALES[../../R_MY=current()/MM_YYYY]/amt) > 0"> <xsl:value-of select="sum ($SALES[../../R_MY=current()MM_YYYY]/amt)"> <xsl:when> <xsl:otherwise>0<xsl:otherwise> <xsl:choose> <Cell> <xsl:for-each> <RowData>

Q. If statements are not evaluating correctly or just plain not working.
A. It's likely the if statement may be evaluating the value as string and not number. To solve this use number() (See Example 2)

Q. If statements fail when I use in less than or greater than signs.
A. Use & lt; or & gt; (See Example 2)

Q. My chart keeps failing because the xml field is being recognized as literal.
A. Use the squiggles { } to avoid this issue. (See Example 2)

Example 2: If statement, number check, avoiding literal interpretations
<xsl:if test="number(//P_ACTIVE_ENGAGEMENT_RATE) > number(0)"> <Y1ReferenceLine> <ReferenceLine index="0" visible="true" lineWidth="2" text="Default Active Engagement Rate" value="{.//P_ACTIVE_ENGAGEMENT_RATE}" displayedInLegend="true" lineColor="#ff00"> </Y1ReferenceLine> </xsl:if>

Q. How do I mix static text with dynamic text without using append.
A. This is actually surprisingly simple. Create a “some text like this for {$YOUR_VAR or YOUR_XML_NODE} " (See Example 3)

Example 3: Dynamic substitution text
<Title text="Monthly Sales - {$CURRENT_MONTH_NAME}" visible="true" horizontalAlignment="CENTER"> <LocalGridData colCount="1" rowCount="5"><RowLabels>

Q. Can I use reports1og to create a chart.
A. Yes you can use repors10g to setup your graph. There is one caveat; it’s not runnable in BIP. However, it’s a great starting point for complex charting were the bip wizard can be sometimes lacking. When you’re done creating your mockup, save the report as xml file. You can rip out the nodes to figure-out what you need to do in chart in BIP.

This all I got for now. Like I said before if you have a question don't hesitate to leave a comment!

Ike

BI Publisher: Data Templates vs. Reports6i

Top 5 Reasons to use Data Templates over Reports6i

To start off there are more than 5 reasons!

1. Displaying a reports in real-time
2. Advanced PL/SQL
3. Easy of use
4. Multiple Integration Points
5. No Binary Conversions
6. Used across multiple platforms
7. Integration with multiple data sources
8. Excellent debugging facilities
9. Eliminates Ambiguous Crashing (who doesn't love terminated by signal 11)

I forgot to mention a couple of other benefit about data templates vs reports6i that we haven't ever discussed. Data templates are the only way a developer can generate the xml in real-time in the eBusiness suite or locally on there pc. In comparison, a developer always has to submit a concurrent request in order to generate an xml file with reports6i.

Other benefits of using data templates would be that the logic has to be put into a pl/sql package, whice essentially makes the business logic globally available to all other objects in the database. This provides an amazing code reduction. Developers can create other data templates (that are similar but different) and reuse existing business logic.

Ike