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:

http://apex.oracle.com/pls/otn/f?p=31057

And download it form here:

http://www.oracle.com/technology/products/database/application_express/html/3.0_new_features.html

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.