Category Archives: APEX

“Oracle APEX Reporting Tips&Trick” reviewed by Oracle ACE Roel Hartman

After the “not-so-positive” review of “APEX 4.2 Reporting“, Roel Hartman has kindly accepted to review my book Oracle APEX Reporting Tips&Tricks.

Of course, it actually took some guts to leave my book with Roel, knowing that there was a good chance it would result in a catastrophe, as far as I am concerned :)

Just after my not-so-positive previous book review, another author dared to ask me for my review. I asked him whether he was sure, and he – confidently – answered positive. This book, written by George Bara from Romania, is called “Oracle APEX Reporting Tips & Tricks”, so – from the title –  it seems to be covering similar subjects as the previous one.

Read the full review here: http://roelhartman.blogspot.ro/2013/12/oracle-apex-reporting-tips-tricks-book.html

Reporting Tips & Tricks – Table of Contents

Oracle-Apex-Reporting-Tips-Tricks-George-Bara_small

Here is the complete Table of Contents for “Oracle APEX Reporting Tips & Tricks“.

Remember, you can buy my book from:

– Lulu.com: http://www.lulu.com/spotlight/george_bara/

– iTunes:https://itunes.apple.com/us/book/oracle-apex-reporting-tips/id648562856?mt=11

– Barnes and Noble: http://www.barnesandnoble.com/w/oracle-apex-reporting-tips-tricks-george-bara/1115380466?ean=9781304020222

– Amazon: http://www.amazon.com/dp/B00DHHRH7M

Table of Contents

Introduction
About the Author

Chapter 1: Introducing APEX
GETTING STARTED WITH APEX
INSTALLING ORACLE APPLICATION EXPRESS
SETTING UP A WORKSPACE AND CREATING YOUR FIRST APPLICATION
CREATING YOUR FIRST APPLICATION
HOW DOES APEX WORK?
PL/SQL Web Applications
Choosing an APEX Web Server
APEX REPORTING BASICS
The Classic Report
The Interactive Report

Chapter 2: Interactive Reports Basics
INTERACTIVE REPORT MAIN FEATURES
Report Attributes
Saving Customized Reports
INTERACTIVE REPORT ALTERNATIVE VIEWS
Single Row Detail View Grouping
Alternative Detail View
Interactive Report Icon View
MIGRATING FROM CLASSIC TO INTERACTIVE REPORT
CUSTOM INTERACTIVE REPORT CSS STYLING
The APEX Theme CSS
Creating Custom Theme CSS
CSS with a Custom HTML Source
INTERACTIVE REPORTS DOWNLOAD OPTIONS
Standard Download Options
Creating a Custom Download Link
CREATING A CUSTOM DOWNLOAD FORMAT
How does APEX Render a Report for Download?
Creating a Download Procedure for CSV Format

Chapter 3: Advanced Interactive Reports
INTERACTIVE REPORTS BASED ON APEX COLLECTIONS
About APEX Collections
Interactive Reports Based on Collections
Interactive Report Column Headings
Hardcoding Report Column Headings
HACKING FILTERED INTERACTIVE REPORTS
The APEX Filter Condition Views
Interactive Reports IDs

Chapter 4: Beyond Interactive Reports
ADDING CHECKBOXES TO INTERACTIVE REPORTS
Checkbox Selected State
Select and Unselect All
JQUERY DYNAMIC ACTIONS ON CHECKBOX CHANGES
Installing jQuery on APEX
Creating Checkboxes with Dynamic Actions
Summary of Dynamic Calculations with Checkboxes
DYNAMIC INTERACTIVE REPORTS FILTERING
Default Filtering Methods
The “gReport.search” Function
Report Filtering Based on APEX Items
The “APEX_UTIL.IR_FILTER” Procedure
FILTER AND REFRESH USING DYNAMIC ACTIONS
Interactive Report Filter Using Dynamic Actions
Interactive Report Refresh Using Dynamic Actions
DISPLAYING IMAGES IN INTERACTIVE REPORTS
Local Filesystem, CDN or Database?
Uploading Images to the Database
Displaying Images Stored in the Database

Chapter 5: Reporting on Web Services Results
ABOUT WEB SERVICES
USING WEB SERVICES IN APEX
CREATING WEB SERVICE REFERENCES
Creating REST Web Service Reference
Creating WSDL Web Service Reference
Creating Manual Web Service Reference
Using Web Services over HTTPS. Oracle XE Limitations
CREATING REPORTS BASED ON WEB SERVICES RESULTS
Reports Based on REST Web Services with Forms
Reports Based on WSDL Web Services

Chapter 6: PDF Report Printing
ABOUT APACHE FOP
DEPLOYING FOP IN A SERVLET
Using OC4J as Servlet Container
Deploying FOP in OC4J
SETTING UP APACHE FOP PRINTING IN APEX
HOW DOES APEX USE THE FOP PRINTING ENGINE
SINGLE REPORT PRINTING
REPORT QUERIES
Creating a Report Query
How Does APEX Store Report Queries
REPORT LAYOUTS
Creating a Report Layout
Creating a Download Button
UNDERSTANDING REPORT LAYOUTS
Generic Columns Report Layout Sections
Customizing the Page Template Section
How Does APEX Store Report Layouts
MULTIPLE REPORT PRINTING

Chapter 7: Complex Report PDF Printing
DESIGNING XSL-FO REPORT LAYOUTS
Display Images in the PDF with XSL-FO
Conditional display in PDF with XSLT
DOWNLOADING FILTERED INTERACTIVE REPORTS AS PDF FILES WHEN USING REPORT QUERIES
Retrieve the filter applied on the Interactive Report from the APEX internal views
Create a View Synchronized with the Filtered Interactive Report
Creating the synchronized Report Query
Create the PDF Download Button

Conclusions

“Oracle APEX Reporting Tips & Tricks” – Out Now!

I am extremely proud to announce the release of my new software development book: “Oracle APEX Reporting Tips & Tricks”.

If you are keen in learning a very robust Rapid Application Development (RAD) tool for web-based applications using Oracle Application Express (APEX), or if you just want to get yourself familiarized with the new trends in database development, this is the book for you. From conception to publication, it took me over 2 years to get this done, with numerous reviews, rewrites and constantly keeping up with the new Oracle Application Express releases (updated for the latest APEX 4.2 release).

Available in iPub format (iPhone, iPad, NOOK, Kindle and PC) and also in paperback format:

iBook Store

iBooks Store

Lulu.com

Lulu.com

Barnes & Noble - NOOK

Barnes & Noble – NOOK

Amazon Kindle

Amazon Kindle

“Take advantage of all the exciting Reporting features of Oracle Application Express 4.2. Designed for a hands-on approach, this book will give you in-depth practical guidelines from George Bara, a well-known APEX expert and blogger. From Classic to Interactive Reports, Web Services and PDF Printing, this book is must-have for all developers that want to get the most out of the Oracle APEX 4.2. reporting engine.”

Best Regards,

George Bara

Explained: How does APEX work?

This might be one of the most common questions when introducing APEX to someone used to a totally different web stack. Or, as I’ve witnessed it many times, to a beginner or intermediate APEX developer who was to focused on building apps, but not really trying to understand how APEX really works. Here’s an explanation:

PL/SQL Web Applications

What are those? Well, a web application written in PL/SQL is a set of subprograms that interact with web browsers through HTTP. APEX itself and the applications it produces is exactly that: PL/SQL code that interacts with the web server of choice, whether EPG, Apex Listener or Oracle HTTP server.

In order to develop and execute a PL/SQL web application, you need to use two components:

The gReport.search function for APEX Interactive Reports

The gReport.searchfunction is used internally by Interactive Reports when a filter is applied on the report. Even if the gReport.search function is not documented by Oracle, taking a look at the source code for the Interactive Report Search Field gives us a clue about how the function is used internally:

<div>
  <input title=”Search Report” type=”text” size=”30″ maxlength=”4000″ value=”” id=”apexir_SEARCH” onkeydown=”if($f_Enter(event))
            {gReport.search(‘SEARCH’); return false;}”/>
<button
        type=”button”
        value=”Go”
        onclick=”gReport.search(‘SEARCH’)” >
  <span>Go</span>
</button>
</div>

The code above defines the Search Field input item and adds the search action for when the Enter key is pressed:

onkeydown="if($f_Enter(event))          {gReport.search('SEARCH'); return false;}

Similar code is used for the Go button:

onclick=”gReport.search(‘SEARCH’)”

The code snippets above reveals the fact that the gReport.search(‘SEARCH’) function is called to refresh and filter the Interactive Report on the fly, without refreshing the entire page.

Report filtering using gReport.Search

Using the gReport.search function described above, you can create custom filter mechanisms on Interactive Reports. For example, you can create an Interactive Report dynamic filtering mechanism based on a simple select list.

  1.  Create a Select List item in the same region as the existing Interactive Report.
  2.  Fill in the Label field with the value P15_FILTER.
  3. For the List of Values Query, paste in the following query and create the item:select distinct JOB display_value, JOB return_valuefrom EMP
  4. Open the created select list and go to the Element Section. Set the HTML Form Element Attributes to: onchange=”filterReport()”
  5. This will trigger the filterReport() JavaScript function each time the select list value is changed.
  6. Edit the page when the Interactive Report and select list are created, go to the JavaScript tab and add the filterReport() function code in the Function and Global Variable Declaration:

function filterReport()
{
   var selList = document.getElementById(‘P15_FILTER’);
   var IR = document.getElementById(‘apexir_SEARCH’);
   IR.value  = selList.value;
   gReport.search(‘SEARCH’);
}

When selecting a value from the select list containing the job titles, the selected value will be assigned to the Interactive Report’s search field and the report will be filtered dynamically on this value.

The code above simply gets the select list value (document.getElementById(‘P15_FILTER’).value) and assigns it to the apexir_SEARCH item, which is actually the report’s search field. Then, the gReport.search() function is called to filter the Interactive Report.

Create a custom download link for an Interactive Report

The default method of downloading an Interactive Report is clicking on the Action button, then the Download button and then selecting the desired format.

For the CSV report download format:

The download link will be:

http://localhost:8080/apex/f?p=105:2:67119041146700:CSV:

For the the HTML report download format:

The download link will be:

http://localhost:8080/apex/f?p=105:2:67119041146700:HTMLD:

 

The download link structure is:

URL/f?p=<app_id>:<page_id>:<session_id>:<format>

Continue reading

Test the NinjaPress blogging platform!

You can now test the NinjaPress blogging platform on apex.oracle.com! Just follow the URLs below and you configure the blog, create articles, manage images, link, CSS settings and more. You only need an username or email to access the administrative layer.

 

 

 

This version of the NinjaPress blogging platform is not the full version, due to limitations of apex.oracle.com:

– just 1 schema available, so some of the security features of the blog platform (which uses on w”shadow”schema and a schema for the administrative layer) are not present in this installation

– no role-based user management, as apex.oracle.com does not allow using Application Context and having a custom authentication scheme with VPN

Please feel free to test it, keeping in mind that others might want to use it, so don’t tamper with the main settings!

Try it out live here: http://apex.oracle.com/pls/apex/f?p=39318:1

Administrative Panel (just username required) here: http://apex.oracle.com/pls/apex/f?p=20559:101

The ApexNinjas Blog Platform v2.0 is released!

I’ve been promising the release of the new version of our ApexNinjas Blog Platform that is running www.apexninjas.com. You can read the initial release notes on the Blog Plaform page. You can download and install it with no costs or hidden agendas.

Soon, we will update the release notes with detailed information about security and CSS customization. Just as a peak preview, the application was tested for security leaks using Recx ApexSec tool and respects all the best practices involved in developing APEX applications. It also has a simple role-based access mechanism, making some pages and features available only for administrator users and separating the data between regular users.

Also, we spent an important amount of time making the platform configurable, so you can customize the sidebar widgets, article bars, CSS layouts and so on. We will get back with instructions on how to work with the Administration Panel soon.

Meanwhile, download it, install it, enjoy it and please, send us some feedback!

Blog Platform download page.

Continue reading

Pimp up you APEX application with dynamic CSS styling

I was describing how we added a custom CSS to our ApexNinjas main menu in an article called APEX Creating a horizontal menu from a SQL Report, but while working on the new version of our blogging platform (which will be released together with hte source code by the end of November) we stumbled upon a new and coll way to “pimp up” you APEX app with CSS stylesheets.

Picking up where we left off in the aforementioned article, applying custom CSS would require:

1. copying a CSS file in the APEX themes folder and referencing it directly from there.

or

2. uploading the CSS file in the application Shared Components -> Files: Cascading Stylesheets area and referencing it directly from the APEX repository.

The best solution, however, it to create your own CSS table and encapsulate all the page components into <div> regions.

Continue reading