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.
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
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.
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.
- 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.
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:
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:
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.