After learning how to connect to a local or remote JSON data source, the next step is to configure its structure for successful data manipulation and building a report.

It is recommended to define the first object in a JSON array which serves for:

  • setting data types for specific fields to ensure the correct interpretation of the data;
  • creating of multi-level hierarchies;
  • defining captions of the hierarchies.

Properties of the first object in a JSON array:

    Name Description
    type Sets a field’s data type. Possible values are:
  • “string”
  • A string value. Available aggregations: Count and Distinct Count. The values of such a field type will be sorted alphabetically.
  • “number”
  • A numerical value. It is possible to apply any available aggregation function to this type of field. The values of such a field type will be sorted numerically.
  • “level”
  • This field type is used to define a separate level when creating a hierarchy. Use it along with hierarchy, level, and parent properties. See the example.
  • “month”
  • This field type is used for months. This field type sorts months in natural sorting order, not alphabetically.
  • “weekday”
  • This field type is used for days of the week. A natural sorting order for days will be applied then: from Sunday to Monday.
  • “date”
  • This field type is used for dates. It is applied to all dates by default. The dates are divided into 3 separate subfields: Year, Month, Day. See the example.
  • “date string”
  • This field type is used for dates. The dates are displayed as strings and can be formatted using date pattern (default is dd/MM/yyyy).
  • “year/month/day”
  • This field type is used for dates. The following hierarchy will be applied: Year > Month > Day. See the example.
  • “year/quarter/month/day”
  • This field type is used for dates. The following hierarchy will be applied: Year > Quarter > Month > Day.
  • “time”
  • This field type is used for time that can be selected to values. It will be formatted according to HH:mm pattern. Available aggregations: Min, Max, Count, Distinct Count. Note that time should be specified in seconds. See the example.
  • “datetime”
  • This field type is used for dates that can be selected to values. By default, it will be formatted according to dd/MM/yyyy HH:mm:ss pattern. Available aggregations: Min, Max, Count, Distinct Count. See the example.
  • “hidden”
  • This field type is used to hide a field from the Field List.
    caption A caption of a hierarchy.
    hierarchy A name of a hierarchy. Set it if the current field should be a level of the certain hierarchy (if "type": "level"). See the example.
    level A caption of a hierarchy level (if "type": "level"). See the example.
    parent A caption of a parent level (if "type": "level"). See the example.

If the type of the data is not defined explicitly, the component determines the type of a field based on the first value of that field. Though the pivot table tries to guess the type correctly, if null is the first value, the results of data processing may be unpredictable. Setting types is a good practice before loading the data into the pivot table.

Supported types of JSON data

There are two possible types of a JSON dataset:

  1. An array of JSON objects.

    See the following code snippet to understand better how the first object in a JSON array influences the pivot table’s interpretation of the data:

    var pivot = new WebDataRocks({
        container: "wdr-component",
        toolbar: true,
        report: {
            dataSource: {
                data: getJSONData()
            },
            formats: [{
                name: "calories",
                maxDecimalPlaces: 2,
                maxSymbols: 20,
                textAlign: "right"
            }],
            slice: {
                rows: [{
                    uniqueName: "Food"
                }],
                columns: [{
                    uniqueName: "[Measures]"
                }],
                measures: [{
                    uniqueName: "Calories",
                    aggregation: "average",
                    format: "calories"
                }]
            }
        }
    });
    
    function getJSONData() {
        return [{
                "Category": {
                    type: "level",
                    hierarchy: "Food"
                },
                "Item": {
                    type: "level",
                    hierarchy: "Food",
                    level: "Dish",
                    parent: "Category"
                },
                "Serving Size": {
                    type: "level",
                    hierarchy: "Food",
                    level: "Size",
                    parent: "Dish"
                },
                "Calories": {
                    type: "number"
                },
                "Calories from Fat": {
                    type: "number"
                }
            },
            {
                "Category": "Breakfast",
                "Item": "Frittata",
                "Serving Size": "4.8 oz (136 g)",
                "Calories": 300,
                "Calories from Fat": 120
            }
    ];
    }
    

    The full example is on CodePen.

  2. A JSON array of arrays.

    It is recommended to add the first object for setting data types and complex hierarchies.

    The full example is on CodePen.

Undefined types in JSON

The definition of all fields’ data types is optional. It is possible to set only the necessary fields’ types and leave all others empty – {}. Rely on WebDataRocks – it will automatically pick up a data type for such fields.

If some fields’ data types need to be omitted at all, just do not include them into the first JSON object or set "type": "hidden".

See the example about undefined types of fields.

Supported date formats

The input values of dates have to be compliant with ISO 8601 – The International Standard for the representation of dates and time. It means that if the field’s date value is formatted according to this standard, the correct interpretation and processing of such fields are guaranteed.

For example, “2018-01-10” (date) or “2018-01-10T08:14:00” (date and time).

JavaScript Date objects and timestamps expressed in milliseconds are supported as well.

WebDataRocks Pivot Table supports 5 output date types:

  1. “date”
  2. “date string”
  3. “year/month/day”
  4. “year/quarter/month/day”
  5. “datetime”