Enabling end users to create their own PDF report layouts

With Oracle Application Express 3.0 we introduced advanced reporting printing using BI Publisher as the PDF rendering engine and using BI Publisher Desktop for designing sophisticated report layouts. In Oracle Application Express 3.1 we added the ability to create reports based on multiple queries. We also added a PL/SQL API that allows you to programmatically extend the built-in printing functionality. One interesting option this API offers is to dynamically link a report layout to a report query at runtime. Why is that interesting? Well, it can give your users more choices, e.g. they can pick which layout they prefer when printing reports. But more importantly, it allows your users to actually create their own report layouts, upload them into your own custom applications, and print the data in whichever format they like.

So what does that mean? How would this look like? Try out this application and see for yourself:

http://apex.oracle.com/pls/otn/f?p=49181:1 (username: demo, password: demo123)

The first page in this application shows a report, which can be filtered. And it can also be printed by clicking on "Print Report". When printing, you have a choice of which report layout you want to use. There are two report layouts pre-loaded, but you can also create your own, and upload them via manage report layouts. This takes you to a page where you can look at the report layouts already loaded, and upload you own ones. To create a report layout, you need to have BI Publisher Desktop installed (and MS Word), once that's installed, just download the XML data of your report on page 1, import this into MS Word, use the BI Publisher Desktop Word Plug-In to create your own custom layout, save as RTF and load it up into the application. Once the RTF layout is loaded, you'll find it in the select list on page 1 and can print out your report with your own personalized layout.

Want to try this at home? You can download the application, packaged up with the underlying DDL and including the two sample layouts here.

Where Parking Rules the Days, a Little Miracle

I stumbled upon this article in the New York Times today, and couldn't believe what I was reading. It's about my neighborhood, and the parking rules we have to obey here in New York. The article starts as follows:

"It is a routine so ingrained in the New Yorker's life that it seems to have always existed: And on the sixth day, God created man. And on the seventh day, while God rested, man had to go move his car because of alternate-side parking rules."

Well, looks like this summer, at least in my neighborhood, this will be suspended. And this will in fact be life-altering. I will have soo much spare time one my hands, I won't know what to do, I might even get to blog more often. So read on and enjoy:

Parking Rules the Days, a Little Miracle

APEX Advanced Training in Munich, Germany (June 2-4)

I'm happy to announce that I've been invited to speak at the APEX Advanced Training in Munich, Germany, June 2nd to June 4th. This is a three day training class held by Denes Kubicek, Patrick Wolf and Dietmar Aust. All three of them are well know APEX developers and very active participants in the APEX community and so I was particularly pleased to accept their invitation to talk about what we're working on for our next release of Oracle Application Express and to demonstrate our newest release of Oracle SQL Developer.

I'll also be participating in the two evening Q&A sessions, helping to answer customer questions and hoping to learn a few things about the experience our customers have with APEX and hearing about their ideas and need for future versions.

Please visit the training web site Opal Consulting for more information on this event.

Generating CSV files and storing them in the database

This week I faced the following problem: we wanted to generate CSV files based on SQL queries, and store them in the database rather than directly downloading them from a report region. We also wanted a report, from which you could choose which CSV file to download and lastly, the rows in those CSV files could potentially exceed the 32k limit that we currently have with standard report regions.

In Application Express 3.1 we made it really easy to store files in tables with a BLOB column and to build reports on those tables with a download link that allows downloading the files to your client. So this feature takes care of storing and downloading the CSV files. No I only needed to write some logic to actually generate the CSV file and insert it into a table.

The following function does that. You supply a query, then the query is parsed and executed. And then the column headings are derived from the describe table and written to the CSV file, followed by the result set. And in the end, the file is returned back as a BLOB. This BLOB can then be written to a table, or otherwise processed.

<br />create or replace function get_report (<br /> p_query varchar2<br />) return blob is<br /> <br /> l_cursor integer;<br /> l_cursor_status integer;<br /> l_col_count number;<br /> l_desc_tbl sys.dbms_sql.desc_tab2;<br /> l_col_val varchar2(32767);<br /> <br /> l_report blob;<br /> l_raw raw(32767);<br />begin<br /> <br /> -- open BLOB to store CSV file<br /> dbms_lob.createtemporary( l_report, FALSE );<br /> dbms_lob.open( l_report, dbms_lob.lob_readwrite );<br /> <br /> -- parse query<br /> l_cursor := dbms_sql.open_cursor;<br /> dbms_sql.parse(l_cursor, p_query, dbms_sql.native);<br /> dbms_sql.describe_columns2(l_cursor, l_col_count, l_desc_tbl );<br /><br /> -- define report columns<br /> for i in 1 .. l_col_count loop<br /> dbms_sql.define_column(l_cursor, i, l_col_val, 32767 );<br /> end loop; <br /><br /> -- write column headings to CSV file<br /> for i in 1 .. l_col_count loop<br /> l_col_val := l_desc_tbl(i).col_name;<br /> if i = l_col_count then<br /> l_col_val := '"'||l_col_val||'"'||chr(10); <br /> else<br /> l_col_val := '"'||l_col_val||'",';<br /> end if;<br /> l_raw := utl_raw.cast_to_raw( l_col_val );<br /> dbms_lob.writeappend( l_report, utl_raw.length( l_raw ), l_raw );<br /> end loop; <br /><br /> l_cursor_status := sys.dbms_sql.execute(l_cursor);<br /><br /> -- write result set to CSV file<br /> loop<br /> exit when dbms_sql.fetch_rows(l_cursor) <= 0; <br /> for i in 1 .. l_col_count loop<br /> dbms_sql.column_value(l_cursor, i, l_col_val);<br /> if i = l_col_count then<br /> l_col_val := '"'||l_col_val||'"'||chr(10); <br /> else<br /> l_col_val := '"'||l_col_val||'",';<br /> end if;<br /> l_raw := utl_raw.cast_to_raw( l_col_val );<br /> dbms_lob.writeappend( l_report, utl_raw.length( l_raw ), l_raw );<br /> end loop; <br /> end loop;<br /> <br /> dbms_sql.close_cursor(l_cursor);<br /> dbms_lob.close( l_report );<br /> <br /> -- return CSV file<br /> return l_report<br /> <br />end;<br />

To showcase this function, I create an APEX application that has a report page for downloading the CSV files. And a form page that lets you create CSV files based on the demo tables that ship with our APEX sample application. You can try this out here:

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

logon as demo/demo123

If you’d like to try this on your local APEX instance, review the implementation, and use this for your own application, you can download the packaged application here: CSV and BLOB Demo Application.

This file contains the application along with the supporting objects (get_report function and other required database objects). It does not include the demo tables, so you’ll have to have the APEX default sample application installed in your workspace. If you don’t have this application, click on Create Application -> Create Demonstration Application.

Some other interesting usages for this code could be to generate XML instead of CSV. You would only have to replace the delimiters, etc with XML tags using the column names. This XML file could then be used together with our new PL/SQL Print API, i.e. you could send it off to BI Publisher or Apache FOP, along with a XSL-FO stylesheet or RTF laytout, and produce PDF, Word or Excel files, and either download them to the client or store them in BLOB columns the same way as above. More on this in my next posting.

Oracle Application Express 3.1 available now!

Oracle Application Express 3.1 was released a few days ago, you can download the latest release here:

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

Many of you have probably already heard of or even tried out the new interactive report regions. If you haven’t heard about this new region type yet, I’d recommend starting with the brief introduction which can be found here:

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

In addition to interactive report regions, there have been numerous other enhancements, and one of my focus areas were the enhancements to report printing. The two main enhancements in this area are support for multiple SQL source statements for report queries, allowing to combine several reports or combinations of reports and charts in a single print document, and additionally, the introduction of a PL/SQL print API. I will cover these enhancements and some of the other changes on my Blog in the coming weeks. For now, I’d like to point out the 3.1 new features application, which showcases some of the printing enhancements along with interactive reports and other 3.1 new features:

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

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