Creating a Mail Merge with BI Publisher in Apex

Here is a simple instruction set to get you started on creating an Apex page that interfaces with BI Publisher to produce what's essentially a Mail Merge.

My presumption is you've already visited OTN to download the Oracle database software and desktop Add-In for Microsoft Word.

Step 1) Create a page to call the report.

I created a simple page that held my parameters for my query.

The only special part of this page is a branch to page zero that contained the request naming my Report Query:
PRINT_REPORT=Mail%20Merge

It would probably be better practice to use an underscore in lieu of a space, but I was curious in this case to see how it behaved.

Step 2) Create a Report Query

This step is really formed of a number of key sub steps.

a) Start the wizard
- Report query name : this is the name that forms part of your request string. I used "Mail Merge".
- Output format : PDF
- View file as : Attachment
- Session State : I chose to check this. Information supplied in your XML can be ignored.

You are then prompted for your SQL Query. I used:
SELECT name, address1 st, address2 suburb, state, postcode
FROM organisations
WHERE address1 IS NOT NULL
AND state = :P2_STATE

You should now be at a point where you can download an XML definition of your report.

Save this .xml file for the next step.

b) Create your Word document
- Leaving your Apex window sitting happily where it is, open up Microsoft Word (which has your Add-In installed)
- Select Add-Ins Oracle BI Publisher -> Data -> Load Sample XML Data
- Select your .xml file, Word will let you know the load was successful.

 

- You can then add all the fields to your blank document via Oracle BI Publisher -> Insert -> All Fields

- By default, this won't look overly pretty

- Using standard Microsoft Word editing, I shifted the fields around to resemble a standard letter
- The critical task here is to add a page break within the "for-each-region" field. This ensures each letter starts on a new page.
- I also chose to retain the application name/parameter fields on the first page as a control for the document. These fields are automatically available if you check the "Include application and session information" check-box as you create the Report Query definition.
- Save this Word document as Rich Text Format (.rtf)

c) Upload document as Report Layout
- Back in your Apex Report Query wizard, the next step allows you to define the Report Layout Source as a file based report layout.

- Confirm creation of your Report Query
Here you are presented with a URL target for use in your application. Note here the usage of page zero as the target. In the URL target for our submit button, all we needed to define was the PRINT_REPORT request:
f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=Mail%20Merge

How easy is that? We have a BI Publisher report linked to Apex in two steps! He he

Step 3) Iteratively modify your Word document
None of us will get the .rtf definition perfect on the first go, we may need a few iterations of this, so there's a couple of things to note here. Once we've created our Report Query, a Report Layout is subsequently added.
Unfortunately from this Report Layout page, we can only download the .rtf definition.

If we wish to modify it, we need to delete the Repory Layout, recreate the Report Layout (which is only a two step process - naming it and reloading the file), but we also need to revisit the Report Query and reset the Report Layout selection from "Use Generic Report Layout" back to our Mail Merge.

So my basic sample ultimately looked like this, opened in Adobe Reader:

The content started on the second page, the first page was my control containing session state information.
In this example, the text in black are fields populated via XML, and the text in blue is the fixed content I added.

If you have the pleasure of jumping into an environment with BI Publisher, I hope this helped you get started.