Tuesday, 15 May 2018

Oracle APEX Interactive reports and google maps integration

Oracle APEX Interactive reports and google maps integration


Great way to extend your IR functionality 

GEO location data viewed on a map?

Recently I worked on APEX project that I thought would be cool to show case here purely to demonstrate scaling ability of APEX and one of its core components -  Interactive reports.

Problem: key requirement users to report using maps.

Idea was that they would filter out the data prepare all the columns etc ...and than per each column they would be able to categorize before showing it on a map. 

One way would be to custom build you filters and based on it drive the map data. But we figured why would we reinvent the wheel?

Hope this give you enough insight to why we opted to go with mighty Interactive reports - purely because we had API access to user report data. This was a key driver for us :)
Before further reading there is an excellent blog post by fellow APEX-er Adrian Png where he went into more details of how to setup and live feed your maps data. Great read and another awesome post by Adrian #hugefan. Also there are heaps of Google maps APEX plugins around for you to start playing.

Straight difference to how Adrian's team stored the data was, we were purely working on simple LAT and LONG that were saved in database tables. There was no use of SDO_geometry and all config that comes with it. So we could say this would be another use case.

Our technology was basic: Oracle APEX 5.1 and Leaflet JS library so in theory it should work with any version of APEX as long as IR are there. 

For start a demo that demonstrates how IR works with maps using its own SQL data.

Key features:
  1. All original APEX IR functionality is intact - giving end users all the freedom to analyze their data
  2. Per each column (or most of them) users will be able to show them graphically on a map (for example list all positive screening results in this region or what animals were involved in accident report at xx location)
  3. As we filter out our report data sets all map filters will dynamically show only data contained in a 'user view' 
  4. There is ability to categorize elements shown on a map as markers with different pins and shapes
How does it work?

All data are stored in collections where using APEX_IR package we extracted report SQL running. Pretty much that is it. Once you have this you can filter down any values you are after to be used in your 'prepare' map section. The rest is pure JavaScript and map APIs.

On a map screen to your fetch data you can use a simple apex.server.process and you are ready to rock and roll.

All in all we had ~ 10 reports where this was used and only regret we have is not being able to utilize Interactive grid functionality. :)

Happy APEXing,

Wednesday, 25 April 2018

Oracle APEX region help text hacks

Oracle APEX 5 region help text


Simple way for creating region help details 

Universal theme - how to implement region help text?

Prior or later we all had this question from our end users "I want a region help text same as what you did for page items". 

Fair enough you say this should be simple to do. Something that is similar to 

Unfortunately the only current region in APEX (Universal theme) that support this functionality out of the box is new interactive grid. But even here it is not ideal as it sits under Actions menu -> Help and contains additional text about IG functionalities which might be not what you want to show anyway.    

Ok so what about workarounds?

In this post I will cover an easy alternative that might be handy if this is not a general application requirement where all regions in your application would need to have a help text. In this case approach would be slightly different and more generic which I will cover this in separate post to follow. 

  1. Create a interactive report for example give it a static ID: my_sample_region
  2. Create new standard region item with specific Help text (that you want to show for your region)
    - Item name: P11_HELP_ITEM
    Type: Text field
    - Label: Your Help text title
    - Label Column span: 0
    - Under Advance CSS classes set your CSS class for example: hide_input
    - Help Text: This is my help text! 

    So far region should look like this

  3. Apply some CSS
    .hide_input  {
            display:none !important;
  4. Apply some simple JQuery magic                                                                               $('#my_sample_region .t-Region-title').append($('#P11_HELP_ITEM_CONTAINER button'));
  5. And we are done. 

Please note that JQuery selectors do depend on region template you are working with but generally speaking this should still work. Also I am assuming you are using Universal Theme.

Probably not the ideal way but still you can do this for any region you have on a page.

Happy APEXing,

Thursday, 15 February 2018

Oracle APEX Interactive report date order by

Interactive report date order by


DECODE over CASE statement

Oracle APEX - IR sort order not working?

We all know and love Interactive reports in APEX. This is a quick post showing a typical user case where sort order was rude to a customer.  


Looking at the source code for this region nothing jumps out:
     DECODE (evf.start_date_did,
             0, null,
     AS event_start_date        
 FROM event_fact evf
 JOIN date_dim evdat
    ON evdat.date_did = evf.start_date_did   

For some reason APEX was seeing this date as a varchar. But again why would this not work if column returned is defined as date in a table.

Digging deeper into a problem we looked at definition of DECODE function and noticed this: 
"..If the first result is NULL, then the return value is converted to VARCHAR2."
Great this as usual confirms that we have an issue in the query not in APEX itself. 

1. Rewrite your query to use a date over a NULL in your decode statement
     DECODE (evf.start_date_did,
             0, to_date('01-JAN-1900', 'dd-mon-yyyy'),
     AS event_start_date        
 FROM event_fact evf
 JOIN date_dim evdat
    ON evdat.date_did = evf.start_date_did
Or even better use CASE statement
      WHEN evf.start_date_did != 0
       THEN evdat.calendar_date END        
     AS event_start_date        
 FROM event_fact evf
 JOIN date_dim evdat
    ON evdat.date_did = evf.start_date_did  

Summary, there is a difference between DECODE and CASE statement working with NULLS which can cause similar issues so be warned and keep an eye out. 

Happy APEXing,

Friday, 9 February 2018

Oracle APEX - Show hide regions and items on large scale

Show hide regions and items on large scale


Using DA or JavaScript?

Oracle APEX - handling hide and show methods with a catch

Simple problem - Page with large number of items (527+ form fields for example :D) and depending on certain field you want to show some where the rest stay hidden.  

Of course you could do this declarative way by using Dynamic actions where for you first hide all then for certain condition you show items. Only concern the more conditions you put in things become cumbersome.

But issues that I came across came from the fact that my approach was for that reason different. Why? Because I was dealing with 527 items on the form where there were 37 different groups of items dictated by 1 form field so was looking into a way how to process most of show/hide behavior with less code. 

The example of code above is a demo one not the original form but concept and the problem stays the same. Lets say APEX_APP_ID contains 37 values where the rest columns 400+ of them fit in one of groups (sometimes in more than one so could not just group them easily in regions). Hopefully this describes the problem well. I used regions to group most of items together but still challenge was there. 

Task number 1. Hide all

Great way of doing this is by using CSS Classes attribute under your page item/region properties.

 Then all it comes down to is running once all elements have their class set:
$( ".my_hide_all").hide();
Awesome - all items are now hidden. So simply have to show them when I want. Easy right? Well I thought so too before learning this lesson. 

Task 2. Show items
I thought this should do the trick:
apex.item( "P1_ITEM" ).show();

As you can image it did not work. The reason is because my class was set on a wrong level;  so DOME row CSS still had display:none;  set.

In my above example solution was to use: 

This then worked fine. I admit did not see this coming. 

Once I placed a CSS class on 
appearance section CSS Classes things were back as expected and I was able to show it using
apex.item( "P1_ITEM" ).show();
On the other side if I used DA to first hide then to show an item (with True Action-> Action Show) worked like a char straight away. Well I guess lesson learned for today. :D

Happy APEXing,