Grand totals and subtotals

A grand total is a value that is composed by adding together other total amounts (subtotals) of the row/column.

When creating a report, you can change the default way grand totals and subtotals are displayed on the grid. The available options are:

Show and hide grand totals for the entire report
Show and hide subtotals for the entire report
Show grand totals only for the rows or columns
Show subtotals only for the rows or columns

To show or hide grand totals and subtotals

Via UI
1. Go to the Options () tab on the Toolbar.
2. Choose how to display grand totals and subtotals.
3. Apply the changes.
Programmatically
- Configure totals through the grid.showTotals property of the Options Object:
```
report = {
  options: {
    grid: {
      showTotals: "off"
    }
  }
}
```
- Configure grand totals through the grid.showGrandTotals property of the Options Object:
```
report = {
  options: {
    grid: {
      showGrandTotals: "rows"
    }
  }
};
```
It is also possible to set grid.showTotals and grid.showGrandTotals options for all reports.

Example

Hide grand totals from the entire report:

Hide grand totals from the rows:

The layout of subtotals is changed similarly.

In WebDataRocks Pivot Table, data can be displayed in aggregated views or as a simple table. You can switch between three standard layouts to display the report data differently:

Compact pivot table – neat and concise data presentation.
Classic pivot table – an Excel-like layout.
Flat table – a simple grid with non-aggregated data.

Compact form

The compact pivot table form is enabled by default. This layout helps keep your data in a neat and concise style.

Features:

Compact form enhances the readability of the report.
If the rows contain more than one field, the members of the inner field can be expanded and collapsed by clicking on the outer field’s member name.
If the members of the field in the rows are expanded, they are placed one under the other, without being transferred to a separate column.
If the members of the field in the columns are expanded, they are placed in a separate row.
Subtotals are shown at the end of each row in a separate column.
Grand totals are placed at the bottom in a separate row.

Classic form

The classic pivot table form suits perfectly those who would like to have an Excel-like user experience.

Features:

If the rows contain more than one field, the members of the inner field can be expanded and collapsed by clicking on the outer field’s member name.
If the members of the field in the rows are expanded, they are placed in a separate column. It is the main feature that distinguishes the classic layout from the compact one.
If the members of the field in the columns are expanded, they are placed in a separate row.
Subtotals are shown in a separate row after each hierarchy in the rows.
Grand totals are placed at the bottom in a separate row.

Flat form

The flat table form displays the raw data view, which reflects the data’s original structure. It’s the simplest form among the others.

Features:

The data is shown without aggregation.
Each field is placed in a separate column.
Grand totals are placed in the first row.

To change the grid form

Via UI
1. Go to the Options tab () on the Toolbar.
2. Select the form of your choice.
3. Apply the changes.
Programmatically
Set a grid layout through the grid.type property of the Options Object:
```
report = {
  options: {
    grid: {
      type: "classic"
    }
  }
};
```
It is also possible to set the grid.type option for all reports.

You can save your current report and load it later with all your settings: slice, formats, filters, sorting, conditions, and options.

To save the report via UI

Click the Save button () on the Toolbar.

The report will be saved to your local file system in JSON format. Later you can open this report in WebDataRocks and see your previous configurations on the grid.

Here’s a demo showing how to save a report via the Toolbar:

To save the report programmatically

Use the save() method:

this.pivotRef.webDataRocks.save();

The saved report will contain only the configurations you’ve explicitly defined. To save the report with default or global configurations, see the next section.

Saving the report with default and global configurations

The save() method has parameters that allow you to save the report:

With global configs
With default configs
With global and default configs

Now let’s have a look at each of these approaches in detail. For simplicity, we’ll focus on how options are saved, but data source and slice configs are saved similarly.

With default configs

How to save. Run the save() method with the withDefaults option:

this.pivotRef.webDataRocks.save({
  withDefaults: true
});

How it works. Let’s define the following report:

import { Component, ViewChild } from "@angular/core";
import { WebdatarocksPivotModule, WebdatarocksComponent } from "@webdatarocks/ngx-webdatarocks";

@Component({
  selector: "app-root",
  standalone: true,
  imports: [WebdatarocksPivotModule],
  templateUrl: "./app.component.html",
  styleUrls: ["./app.component.css"]
})
export class AppComponent {
  @ViewChild("pivot") pivotRef: WebdatarocksComponent;

  reportJson = {
   dataSource: {
     filename: "https://cdn.flexmonster.com/data/data.json"
   },
   options: {
     grid: {
       type: "flat",
       showHeaders: false
     },
     configuratorActive: true
   }
 };
}

<app-wbr-pivot #pivot
 [toolbar]="true"
 [report]="reportJson">
</app-wbr-pivot>

As you can see, we’ve defined only a few options in the report. Now if we save this report with default configs, we’ll see more options in the saved file:

{
 "dataSource": {
   // Data source configs
 },
 "slice": {
   // Slice configs
 },
 "options": {
   "grid": {
     "type": "flat",
     "title": "",
     "showFilter": true,
     "showHeaders": false,
     "showTotals": "on",
     "showGrandTotals": "on",
     // Other grid options
   },
   "configuratorActive": true,
   "configuratorButton": true,
   "showAggregations": true,
   "showCalculatedValuesButton": true,
   "drillThrough": true,
   "showDrillThroughConfigurator": true,
   // Other general options
 }
}

Notice that the options.grid.type, options.grid.showHeaders, and options.configuratorActive properties have values that we’ve set manually, whereas their default values are different — check them in the Options Object.

You can also check default values for the Slice Object and Data Source Object.

With global configs

How to save. Run the save() method with the withGlobals option:

this.pivotRef.webDataRocks.save({
  withGlobals: true
});

How it works. Let’s configure the component as follows:

import { Component, ViewChild } from "@angular/core";
import { WebdatarocksPivotModule, WebdatarocksComponent } from "@webdatarocks/ngx-webdatarocks";

@Component({
  selector: "app-root",
  standalone: true,
  imports: [WebdatarocksPivotModule],
  templateUrl: "./app.component.html",
  styleUrls: ["./app.component.css"]
})
export class AppComponent {
  @ViewChild("pivot") pivotRef: WebdatarocksComponent;

  reportJson = {
    dataSource: {
      filename: "https://cdn.flexmonster.com/data/data.json"
    },
    options: {
      grid: {
        type: "classic"
      },
      configuratorActive: false
    }
  };
  globalReportJson = {
    options: {
      grid: {
        type: "flat",
        showHeaders: false
      },
      configuratorActive: true
    }
  };
}

<app-wbr-pivot #pivot
 [toolbar]="true"
 [report]="reportJson"
 [global]="globalReportJson">
</app-wbr-pivot>

We have defined global options, and some of them are overwritten in the report.

Now if we save this report with global configs, we’ll see the following options section in the saved file:

{
  "dataSource": {
    // Data source configs
  },
  "slice": {
    // Slice configs
  },
  "options": {
    "grid": {
      "type": "classic",
      "showHeaders": false
    }
  }
}

If we compare the initial configurations and the saved report, we’ll learn the following:

If a configuration is defined globally and not defined in the report, the global value will be saved to the report (see the grid.showHeaders option in the example).
If a configuration is defined both globally and in the report, the global value is ignored (see the grid.type option in the example).
If a global configuration is overwritten in the report, but its new value matches its default value, the configuration is not included in the saved report (see the configuratorActive option in the example).

With default and global configs

How to save. Run the save() method with the withDefaults and withGlobals options:

this.pivotRef.webDataRocks.save({
  withDefaults: true,
  withGlobals: true
});

How it works. This approach is a combination of the previous approaches. For example, let’s save the following report with defaults and globals:

import { Component, ViewChild } from "@angular/core";
import { WebdatarocksPivotModule, WebdatarocksComponent } from "@webdatarocks/ngx-webdatarocks";

@Component({
  selector: "app-root",
  standalone: true,
  imports: [WebdatarocksPivotModule],
  templateUrl: "./app.component.html",
  styleUrls: ["./app.component.css"]
})
export class AppComponent {
  @ViewChild("pivot") pivotRef: WebdatarocksComponent;

  reportJson = {
   dataSource: {
     filename: "https://cdn.flexmonster.com/data/data.json"
   },
   options: {
     grid: {
       type: "classic"
     },
     configuratorActive: false
   }
 };
 globalReportJson = {
    options: {
      grid: {
        type: "flat",
        showHeaders: false
      },
      configuratorActive: true
    }
  };
}

<app-wbr-pivot #pivot
 [toolbar]="true"
 [report]="reportJson"
 [global]="globalReportJson">
</app-wbr-pivot>

The saved file will look similar to the following:

{
  "dataSource": {
    // Data source configs
  },
  "slice": {
    // Slice configs
  },
  "options": {
    "grid": {
      "type": "classic",
      "title": "",
      "showFilter": true,
      "showHeaders": false,
      "showTotals": "on",
      "showGrandTotals": "on",
      // Other grid options
    },
    "configuratorActive": false,
    "configuratorButton": true,
    "showAggregations": true,
    "showCalculatedValuesButton": true,
    "drillThrough": true,
    "showDrillThroughConfigurator": true,
    // Other general options
  }
}

As you can see from the saved report:

The grid.showHeaders option is not overwritten in the report, so it has its global value (false).
The grid.type option is overwritten in the report, so its global value is ignored (grid.type is "classic", not "flat").
All other options have their default values, thanks to the withDefaults: true.

Connect to JSON via UI

Step 1. Go to the Connect tab () on the Toolbar.

Step 2. Choose to which JSON you want to connect:

To local JSON. Use this option to load a file from your local file system.
To remote JSON. Use this option to load a file by its URL.

Connect to JSON programmatically

There are two ways to connect to JSON programmatically:

1) To connect to inline JSON data in the report, use the dataSource.data property:

import { Component } from "@angular/core";
import { WebdatarocksPivotModule } from "@webdatarocks/ngx-webdatarocks";

@Component({
  selector: "app-root",
  standalone: true,
  imports: [WebdatarocksPivotModule],
  templateUrl: "./app.component.html",
  styleUrls: ["./app.component.css"]
})
export class AppComponent {
  reportJson = {
   dataSource: {
     data: [
       {
         "Product": "Apple",
         "Price": 2.50
       },
       {
         "Product": "Cherry",
         "Price": 5.25
       }
     ]
   }
 };
}

<app-wbr-pivot
 [toolbar]="true"
 [report]="reportJson">
</app-wbr-pivot>

2) If your JSON data is in a file, specify the file’s URL in the dataSource.filename property:

import { Component } from "@angular/core";
import { WebdatarocksPivotModule } from "@webdatarocks/ngx-webdatarocks";

@Component({
  selector: "app-root",
  standalone: true,
  imports: [WebdatarocksPivotModule],
  templateUrl: "./app.component.html",
  styleUrls: ["./app.component.css"]
})
export class AppComponent {
  reportJson = {
   dataSource: {
     filename: "URL-to-your-JSON-file"
   }
 };
}

<app-wbr-pivot
 [toolbar]="true"
 [report]="reportJson">
</app-wbr-pivot>

Set a JSON data source for all reports

To set a JSON data source for all reports, specify it in the Global Object:

import { Component } from "@angular/core";
import { WebdatarocksPivotModule } from "@webdatarocks/ngx-webdatarocks"

@Component({
  selector: "app-root",
  standalone: true,
  imports: [WebdatarocksPivotModule],
  templateUrl: "./app.component.html",
  styleUrls: ["./app.component.css"]
})
export class AppComponent {
  globalReportJson = {
    dataSource: {
      type: "json",
      filename: "URL-to-your-JSON-file"
    }
  };

  reportJson = {
    // Your report
  };
}

<app-wbr-pivot
 [toolbar]="true"
 [global]="globalReportJson"
 [report]="reportJson">
</app-wbr-pivot>

Connect to CSV via UI

Step 1. Go to the Connect tab () on the Toolbar.

Step 2. Choose to which CSV you want to connect:

To local CSV. Use this option to load a file from your local file system.
To remote CSV. Use this option to load a file by its URL.

Here’s an example of connecting to a remote CSV file:

Connect to CSV programmatically

To get your CSV data from a file, specify the file’s URL in the dataSource.filename property:

import { Component } from "@angular/core";
import { WebdatarocksPivotModule } from "@webdatarocks/ngx-webdatarocks";

@Component({
  selector: "app-root",
  standalone: true,
  imports: [WebdatarocksPivotModule],
  templateUrl: "./app.component.html",
  styleUrls: ["./app.component.css"]
})
export class AppComponent {
  reportCsv = {
   dataSource: {
     filename: "URL-to-your-CSV-file"
   }
 };
}

<app-wbr-pivot
 [toolbar]="true"
 [report]="reportCsv">
</app-wbr-pivot>

Specify a custom field separator

By default, WebDataRocks supports a comma , or a semicolon ; as a field separator. If fields in your data are separated by another character, e.g., a colon :, you need to specify this character in the dataSource.fieldSeparator property. For example:

reportCsv = {
  dataSource: {
    filename: "URL-to-your-CSV-file",
    fieldSeparator: ":"
  }
};

Set a CSV data source for all reports

To set a CSV data source for all reports, specify it in the Global Object:

import { Component } from "@angular/core";
import { WebdatarocksPivotModule } from "@webdatarocks/ngx-webdatarocks";

@Component({
  selector: "app-root",
  standalone: true,
  imports: [WebdatarocksPivotModule],
  templateUrl: "./app.component.html",
  styleUrls: ["./app.component.css"]
})
export class AppComponent {
  globalReportCsv = {
   dataSource: {
    type: "csv",
    filename: "URL-to-your-CSV-file"
   }
  };
  reportCsv = {
   // Your report
  };
}

<app-wbr-pivot
 [toolbar]="true"
 [global]="globalReportCsv"
 [report]="reportCsv"
>
</app-wbr-pivot>

To select the fields in the Field List

Go to the Fields tab () on the Toolbar.
Pay attention to the All Fields section on the left. It contains all fields from your data source.
Drag and drop the fields to the Rows, Columns, Values, and Report Filters areas.
To change the aggregation for a field in the Values box, press the Edit button () next to its name.
Use the Add calculated value button to compose new values based on your data source.
Apply the changes.

Try it yourself:

Configure your fields using the Field List.
Save your current configuration and apply it when loading a new report. For more details, see Loading the report.

Want to check how is the field configuration defined in the report? Find the slice section in our online demo.

Name	Description
`-`	Numbers.
`+`	Strings.
`d+`	Dates divided into 3 subfields: `Year`, `Month`, `Day`.
`ds+`	Dates displayed in the `"dd/MM/yyyy"` format.
`D+`	Dates represented as the multilevel hierarchy: `Year` > `Month` > `Day`.
`D4+`	Dates represented as the multilevel hierarchy: `Year` > `Quarter` > `Month` > `Day`.
`dt+`	Dates displayed in the `"dd/MM/yyyy HH:mm:ss"` format.
`t+`	Time intervals displayed in the `"HH:mm:ss"` format.
`m+`	Months. Natural sorting is applied to field members.
`w+`	Days of the week. Natural sorting is applied to field members.

Examples

1) Here is a sample CSV data where the ds+ and w+ prefixes are added to the field names:

ds+Invoice Date, Quantity, Country, w+Week Day
2018-05-15, 3, France, Tuesday
2018-05-16, 4, Italy, Wednesday
2018-05-17, 2, Spain, Thursday
2018-05-12, 2, Japan, Saturday

After loading the CSV data and selecting fields in the Field List, you can see that the Invoice Date is displayed as a string in the "dd/MM/yyyy" format, and the Week Day is interpreted as a day of the week:

2) You can represent a date as a multilevel hierarchy using the D+ or the D4+ prefix.

Here is an example with the D+ prefix:

D+Invoice Date, -Quantity, -Price, +Country
2018-05-15, 3, 329, France
2018-05-16, 4, 139, Italy
2018-05-17, 2, 429, Spain
2018-05-12, 2, 559, Japan

See how the Invoice Date is displayed in the pivot table when the CSV file is loaded to WebDataRocks:

This demo is also available on our CodePen.

To create multilevel hierarchies from fields of other types, use the JSON data source.

Available data types

The following data types are available in CSV:

Number
String
Date
Time
Month
Weekday

Some of these types can be auto-resolved by the component.

Automatic type resolution

If you omit the data type prefix, the component automatically assigns the data type based on the field’s first value. The following data types can be auto-resolved:

Number
String
Date

The results of the automatic resolution may be unexpected when:

The first field value is blank or not specified.
The field values are not formatted correctly. For example, a date was specified in a format different from the ISO 8601.
The field type cannot be resolved automatically. For example, time values are processed as numbers if the t+ prefix is not explicitly specified.
The first field value is formatted differently from other values. For example, the first value is a number while others are strings.

Learn more about each field data type in the sections below.

Number

This field type is used to store numbers.

Field values can contain digits, -, and + characters. Point . must be used as a decimal separator. Numbers in exponential notation are also supported. Examples of valid values: -20, 2.50, 1.0E+2.

To mark the field as a number, add the - data type prefix to the field name.

Can be auto-resolved: yes (learn more).

Available aggregations: all.

String

This field type is used to store strings.

If a field value contains line breaks or a field separator, it must be enclosed in double quotes ". A double quote inside such values must be escaped using another double quote. Examples of valid values: Apple, "A-Z" section, "1, 2, 3", "The ""A, B, C"" magazine".

To mark the field as a string, add the + data type prefix to the field name.

Can be auto-resolved: yes (learn more).

Available aggregations: Count, Distinct Count.

Date

This field type is used to store dates.

Field values must be specified in the ISO 8601 format. Examples: 2018-01-10 (date), 2018-01-10T08:14:00 (date and time), 2018-01-10T06:14:00Z (date and time in UTC).

To mark the field as a date, add one of the following data type prefixes to the field name:

d+ – dates are divided into 3 subfields: Year, Month, Day.
Can be auto-resolved: yes (learn more).
Available aggregations: Count, Distinct Count.
ds+ – dates are displayed as strings in the "dd/MM/yyyy" format. The format can be changed using the datePattern option.
Can be auto-resolved: no (learn more).
Available aggregations: Min, Max, Count, Distinct Count.
D+ – dates are represented as the multilevel hierarchy: Year > Month > Day.
Can be auto-resolved: no (learn more).
Available aggregations: Count, Distinct Count.
See the example.
D4+ – dates are represented as the multilevel hierarchy: Year > Quarter > Month > Day.
Can be auto-resolved: no (learn more).
Available aggregations: Count, Distinct Count.
See the example.
dt+ – dates are displayed as strings in the "dd/MM/yyyy HH:mm:ss" format. The format can be changed using the dateTimePattern option.
Can be auto-resolved: no (learn more).
Available aggregations: Min, Max, Count, Distinct Count.

Here is an example of CSV data with the Invoice date date (dt+) field:

dt+Invoice Date, Price, Country
2018-05-15T18:30:00, 329, France
2018-05-16T06:20:00, 139, Italy
2018-05-17T13:45:00, 429, Spain
2018-05-12T04:50:00, 559, Japan

Time

This field type is used to store time intervals, such as duration.

Field values must be specified as a number of seconds. In the component, values are displayed in the "HH:mm:ss" format. Examples of valid values: 5400 (displayed as "01:30:00" in the component).

To mark the field as time, add the t+ data type prefix to the field name.

Can be auto-resolved: no (learn more).

Available aggregations: Min, Max, Count, Distinct Count.

Here is an example of CSV data with the Duration time field:

t+Duration, Movie, ReleaseYears
7020, Blade Runner, 1980-1990
5220, The Lion King, 1990-2000
7560, Jurassic Park, 1990-2000
9120, The Dark Knight, 2000-2010

Month

This field type is used to store months. Natural sorting is applied to the field members: from January to December.

Field values must start with a capital letter. Full names and three-letter abbreviations of months are supported. Examples of valid values: October, Dec, May.

To mark the field as months, add the m+ data type prefix to the field name.

Can be auto-resolved: no (learn more).

Available aggregations: Count, Distinct Count.

Weekday

This field type is used to store the days of the week. Natural sorting is applied to the field members: from Sunday to Monday.

Field values must start with a capital letter. Full names and three-letter abbreviations of the days of the week are supported. Examples of valid values: Monday, Sun, Friday.

To mark the field as a day of the week, add the w+ data type prefix to the field name.

Can be auto-resolved: no (learn more).

Available aggregations: Count, Distinct Count.

Grand totals and subtotals

To show or hide grand totals and subtotals

Example

Compact form

Classic form

Flat form

To change the grid form

To save the report via UI

To save the report programmatically

Saving the report with default and global configurations

With default configs

With global configs

With default and global configs

See also

Connect to JSON via UI

Connect to JSON programmatically

Set a JSON data source for all reports

See also

Connect to CSV via UI

Connect to CSV programmatically

Specify a custom field separator

Set a CSV data source for all reports

See also

To select the fields in the Field List

To show certain fields when loading data

See also

Examples

See also

Available data types

Automatic type resolution

Number

String

Date

Time

Month

Weekday

See also

See also

See also