I am going to start this blog post off with a firm statement that I am not being paid in any way, shape, or form to write this blog. I am writing it because it solved a problem I had in, quite possibly, the simplest way possible!
Business Case: I have created an APEX application that generates multiple calculations based off measurement readings that needs to be exported to a formatted document (Word or PDF).
Business Problem: APEX doesn’t not export formatted Word or PDF (or other Microsoft documents).
History & Resolution: I’ve said this many times, but I want to state, again, that I am not an APEX expert. I like to say I’m an APEX basic user. I know enough knowledge to be dangerous, but not revolutionary. With this said, when I tried to implement exporting from APEX, I was surprised to learn that (this wonderful, fun, dynamic) tool does not export to, say, Word or PDF. This was a bit sad and frustrating for me because this was the last piece of my application’s puzzle. At first, I thought it was my lack of knowledge regarding the APEX toolset, but I quickly found out that it was a tool limitation, not a personal skillset limitation. I did some Googling to see what other APEX developers used to get around this feature limitation and one of the first I found was APEX Office Print (AOP) by APEX R&D. It seemed like a straightforward process to implement, so I went down the rabbit hole to test the tool out in my apex.oracle.com environment.
Recall that I am not an APEX developer… I am a BI & EPM person. I work with KPIs, calculations, statistics, and numbers…not hardcore DBA or APEX standard skillsets. So when I say that AOP is easy to install and get up and running, trust me! I am going to show my process to install the tool via an upgrade process since I did not take screenshots around a year ago when I originally set up the tool.
After you create a login to the AOP website, you can download the tool based on your APEX implementation type. My APEX installation is the basic (and free!) apex.oracle.com account, so I chose to download the Cloud Package.
A zip file is downloaded that contains the files you need to get up and running in a matter of seconds to minutes.
The db -> aop_db_pkg.sql is the first script you will use.
In APEX, you will Upload this script the “SQL Scripts” area then “Run” the script.
Next, I imported the plug-in to my application’s Shared Components -> Plug-ins section:
Note that the last 2 digits corresponds to the version of APEX you are running. If using the cloud version, you look in the lower right-hand portion of your screen to get your details:
Once uploaded, I can see my process type plug-in loaded.
That’s it for installation!
Now to use the tool…it’s the same level of easy…
Say you have a template you want to use for AOP. I have a *very* simple example template in Word, shown below.
Notice the parts that I want pulled from the database are in the curly parentheses.
I’m going to load this file to my application’s Shared Components -> Static Application Files folder.
Now I’m going to go to the page where I want to utilize AOP.
I’m using it on a button as a dynamic action. To correspond with the numbers below:
1. Give the dynamic action a name.
2. Choose APEX Office Print (AOP) [Plug-In] as the type.
3. Choose Static Application Files as our Template Type since we loaded our Word document there.
4. Enter the name of the Word document.
5. Enter the SQL (second section below for those details).
6. Choose an output file name.
7. Choose the type of output you would like.
Now, Save and run that page!
The SQL I used was this:
Notice that the column aliases correspond to the curly parentheses areas on the Word document.
When I click the button to run my dynamic process, I get a Word document:
Perfect and SO simple! That’s it!
I want to add that there are certain number formatting options that should be observed when using AOP. With my inexperience with being an Oracle DBA, I struggled a bit to get it all working the first time around. I want to highlight that the support by the APEX R&D team is about the best I have ever encountered. Their response time was within minutes and their patience with an APEX beginner was commendable. I even learned some things about data types from them! When I had issues this past weekend with some output changes, they, again, were extremely responsive and helped me get up and going on the weekend. I have no doubt that they would take care of a large installation just as well (if not better) than they took care of me, an installation that does around 100 printings a year.
To conclude, if you are looking for a quick and easy APEX tool or plug-in to print Word, PDF, Excel, PowerPoint, or HTML, this is your tool. Easy to implement and easy to use. …And their support is top notch. Again, I’m not being paid in any way, but I want to share a simple solution to my APEX output issue!