Data Table

Examples

Displaying All Columns in Query

<DataTable data={orders_summary}/>
No Results

Selecting Specific Columns

<DataTable data={orders_summary}> 
    <Column id=state title="Sales State"/> 
	<Column id=item/> 
	<Column id=category/> 
	<Column id=sales fmt=usd/> 
	<Column id=channel/> 
</DataTable>
No Results

Custom Column Formatting

You can use the fmt prop to format your columns using built-in format names or Excel format codes

<DataTable data={country_summary}>
	<Column id=country />
	<Column id=category />
	<Column id=value_usd fmt=eur/>
    <Column id=yoy title="Y/Y Growth" fmt=pct3/>
</DataTable>
No Results

Formatting Driven by Another Column

This example includes a custom_format column, which contains a different currency format code for many of the rows.

<DataTable data={country_summary_fmts}>
	<Column id=country />
	<Column id=category />
	<Column id=value_usd fmtColumn=custom_format/>
    <Column id=yoy title="Y/Y Growth" fmt=pct3/>
</DataTable>
No Results
<DataTable data={orders_summary} search=true/>
No Results

Sort

<DataTable data={orders_summary} sort="sales desc">
    <Column id=category/> 
    <Column id=item/> 
    <Column id=sales fmt=usd/> 
</DataTable>
No Results

Deltas

<DataTable data={country_summary}>
	<Column id=country />
	<Column id=category />
	<Column id=value_usd />
    <Column id=yoy contentType=delta fmt=pct title="Y/Y Chg"/>
</DataTable>
No Results

Sparklines

Sparklines require an array inside a cell of your table. You can create an array using the array_agg() function in DuckDB syntax. Below is an example query using this function, and the resulting DataTable.

WITH monthly_sales AS (
    SELECT 
        category,
        DATE_TRUNC('month', order_datetime) AS date,
        SUM(sales) AS monthly_sales
    FROM 
        needful_things.orders
    GROUP BY 
        category, DATE_TRUNC('month', order_datetime)
)
SELECT 
    category,
    sum(monthly_sales) as total_sales,
    ARRAY_AGG({'date': date, 'sales': monthly_sales}) AS sales
FROM 
    monthly_sales
GROUP BY 
    category
order by total_sales desc
<DataTable data={categories}>
    <Column id=category/>
    <Column id=sales title="Orders" contentType=sparkline sparkX=date sparkY=sales />
    <Column id=sales title="Sales" contentType=sparkarea sparkX=date sparkY=sales sparkColor=#53768a/>
    <Column id=sales title="AOV" contentType=sparkbar sparkX=date sparkY=sales sparkColor=#97ba99/>
</DataTable>
No Results

Bar Chart Column

<DataTable data={country_summary}>
	<Column id=country />
	<Column id=category align=center/>
	<Column id=value_usd title="Sales" contentType=bar/>
  	<Column id=value_usd title="Sales" contentType=bar barColor=#aecfaf/>
  	<Column id=value_usd title="Sales" contentType=bar barColor=#ffe08a backgroundColor=#ebebeb/>
</DataTable>
No Results

Bar Chart Column - Right Aligned

<DataTable data={country_summary}>
	<Column id=country />
	<Column id=category align=center/>
	<Column id=value_usd title="Sales" contentType=bar align=right />
  	<Column id=value_usd title="Sales" contentType=bar barColor=#aecfaf align=right />
  	<Column id=value_usd title="Sales" contentType=bar barColor=#ffe08a backgroundColor=#ebebeb align=right/>
</DataTable>
No Results

Total Row

Default total aggregation is sum

<DataTable data={country_example} totalRow=true rows=5>
  <Column id=country/>
  <Column id=gdp_usd/>
  <Column id=gdp_growth fmt='pct2'/>
  <Column id=population fmt='#,##0"M"'/>
</DataTable>
No Results

Using Built-in Aggregation Functions

<DataTable data={country_example} totalRow=true rows=5>
  <Column id=country/>
  <Column id=gdp_usd totalAgg=sum/>
  <Column id=gdp_growth totalAgg=weightedMean weightCol=gdp_usd fmt='pct2'/>
  <Column id=population totalAgg=mean fmt='#,##0"M"'/>
</DataTable>
No Results

Custom Aggregations Values

<DataTable data={countries} totalRow=true rows=5>
  <Column id=country totalAgg="Just the USA"/>
  <Column id=gdp_usd totalAgg={countries[0].gdp_usd} totalFmt=usd/>
</DataTable>
No Results

Custom Total Formats

<DataTable data={countries} totalRow=true rows=5>
  <Column id=country totalAgg="All Countries"/>
  <Column id=continent totalAgg=countDistinct totalFmt='# "Unique continents"'/>
  <Column id=gdp_usd totalAgg=sum fmt='$#,##0"B"' totalFmt='$#,##0.0,"T"'/>
  <Column id=gdp_growth totalAgg=mean fmt='pct2' totalFmt='pct1'/>
  <Column id=interest_rate totalAgg=mean fmt='pct2' totalFmt='pct1'/>
  <Column id=inflation_rate totalAgg=mean fmt='pct2' totalFmt='pct1'/>
  <Column id=jobless_rate totalAgg=mean fmt='pct0'/>
  <Column id=gov_budget totalAgg=mean fmt='0.0"%"'/>
  <Column id=debt_to_gdp totalAgg=mean fmt='0"%"'/>
  <Column id=current_account totalAgg=mean fmt='0.0"%"'/>
  <Column id=population totalAgg=sum fmt='#,##0"M"'/>
</DataTable>
No Results

Conditional Formatting

Default (scaleColor=green)

<DataTable data={countries}>
    <Column id=country />
    <Column id=country_id align=center/>
    <Column id=category align=center/>
    <Column id=value_usd contentType=colorscale/>
</DataTable>
No Results

scaleColor=red

<DataTable data={countries}>
    <Column id=country />
    <Column id=country_id align=center/>
    <Column id=category align=center/>
    <Column id=value_usd contentType=colorscale scaleColor=red/>
</DataTable>
No Results

scaleColor=blue

<DataTable data={countries}>
    <Column id=country />
    <Column id=country_id align=center/>
    <Column id=category align=center/>
    <Column id=value_usd contentType=colorscale scaleColor=blue/>
</DataTable>
No Results

Custom Colors

When you pass a custom color to scaleColor, Evidence will create a color palette for you, starting at white and ending at the color you provided. See examples further down the page to see how to specify a custom color palette with multiple colors.

<DataTable data={orders_by_category} rowNumbers=true>
  <Column id=month/>
  <Column id=category/>
  <Column id=sales_usd0k contentType=colorscale scaleColor=#a85ab8 align=center/>
  <Column id=num_orders_num0 contentType=colorscale scaleColor=#e3af05 align=center/>
  <Column id=aov_usd2 contentType=colorscale scaleColor=#c43957 align=center/>
</DataTable>
1
2
3
4
5
6
7
8
9
10
No Results

Custom Color Palettes

Diverging Scale

<DataTable data={numbers}>
  <Column id=name/>
  <Column id=number contentType=colorscale scaleColor={['#6db678','white','#ce5050']}/>
</DataTable>
No Results

Heatmap

<DataTable data={numbers}>
  <Column id=name/>
  <Column id=number contentType=colorscale scaleColor={['#6db678','#ebbb38','#ce5050']}/>
</DataTable>
No Results

Color Breakpoints

Use colorBreakpoints or colorMid/colorMin/colorMax to control which values are assigned to which sections of the color scale

<DataTable data={negatives} rows=all>
  <Column id=name/>
  <Column id=number contentType=colorscale scaleColor={['#ce5050','white','#6db678']} colorMid=0/>
</DataTable>
No Results

Create Scale from Another Column

The number column in this example has a color scale defined by the scale_defining_number column, rather than by its own values.

<DataTable data={numbers_othercol}>
  <Column id=name/>
  <Column id=scale_defining_number fontColor={['green','red']}/>
  <Column id=number contentType=colorscale scaleColor={['#6db678','white','#ce5050']} scaleColumn=scale_defining_number fmtCol=fmt/>
</DataTable>
No Results

Red Negatives

<DataTable data={negatives}>
  <Column id=name/>
  <Column id=number redNegatives=true/>
</DataTable>
No Results

Including Images

You can include images by indicating either an absolute path e.g. https://www.example.com/images/image.png or a relative path e.g. /images/image.png. For relative paths, see storing static files in a static folder.

In this example, flag is either an absolute path or a relative path to the image.

<DataTable data={countries}>
	<Column id=flag contentType=image height=30px align=center />
	<Column id=country />
	<Column id=country_id align=center />
	<Column id=category />
	<Column id=value_usd />
</DataTable>
No Results
<DataTable data={countries}>
	<Column id=country_url contentType=link linkLabel=country />
	<Column id=country_id align=center />
	<Column id=category />
	<Column id=value_usd />
</DataTable>
No Results
<DataTable data={countries}>
	<Column id=country />
	<Column id=country_id align=center />
	<Column id=category />
	<Column id=value_usd />
	<Column id=country_url contentType=link linkLabel="Details →" />
</DataTable>
No Results

HTML Content

```sql html_in_table
select '<b>Bold</b> text' as "HTML in Table", 0 as row_number union all
select '<i>Italic</i> text', 1 union all
select '<a href="https://evidence.dev">Link</a>', 2 union all
select '<img src="https://raw.githubusercontent.com/evidence-dev/media-kit/main/png/wordmark-gray-800.png" width="200px"/>', 3 union all
select 'Inline <code class=markdown>Code</code></br> is supported', 4
order by row_number
```

<DataTable data={html_in_table}>
    <Column id="HTML in Table" contentType=html/>
</DataTable>
No Results

To apply styling to most HTML tags, you should add the class=markdown attribute to the tag in your column. This will apply the same styling as the markdown renderer.

This example includes a column country_url which contains a country name as a search term in Google (e.g., https://google.ca/search?q=canada)

<DataTable data={countries} search=true link=country_url showLinkCol/>

Click on a row to navigate using the row link:

No Results

In this example, the SQL query contains a column with links to parameterized pages in the app. Below is an example of the SQL that could be used to generate such links:

select
    category,
    '/parameterized-pages/' || category as category_link,
    sum(sales) as sales_usd0
from needful_things.orders
group by 1

You can then use the link property of the DataTable to use your link column as a row link (category_link in this example):

<DataTable data={orders} link=category_link />

By default, the link column of your table is hidden. If you would like it to be displayed in the table, you can use showLinkCol=true.

Styling

Row Shading + Row Lines

<DataTable data={countries} rowShading=true />
No Results

Row Shading + No Row Lines

<DataTable data={countries} rowShading=true rowLines=false />
No Results

No Lines or Shading

<DataTable data={countries} rowLines=false />
No Results

Column Alignment

<DataTable data={country_summary}>
	<Column id=country align=right />
	<Column id=country_id align=center />
	<Column id=category align=left />
	<Column id=value_usd align=left />
</DataTable>
No Results

Custom Column Titles

<DataTable data={country_summary}>
	<Column id=country title="Country Name" />
	<Column id=country_id align=center title="ID" />
	<Column id=category align=center title="Product Category" />
	<Column id=value_usd title="Sales in 2022" />
</DataTable>
No Results

Raw Column Names

<DataTable data={country_summary} formatColumnTitles=false />
No Results

Groups - Accordion

Without subtotals

<DataTable data={orders} groupBy=state>
 	<Column id=state/> 
	<Column id=category totalAgg=""/> 
	<Column id=item totalAgg=""/> 
	<Column id=orders/> 
	<Column id=sales fmt=usd/> 
	<Column id=growth fmt=pct1/> 
</DataTable>
No Results

With Subtotals

<DataTable data={orders} groupBy=state subtotals=true> 
 	<Column id=state/> 
	<Column id=category totalAgg=""/> 
	<Column id=item totalAgg=""/> 
	<Column id=orders/> 
	<Column id=sales fmt=usd/> 
	<Column id=growth fmt=pct1/> 
</DataTable>
No Results

Closed by Default

<DataTable data={orders} groupBy=state subtotals=true totalRow=true groupsOpen=false> 
 	<Column id=state totalAgg=countDistinct totalFmt='0 "states"'/> 
	<Column id=category totalAgg=countDistinct totalFmt='[=1]0 "category";0 "categories"'/> 
	<Column id=item  totalAgg=countDistinct totalFmt='[=1]0 "item";0 "items"'/> 
	<Column id=orders/> 
	<Column id=sales fmt=usd0k/> 
	<Column id=growth contentType=delta fmt=pct totalAgg=weightedMean weightCol=sales/> 
</DataTable>
No Results

With Configured Columns

<DataTable data={orders} groupBy=category subtotals=true totalRow=true> 
 	<Column id=state totalAgg=countDistinct totalFmt='0 "states"'/> 
	<Column id=category totalAgg=Total/> 
	<Column id=item  totalAgg=countDistinct totalFmt='0 "items"'/> 
	<Column id=orders contentType=colorscale/> 
	<Column id=sales fmt=usd0k/> 
	<Column id=growth contentType=delta fmt=pct totalAgg=weightedMean weightCol=sales/> 
</DataTable>
No Results

Groups - Section

Without subtotals

<DataTable data={orders} groupBy=state groupType=section/>
No Results

With Subtotals

<DataTable data={orders} groupBy=state subtotals=true groupType=section>
 	<Column id=state totalAgg=countDistinct totalFmt='[=1]0 "state";0 "states"'/> 
	<Column id=category totalAgg=Total/> 
	<Column id=item  totalAgg=countDistinct totalFmt='0 "items"'/> 
	<Column id=orders/> 
	<Column id=sales fmt=usd1k/> 
	<Column id=growth contentType=delta neutralMin=-0.02 neutralMax=0.02 fmt=pct1 totalAgg=weightedMean weightCol=sales /> 
</DataTable>
No Results

With Configured Columns

<DataTable data={orders} groupBy=category groupType=section subtotals=true totalRow=true totalRowColor=#fff0cc> 
 	<Column id=state totalAgg=countDistinct totalFmt='[=1]0 "state";0 "states"'/> 
	<Column id=category totalAgg=Total/> 
	<Column id=item  totalAgg=countDistinct totalFmt='0 "items"'/> 
	<Column id=orders contentType=colorscale/> 
	<Column id=sales fmt=usd1k/> 
	<Column id=growth contentType=delta neutralMin=-0.02 neutralMax=0.02 fmt=pct1 totalAgg=weightedMean weightCol=sales /> 
</DataTable>
No Results

Column Groups

<DataTable data={countries} totalRow=true rows=5 wrapTitles groupBy=continent groupType=section totalRowColor=#f2f2f2>
  <Column id=continent totalAgg="Total" totalFmt='# "Unique continents"'/>
  <Column id=country totalAgg=countDistinct totalFmt='0 "countries"'/>
  <Column id=gdp_usd totalAgg=sum fmt='$#,##0"B"' totalFmt='$#,##0.0,"T"' colGroup="GDP"/>
  <Column id=gdp_growth totalAgg=weightedMean weightCol=gdp_usd fmt='pct1' colGroup="GDP" contentType=delta/>
  <Column id=jobless_rate totalAgg=weightedMean weightCol=gdp_usd fmt='pct1' contentType=colorscale scaleColor=red colGroup="Labour Market"/>
  <Column id=population totalAgg=sum fmt='#,##0"M"' totalFmt='#,##0.0,"B"' colGroup="Labour Market"/>
  <Column id=interest_rate totalAgg=weightedMean weightCol=gdp_usd fmt='pct2' wrapTitle=false colGroup="Other"/>
  <Column id=inflation_rate totalAgg=weightedMean weightCol=gdp_usd fmt='pct2' colGroup="Other"/>
  <Column id=gov_budget totalAgg=weightedMean weightCol=gdp_usd fmt='0.0"%"' contentType=delta colGroup="Other"/>
  <Column id=current_account totalAgg=weightedMean weightCol=gdp_usd fmt='0.0"%"' colGroup="Other"/>
</DataTable>
No Results

Wrap Titles

<DataTable data={countries} wrapTitles=true /> 
No Results

DataTable

Options

Required

Query name, wrapped in curly braces

Options:
query name

Number of rows to show in the table before paginating results. Use rows=all to show all rows in the table.

Options:
number | all
Default:
10

Background color of the header row

Options:
Hex color code | css color name

Font color of the header row

Options:
Hex color code | css color name

Show a total row at the bottom of the table, defaults to sum of all numeric columns

Options:
Default:
false

Background color of the total row

Options:
Hex color code | css color name

Font color of the total row

Options:
Hex color code | css color name

Turns on or off row index numbers

Options:
Default:
false

Turns on or off borders at the bottom of each row

Options:
Default:
true

Shades every second row in light grey

Options:
Default:
false

Background color of the table

Options:
Hex color code | css color name

Enable sort for each column - click the column title to sort

Options:
Default:
true

Column to sort by on initial page load. Sort direction is asc if unspecified. Can only sort by one column using this prop. If you need multi-column sort, use the order by clause in your sql in combination with this prop.

Options:
column name + asc/desc

Enable download data button below the table on hover

Options:
Default:
true

Enable auto-formatting of column titles. Turn off to show raw SQL column names

Options:
Default:
true

Wrap column titles

Options:
Default:
false

Enable a more compact table view that allows more content vertically and horizontally

Options:
Default:
false

Whether to show the column supplied to the link prop

Options:
Default:
false

Helper for writing DataTable syntax with many columns. When set to true, markdown for the DataTable including each Column contained within the query will be generated and displayed below the table.

Options:
Default:
false

Sets behaviour for empty datasets. Can throw an error, a warning, or allow empty. When set to 'error', empty datasets will block builds in build:strict. Note this only applies to initial page load - empty datasets caused by input component changes (dropdowns, etc.) are allowed.

Default:
error

Text to display when an empty dataset is received - only applies when emptySet is 'warn' or 'pass', or when the empty dataset is a result of an input component change (dropdowns, etc.).

Options:
string
Default:
No records

Groups

Groups allow you to create sections within your table, increasing the density of the content you're displaying. Groups are currently limited to 1 level, but will be expanded in future versions.

Column to use to create groups. Note that groups are currently limited to a single group column.

Options:
column name

How the groups are shown in the table. Can be accordion (expand/collapse) or section (group column values are merged across rows)

Default:
accordion

Whether to show aggregated totals for the groups

Options:
Default:
false

Specify an override format to use in the subtotal row (see available formats). Custom strings or values are unformatted by default.

Options:
Excel-style format | built-in format | custom format

[groupType=accordion] Whether to show the accordions as open on page load

Options:
Default:
true

[groupType=accordion] Background color for the accordion row

Options:
Hex color code | css color name

[groupType=section] Background color for the subtotal row

Options:
Hex color code | css color name

[groupType=section] Font color for the subtotal row

Options:
Hex color code | css color name

[groupType=section] Where the group label will appear in its cell

Default:
middle

Column

Use the Column component to choose specific columns to display in your table, and to apply options to specific columns. If you don't supply any columns to the table, it will display all columns from your query result.

Options

Required

Column id (from SQL query)

Options:
column name

Override title of column

Options:
string
Default:
column name (formatted)

Align column text

Default:
left

Format the values in the column (see available formats)

Options:
Excel-style format | built-in format | custom format

Column to use to format values in this column. This is used to achieve different value formats by row. The fmtColumn should contain strings of format codes - either Evidence built-in formats or Excel codes.

Options:
column name

Specify an aggregation function to use for the total row. Accepts predefined functions, custom strings or values

Default:
sum

Specify an override format to use in the total row (see available formats). Custom strings or values are unformatted by default.

Options:
Excel-style format | built-in format | custom format

Column to use as the weight values for weighted mean aggregation. If not specified, a weight of 1 for each value will be used and the result will be the same as the mean aggregation.

Options:
column name

Wrap column text

Options:
Default:
false

Wrap column title

Options:
Default:
false

Lets you specify how to treat the content within a column. See below for contentType-specific options.

Group name to display above a group of columns. Columns with the same group name will get a shared header above them

Options:
string

Conditionally sets the font color to red based on whether the selected value is less than 0

Options:
Default:
false

Images

contentType=image

Height of image in pixels

Options:
number
Default:
original height of image

Width of image in pixels

Options:
number
Default:
original width of image

Alt text for image

Options:
column name
Default:
Name of the image file (excluding the file extension)

contentType=link

Text to display for link

Options:
column name | string
Default:
raw url

Whether to open link in new tab

Options:
Default:
false

Deltas

contentType=delta

Whether to show the up/down delta arrow symbol

Options:
Default:
true

If present, negative comparison values appear in green, and positive values appear in red.

Options:
Default:
false

Whether to show the delta value. Set this to false to show only the delta arrow indicator.

Options:
Default:
true

Start of the range for 'neutral' values, which appear in grey font with a dash instead of an up/down arrow. By default, neutral is not applied to any values.

Options:
number
Default:
0

End of the range for 'neutral' values, which appear in grey font with a dash instead of an up/down arrow. By default, neutral is not applied to any values.

Options:
number
Default:
0

Whether to display the delta as a 'chip', with a background color and border.

Options:
Default:
false

Sparklines

contentType=sparkline contentType=sparkarea contentType=sparkbar

Column within an array cell to use as the x-axis for the spark viz. Arrays can be created inside a query using the array_agg() function from DuckDB

Options:
column from array cell

Column within an array cell to use as the y-axis for the spark viz. Arrays can be created inside a query using the array_agg() function from DuckDB

Options:
column from array cell

Whether to truncate the y-axis

Options:
Default:
false

Height of the spark viz. Making the viz taller will increase the height of the full table row

Options:
number
Default:
18

Width of the spark viz

Options:
number
Default:
90

Color of the spark viz

Bar Chart Column

contentType=bar

Color of the bars. Affects positive bars only. See negativeBarColor to change color of negative bars

Color of negative bars

Whether to hide the data labels on the bars

Options:
Default:
false

Background color for bar chart

Default:
transparent

Conditional Formatting (Color Scales)

contentType=colorscale

Color to use for the scale

Default:
green

Set a minimum for the scale. Any values below that minimum will appear in the lowest color on the scale

Options:
number
Default:
min of column

Set a midpoint for the scale

Options:
number
Default:
mid of column

Set a maximum for the scale. Any values above that maximum will appear in the highest color on the scale

Options:
number
Default:
max of column

Array of numbers to use as breakpoints for each color in your color scale. Should line up with the colors you provide in scaleColor

Options:
array of numbers

Column to use to define the color scale range. Values in this column will have their cell color determined by the value in the scaleColumn

Options:
column name

HTML

contentType=html

To apply styling to HTML tags, you will need to add the class=markdown attribute to the HTML tag in your column. This will apply the same styling as the markdown renderer. E.g., <code class=markdown>Code</code>