The Big Table Issue

My friend/ex-coworker Sam the Wonder Boy used to send me late night AIM messages comprised of only three letters, “M.F.R.” This would then send chills up my spine, and I’d curl up on the floor in fetal position and weep nonstop.

OK, I’m exaggerating a quite bit, and Sam doesn’t do that anymore.

“M.F.R.” stands for “Monthly Forecast Report.” It was one of the many modules of a huge intranet web application we worked on years ago. As the name implies, it was a report. Before arriving to the actual report screen, the user could select some criteria such as date range, products, etc. Depending on the selection, the report can have up to sixty columns and thousands of rows. It was quite a challenge both on the backend and frontend. I initially created it using server side Excel API and dumped it to the frontend as an excel sheet. In version 2 I made it as an HTML table with the Excel export option. In version 3 I ditched HTML and went for Crystal Report, in version 3.5 it became Active Report. In version 4 we rewrote the whole application as a .NET client app, with the report section being Excel again. In version 5, well there wasn’t a version 5. The whole project got outsourced to India and the team was disbanded. But that’s a blog for another day.

The Big Table Problem

In the perfect world of a web developer’s life, a data table would have a few columns and rows so it’d fit perfectly in our content area and blends with the rest of the UI elements seamlessly. But in reality, a data table can get very big and ugly especially in financial web applications.

The problem is when the table gets big it no longer fits in the viewport. A viewport can be the whole area of the browser window, or a container area within the browser window(e.g. a scrollable DIV). To see the rest of the data the user would have to scroll. However this causes the user to lose sight of the column headers and the first column.

Another problem is performance. A huge recordset takes a while to generate on the backend, and even longer to render to the frontend.

The Approach

I feel as UI developers/designers, our first role is a problem solver. This means the solution doesn’t always lie in HTML/CSS/JS. Looking at the situation at hand, is HTML the best medium? I have to say no, that’s why I opted for Excel first. The benefit of using Excel:

Excellent with table format display.

Allows users for additional manipulation such as formulas and freeze panels.

Most printing friendly, the user can define print area.

Fast rendering.

However, there are downsides to using Excel:

Users have to have Excel client installed to view sheets.(Although this isn’t too much of a problem for an intranet app)

Does not allow data manipulation(not easily at least, unless you make custom VBA)

Let’s look at other alternatives.

Active Reports and Crystal Reports are two popular enterprise level reporting tools. I used both of them with .Net. Both software have RIA report viewers using ActiveX.

Pros:

Fast rendering time.

Allows pagination.

Allows custom template report format.

Cons:

Additional cost.

Not printing friendly.

Does not allow data manipulation, view only.

Of course, the approaches I listed are for Microsoft shops. There may be other non-MS solutions I’m not familiar with.

Excel is my favorite so far. However, there are times when you’re stuck doing it the HTML way, which is what the rest of this article is about.

HTML Solutions

We have three issues: vertical, horizontal, and performance.

Vertical is the easiest to solve. First, we can lock the column headers, so when the user scrolls down they’d still see what data they’re looking at.

When the table has hundreds or thousands of rows, it’s best not to display them all at once. Not only is it a performance hog, but it’s also rather meaningless from a usability POV. Pagination is the most commonly used. One of the best examples of pagination combined with AJAX I’ve seen is done by Blizzard’s World of Warcraft Armory page. The filtering feature allows the user quickly find data on the fly.

Another method without using pagination is, to load just enough rows to over fill the viewport area. Then when the user scrolls a bit more, it loads additional rows using AJAX. Google reader does this when loading feeds. I feel this is the best way for front-load performance. However, it may not be feasible where the user needs to see additional data quickly.

Horizontal scrolling issue, unfortunately is something I haven’t been able to solve despite much research. Ideally, we should be able to lock the first column(typically the identifier column), so when scrolling sideways, the user can easily identify which row they’re looking at. As of now, we can’t freeze column easily. During my research, I’ve run into a few sites that came close, but all with drawbacks.

Lock or Freeze Table Columns – Great work by Brett Merkey. This is as close as it gets. Unfortunately, it uses IE expression, which means it won’t work with Firefox or Safari. However, if you’re building an intranet app where IE is the standard browser, this is the only solution I know of.

Smart Grid, an Ajax-driven table – This does the job somewhat, however it has no control over data cell height, if the data spans to multiple lines.

Jquery driven table – This one uses nested tables and Jquery. It’s quite a performance hog.

xTable – The end effect is good, however this was done by nesting multiple tables.

Freeze Panes – Again, huge nested tables. Not too realistic for real world applications.

Google Docs – The spreadsheet in Google Docs behaves very much like Excel. However it’s done by DIVs. Although this is a viable solution, but I feel the cost of development is too much.

My Proposal

I think the best way is to have the browser to have the ability to lock columns natively. Unfortunately as of now, COL and COLGROUP have very little support other than few CSS attributes. Ideally, to freeze a column, I’d like it to be as easy as:

COL.freeze, COLGROUP.freeze { position: fixed/relative; /* lock */ display: none/inline/block etc /*so we can toggle */ left: x; stacking-order: n; /* allows multiple freezing */ }

I’ve been reading up on HTML5 and CSS3 specs. I don’t see any evidence yet that much, or any improvement will be done with COL/COLGROUP in the near future. One may argue that this is asking too much, after all HTML/CSS is for presentation only. But seeing the advanced selectors in CSS3, I believe the consensus is to have CSS replace some of the workarounds that are currently done by Javascript.

If you have any suggestions/solutions to this problem, I’d LOVE to hear from you.

update: this post has been translated to Serbo-Croatian language, courtesy of Jovana Milutinovich.