Using application and page items in PDF reports

One of the benefits of the advanced printing capabilities built into Oracle Application Express is that users are able to include application and page level item values in their print documents along with the actual report result-set. These values can be used to filter the report result set when used as bind variables in the where-clause. And they can also be used as additional dynamic data shown along with the report result-set, e.g. dynamic page headers and footers or showing the master record of a master detail form.

A common challenge users face when integrating page level items is that values need to be written to session state first before they can be referenced in the print document. If your print button or link is located on a different page than your page items, this won’t be a problem because when navigating to the print page, your item values get written to session state. But in some scenarios, you have both the page items and your print button or link on the same page, an example would be a search form and report page, where you use your search form to limit your search results, another example would be a form & report page (e.g. master-detail), where you want to fill in some values into the form, and then print the document without navigating to another page.

An easy way around the session state issue is actually writing session state without leaving the page. This can be done by performing a submit action, but then, rather than redirecting to another page (or reloading the current page), you would directly initiate the PDF download. When working with report queries, this can be achieved very easily. Rather then specifying the print URL as the redirect target of a button, you would make the button a submit button, and then define a branch to the pseudo page 0 and use a special request string, referencing your report query.

The syntax for the request string is:

PRINT_REPORT=[your report query name]

So if your report query were called “employees”, then your request string would be:

PRINT_REPORT=employees

Similar to report queries, this technique can also be used when working with report regions. This is particularly helpful when working with search or filter regions and you want the filter to be applied to both your result-set on your web page as well as the print document. In this case, you would print-enable your report query (on the print attributes page) but not specify a print link. Instead you would create a submit button similar to the sample above and create a branch that is executed when this button gets pressed. In this scenario, the target page of your branch would be the current page, and just like shown above, you would define a special request string:

FLOW_XMLP_OUTPUT_R[Report Region ID]

This request causes the page not to reload but initiate the PDF download instead. To specify this request, you need to lookup the region ID of your report regions. So if for example your report region ID is 90104803966374878, then the request would be:

FLOW_XMLP_OUTPUT_R90104803966374878

In summary, there are cases where item values are not available in session state when you need them for printing your documents. And in order to make sure you write them to session state on time, always perform a submit action before initiating the download of your PDF document.

We have a new statement of direction available on OTN outlining our road map for the next two releases of Oracle Application Express:

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

Having written the current reporting engine, incl. tabular forms, I’m very excited about finally getting to work on the much asked for tabular form validations and of course after adding PDF printing in 3.0, I’m looking forward to continue my work on this feature as well.

After working through Patrick’s APEX 3.1 enhancements thread on the OTN forum, I think much of what customers had asked for will actually make it into 4.0 (3.1 will just be an incremental update).

Here’s what we have planned:

Oracle Application Express 3.1

An incremental release of Oracle Application Express, 3.1, will incorporate the following:

- Optional runtime-only installation, which will install the minimum number of database objects and grant the minimum number of privileges to run Application Express applications in a production environment
- PL/SQL API to manage a runtime installation of Application Express
- Documented and supported Application Express JavaScript libraries
- Enhanced integration with Oracle SQL Developer for MS Access to Application Express migration

Oracle Application Express 4.0

The next major release of Oracle Application Express will be 4.0. This release will focus on declarative support for Web 2.0 features in Application Express, as well as the extensibility of the Application Express framework. Application Express 4.0 will incorporate the following:

- Interactive reporting region types which natively integrate Web 2.0 features to filter, break, sort data, etc.
- Extensible item framework, enabling developers to "plug in" their own custom item types
- Declarative support for AJAX validations
- Declarative support for cascading select lists and other similar item types
- Improved tabular forms, including support for validations, integration with collections, and additional item types
- New item/region types, including DHTML calendar and AJAX tree
- Improved PDF report layout
- Enhanced print attributes allowing for more control of PDF document structures
- Improved error message handling
- Numerous functional and performance improvements

Oracle PL/SQL Programming Conference

After presenting Oracle Application Express and Oracle SQL Developer at a number of events in Europe during the month on June, I’ll be presenting SQL Developer next month much closer to home for a change – on September 19th I’ll be across the Hudson River in Newark, NJ at the Oracle PL/SQL Programming Conference and talk about performing common database tasks with Oracle SQL Developer. It would be great to see you there, you can learn more about this event here:

http://www.odtugopp.com/

Application Express 3.0.1 & Oracle Develop Conference

It’s been a busy few weeks since my last post. Lots to do getting Application Express 3.0.1 ready, and preparing for the upcoming Oracle Develop Conference in Europe, where I will be presenting Application Express, do an APEX hands-on lab, and two sessions on SQL Developer. We just released version 1.2 of SQL Developer, and I’m really excited about the ever closer integration with Application Express. Your APEX applications now show up in the object tree just like any other database object, you can drill down to pages, regions, shared components, etc and you can even import and export APEX applications from within SQL Developer. I’ll write more about this soon.

First though, it’s off to Europe for the Oracle Develop Conference. In case you’re in the area, here are the dates again: Munich (June 18-19), Prague (June 21-22), and London (June 26-27). To find out more, go to this page:

http://www.oracle.com/technology/events/develop2007/index.html

Here’s an overview of my sessions, they’ll be the same at all three locations:

Day 1:

12:15 - 13:15: Introducing Oracle SQL Developer: Features and Futures
16:15 - 18:30: Hands-on Lab: Oracle Application Express - Building a Functional Application

Day 2:

14:45 - 15:45: Advanced Database Development with Oracle SQL Developer
17:30 - 18:30: Oracle Application Express, Now and in the Future

Hope to see you there.

Get your BIP on

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

http://blogs.oracle.com/xmlpublisher/2007/04/16

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

http://www.oracle.com/technology/software/products/ias/htdocs/101320bi.html

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.

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.

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.

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:

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

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:

http://www.oracle.com/technology/products/applications/publishing/index.html

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.