Retail Income Statements are issued on a regular basis to measure the performance and success of many businesses. This blog will show how you can create and add these types of financial reports to your in-house or commercial JavaScript applications.

This blog will make use of PivotTables, a powerful data analysis tool, and PivotTables Slicer to create and interact with a dynamic Income Statement Report.

Let’s get started!

To create the income statement:

• Set up your data
• Insert the pivot table
• Build the pivot table
• Add Calculated Items
• Add Calculated Fields
• Add Slicers
• Make some appearance adjustments
• Final report

Feel free to download here the sample file and follow along.

Set Up Your Data

The first thing you need to do is to make sure that your data is in a tabular format. This means your data respects the following conditions:

1. Every record is housed in one row.
2. Each Column contains a type of data.
3. There are no blank rows or columns.
4. There are no subtotals or totals in the data.

In this example, we have added the Account Group column to represent different sections of the report and have named the table containing the data tblIncome.

Insert the Pivot Table

If you are using our Designer Component, insert the PivotTable following these steps:

1. Select tblIncome table
2. On the Insert tab click PivotTable
3. Choose "New Worksheet"
4. Ok at the end

Or you can add it using the below code if you are using javascript:

let pivotTable = sheet.pivotTables.add("myPivotTable", "tblIncome", 1, 1);

Build the Pivot Table

To exactly build the PivotTable we will be using the PivotTable Panel. If you are using Designer the panel will be shown at the right of the sheet every time you click on the PivotTable.

You can also add it to your javascript instance using the code below:

var pt = spread.getActiveSheet().pivotTables.all()[0]; 

var panel = new GC.Spread.Pivot.PivotPanel('myPivotPanel', pt, document.getElementById("panel")); 

panel.sectionVisibility(GC.Spread.Pivot.PivotPanelSection.fields |GC.Spread.Pivot.PivotPanelSection.area);

Now drag and drop the fields on the right areas to build the PivotTable. In our example: the Account Group and Account field to the Rows and add Actual and Budget to the Values.

Notice: The PivotTable can work without PivotPanel, we have just added it for ease of use. 

Add Calculated Items

In addition to the existing items in a pivot table field, you can create one or more calculated items, using custom formulas.

1. Click PivotTable Analyze
2. Fields, Items & Sets → Calculated Item
3. Set the name 3. Gross Profit and as formula: ='Account Group'['1. Revenue']-'Account Group'['2. COGS']

Add the calculated Item using only code as follows:

pivotTable.addCalcItem("Account Group", "3. Gross Profit","='Account Group'['1. Revenue']-'Account Group'['2. COGS']");

Add the other Calculated Items following the above steps. The formulas for those are shown in the table below:

This will make your Income Statement more readable for the user.

Add Calculated Fields

Income Statements often use the Variance Analysis for performance comparison. When actual revenue returns are higher than budget projections or expenses are lower than budget, a budget variation is positive or favorable.

We will be using the Calculated Fields feature to add on our PivotTable the Difference and Difference %.

1. Click PivotTable Analyze.
2. Fields, Items & Sets → Calculated Field.
3. Set the name, Difference, of the calculated field.
4. To add the fields on the formula, select the field and then click Insert Field.
5. Click the Add button.

Javascript code:

pivotTable.addCalcField('diff', '=Actual-Budget'); 
pivotTable.add("diff", "Difference", GC.Spread.Pivot.PivotTableFieldType.valueField); 

pivotTable.addCalcField('diff%', '=Actual/Budget-1'); 
pivotTable.add("diff%", "Difference %", GC.Spread.Pivot.PivotTableFieldType.valueField);

The two fields we have added are Difference and Difference %. The formulas used are as below:

Add Slicers

On our SpreadJS v15 release, we have added Slicers as a new feature used to filter your PivotTable. Use this feature to filter your data by Region and Financial Year.

If you are using Designer Component do as follow:

1. Click PivotTable Analyze
2. Insert Slicer
3. Select Region and Financial Year

To add slicers in javascript use the code below:

var regionSlicer = sheet.slicers.add("Region", pivotTable.name(), "Region", GC.Spread.Sheets.Slicers.SlicerStyles.dark1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable); 
var yearSlicer = sheet.slicers.add("Financial Year", pivotTable.name(), "Financial Year", GC.Spread.Sheets.Slicers.SlicerStyles.dark4(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);

Make Some Appearance Adjustments

For the PivotTable to be more easily read, make some adjustments like:

• Collapse the Calculated Items

This will hide the accounts summed to show the value of the calculated Item.

• Show SubTotals at the Top of the Group *

1. Go to the Design tab.
2. Click Subtotals
3. Select "Show all SubTotals at the Top of the Group"

• Insert Blank Line after Each Item *

1. Go to the Design tab.
2. Click Blank Rows
3. Select "Insert Blank Line after Each Item"

• Hide Buttons and Field Headers *

1. Go to the PivotTable Analyze tab.
2. Click Buttons and Field Headers

• Change the Pivot Layout *

1. Go to the Design tab.
2. Click Report Layout
3. Select "Show in Outline Form"

The modifications mentioned above can be easily changed via code. SpreadJS provides many different options to customize the appearance and functionality of the PivotTables as per the needs of your application. You can change the PivotTables options and the layout as below:

let option = pivotTable.options;
option = {
        allowMultipleFiltersPerField: true,
        insertBlankLineAfterEachItem: true, // Insert Blank Line after Each Item *
        grandTotalPosition: GC.Spread.Pivot.GrandTotalPosition.row,
        subtotalsPosition: GC.Spread.Pivot.SubTotalsPosition.top, // Show SubTotals at the Top of the Group *
        displayFieldsInPageFilterArea: GC.Spread.Pivot.DisplayFields.downThenOver,
        reportFilterFieldsPerColumn: 1,
        bandRows:true,
        bandColumns: true,
        showRowHeader: true,
        showColumnHeader: true,
        showDrill: true, // Collapse Buttons *
        showMissing: true,
        showToolTip: true,
        missingCaption: 'something',
        fillDownLabels: false,
        repeatAllItemLabels: false,
        rowLabelIndent: 4,
        mergeItem: false,
        showHeaders: true // Collapse Field Headers *
    };
pivotTable.layoutType(1); // Change the Pivot Layout to Outline Form *

• Conditional and Custom Formatting

Next, we are going to format the PivotTable fields. You can set the format using the pivot panel as below:

1. Go to Values - > Value Field Settings
2. Click Number Format
3. Set Format. In our case: $#,##0
4. Confirm.

If you want to do that by code see below:

//identify the area
var areaActual= {
            dataOnly: true,
            references: [
                {
                    fieldName: "Actual",
                    items: [fieldName]
                }
            ]
        };
 
var style = new GC.Spread.Sheets.Style();
style.formatter = "$#,##0";
//set style to the area
pivotTable.setStyle(areaActual, style);

We can use the same logic for the other fields. Use the corresponding formats as the table below:

We would recommend for you to use Conditional Formatting to make it quicker for the viewer to see the biggest accounts. PivotTables offers the ability to set conditional rules to the specified dimensions. No matter how the PivotTable layout changes, the conditional rules only follow the specified dimensions.

If you are using Designer follow the below steps to add conditional formatting rules:

1. Select the cells: G7:H11
2. Home → Conditional Formating → New Rule.
3. Choose "Format all cells based on their values".
4. Format style: 2-Color Scale (blue for the highest value, white for the lowest).

Repeat the same for other Account Groups having in mind to use Yellow for the Expenses and Blue for the Incomes.

Final Report

Below you have a screenshot of what your Income Statement Report would look like at the end:

This is how you can use our javascript component for generating the needed financial reports to assess your retail business performance and set the stage for the discovery of insights that can influence decision-making and drive change.

Discover more about the opportunities SpreadJS offers, by getting the free trial.

If you have any questions or insights you like to share, please post a comment below. Thanks!