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
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
vdf.hdf are then built on top of
Aggregate Summary Rows
Analytic functions allow performing calculations among a
peer group of rows and most of the common aggregates such as
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.
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 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!
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.
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.
- All DataFrames used in the union should be aggregate DataFrames.
- The first DataFrame used is the primary aggregate DataFrame.
- The union DataFrame still preserves uniqueness of the combination of the row/column values used to identify a specific cell
Here the summaries are organized along the rows of the pivot table.
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.