APEXBlogs v2 - Statistics

This post is part of the 10 days of APEXBlogs. This series of blog posts highlight the features of the new version (APEXBlogs v2) of APEXBlogs.info which will be released shortly.

So far I blogged about:

1. Backend changes
2. Showing the blog posts APEX 4.0 style
3. Twitter sync for #apexblogs and #orclapex
4. Plugins section
5. Links
6. APEX Usage
7. Make your APEX app iPad compatible
   
8. Statistics (this post)

I guess you know by now I really like playing with charts (and numbers)... what about some statistics about what's happening in APEX world?

I included statistics about the number of Blogs, Blog Posts, Plugins, Tweets and Links in the new APEXBlogs site:

Once the new APEXBlogs goes into production I plan to add statistics about number of visitors and which countries are accessing the site the most.

If you can think of other statistics you want me to include, feel free to add a comment to this blog post.

Two posts to go before the launch...

APEX 4 (bug) - Series Type (Bar, Line, Marker) and Multiple Series

A new feature in APEX 4.0 is that you can define per series what you want the series type (Bar, Line or Marker).

Lets start with an example.

We create a 3D Column chart based on the salary of the employees.

Our first Series looks like this (the Series Type is per default Bar):

select null as link, ename, sal

from emp

Now to add a second series you have two choices, either you add another value to the existing series or you create a new series. We will go with the first option, so our query becomes:

select null as link, ename, sal, nvl(comm,0) as comm

from emp

If you run the chart you see for both salary as commission a bar (column)

Now we want to add a line with the average of the salary, so we add another series with as sql query:

select null as link, ename, avg(sal) over() as avg_sal

from emp

The Chart Series look like this

Make sure you select Line as Series Type for Series 2 (the average salary)

When you run the chart you expect to see the same chart as above but with a line that represents the average of the salary...

As you can see that is not really what happened. It seems there is a bug in APEX 4.0.2.00.07 (and probably before) with defining multiple series in the same query.

What happened is that the first value, salary, is still a bar, but the second value, commission, changed from bar to line and the third value, average salary, became a bar (that is because our main chart type is a 3D Column chart, so it takes whatever is default). So APEX doesn't take the Series type into account correctly.

The workarounds is to not use the single query with multiple values, but a single query per series.

Running the chart again shows us what we expected

(Found in APEX 4.0.2.00.07)

APEX 4 (bug) - Chart Attributes > Add Series > Build Query button

Let's pretend you still have that 3D Column chart of my previous post or create a new chart.

Now edit the chart and go to Chart Attributes and look for the Chart Series section.

Click the Add Series button, you'll get a screen like

Hit the Build Query button to open the wizard to generate the SQL statement and follow the wizard. Define the owner

Define the table or view

Define the Label and Value...

Hmm... the value option is not here... yes, that is a bug... if you go further with the wizard you will end up with "select * from emp" instead of a real query for the chart.

The strange thing though is that during the creation of the chart it works just fine. Also when you edit an existing series and hit the Build Query button it works ok as you can see on the next screenshot.

My guess is there's something wrong with session state.

The workaround is to edit an existing series, follow the wizard there, copy the sql statement, cancel the edit, and then add a new series and paste the sql statement. Or if you are more experienced and know the syntax of the chart you can just manually enter the sql statement.

(Found in APEX 4.0.2.00.07)

APEX 4 (bug) - Font Grid Label in Charts

For a chart you can define the fonts and colors you want to use for the different components (mostly text) on the chart. This works great except for the Grid Label. It seems that that value never gets stored when you change it.

I thought if I wanted to change the grid lines of the chart I could change the Grid Label settings. I'm not sure why Label is specified after Grid and not Line, as I'm not aware of labels against the grid. It seems that currently there is a hardcoded value of "Black" against the line color of the X axis grid and nothing for the Y axis grid (so default of AnyChart).

Again the workaround is simple if you want to have a different line colors of the grid (X / Y Axis), you can just change it in the XML after you specified Custom XML = Yes.

The chart looks like this after doing the above change:

(Found in APEX 4.0.2.00.07).

APEX 4 (bug) - Axes in Charts

Consider this chart, it just shows the salary of the employees in a 3D Bar chart.

Now, if you want your chart to start with e.g. 500 instead of 0 you need to define the axis minimum value.

The wizard shows X Axis: Min, so lets put 500 as the value in... However running the chart again gives the same result.

This is in fact a known bug in APEX 4 (bug id 11700598), or you can read on it in this forum thread. Basically the Axes Settings should show the Y Axis Min instead of the X Axis.

The workaround is to change your chart type to 3D Column (instead of 3D Bar Chart).

Running the page gives us this result, so yes it's different, but for this chart the wizard is correct. Later you can change the chart type again and it should be ok.

So again we want the Y Axis to start with 500 instead of 0, so we define the Y Axis: Min in the Axes Settings. (see that now Y Axis: Min is listed and not X Axis: Min)

Run the page again and the chart will start from 500 instead of 0.

So now you can change the type back to 3D Bar Chart and you see it applied the start value correctly (the axis start now from 500).

My recommendation to the APEX Development team would be that either we see both X and Y Axis min and max or add a dynamic action to the drop down list of the chart type to dynamically show the appropriate axis. (next to fixing the bug)

!! But there is even a nastier bug related to the above...

Let's say we kept the 3D Column chart type but added a scrollbar for the X-Axis.

Running the page shows us this! The chart is only partially visible.

This is related to the previous bug... as we defined a value of the X-Axis Min when the chart type was still 3D Bar Chart, APEX still keeps that into account... (that is why the workaround worked before). However in this case it has a negative impact on the way the chart is shown.

The way to correct the issue is, change the Chart Type back to 3D Bar Chart, delete the value that is in X-Axis Min, hit the Apply Changes button, edit the chart again and change the Chart Type back to 3D Column.

Running the page again shows the correct chart.

(Found in APEX 4.0.2.00.07).

APEX 4 (bug) - Gradient in Charts

Something surprised me in APEX 4.0.2.00.07 (and probably entire 4.0).

APEX allows to specify a Gradient Angle when you specify two background colors for the chart and put the Background Type to Gradient. (see screenshot)

A value of 0 degrees results in a horizontal gradient with the first background color on the left and the second background color on the right.

Running the page results in:

Cool isn't it? ... But I just wanted it a little bit different, I wanted the first color (green) on top and the second color (yellow) at the bottom. So I would have thought if I change the value of the Gradient Angle to 90 that will do it... but no, the same (or very very similar).

Looking at the XML that was generated it showed me:

So although I put 90 degrees in the Gradient Angle, it seems like APEX divided it by something close to 60...

If you want the same as me and really use the Gradient Angle you either have to multiply your value by 60 or change the XML yourselves (Custom XML = Yes and you can change it). I prefer the second option as if they fix it (as I can't believe it was intentional!) you end up with a different gradient angle.

Happy charting ...

APEX 4 (bug) - Add marker to your chart

It seems to me that in APEX 4.0.2.00.07 (and probably entire 4.0, but can't check at the moment) there's a bug with adding markers to your (bar/column) chart. For a Pie chart it works fine.

If you edit your Chart Attributes you can define a Marker in the Display Settings.

However when you run the page and look at the chart there is no Marker to see.

If you need Markers, there are two workarounds at the moment:

- customise the XML (my preferred option): you set Custom XML in the Chart Attributes to Yes and you change the marker section to the marker you want. You will see marker type="None", but you can easily change that. Below I changed it to Cross, to have a cross marker.

- add another Series in the chart of type Marker, but this is not my preferred option because you lose performance as it will need to run the query twice.

When you run the page after you changed the XML, the chart with the marker looks like this (see the cross on top of the bar):

I'm sure the APEX development team will fix it soon, but for now you know a workaround.

OBUG Benelux Connect 2011

The OBUG Benelux Connect 2011 conference is almost there. If you want to go to the biggest Oracle conference in the Benelux, you have to be present on March 29 at SQUARE in Brussels, Belgium!

This year it's even better than last year, with top speakers such as Chris Leone, Tom Kyte, Wim Coekaerts, Andrew Sutherland and Peter Gates. There are over 42 different (mostly customer) presentations, User Experience Customer Feedback sessions, an enormous vendor showcase and a large Oracle demo ground.

Also for Oracle Application Express (APEX) there is again a complete track. The APEX sessions are not pure technical, but more case studies. It's a great way to see what others have done with APEX! I'll also do a presentation and this time it will be special for me, as it will be the first time I'll publicly show a product we developed "AE GPS Tracker/Intelligence", which is currently beta-tested by some pre-selected customers.

So I would say, OBUG Benelux 2011 is definitely not to miss! People like Tom Kyte and Wim Coekaerts (a Belgian) are not that much in the Benelux!

You find the complete program in the Program magazine OBUG Benelux Connect 2011.

You can register for the event here.

Hope to see you there!

Make your APEX app iPad compatible

I really like surfing on my iPad whenever I'm not behind my laptop.

As the iPad doesn't have a big resolution, Apple made it so that it scales websites to fit on the device. Most of the time it's doing a really good job, but a lot depend on how the developer created the site.

If you look at APEX applications, if you are using a standard theme, you have a big chance it renders well and you don't have to do that much.

However if you find that something doesn't look good, the way I make my application 'iPad compatible' is by using a different css depending the browser and device that hits the app.

Below you see a screenshot of the Header in the Template of the new APEXBlogs site.

My starting theme for APEXBlogs v2 was theme 22, but I customised it heavily as you'll see in a couple of days. I'm doing that by overwriting the standard css by defining my own in the apexblogs.css file (so make sure your own css is defined after the standard APEX ones). If the max width of the device is 1024px (like the iPad) I overwrite my own css with specific settings for these devices. So in your link you define for the media "only screen and (max-device-width: 1024px)".

As you can see theme 22 of APEX 4 itself actually renders different css's based on the browser (see if IE in screenshot). I chose to not worry about IE6 compatibility, that is why that div "outdated-browser" is in.

75 days of silence

I've been quiet for 75 days now, which seems a lot to me and never did feel like that! Time is flying...

After Tobias's message I felt I had to do another post and give you some feedback what is going on.

Just to assure you I'm fine I took a quick picture with my webcam. I might look a bit more tired as usual and my first grey hairs start to appear, but I'm still alive and kicking...

I'm still passionate about Oracle and APEX and work every day (and night) with this technology. That is also the biggest reason I was so quiet the last weeks. I've been very busy with so many exciting things! But I'll do some more blog posts about these in the next days and weeks as many are useful for the APEX community too.

I'm not sure you already knew but APEX Evangelists is back on the map. Sumneva didn't work out as expected and got dissolved in the form it was, in November last year (as a joint venture of APEX Evangelists and Sumner Technologies). So both John Scott and myself went back to our original name of APEX Evangelists. Scott Spendolini and Doug Gault decided to keep the name Sumneva as a new business venture in North America.

John and I decided to freshen up the look and feel of AE (APEX Evangelists) and the new website should go live very soon now. The new logo is already on, the content is coming...

Enough for now in this post... there is more to come :-) I'm hoping to go back to normal posting, meaning a new post every three days... and yes the new APEXBlogs is coming too...

Speak soon...

Dimitri

APEXBlogs v2 - APEX Usage

This post is part of the 10 days of APEXBlogs. This series of blog posts highlight the features of the new version (APEXBlogs v2) of APEXBlogs.info which will be released shortly.

So far I blogged about

1. Backend changes
2. Showing the blog posts APEX 4.0 style
3. Twitter sync for #apexblogs and #orclapex
4. Plugins section
5. Links
6. APEX Usage (this post)

As I travel quite a lot and speak to different people, I get the impression that APEX is more and more used in all parts of the world. So I would find it very interesting which part of the world is using Oracle Application Express a lot and which parts of the world are just adopting it.

It's very hard to get these numbers, maybe Oracle themselves have an idea if they look at the statistics of apex.oracle.com.

I thought a long time about a way to get to that info, but there is no easy answer really:

• I could look at the IP's that hit my sites and by that I can figure out from which country they are (e.g. by using IP2Location website)
• I could ask people to create a profile/login on the APEXBlogs v2 site, but why would they do that?
• I could mine other sites like Facebook, Linkedin, Blogs, Google, the APEX Forum etc. to see which people are talking about APEX or asking questions about it, but that would be technically the hardest job to do and I wouldn't find everybody that way either
• I could manually enter numbers, based on my talks at conferences and other persons feedback
• I could use Google Analytics (which is actually using the IP based solution) to see from which countries people come

My absolute dream would be that I would get APEXBlogs v2 in such a state that it's worthwhile for everybody dealing with APEX wanting to go to that site. We'll see in the future how that turns out, but you could definitely help me with that. I see APEXBlogs v2 evolving to more a portal page for the APEX Community... v2 is not anymore about just aggregating the blogs, it's doing a lot more as you could read before and will read in the next couple of posts.

Once we are online with APEXBlogs v2, the way you could help is talking about the site and tell everybody it would be fantastic to get the APEX adoption in card. You are probably as much a fan of APEX as me, but you might find it difficult to convince others to go with APEX and do more APEX projects. By showing that APEX is used all over the place, it might help to convince your client(s) and/or boss.

The other way you could help is to give me feedback about what would be useful for you and the people you know, so they have a reason to come to the site.

So back to the APEX Usage and how it will be shown in APEXBlogs v2.

I wanted to use a combination of two methods. My first routine looks at the people who are registered and plots that on a World Map (implemented and works great). In APEX 4.0 there's a wizard to create these maps. The second routine will look at the IP addresses that hit the site and verify if the numbers are aligned from the first routine. But I've an issue here... if I'm with a client and hit the site I've a certain IP address, when I'm at home I've another IP address, but I'm the same person. Or behind one IP address (e.g. a government) there might be 100 people using APEX.

I doubted to use a Google Map and show the points where people exactly are, but that would probably have the reverse affect and people don't want (or are not allowed) to share that information (privacy reasons).

APEX Patch 4.0.2 released

Just a quick note to say that there's a new patch for Oracle Application Express that fixes a number of issues and also adds some enhancements to APEX (e.g. two new themes, API additions to APEX_UTIL etc.)

To download and read more about the patch set, Joel Kallman from the APEX Development team wrote a good blog post about it.

Thumbs up for the APEX Development team for giving us these nice additions and the fixes.

Oracle Application Express (APEX) 4.1 and beyond

The APEX Development team updated the Statement of Direction for the future releases of Oracle Application Express.

As we can read on that page, Oracle Application Express 4.1 will focus on enhancement to existing functionality and additional capabilities to support applications running on mobile devices. Application Express 4.1 is planned to incorporate the following:

• Development for Mobile Applications – Include themes and HTML templates suitable for smart phones and mobile devices.
• Charting - Provide for chart rendering without using Flash (to enable display on mobile devices).
• Error Handling - Improve error handling and user-defined exception processing.
• Interactive Reporting – Allow multiple reports on one page and support pivot queries.
• Tabular Forms – Allow multiple tabular forms on one page and continue to expand tabular forms validations.
• Master-Detail-Detail – Allow the generation of pages to support master-detail-detail relationships.
• Dynamic Actions – Enhance conditional processing and allow dynamic actions to be defined for tabular forms, reports, and buttons.
• Plug-Ins - Add plug-in support for additional components and enhance plug-in definitions.
• Use of ROWID – Allow usage of ROWID for Automatic DML processing (as an alternative to identifying the PK columns).
• Modal Dialogue - Add ability to display a dialog on top of a page (the rest of the page will be grayed out).
• Websheets – Allow for greater control over user interface, new page section types, and enhanced spreadsheet / datagrid integration.
• Data Upload - Enable end-users to upload data into an existing table (within an application).
• Accessibility – Improve accessibility in existing themes and HTML templates.
• Numerous functional and performance improvements.

Great features I would say! For many of the above items we work around them with some hand written coding. As many of you know, I'm a heavy supporter of charts and dashboards and we've already been working on a non-Flash based charting engine for the current release of APEX.

This solution I actually showed at Oracle Open World too. We plan to release these non-Flash based charts soon too, so if you are on APEX 4.0 and you don't want to wait till 4.1 or later, no worries, something is coming ;-)

APEXBlogs v2 - Links

The Links section as it currently stands on apexblogs.info I moved off the homepage. In APEXBlogs v2 it will be an entire new page which holds an Interactive Report with all the Links I think are useful for people wanting information about Oracle Application Express.

As it's an Interactive Report, you can use the Group By functionality (new in APEX 4.0) to see the number of links by type. I created that report as the alternative view.

As you can see in the screenshot I started to capture links to Blogs (obviously), Oracle User Group APEX SIGs, APEX Examples and General APEX Links.

APEXBlogs v2 - Plugins section

I'll have to push back the release of APEXBlogs version 2 for some time. Most of the development is completed, but I'm still working on the new UI of the site. Before going live I also want to do some blog posts about what will be in this release, so if some of you have comments I can still consider these and possibly make changes. There are at least five other blog posts waiting, before the go-live...

As I wrote before, it's not just an update of the current APEXBlogs, instead it's a complete new release written from the ground up with many new sections/features.

One of the new sections in APEXBlogs v2 is the APEX 4 Plugins section. Aggregating blogs and tweets is fine, but in the APEX 4 world, plugins became very important, so I definitely wanted to keep up with what people wrote. So this new section of APEXBlogs is exactly doing that. It searches for the APEX 4 Plugins out there and aggregates them and provides you with an easy search (Interactive Report).

At this stage it aggregates the Oracle Plugins page and the Community Plugins rss feed. I decided to keep it like that for now and am not including other personal links to APEX plugins, but might consider to do that in the future if I see they don't get posted in the ones I aggregate now.

Technically it was interesting to get the Oracle Plugins as they don't provide an RSS feed of their plugins, so I parse the complete html page (with utl_html and regular expressions). I hope Oracle (Patrick) doesn't start to change the look and feel and the way they organized the page too many times ;-)

As you might see in the screenshot, I added a Rating column... I thought to review the plugins I aggregate and add my personal rating against them and some comments why. Obviously it will be a personal rating and might be subjective. On the community plugins page (apex-plugin.com) people can rate the plugins too, but the rating is not part of the RSS feed so I can't show that.

APEXBlogs v2 - Twitter sync for #apexblogs and #orclapex

Another new feature of APEXBlogs v2 will be the synchronisation of Tweets with the hashtags of #apexblogs and/or #orclapex.

Some of you might remember I enabled the synchronisation of Tweets with the hashtag of #apexblogs already in version 1, but I had to take it out as my procedure was sometimes hanging. My procedure/query would work in 95% of the times, but if Twitter was down, it would hang and the process would stay there forever. As Twitter was not that stable in the earlier days it caused to much of an issue, that was the reason it disappeared after a few weeks.

But now it's there again and better than before! Again I use the same new APEX 4 features as with the blogs; namely the detail view of the Interactive Report to show the tweets exactly like I want.

Here's a screenshot:

For the people interested behind the query to retrieve a Twitter stream, you can use something like this:

select *
from xmltable(xmlnamespaces('http://www.w3.org/2005/Atom' as "AE"), '//AE:entry'
passing httpuritype('http://search.twitter.com/search.atom?q=apexblogs').getxml()
columns title varchar2(250) path '/AE:entry/AE:title/text()'
);


That returns something like:

The above query would be easiest, but it won't always work. It depends the version of your database, your security settings and your environment.

Update:

Forgot to add that the twitter stream in APEXBlogs v2 is not realtime anymore. I'm going to sync every hour and will put a timer on the page till the next sync.

I choose to store the tweets in my own tables to have an archive, to increase performance and to enable easier search options through the Interactive Report.

APEXBlogs v2 - Showing the blog posts APEX 4.0 style

The main reason for APEXBlogs was to aggregate the blog posts and be able to search in an easy manner.

The way it was initially implemented was through a normal (classic report) in APEX and a search bar which allowed you to search for any text. Next you had the possibility to search between dates or on specific blogs.

To improve the standard functionalities of APEX 3.x I added some jQuery. E.g. clicking on the icon of "Blog Selection" would slide open the checkboxes with the blogs. Another example is the use of the jQuery date picker instead of the standard date picker.

This is how the search and blog view looked in version APEXBlogs v1.

APEXBlogs version 2 is written completely from scratch in APEX 4.0 and tries to use as much functionalities of APEX 4.0. So the above way of searching and the use of a classic report got replaced by an Interactive Report.

This is how the report view of the Interactive Report looks like.

This is how the detail view of the Interactive Report looks like (new in APEX 4.0).

As you can see, that view is similar to the classic report view I had in version 1.

The big difference is that it's just an Interactive Report, so all functionality of the Interactive Report you can still use! Adding filters, group by, doing highlighting etc.

You can just switch between the two views.

If you didn't know this functionality existed, you find it in the Report Attributes of your Interactive Report. There is a Detail View section which you have to enable and then you can add the html code you want it to look like.

Very neat feature of the Interactive Reports and it became very useful with building this new version of APEXBlogs.