RFC: HTML::Dashboard (Spreadsheet-like formatting for HTML tables)

[janert]

I am looking for comments on a new Perl module. The purpose of the module is to take a data set (e.g. from a DB query), apply a bunch of formatting rules, and render the resulting information in the form of an HTML table.

Why this module?

For reports delivered via the web, it is often desirable to be able to apply data-dependent formatting rules, such as:

• shading every second row to make reading easier
• highlighting table cells (or rows, or columns) when some condition is fullfilled (e.g. when the value of cell is outside of some range)
• passing the content through some random formatting filter (such as sprintf(...) or substr(...))

It is often also desirable to be able to sort the data (on a random column) or to restrict the set of columns included in the report.

It is quite painful to build and apply such rules in an ad-hoc fashion while building the HTML table from a data set. (In particular when working on a one-off report which should take no more than half an hour to write.)

Hence a module to take care of all of that.

Proposal:

I have put together a module (tentatively named HTML::Dashboard), which allows the programmer to specify a number of formatting rules, which will be applied to the data as it is rendered into HTML. The module is currently in alpha, and I am looking for comments (in particular in regards to usability, features, documentation, bugs).

The best way to get an idea of what the module (currently) can do is to look at some examples. I put together a "gallery" show-casing the most important features. You can find it here: Gallery

The POD can be found here: Documentation

and the download here: Download

Questions:

Feel free to take a look and let me know what you think. In particular, I'd like to know: Is this useful? And does it work?

Is the documentation understandable (the API is uncomfortably large, in my opinion)?

Is anything missing?

And finally, I am looking for comments on the proposed module name. It's not perfect - any better ideas?

Thanks!

---

Responses to Comments

Thanks everyone for your comments.

The first thing I took away from the comments was that my priority naming convention "red/yellow/green" is confusing. I think this makes sense, I will change the API to "hi/med/low". (The rationale for the original naming was that in a dashboard you often want to show whether something is "in the red" or "in the green".)

I also wanted to respond to two questions which seemed to be raised several times: Why not CSS? And: Why not a templating solution?

To answer the first: All of this is done through CSS --- but I have to be able to insert the appropriate class- or style-attributes into the cell tags, based on the value of the data. This is what this module does!

A similar argument holds for templates. Templates establish the layout of a table from the outset, which makes it hard to do cell-content-dependent formatting from within the template. And it is simply not convenient to build templates with lots of conditional code in the template --- I know, having used HTML::Template quite extensively (and liking it a lot: cf. an article I did for Perl.com on Filters within HTML::Template.)

Again, thanks for your comments.

[clscott]

Re: Naming
A table is definitely not a dashboard, even though I guess it's part of a dashboard project for you.

Perhaps HTML::FormattedTable would be better?

Re: API


1. What are the benefits over using a template to produce my html table and changing my style sheet?
2. Can I call $dash->set_row_grn( sub { $_[0]->1%2 }, 'black' ); ? If so then what does 'grn' mean and what's the point of *_ylw, *_grn, *_red methods?
3. How would I achieve the following? I want the value 'Smith' to be highlighted in the 'String' column but not in the 'Name' column.

Those questions aside I think its a very cool module and one I could see myself using for those ad-hoc reporting/monitoring applications that everyone has to hack up once in a while.

Edited to fix a nonsensical sentence.

[hossman]

I haven't looked too closely at your API, just at your output -- but i definitely think a majority of this should be done with CSS ... the value adds you can provide are:

• generating the xhtml tables from the raw data
• providing a rule based interface to add a CSS class to rows/columns/cells based on coderefs or regexes
• automaticaly sorting the data as specified

...i would leave the concrete specifics of how the rows/columns/cells look to a user supplied CSS. That way if i decide i want all the negative numbers to be in red instead of bold, i can just change my (possibly site global) CSS file, instead of editing the script that calls your module.

Your mechanism for prioritizing rules is interesting ... but i would avoid naming them red/ylw/grn ... it's too easy for people to assume those are color specific and not realize they relate to precedence. I would just have a precedence argument that is numeric, higher number wins (or lower, your choice) .. the interesting cases are:

• multiple rules have the same precedence? ... in that case i would just use all the CSS classes for that rule (it's legal to have multiple class names separated by space, the browser applies them all)
• should cell rules override row rules override column rules? .. i would say not necessarily, you can allow undef as a precedence and use that to indicate that it's applied no matter what other rules are picked based on their precedence (ie: it's equal to whatever the highest value found is). ... perhaps an optional method could be called by the client specifying an uber-precedence of row/col/cell (if this method is never used, their rules are all equal)

[dragonchild]

Just out of curiousity, if you're building these kinds of reports, why not just use Excel::Template? It provides all these formatting capabilities, and more.

[ChemBoy]

I have some code somewhat like this that uses a set of modules I built on top of HTML::Template, rather than using H::T directly (I was hoping to extend it to Excel::Template and PDF::Template, but it appears I won't be so lucky). I can't speak to what the OP is doing, but I know that my application takes a lot of datasets in different sizes and shapes, and has to figure out (or guess) such things as the color-coding of each column on the fly, which is somewhat outside the scope of a generic templating system. That said, using a template module to actually produce the output at the end has made that project a lot easier, and I heartily recommend it.

[TGI]

This library sounds very useful.

HTML-Table seems to be a popular module for building HTML tables. Does it make sense to sublcass it and make your module into HTML::Table::AutoFormatted?

Are the red, ylw and grn formatting routines providing a way to set priority for the routine? So if conditions would call for both red and grn rules to apply, only the red is used? This is confusing. Why not either pass in a priority number? Or use "high", "low" and "standard" as your priority names. The color thing is confusing.

[trs80]

Class::DBI::Plugin::FilterOnClick does those tasks and more. You can create your own sub routines to allow very complex formatting based on a number of factors. The built in sorting and searching features also assist with making finding information easier. I have used it on a number of databases and it has performed well on MySQL, MSSQL and Postgres backends.

[rodion]

I looked at your examples and documentation, and at Class::DBI::Plugin::FilterOnClick, as suggested by trs80. FilterOnClick looks very comprehensive, but with a lot to learn just to get oriented. Your module is simple and straight forward. It looks like it would be easy to use, and quite useful for those aplication where what you want to do is fairly simple. I agree that Dashboard is not a name that would be easily recognized. If I had to guess what the module did from the name, I'd probably guess wrong. It is, however, a distinctive name.

The examples where a major plus, and were the best way to communicate what the module does.

I noticed a trivial typo in the second TODO bullet, "neat to" was repeated. (I didn't read it carefully, but didn't notice others, unlike Class::DBI::Plugin::FilterOnClick. That's much better than I could do.)

If you happen to remember, drop me a message when you release it. I'd like to know about it, and what it's called. Good luck with the module.

[janert]

Thanks!