How to Build a JavaScript Retail Income Statement App
Tags: webdev, devops, javascript, tutorial
Published: 2023-11-10 18:09:20

---
canonical_url: https://developer.mescius.com/blogs/how-to-build-a-javascript-retail-income-statement-app
description: Learn how to build a JavaScript retail income statement application using a SpreadJS pivot table. See more and build your app today.
---

Retail Income Statements are issued regularly 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 use PivotTables, a powerful data analysis tool, and the PivotTable Slicer to create and interact with a dynamic JavaScript Income Statement.

Let’s get started!

To create the income statement:

*   [Set up the project](#project)
*   [Set up your data](#set)
*   [Insert the pivot table](#insert)
*   [Build the pivot table](#build)
*   [Add Calculated Items](#add)
*   [Add Calculated Fields](#add-fields)
*   [Add Slicers](#slicers)
*   [Make some appearance adjustments](#adjust)
*   [Final report](#final)

**Feel free to [download the sample](https://cdn.mescius.io/assets/developer/blogs/spread/20231027-how-to-build-a-javascript-retail-income-statement-app/spreadjs-pivot-table.zip) and follow along.**

## <a id="project"></a>Set Up the Project

You can start by creating the HTML file and referencing the SpreadJS files you will need. In this case, you can add them to the project using NPM:

```
    npm install @grapecity/spread-sheets @grapecity/spread-sheets-shapes @grapecity/spread-sheets-slicers @grapecity/spread-sheets-pivot-addon
```
You can then reference these in the HTML file and create the host for the SpreadJS instance:

```
    <!DOCTYPE html>
    <html>
    <head>
        <title>Income Statement</title>
        <link href="./node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css" rel="stylesheet" type="text/css" />
    	<script src="http://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script>
        <script type="text/javascript" src="./node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js"></script>
    	<script src="./node_modules/@grapecity/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
        <script src="./node_modules/@grapecity/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js"></script>
    	<script src="./node_modules/@grapecity/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script>

    	<link rel="stylesheet" type="text/css" href="styles.css">
    </head>
    <body>
        <div class="sample-tutorial">
         <div id="ss" class="sample-spreadsheets"></div>
    	 <div class="sample-panel">
                <div id="panel"></div>
            </div>
    </div>
    	 </body>
    </html>
```

## <a id="set"></a>Set Up Your Data

Now, you need to ensure 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_**.

![Pivot](//cdn.mescius.io/umb/media/k2mdpooo/new1.png?rmode=max&width=756&height=336)

## <a id="insert"></a>Insert the Pivot Table

If you are using our [Designer](https://developer.mescius.com/spreadjs/designer/index.html) 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

![JavaScript Income Statement](//cdn.mescius.io/umb/media/iwlhg343/image2_insert.png?width=500&height=258.98520084566593)

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

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

## <a id="build"></a>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);
```
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.

![JavaScript Income Statement](//cdn.mescius.io/umb/media/it4illna/image3_gif1_create.gif?width=500&height=315.74394463667824)

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

## <a id="add"></a>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']**

**![Calculated Item](//cdn.mescius.io/umb/media/tuimu3bf/image4_calculated-item.png?width=500&height=312.5)**

Add the calculated Item using only the 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:

![Calculated Items](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/saqrfrs4v9488l6kswgg.png)

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

## <a id="add-fields"></a>Add Calculated Fields

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

We will use the Calculated Fields feature to add the _Difference_ and _Difference %_ on our PivotTable.

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.

![Fields](//cdn.mescius.io/umb/media/w1imwwjl/image5_fields.png?width=500&height=364.1456582633053)

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 follows:

![Difference Fields](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/56i3gt1t13sadzlp41qk.png)

## <a id="slicers"></a>Add Slicers

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

If you are using Designer Component, do as follows:

1.  Click _PivotTable Analyze_
2.  Insert Slicer
3.  Select _Region_ and _Financial Year_

_![Slicers](//cdn.mescius.io/umb/media/5tfp2z3l/image6_slicer.png?width=500&height=355.80304806565067)_

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);
```

## <a id="adjust"></a>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.

![JavaScript Income Statement](//cdn.mescius.io/umb/media/ylsfbwvd/image7_gif2_buttons-video.gif?width=500&height=388.62102217936354)

*   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"

![Subtotal](//cdn.mescius.io/umb/media/ncnfqzha/image8_subtotal.png?width=500&height=164.92146596858638)

*   Insert Blank Line after Each Item *

1.  Go to the _Design_ tab.
2.  Click _Blank Rows_
3.  Select "Insert Blank Line after Each Item"

![Blank](//cdn.mescius.io/umb/media/orxg0qoy/image9_blank.png?width=500&height=147.87234042553192)

*   Hide Buttons and Field Headers *

1.  Go to the _PivotTable Analyze_ tab.
2.  Click _Buttons_ and _Field Headers_

_![Buttons](//cdn.mescius.io/umb/media/0xig3jth/image10_buttons.png?width=500&height=118.94108873974646)_

*   Change the Pivot Layout *

1.  Go to the _Design_ tab.
2.  Click _Report Layout_
3.  Select "Show in Outline Form"

![Layout](//cdn.mescius.io/umb/media/f3vmjlv0/image11_layout.png?width=500&height=160.97308488612836)

The modifications mentioned above can be easily changed via code. SpreadJS provides many different options to customize the appearance and functionality of the PivotTables according to 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.

![Format](//cdn.mescius.io/umb/media/wi2d5yt2/image12_gif3_format.gif?width=500&height=260.551248923342)

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 in the table below:

![Field Formats](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4s264slfw5pmvf0xkcz1.png)

We recommend using 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).

![Conditional](//cdn.mescius.io/umb/media/comb543p/image13_conditional.png?width=500&height=323.8498789346247)

Repeat the same for other Account Groups, considering using Yellow for the Expenses and Blue for the Incomes.

## <a id="final"></a>Final Report

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

![Final Report](//cdn.mescius.io/umb/media/xnwkyu2l/image14_final.png?width=500&height=332.88409703504044)

You can then load this into your application by first exporting as a JavaScript file using File>Export>SpreadJS JSON>Export JavaScript Files:

![Export JavaScript Files](//cdn.mescius.io/umb/media/i44hbqzz/new2.png?rmode=max&width=763&height=401)

Once the file is saved within your project, you can add a script reference to the head section of the HTML:

![code](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lg0s60ho8vb6ftddutnv.png)

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 discovering insights that can influence decision-making and drive change with a JavaScript income statement.

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