Pulse provides an extremely powerful pivot concept that allows users to zoom out and drill down into data easily. Pulse has implemented the pivot logic itself to ensure it works on all databases but where possible it uses the databases builtin pivot ability for optimal speed.

Steps to create a Pivot Table

PulsePivot allows users to dynamically choose the group-by or pivot clauses for a table. To create one:

  1. Turn on PulsePivot mode in the editor panel.
  2. Write your SQL query.
    Do NOT use a limit clause else the pivot will only return the result of pivoting the limited data, see details below.
  3. GroupBy clauses are automatically detected, click on one to group by that column.
  4. Drag one of the columns into the pivot section, then click on it to pivot on that column.
  5. Count/Sum aggregations are auto generated, click to edit which aggregations are performed.
Dynamic Pivot Table

How it works

  • If no GroupBy or PivotOn columns are selected, Pulse uses your specified SQL query with an additional limit clause.
  • The first query sent to the database has no GroupOn clause, we use the table returned to automatically detect which columns a user should be able to choose from for grouping based on low cardinality
  • If GroupBy column(s) are specified, it wraps your query with a "select .... from (originalQuery) GROUP BY ..." to perform the aggregation on the database foroptimal speed.
  • If PivotOn column(s) are specified, Pulse pivots rows to become columns for most databases. For special databases that support it, we use their pivot implementation.

You can see that the pivot relies on wrapping the existing query, therefore you cannot have a limit clause on that inner query.

Pivot Controls

Pivot Controls

Clicking on the GroupBy or Select buttons will expand to allow modifying and selecting which columns are shown.
Below you can see the default aggregation calcuations that are available for all databases.

Saving Dashboard with Pivot and Filters

Pivot Variables

By default the configuration of filters and pivots are stored to panel specific variables. Where t0 means tab 0, grid is derived from the panels name. This naming can be overridden in the Interactive->key prefix setting on the right hand side editor panel. Pivot Variables Custom Key Prefix for Variables