Saturday, November 14, 2020

Oracle APEX 20.2: APEX Office Print (AOP) vs native PDF, Excel and HTML

One of the most loved features of APEX Office Print (AOP) is the ability to export an Interactive Report or Grid exactly as you see it on the screen to Excel and PDF. AOP takes into account highlighting, filters, and more as defined by the end-user. This feature has been available since AOP 1.5 (2015) on APEX 5.0 and any higher version of APEX.

With the release of Oracle APEX 20.2, APEX provides this capability out-of-the-box. Because of this, we have received a few questions like: "Is this AOP functionality?", "How does the native APEX functionality compare with AOP?", "What's the roadmap of AOP?" and I thought the best way to answer would be to write about the differences between APEX Office Print and the native printing functionality in APEX 20.2.

Let's go back to the start... AOP's mission has always been to make the printing and exporting of data in Oracle APEX in the format you want  as easy as possible. We really believe that business users should be able to create a template in a tool they know (Word, Excel, Powerpoint) and let AOP merge it with data from the database. From day 1, APEX Office Print has been a template-based reporting engine. In your template, you use tags, which AOP replaces with data. When no custom template is selected AOP will use a default AOP Template. AOP is the most integrated printing solution for Oracle APEX out there and provides some unique features like the ability to print and export different regions by providing a single tag. For example, to print an Interactive Report as you see it on the screen you put {&interactive} in your template, to print a chart you use {$chart}, an image you use {%image}, etc.

The built-in exporting solution of Oracle APEX started from a different angle. The APEX development team wanted a PL/SQL only solution to allow people to export data/regions in different formats. Oracle APEX 20.2 is a big advancement to what was already available previously. For example, before you could download a report to CSV, but now APEX allows you to download to native Excel. Also, for Interactive Reports and Grids, you can choose to get the export as you have it on the screen with breaks, etc. In addition to the built-in download of a region, they now expose APIs (APEX_REGION and APEX_DATA_EXPORT packages) to export programmatically. 

Let's look at the questions people ask: 

Is this AOP functionality?

No, it's not, in the sense that the creation of the file happens completely in PL/SQL in native APEX, whereas AOP reads the APEX meta-data, creates a JSON, and sends it to the AOP Server which creates the file and serves it back to the database. Of course, reading the meta-data is the same and the output for a single report is very similar when you don't define a custom template in AOP. 

Although AOP is super fast in creating files (< 0.5 seconds!), the native APEX functionality will be the fastest solution to export data as it stays within the database. This is why from AOP 20.3 onwards we also now provide the option to select APEX Report, which will use the native APEX functionality behind the scenes.

How does the native APEX functionality compare with AOP? 

The native functionality in Oracle APEX 20.2 focuses on exporting a single region or some flat data (single select statement). This is vastly different from AOP, which focuses on printing and exporting as a whole. AOP is a full templating and printing engine with hundreds of features to create pixel-perfect PDFs and export the data out of your database in the format you want. If you need to create custom letters, bills of lading, documents with images and charts, perform some PDF manipulations like merging, splitting, signing or want to print directly to a printer, for example, AOP is what you need to use. 

When we don't look at general exporting and printing but focus on exporting of APEX regions and data, the native APEX functionality does a great job on basic functionalities. If you want more flexibility and advanced features, AOP is what you want to use. Here are a few examples where AOP shines:

  • put your logo on top of the export (PDF/Excel/HTML)
  • export multiple reports with some custom titles and text to PDF
  • export different Interactive Report, Interactive Grid, Classic Reports, and some custom data to different sheets in Excel
  • keep the styling (e.g. defined in HTML expressions) when exporting to PDF and Excel 
  • have complete freedom as to how the export looks and define your own template
  • support for many different languages
  • export the charts of Interactive Reports and Grids
  • reference APEX Items in your export
  • use a percent graph in your reports
  • export a specific saved private or public report
  • conditionally hide a specific column when you export
  • export Master-Detail(-Detail) Interactive Grids
  • export your reports to Word, Open Document Format, or Markdown
  • export hierarchical data and different blocks of data  

I think its great people can create some custom exports and prints with built-in tools and use AOP when more customization, flexibility, and features are needed. In fact, the new API functionality that is exposed in APEX 20.2 will make AOP even better, but more about that in my answer to the next question.

What's the roadmap of AOP?

We shipped the first version of AOP in March 2015 and since that day we have constantly invested in the product. We typically have 3 major releases per year and some smaller releases between them.

In fact, every year we have invested more in the product than the year before. 2020 is not any different. On the AOP 20.3 release, we have worked for 6 months with 8 people! It was one of the biggest and most challenging releases we have ever done, but it has made it our best release ever! A huge shout-out to the entire team who pulled it together: Sunil, Recep, Gibresh, Niyam, Kelvin, Inias, Jackie, and ... myself 😀

Two years ago it was announced that AOP will be more integrated into Oracle APEX. In APEX 20.1 and above, you can define APEX Office Print as your Print Server on the Instance Level, which replaced the functionality that was deprecated in ORDS. In APEX 20.2 the foundation of further integration of AOP is made available by providing the awesome APEX_REGION and APEX_DATA_EXPORT packages. 

If you've ever wondered why there's a 'PJSON' format in the APEX_DATA_EXPORT package... it's because we worked with the APEX Development team on this. In the last years, we've spent a lot of time understanding the meta-data of APEX and for every new component and release, we've had to put a lot of effort into supporting and testing it. The new packages provide us with a built-in way to get the region meta-data, so we are set up to support any future component.

The Oracle Database and APEX are in our hearts and we do everything we can to provide you with the best printing and exporting solution out there. Just like the APEX Development team, we are dedicated to making you successful. We also understand you might be using additional technologies to Oracle APEX and PL/SQL, and although, today, AOP can already be used from any technology by doing a REST call, we will begin providing SDKs for other technologies too.

I hope the above addresses some of your questions and I'm happy to answer any other you might ask in the comments section below or by emailing our AOP Support team.

Happy printing and exporting! 😀

No comments: