Friday, 29 July 2011

Minimizing IR Filters On Default Report

I had a requirement for an IR report that specified that the background colour of the rows be set to specific colours based on certain data values. To accomplish this the easiest way possible and to allow the user to "turn on" and "turn off" the colours, I created a series of IR filters.

Each filter is set to define the highlight colour based upon specified data:

The result is excellent. The rows are highlighted based on the specified criteria and the user can disable specific highlights if they wish. Obviously, these filters are saved as the default report.

But, my mother taught me to keep things clean and tidy. And, when you first open the report you are faced with a list of filters that you may or may not wish to alter. It makes the report screen rather busy and I wanted to temporarily hide the filters when the page first loads. Hmmmm - dynamic actions?

Here's the solution:

1.  Using the Advanced dynamic action wizard, create a new dynamic action and set the event to Page Load.

2.  Create a true action of type Execute Javascript Code.

3.  Enter the following for the javascript code:

/* Minimize IRR filters on page load*/
if( $('#apexir_CONTROLS_IMAGE').attr("src") == '/i/minus.gif') {

And, that's it!  A result that even my mother would approve of - everything neat and tidy!

The users can still access the filters by clicking on the "plus" sign and can just as easily hide them again.

Wednesday, 27 July 2011

Expert Oracle Application Express

If you have not yet bought your copy of Expert Oracle Application Express -  what are you waiting for? I got my copy in the mail a couple of weeks ago and was thrilled to read it. I am one of those developers who tries to read everything I can on Apex to stay at the top of what I do. And, for me, this book is now a "must read" for anyoune who aspires to be an Apex professional.

This book is written by a collection of authors - most of whom you will know from the forum or from their blogs. What I liked most about the content is that there is a focus on detailed information that you won't find elsewhere. It is not a book for the beginner who knows little about Apex, but rather, is aimed at the professional developer who is keen to know more.

I have already put to use much of what I learned from the book. In particular, Dimitri Gielis's chapter on charts and Martin D'Souza's chapter on dynamic actions really helped to further my understanding on two key areas of importance in my current development. All of the other chapters were also fantastic.

So, if you want to go beyond the boundaries of the basic documentation and move into the world of advanced knowledge - this book is a great place to start. I highly recommend it.

Friday, 15 July 2011

Tooltips On Charts

If you don't know what a "tooltip" on a chart is, it is the little piece of text that pops up when you hover over a segment on the graph. In the Apex Chart Wizard, it is referred to as a hint. When you select the "Hints" checkbox in the wizard, the tooltips are enabled.

While tooltips are available through the Apex Chart Wizard, very little of their real functionality is made available to you directly by the wizard. For example, my customer wanted the following:

1.  A specific font and text colour for the tooltip.
2.  The tooltip needed to show both the value and the percentage of the total.

My first reaction was "that's not possible." But, a bit of research and effort determined that not only was it possible but that we can do all sorts of cool things with the tooltips.

Like everything in Apex charts, it is handled through the XML.  There is a tag for <tooltip_settings> and defines the tooltip functionality. Here is how my XML for the above tooltip looks:

<tooltip_settings enabled="true">
      <format><![CDATA[{%SeriesName}{enabled:False} -
           {%Value}{numDecimals:0,decimalSeparator:.,thousandsSeparator:\,} -
       <font family="Arial" size="10" color="#808080" />
      <position anchor="Float" valign="Top" padding="10" />

As you can see, within the <tooltip_settings> tag there are a number of other tags such as <format>, <font> and <position>. There are also other tags as well that I have not used here.

The format section specifies what information will appear in the tooltip and how it will be formatted. The values in the tooltip are specified by keywords (they have a % in front of them). Here is the information in my tooltip and below are the keywords associated with them:

Air     -               4              -  57%
%SeriesName    %Value        %YPercentOfToal

Typically, Apex will insert the %Value keyword for you when you specify Hints in the Wizard. But that is rather limited. There are a large number of keywords you can use to show other values. For example, I have used %YPercentOfTotal which displays the percentage of the total bar represented by %Value amount. Take a look at some of the cool keywords available to you:

%YPercentOfCategory - the percentage of all the points with the same name
%YPercentOfSeries      - the percentage of the series represented by the point
%High                          - the high value of the point (candlestick)
%Low                          - the low value of the point (candlestick)
%Name                        - the name of the point
%Range                       - the range of the point
%YPercentOfTotal       - the percentage of all the series on the chart

After each keyword is the format definition for that keyword.  For example, {numDecimals:0} set the number of decimals displayed to zero. If you want no formatting at all, you can use {enabled:False} immediately after the keyword.

While the wizard in Apex will get you started, by using "custom XML" for your chart, you can have complete control of the tooltips and make them even more meaningful for your users. Try it out for yourself.

Here is a reference that will assist you in unlocking the power ot tooltips:

Tooltip settings and formatting

Friday, 8 July 2011

Custom Apex Maps - step by step

The cool thing about receiving a complicated requirement is that you learn something new and it turns out to be pretty cool.

In an application I needed to be able to display ocean ports on a map along with the number of shipments shipped from that port. Here is what the map looked like once I was done:

Well, that sounds easy enough! However, the maps that come with AnyChart's anymap in Apex 4, know nothing about ocean ports. The map like the one above has cities in the map information but those cities are not located exactly where the ports are located, nor do they have the same name as the port. The image of the map is exactly as I want it, but I need a set of custom locations to display on the map.

Let's get started.....

I decided to start by creating a collection on my page that would get the information I require from the customer's shipping data. I need the list of ports and the number of shipments per port.  Here is my code to create the collection.

    l_query   varchar2(4000);
     IF apex_collection.collection_exists(p_collection_name=>'CHINAMAP') then

           p_collection_name => 'CHINAMAP' );

     END IF;

    l_query := q'(select null LINK, )';
    l_query := l_query || q'(port_of_load LABEL, )';
    l_query := l_query || q'(count(shipment_id) SHIPMENTS )';
    l_query := l_query || q'(from shipping_rep_sum )';
    l_query := l_query || q'(where export_country = 'China' )';
    l_query := l_query || q'(and main_carriage = 'OCEAN' )';
    l_query := l_query || q'(group by port_of_load)';

    apex_collection.create_collection_from_query (
        p_collection_name => 'CHINAMAP',
        p_query => l_query,
        p_generate_md5 => 'YES');

And here is the resulting collection data:

If you were trying to display the above collection data on a map of China, it wouldn't work. The majority of the port names have no associated locations on the map.

Next I created a table that stores the longitude and latitude of the ports (I looked up these figures on the internet. The table data looks like this:

So good. I've got my customer data, and I have the coordinates for the locations. Now, I need to be able to supply the map with the data in the right format. The map uses xml to plot the locations. I need to pass data to the map in a format that will look like this:

<series name="Ports" type="Marker" palette="Default" >
<point name="Ningbo" x="121.55" y="29.88" >
       <attribute name="value1">23</attribute>
<point name="Qingdao" x="120.3575" y="36.093" >
       <attribute name="value1">45</attribute>
<point name="Shekou" x="112.90" y="21.48" >
       <attribute name="value1">2</attribute>
<point name="Nhava Sheva" x="72.97" y="18.95" >
       <attribute name="value1">2</attribute>
<point name="Shanghai" x="121.375" y="31.254" >
       <attribute name="value1">167</attribute>
<point name="Yantian" x="116.27" y="23.60" >
       <attribute name="value1">3</attribute>
<point name="Hong Kong" x="119.17" y="25.27" >
       <attribute name="value1">1</attribute>

I create a hidden text area on the page. In my case, I called it P6_XML_CHINA. Then I have an on load pl/sql process to generate the xml and populate the hidden item.  Here's my process code:
    l_data      varchar2(4000);
    v_xlocation varchar2(50);
    v_ylocation varchar2(50);
    v_port      varchar2(100);

    cursor get_locations is select x_location
                             from port_locations
                             where port_name = v_port;
    l_data := '<data>'||chr(10);
    l_data := l_data ||'<series name='||chr(34)||'Ports'||chr(34)||' type='||chr(34)||'Marker'||chr(34)||' palette='||chr(34)||'Default'||chr(34)||' >'||chr(10);
     for c1 IN (select c002,c003
                from apex_collections
                where collection_name = 'CHINAMAP')

            v_port := c1.c002;
            v_xlocation  := null;
            v_ylocation  := null;
            open get_locations;
            fetch get_locations into v_xlocation, v_ylocation;
            close get_locations;

            if v_xlocation is not null
               l_data := l_data ||'<point name='||chr(34)||c1.c002||chr(34)||' ';
               l_data := l_data ||'x='||chr(34)||v_xlocation||chr(34)||' ';
               l_data := l_data ||'y='||chr(34)||v_ylocation||chr(34)||' >'||chr(10);
               l_data := l_data ||'    <attributes>'||chr(10);
               l_data := l_data ||'       <attribute name='||chr(34)||'value1'||chr(34)||'>';
               l_data := l_data ||c1.c003||'</attribute>'||chr(10);
               l_data := l_data ||'</attributes>'||chr(10);
               l_data := l_data||'</point>'||chr(10);  

            end if;
      end loop;

      l_data := l_data ||'</series>'||chr(10);
      l_data := l_data ||'</data>';

       :p6_xml_china := l_data;


Next, I create a map region and select a map of China. I set all of the map attributes the way I want them and apply the changes. I then edit the map and select the "map xml" tab and set "Use customer xml" to 'Yes'.  This displays the xml that the map will use. I need to edit the xml slightly.  Scroll down until you find the reference #DATA# and replace it with a reference to the hidden item with &P6_XML_CHART. (remember the period at the end). This will tell the map to use the xml stored in the hidden item which will plot all of the custom points.

You can plot anything on a map as long as you can supply the associated longitude and the latitude and prepare the required xml.

Customer map done!  Cool stuff learned!