Skip to main content

Advanced Reporting

SQL Frames has a polymorphic UI based on the type of the DataFrame. Using this as a foundation, the ideas presented in this section are trying to push the boundaries of tabular data rendering to meet some of the most advanced reporting requirements.


The main idea of the techniques presented here is that rather than creating a hdf, vdf or vdf.hdf on top of a gdf, we create several gdfs containing different peices of the information and then they are all glued together in the desired order using udf. The hdf, vdf and vdf.hdf are then built on top of such a udf.

Aggregate Summary Rows

Analytic functions allow performing calculations among a peer group of rows and most of the common aggregates such as COUNT, SUM, AVG, MIN and MAX have the same aggregate value for each of the peer rows. This repeation of data for each row may not be ideal especially when viewing several such metrics. It may be desirable to instead see these additional metrics of the peer rows as a set of separate summary rows. See the examples below to understand this type of visualization technique.

Single dimension

Here the data is aggregated by a single dimension and summary rows of the resulting peer rows is also added. This is done by first creating two separate aggregate DataFrames (gdf) one for each, then unpivoting (uvdf) the summary dataframe so the measures are arranged as rows and finally combining both sets of data using the (udf).

No-code conditional formatting

The udf inherits formatting features such as rendering and styling from the underlying DataFrames used to create the union. Hence, just by providing styling to the underlying DataFrames, the udf gets conditional styling without any complex conditional logic!

Sort Order

Notice how the orderByPrefix API can be used to ensure the rows of the different row sets stay together even when the user is sorting by other columns.

Multiple dimensions

It is also possible to do this with multiple dimensions and setting up the summary rows for the inner dimension values treated as peer rows. In the following example, the summary rows provide aggregate metrics for all the Item Type peer rows within each Region.


If the order of the rows within the summary rows set needs to be preserved, each summary should be sliced and added to the union. Then each summary has its own Row Set ID and hence the order is preserved.

Hierarchical Summary Rows

The above multi-dimensional example is further displayed as a hierarchy below.


Pivoting of a Union DataFrame (udf) is possible but is based on some assumptions.

  1. All DataFrames used in the union should be aggregate DataFrames.
  2. The first DataFrame used is the primary aggregate DataFrame.
  3. The union DataFrame still preserves uniqueness of the combination of the row/column values used to identify a specific cell

Pivot Summaries

Summary Rows

Here the summaries are organized along the rows of the pivot table.


Summary Columns

Here the summaries are organized along the columns of the pivot table.


Hierarchical Summary Rows

Summary rows work with hierarchical pivot tables as well.


Data with Summary Rows

Even though all the examples above were based on aggregate data, it is possible to display summary rows even with non-aggregate data. Scroll to the bottom of the DataFrame to view the summary rows.