Extend your charts with Google Visualization Charts

As already a lot of you know, I'm a heavy supporter of the Anychart charting engine, but there are other nice charting solutions as well...

A few weeks ago a customer of us requested a chart which you can replay. My first thought was "hmm that can be tricky", because I didn't know a standard feature of Anychart to do that. I could think about an AJAX solution where we refresh the chart so you could have kind of replay in time of your chart. When we asked the customer the exact requirements he pointed us to Google Visualization and the Motion Charts as what he wanted. When I first saw that I was impressed.


If you speak about timing... Roel blogged a few days ago with his showcase application of Google Visualizations. He made a nice application which shows a lot of the possibilities the Google Visualizations has. Louis-Guillaume also blogged about his implementation of the Google Visualization Organizational tree and Maps some time ago.

For me personally I'll still use Anychart for the normal kind of Charts, Dashboards and Gantt charts, but I would use Google's for Motion and the Organizational charts.


If you look at how Google Visualizations works; it needs some code to tell it what it needs to render and it needs data in a specific format (e.g. JSON). When I talked to Roel about it he said he made a PL/SQL package that creates the JSON string (in Google format) by passing in a select statement. We wondered if his package could be called by anyone on apex.oracle.com, so we did a quick test where my APEX app calls Roel's PL/SQL package and it worked out nicely.
We had to make some small changes because if you want to call packages from other schemas you want it to run with the data in your schema and not necessarily from where the package is compiled into. You can read more about the Definer and Invokers rights model here, but basically you need to use the AUTHID CURRENT_USER to let it work e.g.

CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2)
AUTHID CURRENT_USER

I think the visualization industry is very interesting and it seems more and more people are using graphical representations of their data...

APEX 3.2.1 Patch set available - Free Gantts and Maps

Oracle released APEX 3.2.1 patch set. You can download it from the APEX website.

It will do a lot of fixes as you can read in the patch set notes, some of my favorites are:

• Unable to use greater than character (>) in expression in CASE statement of computed column.
• Unable to import SQL script using SQL Workshop, SQL Scripts, Import utility.
• Degraded performance using interactive reports over time.

But another good reason for you to upgrade is because you get AnyGantt and AnyMap for free with it. Until APEX 4.0 comes out you need to write the xml yourself to generate the chart.
I've already some examples about Gantts and Maps on our Anychart Integration Kit website, but I'm working on releasing some more.

European APEX Training Days back in Belgium

Two years after our first "European APEX Training Days" we are coming back to Belgium to do another one. This one will be held in Leuven (the town I live in) from 26th till 28th of October 2009.

The format will remain the same; 3 days immersed in APEX with all APEX minded people and taught by two of the best known advocates of Oracle Application Express :-)

We have new topics for these training days and hope that next to the things you'll learn, you'll also have fun and feel the passion for APEX like we do.

Our Agenda (*) looks like this:

Monday

• Best Practices
• Working with Tabular Forms & Collections
• Real World Apex Dictionary Uses
• Webservice Integration

Tuesday

• Javascript, AJAX & JQuery
• Hands-on AJAX & JQuery
• Mastering Charts and Gantts
• Debugging APEX Applications

Wednesday

• Themes & Templates
• Hands-on Themes & Templates
• Writing Secure Applications
• Error Management

(*) We reserve the right to change content. In the event of any changes you will be given advance notice where possible.

Next to the predefined topics we are also available to answer all your other APEX questions at breaks and after hours. As before, on the second day we also planned something fun!

You can register for the training here.

Hope to see you in October,
Dimitri

Interview in Oracle Magazine

In the September/October edition of Oracle Magazine you find a part of the interview I had with one of the writers. I had to answer some questions and then the magazine selected three of them. You find it in the Peer-to-Peer section.

You can subscribe for free for the Oracle Magazine. I always enjoy reading the articles especially the ones of Tom Kyte, Sue Harper, David Peake and Steven Feuerstein.

APEX for Sales People

You, as a developer, are really convinced about Oracle Application Express (APEX) but that is not enough...

If your company doesn't follow you, or you don't have projects, you can't really do APEX projects right?

The problem is that you need to convince your manager, the sales guy or your customer in another way.
But the typical arguments why to use APEX, in a developer points of view are not the same as what they want to hear!

Most of the conversations by Sales guys or managers are about costs (decreasing IT costs/budgets), increasing margin and revenue, doing more with less people and to get the solution for a given problem in a fast way.
(And if it wasn't difficult enough you might be in front of not only a manager but also an IT person who's not that open for a change ;-))

One of our client's problem for ex. is how to react more quickly to business needs? That is a valid question no? What if the business wants to launch a new campaign but the software/IT organization is not ready for it yet? In a typical waterfall model type of project it can take several weeks or months to get a solution. Maybe a RAD-tool might be a good fit in that case. Especially if you work in modules or an Agile way where you have more releases, more often.

It's not easy to speak those people's language if you are not used to it. I also think it depends the person you have in front of you, as some are more open for new ideas than others. I can't really give you a bullet point of "If he says this Than say that" (although I'm sure some sales people got such training), but I'm convinced that if you can help the people in front of you to full-fill THEIR goals and you speak with confident about your ideas, you already made a good start.

It depends a bit the customer, but in a lot of the cases APEX can decrease costs and get a solution faster which enables them to get more revenue. A demo is in most of the time also a good way of proving your word. But always remember it's not always the cheapest solution that is the best at the end.

There's a Solution Kit for APEX available that can help you to convince other people too.

Using Keystrokes in a Web Application

There was an interesting question in the APEX Forum of somebody wanting to use the keyboard to control the screen.

Controlling your screen with the keyboard is a challenge when you are in a web environment as you can be on different platforms and have different browsers that are not always acting the same way.

I created a quick APEX page and included some javascript to enable that page to know what keys are typed from the keyboard. During testing I thought about what would happen with a text item and you want to type something in there... you don't want that something happens then (e.g. submitting the page when you hit S). Luckily in javascript you can control that as well.

<script type="text/javascript">
function checkKeyPress(evt) {
  // get events
  var evt = (evt) ? evt : ((event) ? event : null);
  if (!evt) return true;
  // get where you are on the page
  var node = (evt.target) ? evt.target : ((evt.srcElement) ? evt.srcElement : null);
  // if you are in a text item show this alert otherwise another
  if (node.type == "text") 
     alert('in item:' + evt.keyCode + ': ' + String.fromCharCode(evt.keyCode));
  else
     alert(evt.keyCode + ': ' + String.fromCharCode(evt.keyCode));
}

// on every Key Down, run the function to check which key was hit
document.onkeydown = checkKeyPress;

</script>

New animation in APEX charts

Lately I've been playing with the different animation types in Anychart 5.

It's pretty cool as you can define animation on the appearance of the chart and/or on the different types (bar, line, ...) of chart itself.

I build a little demo chart where you can select the animation you want for the different components. Just change the select list to the animation you want and you see immediately what it does.

Spline chart with Multiple Axes in APEX

I got a message a few days ago of somebody wanting a Spline chart with multiple Y-axes as the lines had different scaling.

Here's a way to get what you want.

1. Follow the wizards in APEX to create a normal line chart.
2. Standard in APEX you have Anychart 3.3, but that doesn't allow multiple Y-axes and a Spline chart, so you need a higher version of Anychart for that. APEX Evangelists made it easy to upgrade to Anychart 5 with the Anychart Integration kit for APEX.
3. Change the SELECT list of your serie to include a special alias e.g. "{n:Computer;t:Spline;y:ex}"
   That allows the integration kit to know that it needs to display the Spline type chart, with the name of Computer and it need to go on another Y-axe.
4. Run your chart again and that should give you the new chart.

On the picture is a Spline chart with different Y-axes for Video and Computer.

Optional;

If you want a nicer looking chart you might want to change your xml to Custom XML and add some specific Anychart 5 tags (e.g. for animation). The above example you find here with the source I used.

Important;

Apparently some people think I work for Anychart, which is not true. I work for APEX Evangelists (AE), a company 100% focussed on Oracle Application Express. APEX Evangelists offers training, development and consultancy services for APEX.
However it is true that I work(ed) closely with Anychart to create the Anychart integration kit for APEX and enhance it in time. APEX Evangelists is also the main partner of Anychart to deal with the APEX related questions and consultancy assignments (e.g. if you need help with creating your charts, maps, dashboards, ... or you want training in that area you can contact AE).

APEX 4.0 Highlights and Video

If you didn't see the video or ppt of Mike Hichwa's APEX 4.0 presentation at ODTUG, you should definitely have a look at it, so you know what is coming.

I decided to take notes during the video to remember myself about the highlights and decided to share them in this blog post. So here you go...

History of Oracle Application Express

I didn't know that Mike Hichwa got inspired by an ATM machine to create APEX.
Basically an ATM is very simple;
- You authenticate
- You need to select what you want through a menu
- You enter things in a Form
- Validations cick in to check if you do the right things
- Finally the process is run
- You can get your transaction as a printed report
This is exactly what we have in APEX, no? Why make it complex if things can be simple ;-)

Oracle Shop

The APEX Development team created a more sophistic APEX application which is the Oracle Shop where you can basically order Oracle software.
The application has some nice features like:
- it integrates with Oracle Apps in the backend
- it uses webservices to check your credit card
- it has some nice integration with javascript

The Oracle Shop is already live, you see it here.

Seven demonstrations of APEX 4.0

1. Websheets (start at minute 25)
Business people are able to create an application very fast with a lot of features. You can see this as a very rich web 2.0 Interactive Report with collaboration features build-in.
But also for experienced APEX people this is a good thing as you can transform it into a real APEX app. I included a screenshot with the main differences between APEX and Websheets.

2. Dynamic Actions
This basically allows developers to write more AJAX applications without needing to write javascript.
E.g. if you want to show an item dynamically based on a value of a previous item

3. Upgrade of Anychart 3.3 to Anychart 5.1
Not only will you get nicer looking charts, you will also get more types of charts.
I also find it usefull the wizard got improved, so directly you see which type of chart you are going to get.
If you wanted to have the Anychart 5.1 charts already in APEX 3.x you had to use the Anychart Integration Kit for APEX.


4. REST Web Services
It will make things easier to integrate with other sites and applications
In the video you see the integration with Yahoo Maps, Flickr, Picasa and Amazon.
Jason also mentioned that APEX Collections will get stored in an XMLType column, so it will be preparsed and the query will run a lot faster.


5. Plug-Ins (in the Shared Components section)
APEX 4.0 will allow people to extend the built-in functionalities. So we would be able to create our own widgets (item types, region types).
These plugins you can find on a specific site where people can share them. You would create these plugins in PL/SQL.
E.g. the video talks about a plugin to get a video region, or a star-rating item


6. Improved Interactive Reports
E.g. create your own where clauses inside your IR, be able to notify people when a specific report gets changed, be able to provide saved reports (as a developer and not like now that the end-user needs to save the report), share reports, download the IR as a searchable html etc.

7. Improved Application Builder
E.g. make the most important button yellow, very cool search functionalities with regular expressions


APEX 4.0 features that did not get demoed, but are things to look forward to:

- Declarative Tabular Forms
- AJAX Client-Side Validations
- Improved Error Handling
- Improved Tree Controls
- JQuery and JQuery UI
- Item Attributes
- Javascript Date pickers
- Editable combo-box
- APEX Listener is faster and has bigger possibilities than mod_plsql. It will also be able to have native access to the filesystem by using the APEX Listener.
- This will help a lot if you store your javascript files, etc on the filesystem. It will also simplify printing as FOP will be burned in.
- Thight integration with SQL Developer Data Modeler.
- In APEX 4.0 the wizards are also a lot shorter and more intuitive.
- Better integration with Subversion (more declarative).

A release date got also mentioned of APEX 4.0: planned for early 2010.
Just a reminder; APEX 4.0 will only run on Oracle Database 10gR2 and higher. That is another reason for people to upgrade their database...

The APEX Dev-team also showed a new packaged application with higher quality and ready to see web 2.0 features. E.g. "APEX Teamspace"; which allow team collaboration. It's a nice example of a Web 2.0 application. This would also run in APEX 3.2.

If you want to see it live, you have a chance to see it again on Oracle Open World!

Stacked Bar chart with negative values in APEX

Today I saw an interesting question in the APEX Forum: "Is it possible to create a stacked verticle bar chart that can display a negative value below the bottom line?"

I tried an example with this query in the Chart Series:

select null as link, value as label, rownum*-1+3 as value, rownum*-1+5 value2
from bar_order_vw

The bar_order_vw is defined as:

create view bar_order_vw (value) as
select sysdate + level as value
from dual connect by level < 10

The result when you use the standard Flash charts in APEX (based on Anychart 3.3) looks like this:


That is not really a nice chart I would say. It's very hard to see what the values are. Basically you see two values for a date. Value 1 is blue, value 2 is red. But some values are negative.
I already tried to play with the settings of the chart, but it doesn't really help to make it a bit nicer.

Next I wanted to know how this chart would look like with Anychart 5, so I used the Anychart integration kit for APEX. I just changed the source of the swf file to point to the integration kit so the chart would render with Anychart 5. This is what I got:


I think it looks a lot cleaner, don't you?

My guess is that in APEX 4.0 it will look fine without having to change anything as there you already have Anychart 5 integrated. For now you can use the integration kit for Anychart 5 or integrate with a charting engine manually.

Edit: Here you find the example in real-time.