The Pivot Grid Standard is a new component within the Dashboard Maintenance Unit. This feature is only available in the Windows App. A pivot table can be created in a ready-to-go layout, which enables the end-users to personalize the layout of the report. This can be done by a drag-and-drop functionality and conditional formatting. The result can be sorted, grouped and filtered and this is displayed in a cross-tabular format. The pivot table can be printed and exported, and it supports a drill-down functionality.

Pivot Grid loads the entire table contents from the Data Adapter, which is returned to the client machine. Performance test is done and is acceptable until 100.000 records.

Possible data sources are:

• Cube views. The MD command type is recommended for a simplified view of the data.
• SQL
• Method Query, such as CertificationWorkFlowUnit, DataUnit, DataUnitComparison or ICMatchingforWorkflowUnit.

Below a number of tips and tricks are summarized to get the most out of Pivot Grid:

• Dimensions can be dragged to the hidden fields (to limit the amount of data loaded), filter area, row area, column area or data area.
• Multiple dimensions can be assigned to the row or column using a comma separator.
• The Grid settings can be modified via right-clicking the properties box. Users can apply global filters (show pre-filter), activate (sub-)totals for groupings and aggregations. Users can save their adjusted pivot grid or restore to the original setup.
• Calculated fields can be added to the column and/ or grouping to perform string, date-time, logical and math operations over data (both as text and as decimal). These can be built from the Expression Editor.
• The user can also apply (advanced) conditional formatting by right-clicking on a cell in the pivot grid.
• For easy recognition the best practice is to name the resulting table. This table is generated when the data adapter is run.
• A loop parameter can be used to loop through a list of Entities to return multiple entities for a cube view. But don’t loop over any dimension that is in the row/ column of the grid!

The Large Data Pivot Grid is similar to the Pivot Grid. The main difference is the functionality for supporting large data sets. This feature supports paging functionality and server-side processing, where the Pivot Grid processes on the client side. The Large Data Pivot Grid was tested with acceptable performance with 2 to 4 billion records. To remove unwanted database columns from the large data grid, fields can be excluded.

Do you know how to use OneStream Table Views in the version 5.2? If not, then keep reading our next article on this subject