Printer Tray Selection using BIP Web Services (and ApEx)

Earlier on this week I've been delivering some BI Publisher classroom training, with a day at the end for one-to-one sessions with the delegates and some consulting. One of the requirements that came up from the customer was for a report to split the various pages within it to different trays in the printer, so that page one for example went to headed paper, pages two and three went to normal paper and the last page went to specially coloured paper. Their previous solution, using the Microsoft Word mail-merge feature, made this possible through the ability in Word, to specify different page settings for each page in a document, but these page settings are lost when you convert the document to RTF format and upload it to the BI Publisher server.

Another requirement within the same organization was to send reports that are generated directly to the printer, without the intermediate step of displaying them, say, in the Adobe Acrobat browser plug-in. In this case, BI Publisher was due to be invoked from an ApEx application, and whilst they could put a button or a link on the page that when clicked, invoked a BI Publisher report via the user's default Web browser, the user would then have to click on the Adobe Acrobat plug-in's "Print" button, select the print destination and initiate the printing themselves. In their ideal world, the button on the ApEx page would just call out to BI Publisher and have the report sent straight to the printer. Again on the face of it this wasn't going to be easy, as BI Publisher, unlike Oracle Reports, only lets you specify the report format and the report template, not the report destination, when you invoke a report. Also, BI Publisher, unlike Reports, doesn't have a tag or a function that you can include in a report definition to switch printer trays mid-way through the report generation, so again we were at a bit of a loss how to meet these requirements.

Thinking about it for a while though, I did remember that when you schedule a BI Publisher report, you can specify the report destination, the tray if it's a printer, and you can specify a time of "immediate" for the scheduled execution.

Now this looks promising. If we broke the report up into several separate reports, one for each printer tray, and submitted them all at the same, we could select the printer tray to print to and they'd all arrive at more or less the same time, such that the end user wouldn't really know that they had been delivered separately. Moreover, as the report invocation would come from an application, the user would just run the report as normal, in the background several report components to several printer trays would be scheduled, and the user would just think they'd requested a single report that printed to several trays. The only thing missing from this, compared to the Microsoft Word mail-merge approach, is that the report couldn't be sent to the user's "default printer" - BI Publisher has no concept of a default printer, all it can do is either send the report as a PDF to the user's Web Browser, whereapon the user could use the Adobe Acrobat browser plug-in to print to their default printer, or in the solution we were going to use, we'd need to replicate this functionality by maintaining a list of available printers within ApEx and BI Publisher, and allow the user to specify which printer was their "default", ensuring we printed to that one in future.

Following this then, my first thought was to try and invoke the scheduling of a report via a URL, as you can with Oracle Reports. Looking through the documentation though, there's no programmatic way (at least based on my short investigation) to invoke the scheduling of a report, at least through a URL or through the command line. One thing I did remember reading about recently though was a Web Services interface for BI Publisher, which if it was anything like the Web Services interface for OBIEE, would potentially give me a way to schedule a report from a ApEx via a Web Services method invocation. Before I could try this out though, I needed a way to set up a set of "virtual" printers, FTP servers and email servers on my laptop so that I could check that scheduling itself worked correctly. To do this I downloaded and installed Bullzip PDF Printer to create a virtual printer for my laptop, configured Microsoft IIS to provide the access to this virtual printer via a URL, installed FileZilla Server as an FTP server and ArgoSoft Mail Server as a local email server. Once all of these were installed and configured and I proved that I could schedule a report and sent it to these three destinations, I was ready to start working with the BI Publisher Web Services interface.

Now in reality I hit on a bit of luck with the setting up of BI Publisher Web Service access for ApEx as I came across this excellent article by Tyler Muth that documents the whole process. In the article, Tyler uses a tool called soapUI to do the initial Web Service test, which you can use initially to check out the various parameters you send to BI Publisher via this method. Sure enough, the section on invoking a report includes a parameter for the printer tray.

Now unfortunately the one thing I can't do at this stage is actually test it with a printer with multiple print trays, as our printers back in the office only have a single tray and the Bullzip PDF Printer software only simulates a single tray. But if we work on the assumption that the tray parameter in the BI Publisher Scheduler request method actually works, if we can get the Web Service call working this should prove a solution, at least one we can take back to the client in a week or so's time to try and implement.

I used soapUI to put together a proper Web Service request to BI Publisher as a test, requesting that the report be delivered to the email server, FTP server and printer, leaving out the tray parameter at this stage. This is what the request XML looked like:

Executing the Web Service call using soapUI caused the report to "print" via the virtual printer, get delivered as a PDF to the FTP site and emailed to my local email server as an attachment, so it looks like it worked. So now to ApEx to see if I could invoke the report from an application page, again following the instructions on Tyler's blog page.

The first step within ApEx was to set up the Web Service request as a manually-defined Web Service reference reference, which allowed me to add all of the optional parameters around delivery destinations and so forth. The service description URL I determined by taking a look through the WSDL file for the BI Publisher Web Service.

One this was done, the next step was to set up a button on an application page that invoked the web service. In reality, the button on the real customer application will actually need to call several web services, one for each of the report elements, and as this would be a fairly common process it would make sense to create some sort of shared procedure that would take a report name and any report parameters as inputs, and then actually call out to BI Publisher for all of the individual report elements, with that procedure then being tied to a button on a page. For now though I just wanted to check that it worked, so I set up a simple button and process on my ApEx page and told them to invoke the Web Service reference that I just set up.

And it worked. Clicking on the button I created led shortly afterwards to the report arriving via email, via the printer and via the FTP site, and it shouldn't be too tricky to extend the page so that multiple processes, one for each report, get fired off when the button is pressed, or we use a procedure or something like that.

So what we've done here (or potentially, as I haven't yet got my hands on a multi-tray printer to test it out) is a way of taking a report that requires printing to multiple trays, and instead splitting it into one report section per tray and then scheduling it, for immediate delivery, via the BI Publisher Web Services interface. I'm due back there in a week or so's time, so I'll get the chance then to integrate it into their ApEx application and check that the tray support in the Web Services API actually works as advertised. The client is also interested in integrating BI Publisher with Oracle Forms as well, and this other document on OTN sets out how this process works, it's not quite as straightforward as with ApEx but the principal is still the same.