Filtering/excluding certain rows from a result set to render a chart


When making queries I usually need to add a ‘totals’ row to the result, which I do with a UNION ALL. This ‘totals’ row is very useful for the table view, but not so much for the charts, since the ‘totals’ is not an actual data point. So I can’t render charts from this query. To circumvent this I create a second query with exactly the same code, but without the UNION ALL clause, to make the charts from that 2nd query.

Ideally I would want to use one query for both the table and the charts. Filtering or excluding rows from the result to create a graph would be very handy for this use case.

1 Like

Doesn’t a pivot table satisfy both your needs?

  1. just create your query as normal, no union all, no totals.
  2. let the pivot table construct the totals in the X and Y dimensions as needed
  3. because you haven’t explicitly added a summation (causing you extra work), your charts should now work fine.

Indeed functionally wise the pivot table satisfies those requirements, but it has less options for markup, since it doesn’t support html tags, or number formatting in the cells. Also I prefer the look of the normal table over the pivot table. When custom styling becomes easy, the pivot table would be a good choice for me.

So my feature request could also be add more styling options to the pivot table or make custom styling easier by providing a way to load custom css.

I think a better way of looking at this would be for Table visualisations to to include an option to add a Totals row, rather than artificially including totals as part of the query result set. Then the same granular dataset can be used for both Table and Chart visualisations.

1 Like

+1, this is a cleaner solution than what I suggested.

Instead of UNION ALL, you could use LEFT JOIN to put the Total Rows into new column, or maybe a window function to count all rows in your query so you get total rows.

Thus, you can satisfy the styling needs on the table viz. And because your Total Rows is a new column, it won’t appears on your chart

1 Like