Triply grouped-by tabular data - how to display?

Question

I need to display a bunch of data in a table on a website as part of a report. The problem is there's a bunch of it, and it needs to be visually grouped.

Each piece of data is a "usage" of an item, and has a date (representing when it was used), a store number (corresponding to the store that used it), an item name (the item that was used), and a quantity (the amount of the item that was used).

The user will have the option to either group by item, then store or by store, then item. Behind the scenes, I will group by date, as well.

The report needs to sum quantities both by item/store (depending on the group-by option) and by date, and total the sub-sums.

The thing is, I'm not sure how to display it. The best I can come up with right now is something like

                           ++------+------+------+------++-----+
                           || date | date | date | date || sum |
+-----------+--------------||======+======+======+======||=====+---+
| Item Name | Store Number ||  1   |  2   |  3   |  4   ||  10 | ^ |
|           +--------------||------+------+------+------||-----|---|
|           | Store Number ||  5   |  6   |  7   |  8   ||  26 | # |
|           +--------------||------+------+------+------||-----| # |
|           | Store Number ||  9   |  10  |  11  |  12  ||  42 | # |
|           +--------------||------+------+------+------||-----|   |
|                    Total ||  15  |  18  |  21  |  24  ||  78 |   |
+==========================++===========================++=====|   |
| Item Name | Store Number ||  1   |  2   |  3   |  4   ||  10 |   |
|           +--------------||------+------+------+------||-----|   |
|           | Store Number ||  5   |  6   |  7   |  8   ||  26 |   |
|           +--------------||------+------+------+------||-----|   |
|           | Store Number ||  9   |  10  |  11  |  12  ||  42 |   |
|           +--------------||------+------+------+------||-----|---|
|                    Total ||  15  |  18  |  21  |  24  ||  78 | v |
+==========================++===========================++=====+---+
                            | < | ####              | > |
                            +---------------------------+

To group by item then store, or switch "Item Name" with "Store Number" for group by store then item.

The date columns (many more than just four) would scroll left/right and the items/stores would scroll up/down, to keep the table on one page. In other words, the date headers are frozen in place vertically, and the item name, store numbers, and sums are frozen horizontally.

The problem would be with the implementation: how the heck to I represent that in HTML!?

The better question is: is there a better way?

What is your advice on solving this problem? (Preferably elegantly and easily)

Answer 1

It's doable, but i wouldn't be doing this in html directly myself. As complex as it will be to set it up in the first place, it will be just has complicated to modify it later when the business need changes.

If you do end-up trying it, they i'd recommend looking into concepts of stacked and embedded tables, scrolling divs, and fixed-position/overlapping divs. Even so, it won't be fun trying to get something this complex working in more than just one type of browser.

I'd recommend looking into some higher-level tools. ASP.NET has some good controls for this if you still want to take a somewhat manual approach. They have built-in controls, that can be modified in code, or you can code (as opposed to mark-up) tables, and still take advantage of the event model to query/re-query the database.

Better yet, you could look at a reporting tool like SSRS (and there are many others), or even MS Access for basic needs.

Answer 2

I think the best solution is to group the data by item, like the following image http://img51.imageshack.us/img51/6434/layoutsv.png

The HTML would be something like the folowing:

<div style="overflow: auto; width: 100%; height: 500px">
    <h1>Item 1</h1>
    <table>
        <tr>
            <th></th>
            <th>Date</th>
            ...
            ...
        </tr>
        <tr>
            <th>Store #</th>
            <td>1</td>
            ...
            ...
        </tr>
        ...
        ...
    </table>
    <h1>Item 2</h1>
    ...
    ...
</div>

For the fixed column you should look here: http://stackoverflow.com/questions/684211/html-table-with-fixed-headers-and-a-fixed-column