Functionally, pivot engines can be separated by the flexibility options they provide for grouping, filtering, sorting and extracting summary values. In its simplest form a pivot engine would provide row and column grouping by a field value, sorting on a single field value + single formula and summary values based on a single field + single formula.
For example: the pivot data analysis features of Excel are simple. For a table that has two columns – company and sales, you can display a pivot tablix that group the columns by company, sorts the columns in increasing/decreasing order based on the sum of sales and display the sum of sales as data values.
As the tables grew in size and diversity of information the need for pivot engines that perform more sophisticated queries emerged. The simple expression format represented by field + formula couples evolved to functional languages in which grouping, sorting, filtering and data values summaries are defined by formula based expressions. Furthermore the sophisticated pivots allow for multiple grouping expressions, sorting rules, filtering rules and summary values extracted on all possible levels of the data aggregation. This gives the IT professionals the needed flexibility to create reports and dashboards that are truly addressing the specificity of the data and extract valuable information quickly and easily – performing the same tasks from scratch is very hard, expensive or even impossible.
For example: the pivot data analysis in Reporting Services and other major reporting and BI providers are using formula based expressions, allow for summary values to be calculated at all levels of data aggregation (row and column data members etc.) and support multiple grouping, filtering and sorting rules.
Nevron Chart for SharePoint internally uses the Nevron Pivot engine. The Nevron pivot engine is functionally classified as advanced, since it uses formula based expressions, summary values can be calculated at all levels of the data aggregation and supports multiple grouping, filtering and sorting rules. The functional language of the Nevron pivot engine is advanced, since it offers a myriad of functions that help you deal with any type of numeric, date time, time span, boolean, text or array data. Many of the available pivot engines support aggregate functions (like SUM, AVERAGE etc.), but only a few support function chaining. Chain functions allow you to perform a series of data transformations prior to the data aggregation – for example suppose that you need to calculate the sum of the absolute values for a field – with the Nevron Pivot engine you can simply write =(SUM(ABS(Fields!sales)) – in this example the ABS function is a chain function.