Allow Definition of Footer Region

There will be many use cases where you want to use a table, but you also want to have a footer with subtotals, subsequent calculations, etc. An Invoice or Work Order is a perfect example of that. Iā€™m working on a invoice template at the moment, which is how I ran into this issue.

My recommendation would be to allow a user to define the end row of a table region. Or, you can think of this as defining the start of the footer region. That way, when you modify data types for the table, it wonā€™t mess up everything youā€™ve done in the footer.

Although this might be a significantly large effort, I still think itā€™s worth considering - especially early on. Otherwise, youā€™ve basically eliminated the possibility of using Strict data types if you want to use subtotals with labels below your table.

I would recommend using a color other than gray to highlight the portion of the worksheet that is your Table. In Excel, when rows have been filtered, the font color of the row number changes to Blue. You could do something like that, also ā€¦ but I think it would be useful to have a visual of what portion of the worksheet is the relational database table.

To be even more innovative, perhaps you could allow a number of rows in a footer to be frozen, so that you could always see the totals.

Yes. This is a topic we have discussed internally many many times. :slight_smile:
I cannot comment on the timeline right now, but we are aware there are several vital use cases satisfied by the Footer.
Most important being the ability to sort/filter just the Table region with the Summations, footnotes etc being preserved at the bottom.

I spent some more time thinking about it.
My own personal opinionsā€¦

  1. Table portion of the Sheet shaded differently than the rest is interesting. We could also apply a slight shade to just the row-headers Table region rows.
  2. First row of the Footer is often the Summary Row ( stuff like SUM, UNIQUE, STDDEV, AVG etc per column). Rather than freezing the footer, the first row of the footer could stay at the bottom of the Sheet when the footer is scrolled out of the Viewport. -> ā€œSticky Footer-Headerā€. You can see the same effect in Excel if you have a Table and you scroll down such - the Table Header sticks and the Column Headers of the Sheet assume the Tableā€™s column names.

Right ā€¦ I havenā€™t tried sorting the table yet, but that definitely is a critical reason for defining the lower bound of the table. I would rate defining a footer or the last row of the table as a critical feature. That brings you closer to compatibility with Excel, also.

There are plenty of cases where you may want more than one table summary row. For example, a sum on one row followed by a percentage of that sumā€™s total on a second row. If you went with the sticky footer idea, even floating just the first row could be useful, but this is not a critical feature.

Tables in Excel maintain their own unique names, so they donā€™t require an association with a specific worksheet, and this allows there to be more than one table on a worksheet. I typically try to avoid creating more than one Table within a single Excel worksheet, although there are some instances where it can make sense. In general, though, limiting a worksheet to a single relational database Table is not a critical limitation for most use cases.

Thanks. We are pretty clear about not supporting Named Tables. The Sheet is the Table.
However, with the Footer in place, you can place ā€˜psuedo mini Tablesā€™ in the Footer ( or Header region already ) and refer to them using Named Ranges. These ā€˜psuedo mini Tablesā€™ would be immune to any sort/filter that you apply on your main-Sheet-Table as well.

Hereā€™s a thought. Treat the header and footer as if they were on one sheet, and the database on another sheet. The database sheet can be filtered, sorted, deleted, added, etc. without worrying about where the header or footer are. The header and footer can be placed as desired on the header/footer sheet. In between the header and footer is a viewport into the database sheet. Any filter, sorting, adding, deleting done on the header/footer sheet will actually impact the data on the database sheet. Scrolling within the viewport would scroll the database up and down to view different portions of the database records. Any adding/deleting of columns in either sheet would add/delete columns on both sheets.

Defining a viewport window could simply be a range of cells in the header/footer sheet. This allows other information on either side of the viewport. And it wouldnā€™t be limited to just header and footer information above and below, but other info on the sides.

The footer has totals. Floating subtotals could show that subsections subtotals, and scroll with the data in the viewport as that subsection scrolls by.

Hereā€™s a picture showing some of that.
image

Thanks for your input - We have heard similar thoughts from others as well.
Just like Table Header - we are considering introducing Table Footer.
We are considering - I cannot at the moment guarantee the rollout or the date of rollout.
Table Footer, just like the Header now will be free-form and unaffected by Columnā€™s type or Sort/Filters applied.
But it will be below the Table Region(Not by the side of it :slight_smile: ) . We are also considering allowing the Footerā€™s header to stick at the viewportā€™s bottom - Footerā€™s header is widely used as a Summations row in practice.

But these are early days. We havenā€™t started a formal design process.

I think Garyā€™s suggestion is more than just a way to make a footer region possible. I think it falls into a general request for Queries as well.

Defining the query to be viewable and scrollable within a certain range of cells is roughly similar to the idea of embedding another worksheet as a separate object that exists on an upper level much like other objects like images, or an iframe in html.

One of the limitations of an object with limited height/width is the ability to print the report and see all the data at once. This is why I would like a Query that expands to include all the data within a printable region (like an invoice with a table region that extends across multiple pages).