Create Beautiful Reports with Custom Pivot Tables and JavaScript Spreadsheets

No one can contest the importance of Pivot Tables as a powerful tool for data analysis, meant to organize the data for the recurring patterns to be easier to foresee. Pivot Tables help you create customized tables from large data groups. You can summarize, sort, reorganize, group, count, total or average data effortlessly stored in a table using pivot functionalities.

With the SpreadJS v14.1 (Service Pack 1) release, we have added this feature to SpreadJS.

To see how you can use this new feature, check out our How to Use Pivot Tables with SpreadJS blog. This blog will go through some of the most important properties of the SpreadJS Pivot Tables that will help you customize the appearance of your pivot tables and reports derivated from these Pivot Tables.

Create a Pivot Table

Imagine having countless rows of data containing specific information. For example, in the datasheet below, we have records showing the Sales made by a Car Sales Company in a given year.

The goal is to create a report to summarize these raw data into more insightful information. To achieve that, you can use SpreadJS PivotTables.

Here is a snipped example of how you can add a pivot table into your javascript application.

//tablename is the table from where we get our datasource (you can use range as well)

let pivotTable = sheet.pivotTables.add("PivotTable", tableName, 1, 1,/*pivot layout*/,/*pivot theme*/,/*pivot options*/);
pivotTable.suspendLayout();
//row fields
pivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField);
//column fields
pivotTable.add("date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField);
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] };
pivotTable.group(groupInfo);
//value fields
pivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.resumeLayout();

Another way of creating a pivot table is by using the pivot panel available in SpreadJS:

//code to add pivot panel in the working spreadsheet.
var panel = new GC.Spread.Pivot.PivotPanel("myPivotPanel", myPivotTable, document.getElementById("panel"));

After that adding Row, Column, Filter, or Value fields in your pivot table is just like in Excel. You drag and drop the needed fields according to your report logic.

In the snipped code above, we saw that when you initialize your pivot table, you have the possibility to add three parameters besides position, data source, and name. These parameters are:

• Pivot Layout
• Pivot Theme
• Pivot Options

Below we will explain each of these parameters used to meliorate the appearance of the pivot tables and more.

Pivot Layout

Based on what you want to show the data and how you want to show those data, you can use different layouts provided for SpreadJS. By using a single function, you can have different visualization of your data.

There are three layouts for PivotTables you can use.

• Compact Form (0) - contains all the Row fields in one column in a hierarchical structure.

This layout form optimizes readability by keeping related data in one column, but if you copy and paste the data into a new worksheet, it will be harder to do further analysis.

• Outline Form (1) - has a hierarchical structure, but each Row field is in a separate column in the pivot table. It displays one column per field and provides space for field headers.

By using this form, you can include Field headers in each column, repeat all item labels and reuse the data of the Pivot Table to a new location for further analysis, but this form takes too much horizontal space, so it might not be very useful using it in some cases.

• Tabular Form (2) - is a hierarchical structure and each Row field is in a separate column in the pivot table. It can display subtotals at the top of every group because items in the next column are displayed in one row below the current item.

This traditional table format is very preceptive for the users. It includes Field headers in each column and allows you to have all the item labels repeated and reuse the Pivot Table data to a new location for further analysis. As a disadvantage, this layout takes too much horizontal space, and subtotals can never appear at the top of the group if you want to have those at the top.

The function used to change the layout of a pivot table has an integer argument that corresponds to the form layout (0- compact, 1 - outline, 2 - tabular).

pivotTable.layoutType(type);

By default, the layout is Outline Form.

Pivot Table Theme

PivotTable supports three types of themes: light, medium, and dark. A total of 85 different kinds of themes are supported.

You can add a pivot table theme in the time of initialization of the pivot table, the theme is the fourth argument, or you can use the .theme() function.

let pivotTable = sheet.pivotTables.add("PivotTable", table.name(), 0, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium2);
 //pivotTable.theme(GC.Spread.Pivot.PivotTableThemes.dark2);
 //pivotTable.theme("dark2");

Choosing the right theme improves the appearance of your pivot table and offers a more professional presentation of data for the people who are the end receivers of your report.

Pivot Options

SpreadJS allows you to modify the standard PivotTable presentation with specific parameters, customized using different settings. When you add a pivot table, you can also add a group of parameters that will determine the different aspects of your pivot appearance—for example, the totals and subtotals position, filters, visibility of tooltip, etc.

The code below shows you a way of adding an object parameter that will allow you to modify any of the available visualization options for your Pivot Table:

let option = {
        allowMultipleFiltersPerField: true, //whether multiple filters can be used in one field
        insertBlankLineAfterEachItem: true, //whether a blank row should be inserted at end of each item
        grandTotalPosition: GC.Spread.Pivot.GrandTotalPosition.row, //show the grand total in the row, column, or both
        subtotalsPosition: GC.Spread.Pivot.SubTotalsPosition.top, //show subtotal top, bottom, or not
        displayFieldsInPageFilterArea: GC.Spread.Pivot.DisplayFields.downThenOver, //display the page area fields first over then down or first down then over
        reportFilterFieldsPerColumn: 1, //the number of report filter fields per column
        bandRows:true, //show banded rows or not
        bandColumns: true, //show banded columns or not
        showRowHeader: true, //show row header styles or not
        showColumnHeader: true, //show column header styles or not
        showDrill: true, //show expand/collapse button or not
        showMissing: true, //show missing caption or not
        showToolTip: true, //show tool tip or not
        missingCaption: false, //replace empty cell in content area to custom string or number
        fillDownLabels: false, //show repeat label items or not
        repeatAllItemLabels: false, //show repeat label items or not
        rowLabelIndent: 4, //set the indent of each level of title in compact layout
        mergeItem: false //merge and center cells with labels
    };

let pivotTable = sheet.pivotTables.add("PivotTable", table.name(), 0, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium2, option);

Pivot Table AutofitColumn

AutofitColumn function is used to adjust the pivot table layout, so it has a better appearance. It adjusts the column width to accommodate the longest value in a field. Overall it is a helpful function but it can be inconvenient when working with long, text-based fields.

pivotTable.autoFitColumn();

Here is an example of a pivot table when we have not used any column resizing.

Below, instead, is the same pivot table when we have applied the autoFitColumn() function.

Pivot ContextMenu

Pivot ContextMenu enables an end-user to apply sorting, reorder field headers, and customize the control by expanding and collapsing field values. It allows the user to change the appearance of pivot table data via a compact and easy-to-use UI.

As seen in the video below, with the pivot table context menu, you can:

• Sort and Filter the different fields in a pivot table
• Remove certain Row, Column, or Filter Fields
• Expand and collapse the dataset
• Group and Ungroup fields (for example, dates are grouped into quarters)
• Decide how you will summarize your data (by Sum, Average, Count, etc.)
• Decide how the values will be shown (as a percentage, as a difference)
• Format values with certain given formats or customized ones

There are a lot more actions a user can perform by using the ContextMenu, here we focused on the ones that are mostly used to change the visualization of the pivot table.

Pivot Styles

If you wish to add a specific style to certain cells in your pivot table you can do that with setStyle(pivotArea, style) function.

pivotArea - is meant to tell the area of the pivot that will be formatted

style - determines the style of the formatted cells.

let style = new GC.Spread.Sheets.Style();
style.backColor = "red";
style.foreColor = "white";
style.font = "16px Arial";
style.formatter = "$ #,##0";
style.hAlign= GC.Spread.Sheets.HorizontalAlign.center;
style.vAlign= GC.Spread.Sheets.VerticalAlign.center;

let pivotArea = {
    dataOnly: true, //labelOnly property would assign the style to pivot field labels
    references: [{
        fieldName: "Cars",
        items: ["BMW","Mercedes"]
    },
    {
        fieldName: "Salesperson",
        items: ["Alan","Serena"]
    },
    {
        fieldName: "Qt",
        items: ["Qtr1", "Qtr3"]
    }]
};
pivotTable.setStyle(pivotArea, style);

The following images shows two different styles assigned to Value Fields and Label Fields.

You can see how certain label fields have been formatted (with a green font and underlined), and some value fields have been formatted with a red background, certain font settings, and format.

Just like that, you can apply your own rules and styles and format certain value or label fields in your pivot table.

Pivot Conditional Formatting

Conditional Formatting is commonly used to highlight data fields to easily identify outliers, or narrow down the results. No matter how the pivotTable layout changes, the conditional rules only follow the specified dimensions.

In the example above, you can see that the cells belonging to a Salesperson, are highlighted based on a rule. Specifically, the cells containing a small value lean towards a green background and the ones with large values towards a red background. This is done via Conditional Formatting.

To apply the conditional formatting in a pivot table you have to determine a Conditional Formatting ScaleRule and the area of the pivot table where the cells that you want formatted are. Then you add the formatting with the addConditionalRule(area, scaleRule) function.

var scaleRule = new GC.Spread.Sheets.ConditionalFormatting.ScaleRule(11 /* ThreeScaleRule */, 1 /* LowestValue */, 0, "green",
                                                                      0 /* Number */, 2000, "yellow",
                                                                      2 /* HighestValue */, 10000, "red");
pivotTable.addConditionalRule([{ dataOnly: true,
                                    references: [{fieldName: "Salesperson", items:["Alan"]},{fieldName: "Cars"}]
                               }], scaleRule);// add the rule

//pivotTable.removeConditionalRule(scaleRule);
// remove the rule

If needed you can remove the conditional rule by using removeConditionalRule(scaleRule).

Pivot tables are amazing tools that help you explore your large, detailed data set and see significant patterns and trends that you would have missed otherwise.

But once you have a PivotTable created, you may find that sometimes default formatting might be difficult to read or simply bland to see. However, by applying the simple steps explained above you can adjust the visualization of your PivotTables and have them ready for a presentation or report.

Pivot Tables are an add-on feature of SpreadJS that is included with our v14.1.0 release.

The developer Pivot Table add-on component is included with the SpreadJS trial. Please note that you will need a separate Pivot Table deployment license to distribute it with your application.

Contact us to learn more: at us.sales@grapecity.com and visit the SpreadJS page here.