Pivot Tables

This is similar to creating pivot tables in Microsoft Excel. Dashboard reporting feature of SpreadsheetWEB supports creating Pivot Tables. As standard formulas like Sum, Count, Average, Maximum, Minimum are supported, Custom Expressions can also be specified for particular needs to aggregate data in pivot tables.

From the main menu choose Insert Pivot to start Insert / Edit Dialog

In the Insert/Edit dialog box choose the rows and columns to create a pivot table and expression you like for e.g. Sum, Count, Average or Custom. The concept of Rows and Columns are very similar to Pivot Tables in Microsoft Excel.
-Selected Rows: Any field assigned as "a selected row" is consolidated with respect to the according Expression from the Expression selection below.
-Selected Columns: Shows which fields to be shown after the aggregation with regards to the Expression of Selected Row(s).
You can use up/down arrow buttons to change the order of fields in Rows and Columns.

For this sample, a Custom Expression will be created since the other expressions are more straight forward.
A sample application of Rating Engine would be used. This application is a sample insurance calculator with input fields of "Name", "Effective Date", "ZIP", "No. of Eligibles", and "SIC" and several calculated fields such as "Plan1_Family", "Plan2_Family", etc.
For this instructional case, assume that a Pivot table is needed to calculate a weighted Payment Plan for each Number of Eligible brackets.
The weight factors of (2 * Plan1_Family) and (3 * Plan2_Family). The Insert/Edit window must be filled as follows:

Then, "[Eligibles]" field to be added to rows, since the application will go through to find the records with same number of Eligibles each time and calculate the Custom Expression as its entered to the Custom field(i.e. (2*Plan1Family + 3*Plan2Family)/5 ).
Note that only one Column field can be displayed with Custom expressions. Therefore it is not important which field to be selected and added to "Selected Cols" area. For this sample it is selected as "Plan1_Family" but it would not change the result if another field was selected.
The following Pivot Table would be rendered:

The values in the second column are the result of the defined expression (entered to the "Custom" field in Pivot window) for the same "Number of Eligibles".
It should be noted that, the field names are denoted in parenthesis and exactly same (eg:Plan1_Family) for related field.
Supported functions are the SQL supported formulae list, which can be accessed from: http://msdn.microsoft.com/en-us/library/ms177516.aspx