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).
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.
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.
- 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
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.