Wednesday, April 18, 2007

Get your BIP on

Looks like the standalone release of Oracle BI Publisher - which you need when using the product as the PDF rendering engine for Oracle Application Express - is nearing production:

Of course you can already get it today from the BIEE10.1.3.2 installer, which is available for download on OTN:

I found this BLOG entry particular useful, because it points out the exact version of the Java SDK required, and provides other useful links and information on how to set this up.

Friday, April 13, 2007

Report Queries and Session State

With Oracle Application Express 3.0 we added the ability to print report regions as PDF documents (or export to Word or Excel). Setting this up is fairly straightforward, you just go to the report attributes page or region definition page, and there you'll find a new tab to set up printing attributes. All you have to do is change the "Enable Report Print" attribute to "yes", run your page and click on the print link to download your document, provided of course you or your system administrator has already configured a rendering engine, such as BI Publisher.

Not as obvious or well known is "the other printing feature", called report queries, which I had already talked about in my previous posting about PDF & charts. I think it's worth taking another look at this option. The main difference between the two printing options is that with report region printing, you need to have a report region defined, and of course, that also requires having a page defined. But often users just want to integrate a number PDF reports, based on real-time data, with their application. That's where report queries come in, they can be easily integrated with existing applications, just by calling them through standard APEX buttons, page branches, tabs, list items, or any other navigational component, that allows using a URL as the link target.

Besides being available as a stand-alone component, report queries also make it very easy to associate RTF report layouts with them, for maximal customizability of your report documents. The create report query wizard helps setting this up, here's a brief over of the steps involved:

- Go to Shared Components / Report Queries
- Click on Create
- Enter your query, click next
- Test your query to ensure you're getting results back
- If you used bind variables, enter test values, those values will be used throughout the wizard
- Optionally include session state of page or application items (more about this later)
- Download your report data as an XML file
- Open that file in the BI Publisher Template Builder Word plug-in
- Use the plug-in to design your report layout
- Save the finished layout as an RTF file
- Load the RTF file into your APEX wizard and click next
- Test the output, and if satisfied, use the URL shown to integrate with your APEX application

With this technique, you can create pretty much any report layout. You can include images, like company logos, you can define grouping and break columns, you can include charts (see previous posting), you can show sums for numeric columns, etc. The main advantage of using this in Oracle Application Express though is that you can actually include session state of page items and application items as I mentioned above.

So what does that mean and why would you want to include session state? It means that you're not limited to your query result-set in what data you can include in your PDF documents. In fact, you're not even limited to what's stored in your database tables. You can actually include data from your current session. So if you have a classical master-detail form, like an order and order item form, you could include the current session values of your page items that hold your order (master) data as well as the report containing your detail order items. You can also use Application Express to fill out standard forms, like e.g. a W-2 tax form, and print out the completed form in PDF. We created a sample application that's doing exactly that, you can try out the application here:

And download it form here:

Once you have downloaded the application and installed it, go to shared components, and take a look at report layouts. There you'll find the W-2 RTF document, which was created in Word. This document illustrates how you can include page and application items as well as report rows with your PDF documents.

Now one final tip: if you're looking at the PDF demo application, you will see that on page 3 (the form page), we did not use a button that simply redirects to the report query URL. Instead we used a standard submit button, and then we used a branch pointing to page 0, specifying the request string as print_report=[report name]. This ensures that the data actually gets written to session state before rendering the PDF. This is important, because otherwise you would print the PDF with the data that has previously been written to session state and not what you see on your screen. And in pointing to page 0 with this special "print-report=" request, we make sure that the user doesn't actually leave the page, but instead gets file open / download dialog to view the finished PDF document.

Wednesday, April 11, 2007

Oracle Develop hits the road

This spring, Oracle Develop—the premier conference for developers—is coming to a city near you. Don't miss it. You'll experience two days of expert-led, in-depth technical sessions, hands-on labs, advanced how-tos, and detailed tutorials.

Oracle Develop

I'll be presenting Oracle Application Express 3.0 New Features and Oracle SQL Developer at the events in Munich, Prague and London. I'll be also doing an
Application Express 3.0 hands-on lab.

June 18-19, 2007 | Munich, Arabella Sheraton
June 21-22, 2007 | Prague, Prague Conference Center
June 26-27, 2007 | London, ExCeL

If you have a chance to attend any one of those events, I'm looking forward to meeting you there.

Including Charts in PDF documents

One question that is often asked about the new PDF printing feature in Oracle Application Express 3.0 (APEX) is whether it is possible to include charts in the PDF document. And the answer is yes, it's actually quite easy to implement using the BI Publisher template builder plug-in for MS Word (and BI Publisher as the PDF rendering engine for APEX). It's important to understand however, that this is not a "printer-friendly" version of your web page. So if you have e.g. a Flash chart region and a report region on your page, you can't simply print this as PDF. What the PDF printing feature in APEX does is, it generates XML data of your report result-set and using an XSL-FO report layout (or RTF template), the XML data is transformed into PDF (or other supported output formats). Which is then send back to the browser for download.

So in order to create a PDF document that contains e.g. a chart and report, you need to create a report query and associate that report query with an RTF based report layout. Report queries can be found under Shared Components.

I created a simple demo application, which can be found here:

In this sample I created a report region on the EMP table and a pie chart using the new APEX 3.0 Flash charts. The chart shows the sums of the salary column for each job title. I then created a report query, using the report query create wizard under Shared Components. I entered the same query that I used for the reports region. I tested the query and proceeded to the page that lets me download the XML data of my report. I downloaded the file and opened it in the BI Publisher template builder plug-in for MS Word.

You can fine more information on the template builder and BI Publisher in general here:

Using the template builder, I created a chart and report definition and saved this as an RTF file, which I then uploaded back into Application Express. The RTF report layout can be downloaded on the report page of my demo application, there's a button called "Download RTF report layout" next to the Print button.

After completing the wizard, I copied the print URL and made this URL the target for my "Print Chart and Report" button. Now when I press this button, I'm getting a PDF document, which is quite similar to my web page, it includes a pie chart and the report. Of course there are differences, but a PDF document is intended for printing, and a web page is intended for being viewed in a browser. So some differences are to be expected.

... getting started

Time to get started with my own BLOG on Oracle Application Express. Of course I’ve been wanting to do this for a long time, but never quite got around to it. With Oracle Application Express 3.0 available now though, I’d like to do my part in helping the APEX community make the most out of all the new features. And after I found myself answering many APEX 3.0 related questions on our OTN forum, in particular regarding the new PDF printing feature, I figured this would be a good place to collect some of these answers, tips and tricks.