Hey guys. I have been using Oracle BI Publisher for some days now. It is a very powerful tool if you know how to use it. For some days or should I should say months, I have been getting a very irritating error when ever I bursted a report.
The Report would run very nicely, and would give the outputs whenever I click on the view button, but when we go ahead and use the bursting option I used to get the following error,
oracle.apps.xdo.servlet.scheduler.ProcessingException: java.lang.NullPointerException
at oracle.apps.xdo.servlet.scheduler.XDOJob.runBurstingReport(XDOJob.java:2116)
at oracle.apps.xdo.servlet.scheduler.XDOJob.execute(XDOJob.java:358)
at org.quartz.core.JobRunShell.run(JobRunShell.java:195)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
Caused by: java.lang.NullPointerException
at oracle.apps.xdo.servlet.scheduler.XDOJob.runBurstingReport(XDOJob.java:1905)
... 3 more
This is caused because of the incorrect use of the ?for-each-group? in the RTF template.
Lets say, we have the following things,
- A report with, Data Model named Billing. This data model can be of any Type i.e. SQL Query or Data Template etc.
- An RTF template named sampletemp.rtf.
- BI Publisher plugin for MS. Word.
- A little Patience.
What I want to do is write a text "Hello " followed by a field viz. SBF_DP_ID
Create the Data Model in BI Publisher and click on save.
Start MS Word and login to the BI Publisher Server, using the appropriate credentials. Go to the BI Publisher menu and go to Insert> Field . Insert some fields, say for eg. SBF_DP_ID.
Select this inserted field and go to the BI Publisher Menu >Insert>Repeating Group
Here you would see three tabs viz. Properties, Sorting and Advanced.
Inside the Properties tab, you have two fields For Each and group By, both of them are drop downs. Also there are two check boxes provided.
Here comes the important part. When you expand the Dropdown against the For Each field, you will see two options, viz. Billing and BILLING_ROW. You have to select the BILLING_ROW as the repeating For-Each option and not Billing.
Note : The names Billing and BILLING_ROW will appear only if you have named the Data Model as Billing. Incase you have entered another name, the name of the options will be according to the name of your Data model. You have to select the
_ROW option and not the option.
Next, select the Group By field that you would want to group by. In this example SBF_DP_ID since I want my bills to be generated for each DP_ID.
Use the Sorting tab to use sorting on your data (not required if you have taken care of it in the Data model)
And finally click on OK.
Thus you have made a repeating group. You will get a field named F and another field named E. The F stands for the Beginning of the For-Group and E stands for the End of the For Group, i.e. the for each group will be applicable only to the elements between the F and E fields respectively.
Note: The field names 'F' and 'E' can be changed to whatever you want by going in the Advanced tab.
Next, again select the field SBF_DP_ID and go to the BI Publisher Menu>Insert>Repeating group.
This time you will see that the For Each Drop Down is disabled and has a default value Group Item. Here set the Group By option again to SBF_DP_ID (or any other repeating group of your choice). Again set the Sorting options via the Sorting tab.
So Finally you will get the following in the template
F F Hello
SBF_DP_ID E E
Save and Upload the Template and enter the Bursting Query, and select the
Split by and Deliver by elements.
Note : Make sure that the column, that you have selected as the KEY doesnt have any null values. Also the Deliver By Element and KEY should be the same
And cross your fingers!!
Hope this came in handy, for someone. I struggled a lot, and had to do a lot of trial and error in order to find out what was going wrong.
Feel free to contact me if you are still getting the dreaded java.lang.NullPointerException in Oracle BI Publisher Report Bursting .
