Improved tables

[hughess]

Background

We love tables - they’re often the best way to organize and present data, especially when combined with in-table graphics. We haven’t had a chance yet to do tables justice in Evidence, but we’re starting that process now.

This issue will document the features we’re hoping to eventually support in tables, as well as the architecture decisions to make those features possible.

Any feedback or commentary is welcome!

Long-Term Inspiration

We take inspiration from tables that use high display density and graphics to present information. Stephen Few has many great examples of this type of thinking. Publications like The Economist also do a good job of this.

Eventually, we’d like to make it easy to build tables like these using your own data in Evidence.

Feature Ideas

Not an exhaustive list, but covers the major ideas:

• Column selection
  • Select columns
  • Custom column titles
  • Show/hide row numbers
• Styling control
  • Font size
  • Font colour
  • Background colour
  • Borders
  • Display density
  • Header formatting
• Search
• Column sort
• Pagination
• Download to CSV
• Horizontal scroll when table overflows width of page
• Column grouping (shared label above multiple columns - e.g., Financial Metrics > Sales | Gross Profit | Margin %)
• Row grouping (e.g., collapse repeated values into a merged cell to create a category)
• Conditional formatting (e.g., continuous colour scales, green/red for increase/decrease data)
• Cell graphics (sparklines, charts, symbols, etc.)

Immediate Needs

Below are the issues we currently have with our DataTable component:

• Columns truncated by default, without any ability to scroll horizontally
• The table’s display density is not high enough for many use cases (e.g., when you want to scan a table and see a lot of data at once)
• It’s difficult to find specific records in the table
• We’ve received some feedback that the data “scrubber” could be more intuitive

Implementation

Using a library

There are several libraries we’ve looked into for building tables in JavaScript, including AG-Grid, GridJS, Datatables, and others.

In general, it seems that the available table libraries would cover our basic table needs, but would impose a ceiling on what we can build in the long-term. Things like column grouping, row grouping, and cell graphics are difficult to achieve in the libraries we’ve investigated.

Syntax

A lot of the features we want to offer are at the column-level, meaning we will need some way to configure options for each column. Below are a few options to accomplish this:

Column component

<Table data={my_query}>
   <Column name=date align=left/>
   <Column name=sales/>
</Table>

• Big benefit of this syntax is the potential to use slots within the Column component:

<Table data={my_query}>
   <Column name=sales>
      <Sparkline/>
   </Column>
</Table>

Array of columns

<Table data={my_query} columns={["date", "sales"]} align={{"date": "left"}}/>

Steps to Implement

1. Solve the immediate issues we have in the DataTable component and replace DataTable with a more configurable table component
2. Release a "presentation table" which uses a set of defaults to create a publication quality table
3. Enable conditional formatting within table cells
4. Expand our library of table graphics (sparklines, charts, symbols, etc.)

[archiewood]

Syntax

I expected to like the Array syntax more, as it is less boilerplate to pick specific columns:

Array

<Table data={my_query} columns={["date", "sales"]}/>

(52 chars)

vs

Column component

<Table data={my_query}>
   <Column name=date/>
   <Column name=sales/>
</Table>

(76 chars)

But as soon as try to customize the specific column, that benefit falls away. I also find the two types of double braces in the Array syntactically confusing.

The other thing that's nice about the Column component syntax is it's a bit clearer what's in the table.

How could we reduce the boilerplate in Column component?

• Rename Column -> Col or just C
• A good VSCode snippet that writes out a lot of the above for you

[archiewood]

Thinking about this more, the column syntax is also more powerful for conditional logic. For example, in the scenario where you have loads of columns and want to exclude some:

<Table data={my_query_with_100_cols}>
    {#each Object.keys(my_query_with_100_cols[0]) as column} <!-- maybe we can improve this Object.keys() syntax-->
    {#if column != "the_column_to_exclude"}
    <Column name={column}/>
    {/if}
    {/each}
</Table>

[roveo]

I think in BI applications one of the most important things is the ability to make pivot tables, preferably nested, even more so than the ability to make nice tables for flat data.

E.g.

<PivotTable
  data={sales_stats}
  rows={["department", "salesperson"]}
  columns={["product_category"]}
  values-as="rows"
>
  <Value field="sales_amount" format="$" title="Sales Amount (YTD)" />
  <Value
    field="yoy_growth"
    format="%"
    title="YoY Growth in Sales"
    background-color="yoy_growth_sign"
  />
</PivotTable>

I don't know if this syntax would work with Svelte, but you get the idea.

Useful features that I can think of:

• make nested pivot tables with multiple levels for rows and columns
• encode values with cell background/foreground color
• have multiple variables displayed as rows/columns or together inside a cell
• assign human-readable titles to row/column header levels, so "salesperson" becomes "Sales Person"
• even better, embed arbitrary charts or component groups inside cells
• download as CSV/Excel, in the cloud open in a new Google Spreadsheet (asking for a friend in the Finance dept)
• perform simple aggregations on source data, so I don't have to write a separate SQL query for each level of detail that I want to show
• showing totals and subtotals at arbitrary levels
• collapsing sections/levels of the table, drilldown

[yukseltron]

Offering options on how to make tables responsive would be interesting (horizontal scroll, grouped rows, etc).

Based on the data type/size, there are different ideas that could be used for displaying the tables. This short article shows some simple ways to achieve responsive and accessible table design, however, the examples aren't as advanced as having graphs in them. I think that could be an interesting challenge to explore.

[archiewood]

Another thing that could be great here. (Perhaps for our next round of iteration)

Support for some kind of summary row. In an example report I'm speccing out, It would be useful to include a total row as the final row.

This could be configurable at the column level

<DataTable
  data={myquery} 
  totalsRow=true>
 <Column id=partner/>
 <Column id=item/>
 <Column id=num_units agg=sum/>
 <Column id=cost_usd agg=sum/>
</DataTable>

Currently I'm doing it manually like this:

<DataTable data={myquery}/>
<div class='invoice-total'>
<div>
    Total Cost: 
</div>
<div>
    <Value data={myquery} column =cost_usd />
</div>
</div>

[hughess]

@archiewood that’s a great idea. How would you expect totals to work in a table that’s paginated?

[archiewood]

Great question. Hadn’t thought about it.

I would expect it to sum across everything including invisible rows I think.

But I’d rarely implement this row if there was pagination, and I think I’d keep it off by default

[mcrascal]

This is fantastic. Going to close in favour of individual issues for the big ideas in here:

1. Pivot tables #596
2. Expanding table graphics: #744 and #588
3. Colouring table cells #679
4. Arbitrary content for table cells #570
5. Syntax #694