april 06, 2023
Interactive visualisation using HTML in Apache Superset

Past articles have discussed formatting methods using CSS template editing. This is an effective method if static formatting options are required. However, it’s not appropriate if you want to tweak a number in a table based on a value (such functionality is not available in Apache Superset), as values can change dynamically.
You can solve this problem by embedding HTML code as a string value in a table cell. A few examples of the implementation are shown below.
1. Colouring a number in a flat table depending on the value.
1.1. For the visualization example, let’s create a test table with three columns.
Query for dataset:
select 1 as a, 3 as b, 2 as c
union all select 2 as a, -3 as b, 2 as c
union all select 3 as a, 4 as b, -2 as c
union all select 4 as a, -2 as b, 2 as c

The numbers in column B need to be coloured:
- green if the value is positive
- in red if the value is negative.
1.2 Let’s use the dataset from step 1.1. as a subquery to format the table.
To format the values in the cell we will use the HTML element <span>, which is the main string container for the phrase content. To set the colour you need to insert a line in each cell of column B:
<span style=»color:<colour>»><column name></span>
To ensure that a number is colour coded, we use the if logic function and the CONCAT function to concatenate strings. Similar functions can be found in most dialects.
We form a query for the dataset of our table:
select a as A
,concat(‘<span style=»color:’, if(b < 0,’#DC143C’,’#2E8B57′), ‘»>’, toString(b), ‘</span>’) as B
,c as C
from (select 1 as a, 3 as b, 2 as c
union all select 2 as a, -3 as b, 2 as c
union all select 3 as a, 4 as b, -2 as c
union all select 4 as a, -2 as b, 2 as c) as t
The result is a table of this type.

- In the same way a histogram can be plotted in table cells depending on any aggregate. For example, from the column total (the original functionality only allows histograms from the maximum value of the column). Let’s implement the functionality for column C.
As in step 1, we use the <span> HTML element for cell formatting, if for colour selection and CONCAT for concatenation of rows. To draw a histogram bar in a cell, we will use the cell’s fill property and a linear-gradient (background-image: linear-gradient) to fill the necessary area of the cell with colour:
— Divide the cell into two parts and, depending on the sign, fill the left or right part with red or green, respectively.
— The fill boundaries for the gradient will be determined by the percentage ratio: the required percentage will be the share of the value in cells C of the sum of the whole column. Query for table dataset:
select a as A
,concat(‘<span style=»color:’, if(b < 0,’#DC143C’,’#2E8B57′), ‘»>’, toString(b), ‘</span>’) as B
,concat(‘<span style=»display: block; background-image: linear-gradient(to right, ‘,
if(c >= 0,
concat(‘#FFFFFF 50%, ‘, ‘#90EE90 50%, #90EE90 ‘, toString(50 + c/sum_c/2*100), ‘%, ‘, ‘#FFFFFF ‘, toString(50 + c/sum_c/2*100) , ‘%’, ‘); ‘),
if(50 — c/sum_c/2*100 < 0, ‘#FFA07A 50%, ‘,
concat(‘#FFFFFF ‘, toString(50 + c/sum_c/2*100), ‘%, ‘, ‘#FFA07A ‘, toString(50 + c/sum_c/2*100), ‘%, #FFA07A 50%, ‘, ‘#FFFFFF 50%’, ‘); ‘)))
,’)’, ‘text-align: center; , «> ‘, toString(c),’ </span>’ ) as C
from (select a, b, c, sum(c) over() as sum_c
from(select 1 as a, 3 as b, 2 as c
union all select 2 as a, -3 as b, 2 as c
union all select 3 as a, 4 as b, -2 as c
union all select 4 as a, -2 as b, 2 as c
)) as t
The result is a table of this type:

- Add a picture and a hyperlink to the table cells.
Objectives: Generate a header for the dashboard in tabular form, where:
— in the left cell will be a picture with the logo
— in the right cell a link to the site
This task can be done using HTML, use:
— img tag to add an image
— href tag to add a hyperlink
Query for dataset:
select ‘<img src=»https://static.tildacdn.com/tild6230-6132-4331-b361-633935643464/LogoSpacePlanner.png«
alt=»Logo» style=»height: 231px; width:604px;»/>’ as img
,'<p>URL to <a href=»https://lasmart.ru/»>GreenShelf home page</a>.</p>’ as href
We get a table of this type:

Remove the chart header and hide the column names for the table using CSS templates:
.header-title > .editable-title {display: none;}
#chart-id-76 th
{font-size: 0px;
background-color: #4f277c;
padding:0px;}
The final table is as follows
