Choosing the right template and print to PDF from your Oracle APEX application

This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

To motivate players to exercise more, I thought it would be a good idea to give the players a diploma (certificate) when they complete the multiplication table. They will be able to download or print this certificate if they have a specific score. I designed it that we have one official diploma of mtable.online, but there's also the ability so teachers or people at home can print their own certificate in the template or colours they want.

Creating the diploma

Creating a certificate in Microsoft Office is not that hard, in fact online you find many certificate templates you can just download. Most are in Word or Powerpoint format.

In our multiplication table project we have the concept of teams. One or more players can be part of one or more teams. A team could be, for example, a class and the students are the players within that team. I want the team manager be able to upload a template for the certificate (diploma) of the team.

How integrating those certificates in Oracle Application Express (APEX)?

With APEX Office Print, this becomes very easy!

APEX Office Print (AOP) is a print server for Oracle Application Express (APEX) which allows you to define your template in Word, Excel, Powerpoint, HTML or Markdown and merges it with your data in the Oracle database. As output you can select PDF, Word, Excel, Powerpoint, HTML or Markdown. APEX Office Print comes with an APEX Plug-in and PL/SQL API that makes it very easy to select the template and the data in any Oracle APEX application. AOP is also smart and understands Oracle APEX meta-data, so for example printing one or more Interactive Reports or Grids to Excel or PDF is done in a breeze. Till date, APEX Office Print (AOP) is the most integrated, easiest and flexible printing and data exporting solution for Oracle Application Express (APEX).
Full disclosure, my company APEX R&D is the maker of AOP, so I might be a bit biased, but you can check this youtube video where different APEX printing solutions are discussed.

Installation of the AOP plugin

When you go to the APEX Office Print website, you can download the cloud or on-premise version and try it out for 100 days. You just have to click sign up, provide an email go to Downloads and click the Cloud package.

 Extract the zip file that was downloaded and locate the db folder and plugin folder.

Import the APEX Plug-in by going into your APEX app, to Shared Components, Plug-ins, hit the import button and choose the dynamic_action_plugin_be_apexrnd_aop_da_51.sql file. Depending the version of APEX, you might need to choose the _50.sql file (in case of APEX 5.0). Follow the wizard to import the plug-in.

The plug-in calls a package, so there's one more step to do in order to make the plug-in work. Go to SQL Workshop > SQL Scripts and hit the Upload button and select the file aop_db_pkg.sql from the db folder.

Next, click on the Run button to run the script, which will install the AOP_API3_PKG PL/SQL package.

That's it - you successfully installed the AOP plug-in and are now ready to use it in your application.

Note: in the above example we installed the Dynamic Action plug-in. AOP also comes with a process type plugin, in case you prefer a process over a dynamic action.


Calling the AOP plugin from your page

Create a new dynamic action, for example Click on a button and as Action select the APEX Office Print (AOP) Plug-in:

The plug-in is very flexible and has many options, yet it's so easy to use. You first tell the plugin where your template is; in Static Application Files (Shared Components), in a table (define your SQL), a url, the filesystem, ... you just select where and tell it which one.

Next you have to tell which data you want to use, you can define SQL, PL/SQL, URL or even the static id of the region. AOP is so smart it will understand if you put the static id of an Interactive Report or Grid, a Classic Report or even a JET or other chart. Behind the scenes AOP is reading the meta-data, so it will use whatever is behind that region as source. This is one of the most liked features by our customers and something no other printing solution offers.

The plug-in has build-in help and examples, so it's more easy to know how to use the plugin.

You can define some other settings (which items to submit in session state, some special report settings etc.) and finally the output you want; PDF, Word, Excel, you name it.

Print the diploma

When you go to mtable.online, you can view the highscores for all players, or the highscores within a team.

Based on some rules you might be able to print a diploma. The logic which defines if you are allowed to get a diploma I wrapped in a view:

Now the interesting thing is that we can define the selection of the template dynamically in the AOP plugin. So, I've written some PL/SQL code that returns the correct template. If a team is selected, it will use the template of that team, if there is not a team, or if looking at all players, it will use the default template. As described in the first paragraph, the owner of the team can define their own template, they don't need to contact us (the developers) anymore. They just create their template in Word, Excel or Powerpoint and upload it.

From the very start when developing AOP, this was a main goal - we wanted the business users be able to create their own template. As developers we just provide the data, how the data is displayed is defined by the business people. With other printing solutions, such as XSL-FO, iReport, etc. I had to spend hours and hours redefining the template, but with AOP, as a developer, my job is done the moment I write my query :)

Back to the diploma in mtable.online; as default the template I used was a Powerpoint I downloaded from the certificate templates site. The only thing I changed was adding the substitution variables that AOP would understand: {player_name}, {mi_ss}, {play_date} (those are the columns in my query - see further)

 For my team, I went with a Word Template:

 And my plug-in looks like this:

Whenever the diploma link is clicked in the report, the AOP Dynamic Actions kicks in and generates the PDF, based on the template from the team (Word) or the default (Powerpoint). That's it :)

APEX Office Print is even more used in the Multiplication Table project; namely when you go to the  details of an exercise, which are shown in an Interactive Grid, AOP exports this Interactive Grid to our own Excel template. I'll do a dedicated post on that as there're more interesting steps to talk about with adding buttons and custom dynamic actions to an Interactive Grid.

If you didn't try APEX Office Print 3.0 yet, you can sign up for a trial for free. In a few days we ar also releasing AOP 3.1, our best version ever :)