APEX_IR.GET_REPORT Limitations & Wishlist

Luckily nowadays we as APEX developers have access to APEX_IR.GET_REPORT which gives us back the current query of an interactive report.

With that we can actually build really nice plugins enhancing the functionality.

However one thing you have to be aware of is that it will always give you the query of the "Report" view, so if you want to get the query behind the "Group By" view it's back to rebuilding the query manually.

Talking to Patrick Wolf during DOAG 2014 he asked me to write a blog post about it and showcase the behavior.

Therefore I have created a small demo on apexea.oracle.com using an interactive report and a dynamic action to see if the same behavior is also still in APEX 5.

The dynamic action gets the IR query using APEX_IR.GET_REPORT and also sets a read-only field to SYSDATE in order to show it actually fires on event "After Refresh".

You can check out the current behavior here.

So here's my wishlist for the APEX development team:

• Please give us the query also for the other view-types available for an interactive report.
• And if you want to supercharge the function give us an additional parameter for choosing whether we want the query for the currently displayed view-type or a specific one.

I imagine a possible function declaration like below:

FUNCTION get_report( p_page_id      IN NUMBER
                   , p_region_id    IN NUMBER
                   , p_report_id    IN NUMBER DEFAULT NULL
                   , p_view_mode_in IN VARCHAR2 DEFAULT NULL
                   )
  RETURN t_report;

So if you do not specify the fourth parameter you get the query for currently displayed view mode, similar to the third already available parameter.

APEX IR XLSX Downloader V1.2.2

I have just published release 1.2.2 of my APEX IR XLX Downloader.
While attending the DOAG 2014 Conference Dietmar Aust was trying out my package and guess what...
He ran into a bug, so thanks Dietmar for reporting this.

Bugfix

• Columns without help text break the download.

As always the source code can be found on GitHub.

ZIP files for all my GitHub projects can now be found on my GitHub Page.
For a small demo visit my Demo Application on apex.oracle.com.

DOAG 2014 Conference - Program announced

The conference program for DOAG 2014 is available.
It's even harder than last year to plan because there are so many interesting sessions running in parallel.

However one session was easy to choose

APEX und OOXML, Office trifft Web

for one simple reason: I'm the presenter. ;-)

I think the time-slot Thursday 14:00 - 14:45 is also a pretty good one.

Means I have enough time to recover from the community event Wednesday evening...

See you all at DOAG 2014!

   Moritz

Speaking at DOAG 2014

For the first time I did apply to be a speaker at DOAG 2014.
Luckily my presentation has been selected so I get the honor to present at this amazing conference.
Are you interested in a brief overview what I will talk about?
Currently I have two APEX and OOXML (Open Office XML) projects which are finished or near to be finished.

APEX IR XLSX Download

The first project uses the really nice XLSX Builder Package created by Anton Scheffer which makes it easy to create Excel OOXML (XLSX) files using a SQL Query or PL/SQL calls.

I made some enhancements and build an additional package on top of it which takes the information from an Interactive Report and creates a spreadsheet representation of it.

Currently supported is only the "Report View" but there are plans to also support "Group By View" in a later release.

How to Use

The package has one main function which just needs the Region ID of the Interactive Report.

This function returns a record with the generated file and additional attributes you might need when offering the file for download.

You can get more information on GitHub where I store the source code.
ZIP Archive of package and dependencies can be downloaded from Demo Application.

PPTX Slide-Deck Generation

The second one is about creating PowerPoint slide-decks based on a template file.

I based my work on a package called OOXML_UTIL_PKG written by Morten Braten.

The package already allows to replace substitution patterns int XLSX, DOCX and PPTX files and many other things also, take a look at above link to learn more.

This time I didn't build a wrapper but used the ideas and build a package just to deal with PowerPoint files but enhancing the pattern replace to create multiple slides if more than one row of replacement strings is handed to the main function.

How to Use

You create your file with one slide using PowerPoint and replace the content you want dynamic with substitution strings. (e.g. #EMPNO#)

Afterwards you upload the template file as a BLOB into the database.

To generate the slide-deck just call the main function handing in the BLOB holding your template, a 1-dimensional array with your patterns and a two-dimensional array with the values replacing the patterns.

The source code is also stored on GitHub and ZIP archive available in Demo Application.

Can I see a demo?

If you haven't seen the hints above here it is again. :-)

Just head over to my Demo Application to see the packages in action and download ZIP archives for installation.