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

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: HTML table with fixed headers and a fixed column?