Monday, July 28, 2008

Bursting with Web Services

Background

I come from an Oracle Forms 6i background (hardcore PL/SQL only). I'm now exploring a whole new development world using Oracle Application Express (APEX). In order to make APEX dance and sing, from a reporting perspective, you really need BI Publisher.

My requirement was to get users in my APEX application bulk printing and emailing documents seamlessly. One solution was to generate my documents as PDFs and store them as BLOBs in the database. There are APEX APIs that allow you to do this. This works fine if a user wants to print a document on an ad-hoc basis. They can just open the PDF in their browser and print it in the normal way. The problem is that I needed to both bulk print and bulk email documents. These documents needed different templates for each result of the data set so BI Publisher seemed the best way to go.

Now there are ways and means of doing this using JAVA and the BIP APIs but, I don't know Java. Sadly, I didn't have time to learn it to the requisite level in time for my project deadline so I needed to find a different way to skin this particular cat.

The beauty of IT is that there's always another way...

The first thing I needed to do was to get APEX communicating with my BIP instance. After some trawling through the forums I came across this excellent article by Tyler Muth. This gave me the starter I needed. It shows you how to get APEX communicating with BIP via Web Services.

My call to the BIP Web Service passes over a unique identifier. This is used in a temporary table in the database that I pre-populate from within my APEX application. This then gives my BIP Data Template query, amongst other things, a Key column with one or more unique IDs of the documents I want to print or email.

Within the BIP report definition I also set up several customer-specific report layout templates. Utilising the Bursting facility in BIP I can now split the reports by a Key and also pick up different templates on the fly as appropriate.

I got a printer in the office configured on C.U.P.S. Once this was set up in BIP Admin as a Print Server, it was simple enough to configure the Bursting query to send the output to the printer. Reading through the documentation (always a good idea) it also seems that it's simple enough to configure BIP to burst to file. Something that again, until I found this Web Service method, I thought I couldn't achieve without a good handle on Java.

My last requirement was to get my documents display a logo which was picked up from the database. My logos are stored as BLOBs in the database and I needed a way to get these out into the XML that BIP uses. Again, a trawl of the forums led me to the "Using Dynamic Images in PDF reports" guide on Marc Sewtz's BLOG. My post on the APEX forum highlights a subtle difference in the implementation of Marc's guide depending on how you want to use the image in your reports.

Getting the report to BURST using the Web Service.

I tested the bursting of the report from within BIP by manually Scheduling the report to run and it was working fine. I then set about configuring the SOAP request to call the report and let the bursting/delivery be handled by BIP.

Looking at the SOAP request I saw it had a deliveryRequest element. I figured that, as BIP was handling the delivery, I no longer needed the deliveryRequest in my SOAP call.

WRONG!!

I found that I needed the deliveryRequest element in order for the SOAP call to be syntactically correct. Failure to include one meant that the Web Service would fail with an error stating...

oracle.apps.xdo.webservice.exception.InvalidParametersException: PublicReportService::generateScheduledReport Failure: DeliveryRequest is EMPTY

The solution?

Keep the element in your SOAP call. If you have your report configured for Bursting and have Bursting 'Enabled' then this will override whatever Delivery Request you make in your SOAP call to BIP.

What's next?

I will shortly be testing to see if I can use CASE statements in the Bursting query to handle Email and Printer delivery from one report definition.

I will also be trying to get the report to run without the need for passing a user name and password over to BIP via the web service. My report is residing in the GUEST folder but, at the moment, I seem to need to pass a UN & PW for the web service to work.

When time permits, I intend to revisit the PDF merge facility of the BIP APIs.

Summary

So, there you have it! The Java-Agnostic way of getting BIP to do it's thing using Web Services.

5 comments:

Unknown said...

Good post. It's encouraging to see Forms6i veterans move on to the "new" age tools successfully. I plan to transition from an Oracle Reports background to BIP soon.

If you have had any exposure to Oracle Reports, you would know that to run Reports interactively, the default parameter form presented by the product is rather lacking. Typically, you need to create a parameter form application which used to be done with Forms.

Have you done anything similar on APEX? The idea is quite simple, store reports meta-data (name/number, parameters, default values etc.) and present a parameter form with a Run Report button which constructs the Reports URL and launches it.

How difficult is that to do in APEX?

Great blog, appreciate your sharing with the community.

Manish

Simon Gadd said...

Hi Manish.

Yep Reports 6i was a dog.

Dimitri Gielis has posted a great article on simply calling a BIP report via a URL in APEX. For simply running ad-hoc report requests through the UI this is a great way to go. http://tinyurl.com/ytpsbn

BIP can do quite a lot that R6i couldn't. BIP will allow you to change template on-the-fly which never seemed possible with R6i.

Regards

Simon

Anonymous said...

Nice post!

Simon Gadd said...

Thanks Tyler.

Ranjai Banerji said...

Hi,
Nice Blog! I saw your post here http://kr.forums.oracle.com/forums/thread.jspa?threadID=673101. Were you able to get the WSDL from http://xx.xxx.x.xx:9704/xmlpserver/services/PublicReportService?wsdl ? Or did you get the WSDL from another source?
Thanks,
Ranjai