Alternative Pivot Tables

As many of you know, I don't do anything with technology 'in earnest', but love tinkering and it is great to have a 'project' with a functional 'end game'.

Recently, I have been a little 'off' my usual Excel activity and developing (for fun) in the evening, on trains, etc, some additional reporting for our Company Intranet, using VB.Net, ASP.Net and SQL Server. (I had already done some, but it called an iSeries server in the US, from the UK and as it has become very popular with staff I have decided to get the data onto a local SQL server and work it from there).

As an MVP I am lucky to be given an NFR licence for a great set of .Net controls from DevExpress and during the development of this stuff I have used their ASPxPivotGrid control. The features of this thing are amazing and the developer story is excellent. Now, I'm not trying to put the Excel unit down, as that for intuition, usability, etc is excellent, particularly in it's 2007 implementation, but let me run you down a few of the things this grid has that Excel could certainly do with.

Linked Fields

The ability to join two or more fields together in a 'group'. This feature ensures that fields which should be linked stay that way. For example a UserID and Name which should never be allowed separated. The small line, shown below, denotes they are a 'group'.

Multi-User

Just by the fact this is a web control and sits on the server it works great in a multi-user environment, The second benefit to it using the server is the speed of refresh which will pretty well always be quicker on the server that any client.

Developer Restrictable Zones

Each field can be set to show in all zones or only selected ones. This is great to stop a user adding days to the column area which can result in hundreds or thousands of columns, not too desirable. For example, in the demo here, you cannot add the 'Date' field to the 'Value' or 'Column' areas.

Single-Click Sorting

Click on a header and the column or row are sorted ascending, click again and it's done descending.

Paging

With the pager you can simply restrict the 'depth' to which the data goes down. That's an essential on the web, to keep stuff 'above the fold', but wouldn't that be nice in excel too?

Separate Field, DataField and Caption Properties

How often have you either had to 'put up' with either a duff name in the data source, or the same in the Pivot Table itself as Excel allows only unique names in fields, so for example, OrderValue in the database, becomes say 'Sum of OrderValue' in the table, it's long and ugly, so you try just changing it in the P/T back to OrderValue and Bang! In this grid you can display the caption property, have another (or same) name for the DataField property and yet another as the fieldname to refer to in code.

Drag and Drop

Using Ajax callbacks you can simply drag any field to any zone (if allowed by the developer) and when you see the little arrows, drop it and you field will appear there.

Obviously there are standard things like right click and collapse or expand a fields or all fields and filtering with a drop-down on what we would know as 'page fields' in excel (now called report filters)

The demo does not have any formatting but there are tons of 'skins' for it or you can use your own CSS. I'm not a design guy, more interested in the data, but I have it looking really attractive on our intranet.

As a developer story the possibilities are almost endless with so many 'switches' on each column, the whole grid and events on the client (JavaScript) or the server (any .Net language).

I have to say, I was impressed with the Excel P/T and still am. I love them, but this has a great UI story and most importantly, a great developer one too. Try out my basic demo using some (slightly weird) stats from this site (live) but go and look at the full potential at their site

Thank you DevExpress!