Email Distribution of Reports using BI Publisher and Discoverer

One of the presentations I'm giving on the Monday at the UKOUG Conference is a joint talk with Mike Durran, the Oracle Discoverer Product Manager. Mike is going to do an update on Discoverer migration and interoperability with Oracle BI Suite Enterprise Edition, and I'm going to show off one of the first fruits of this program, the ability for Discoverer worksheets to be used as a data source for Oracle BI Publisher. Mike and I discussed this feature, and we thought one of the best ways of showing off what this feature can do would be to do something Discoverer historically couldn't do - schedule reports and distribute them via email. This sort of functionality is built-in to BI Publisher, so whilst Mike's been putting the slides together, I've put together a demo to show off this integration.

The process starts off with a regular Discoverer worksheet, in this case from the Videostore Video Tutorial Workbook. Discoverer is at version 10.1.2.2.0 whilst BI Publisher is at 10.1.3.3 - see this previous posting on getting everything set up for Discoverer / BI Publisher integration.

Then it's a case of starting up Microsoft Word, connecting to BI Publisher Enterprise as an OID user that has access to both Discoverer and BI Publisher, and then selecting the Discoverer workbook from the "New Template" dialog.

Once that's done, the BI Publisher Desktop add-in grabs a sample dataset and presents me with a blank template to start working on. I use the Chart Wizard to create a simple bar chart using "Profit Sum" as the measure, "Deparment" as the label and "Year" as the series, like this:

Then I create a crosstab to accompany it, add a title and some commentary text, and drag and drop a logo onto the top right-hand part of the page, like this:

Now if I'm going to display this template in HTML format (as opposed to say, PDF) I need to make sure this image is available on a web server somewhere, so I copy it to the following location:

$ORACLE_BI_HOME\j2ee\home\applications\xmlpserver\xmlpserver\obi_logo.jpg

which makes it available at the URL:

http://winxpvm:7781/xmlpserver/obi_logo.jpg

and I double-click on the image in the Word document, select "Web" from the "Properties" tab, and enter the following code:

I then save the template as an RTF file, and preview it in HTML form. Everything looks fine.

Then, I use the BI Publisher Desktop menu to upload the template to the BI Publisher Enterprise server, and it's now ready to run within Enterprise. Before I do that though, I log in as an administrator and set up the Scheduler. This involves creating a database schema then passing the connection details to the Scheduler setup wizard, which then creates all the scheduler tables for me.

After I restart BI Publisher so that the new settings can take effect, I go back in as the administrator and set up email delivery. For the purposes of the demo I'm using a freeware email server that installs on Windows called ArgoSoft Mail Server which lets me demo email integration without an internet connection; I enter the connection details into the BI Publisher Enterprise web page and my email connection is set up.

Now it's a case of logging back in to BI Publisher Enterprise as the report owner, locating the report and then clicking on the link to schedule it.

This brings up a screen where I can set the frequency of report runs, provide an email address for the report to be sent to, and provide any other details relating to the running of the report and the different destinations it can go to, including FTP, WebDav and the filesystem.

Once the scheduled job is submitted, I can check back on the schedule details and see that the report has run correctly:

and then finally, I can start up my email application and see that the report has been delivered.

As well as BI-style reports, BI Publisher can also produce reports in any format, including mail-merge letters, a feature that's often required by customers migrating off tools like Cognos Impromptu. Overall, this is a useful feature for customers who've got a lot of investment in Discoverer reports and metadata, but want a bit more flexibility in the way they can use the data.