Friday, 19 August 2011

Enabling and Disabling Classic Report Columns

Sometimes a classic report with many columns takes up too much space on the screen. There are a number of options for dealing with this issue, but one that I like is to provide the user with the ability to turn on and off individual columns or groups of columns.

I have seen several different ways of providing this functionality. For instance,  Denes Kubicek has a solution in his sample application, but it does not handle column groups and is somewhat more awkward. So, for what it is worth, I thought I would share my solution.

First, I create a series of checkbox items representing the columns (or column groups) that I want to allow the user to "turn on and off."  I put these items in a hide and show region so that they are accessible but not necessarily displayed until the user wants to change the columns.

Next, I modify the report. I set the Conditional Display attributes of each column in order to reference the related checkbox.

In some cases, I have more than one column being controlled by a single checkbox. My report has several columns that display dates. I use a single "date" checkbox to turn off and on the entire group of columns.

Next, I create a dynamic action for each of the checkbox items. The dynamic action is triggered by the "Change" event of the checkbox.

There are two true actions. The first, Execute pl/sql Code simply serves to put the value of the checkbox item into session state.

The second action simply refreshes the region containing the classic report.

 You will need to repeat the above steps to create an dynamic action that will trigger on the change of each of the checkbox items.

 The result allows the user to quickly enable or disable columns. Due to the use of the dynamic actions, the report quickly responds to the user changes. You can set defaults for the checkbox items so that some of the columns will show on page load and others will wait for the user to select the column for display.

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!

Sunday, 26 June 2011

One dashboard background and many charts

On a dashboard I have been developing, I wanted to have one large background image and have all of the charts displayed on that background. This turned out not to be as easy as I thought. Actually, the solution is easy, but finding details on exactly how to do it was not. So, I thought it would be useful to share how I ended up meeting the requirement.

First, I selected a background image. In my case it is a map of the world and it is a dark blue image. Using a photo editor, I manipulated the image to be subtle and to achieve the effect I wanted. Then, I needed to load the image so I can use it. To make things easy, I just uploaded the image in Shared Components => Images. Alternatively, I could have uploaded to the /i/ directory.

Next, I created my "main" region.  This is an HTML region with nothing in the source. To the region header I added the following in order to display the image:

<table width="100%" cellspacing="0" cellpadding="0" border="0" align="left"
background = "#WORKSPACE_IMAGES#bkg_image.gif">
<tr height="1000px" align="left" valign="top">

So, now I have my large background image on which to place my various dashboard charts.

Next, I started to create my charts. In my case, I didn't want to use a chart region as it gave me a box look with a title etc.  I wanted my charts to look like they were floating on the background. So, I created a chart and set the region type to "No Template". But there was still a problem. Every time I created a chart, even with "No Template" the chart had it own white background. I could change the background to a solid or gradient colour, but that is not what I wanted. I wanted the large background image to be the background and have my chart background to be transparent. In other words, the chart lines or bars sit on top of the background image.

Here's what to do. Create the chart and get it working and looking exactly as you want. In my case I had to set the font colour for the labels and title to something like #AAAAAA because my background is dark.

Once you have the chart created, do the following:

1.  Open the chart definition.
2.  Click on the Chart XML tab.
3.  Set "Use Custom XML" to Yes.
4.  The XML code will display so that you can edit it.
5.  In the XML you will find an empty tag that looks like this:



It will be an empty tag if you have not selected a background for the chart.

You want to edit this section. In may case, I changed it to be:

        <fill type="Solid" color="0xffffff" opacity="0" />
        <border enabled="false"/>

6.  Apply Changes.

Now, display the chart as a "child" region of the region that you created for your background image.

The actual data area of the chart will now be transparent and your background image will show through. The chart will appear to be floating on your background. The labels, chart title and legend will also appear to be on top of your background image.

I am not sure that the XML I have provied is the best way to do it.... but it works!

The result is a dashboard with a single background image and charts that lay on top of that image.

Why bother to blog about Apex?

For a long time I have been thinking about blogging about Apex. Some of my colleagues had asked when I was going to start blogging. I guess it always seemed to me that maintaining a blog would be quite a commitment and very time consuming. On this point, I am not wrong.

But, there is another side to the story. As an Apex developer, I have used blogs extensively to solve problems, discover new ways of doing things and to learn about new functionality. Almost every day I read an Apex blog of some sort and learn something new. I have relied heavily on blogsaj and found them to be a critical resource.

So, if I use blogs for learning, how can I sit back and not contribute to the community and share those things that I myself am learning.

And, there is another reason I have started to blog. I am an Apex enthusiast and where I live  and consult (South Africa), there is still limited use of Application Express. This is because there is a limited development community here that is using and promoting Apex. What better way to promote the tool than to become involved in sharing information about its capabilities.

So, to blog or not to blog? The answer seems clear..... and here I am.

Saturday, 25 June 2011

Complex Dashboard in Apex

I have been spending time lately developing what has turned out to be a very complex dashboard in Apex. The dashboard has somewhere around twenty different dynamic flash charts on it, most of which have user-defined parameters.

At first, it seemed simple enough. I developed the graphs and the user input fields. One by one the graphs were tested and worked. However, when it was all put together, I was not at all happy. The charts took quite a while to load, and randomly some of the charts would start to load but never render.

After quite a bit of playing around and investigation, I discovered some things that I should have thought about from the start. Firstly, every time the page renders, the charts have to build their data set. The more complex the data and the more series involved, the longer it will take for the chart to render. Also, when there are user defined parameters associated with the data set, the chart will sometimes fail entirely.

Now, to find a solution.  I first deiced that it would be best to have less charts immediately render when the page is launched and allow the user to select what charts they want to see. To accomplish this, I used the parent / child region feature in Apex. I created several parent regions each based on a chart category. Next, I place "child" regions with charts (no template) inside each of the parent regions. To control the display, I created a checkbox list in the page sidebar. When the user selects the checkbox associated with the region, the charts are rendered. This was a great solution because the charts only render if the region is shown thus improving the overall performance. I even allowed the user to save their "dashboard settings" so that the dashboard would always first load using their "checkbox" settings. In a later blog I will wlak through all of the steps to do exactly this.

Now, what about those pesky data sets?  After quire a bit of investigation on the forum and elsewhere, I came to understand that if I could take the task of data generation away from the flash chart itself and make it as simple as possible, the faster the chart would load and the more manageable would be the user-defined paramets. Collections is the answer!

For charts that required a larger amount of data with multiple series, I created a collection on page load. It is not rocket science. Generally the collection must end up have the same query format as the chart itself:

select null link,
         <something> value
from ....

As long as you can successfully  build the collection, the chart can then reference the collection rather than having to build its own data set:

select c001 link,
         to_numcer(c002) value
from apex_collections
where collectin_name = <your collection>
and ....

You will be amazed as to how quickly the chart will render. Bam!  It is displayed!

To make performance even better, I only load up my collections once on the initial page load. The data is available already for all subsequent loads. The collections have already been populated so there is not data fetch required.

The final result is a very slick dashboard that renders very quickly and puts the user in control of what they see and when they see it. No matter how complicated the chart - the rendering is quick and slick.