Pivot Tables in XPages with OpenLink AJAX Toolkit

As good as Dojo is, one of the widgets missing is a pivot table. It’s not a new suggestion for Dojo. A quick search of the internet finds this request for it dating back 3 years. It appears some development was made on code back in 2006 in the 0.9 timeframe, based on an OpenRecord plugin. But unfortunately nothing has been included, as far as I can see, in either dijit or dojox libraries.

However, some time ago a former colleague identified a pivot table plugin as part of the OpenLink AJAX Toolkit (OAT) and I’ve been working over the last few days on implementing this within XPages. This is an open source javascript framework of widgets similar to Dojo available since mid-2006 and licenced under a GNU General Public License. Although most of the widgets have counterparts in Dojo, the notable exception is the pivot chart. There is a website available with examples of all the widgets (if you’re using IE8 you will need to use compatibility mode), as well as documentation.

For those who have delved into Dojo, let me say that the documentation is API documentation only and leaves a lot to be desired, at least for someone who is not an expert in javascript frameworks. The first challenge I faced was finding examples of data format expected by the widget and identifying what additional features needed. The example gives the javascript required, but doesn’t specify either of the above. Only by dissecting the related components in Firebug was I able to find further details. And of course none of this is in the documentation page. The “example” on that page offers nothing more than the list of parameters does.

Identifying the code to instantiate the widget was only the beginning. The next challenge, and the biggest I encountered, was how to embed the javascript that instantiates the pivot table into an XPage (or any Domino page). There is a page of documentation on basic operation to include the features in your web page. Note, that as well as the oat folder the other crucial folder that is required is the “styles” folder (this is missed from the documentation, but soon becomes apparent). The documentation does give a sample web page, but this leaves obvious challenges with XPages, that the script cannot easily be put in the HTML head. It is possible to access the HTML head in 8.5.2, as I’ve outlined before, but the application I wanted to use is on an 8.5.1 server. So the example I will show does not use the new head tag and will work successfully on 8.5.1 and 8.5.2 (I haven’t tried 8.5.0. If you’re still on it, I’d strongly advise you upgrade.)

The first thing you’ll need to do is download the files and install at least the oat folder and styles folder. There are two options here. The first is to save the files to the server. I’ve put them in the dominojs folder, alongside the various Dojo folders. This seemed sensible and also allows us to reference them in XPages resources (see below). The other option is to import the javascript and stylesheets as File Resources, but giving them names that represents their hierarchy (e.g. oat/loader.js). The next step is to include the “loader.js” file as a resource on your XPage. If you’ve installed them on the server in the js folder, the url you need is “/.ibmxspres/domino/domjs/oat/loader.js”. If you’ve used a file resource it’s just “oat/loader.js”, as with any normal file resource.

The next step is to include a variable called featureList. As far as I can deduce, this needs to be available before loader.js is triggered, so I’ve put it in a javascript library and imported that javascript library as a resource on my XPage ABOVE the reference to loader.js. When I included the featureList variable in an output script control on the XPage I found that it only intermittently installed the relevant features (I used Firebug to see the scripts installed, and all I got was loader.js and bootstrap.js a lot of the time), which meant I got a javascript error that window.featureList was not defined and that “new (OAT.pivot)” was not a contructor.

The final bit of code required was a function called init(). It just needs to be in an output script control, it doesn’t need to be called onLoad at all – loader.js will handle it and is just looking for an init() function. Obviously this may mean you need to change your application if you have a pre-existing init() function. The content for the pivot table is two arrays: one array called header and one called data (an array of arrays). Both arrays need the same number of elements. The data can be calculated at runtime – I’ve used a computed field control set to output HTML (escape=”false”) and wrapped in script tags. I will add that I’m yet to test this with large amounts of data, so the computed field control may not be appropriate.

Attached is the code I use for the XPage. With the new (OAT.pivot) there are four arrays referencing indices of the data. The first is a set of elements to appear on the left of the pivot chart, the second are elements to appear on the top, the third is for elements to appear in the page group, and the fourth is for the element used in the body of the pivot chart. For reference, pivot.js comprises the following code:

var featureList=[“pivot”,”statistics”,”barchart”];
var header = [“Company”,”Year”,”Quarter”,”Color”,”$$$’s spent on pencils”];

You can see the final example here: http://hermes.intec.co.uk/XpagesSamples/Javascript.nsf/testPivot.xsp