Wednesday, October 24, 2007

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.