Since 2.7.14 version of Flexmonster, it’s recommended to set field data types and captions, group fields under separate dimensions, create multilevel hierarchies, etc, right in the report using a Mapping Object.
Alternatively, the first object of the input JSON array can be used for these needs.
Here is the list of its supported properties:
type
– String. The data type. Can be:"string"
– the field contains string data. You will be able to aggregate it only with count and distinct count aggregations. It will be sorted as string data."number"
– the field contains numeric data. You will be able to aggregate it with all available aggregations. It will be sorted as numeric data."month"
– the field contains months. Note that if the field stores month names only (in either short or full form), the field will be recognized by Flexmonster as a field of the "month"
type automatically. If the field contains custom month names, specify its type as "month"
explicitly."weekday"
– the field contains days of the week."date"
– the field is a date. Such fields will be split into 3 different fields: Year, Month, Day."date string"
– the field is a date. Such fields will be formatted using a date pattern (default is "dd/MM/yyyy"
)."year/month/day"
– the field is a date. You will see such dates as hierarchies: Year > Month > Day."year/quarter/month/day"
– the field is a date. You will see such dates as hierarchies: Year > Quarter > Month > Day."time"
– the field is a time (numeric data). Such fields will be formatted using "HH:mm:ss"
pattern."datetime"
– the field is a date (numeric data). Such fields will be formatted using "dd/MM/yyyy HH:mm:ss"
pattern. Min, max, count, and distinct count aggregations can be applied to it."id"
– the field is an id of the record. Such fields are used for editing data. This field will not be shown in the Field List."property"
– the field for setting member properties. This field will not be shown in the Field List. For example, it can be used to associate a productID
with a product
. See the example.hierarchy
– String. The hierarchy’s name. When configuring hierarchies, specify this property to mark the field as a level of a hierarchy or as a member property of a hierarchy (in this case, the type
parameter should be set to "property"
).parent
– String. The unique name of the parent level. This property is necessary to specify if the field is a level of a hierarchy and has a parent level.isMeasure
(optional) – Boolean. When set to true
, the field can be selected only as a measure. The isMeasure
property should be used only with the strictDataTypes option. Default value: false
.For example, you can add the following first object in a JSON array and see how it changes the report:
var jsonData = [
{
"Color": {type: "string"},
"Country": {type: "string", hierarchy: "Geography"},
"State": {type: "string", hierarchy: "Geography", parent: "Country"},
"City": {type: "string", hierarchy: "Geography", parent: "State"},
"Price": {type: "number"},
"Quantity": {type: "number"}
},
{
"Color" : "green",
"Country" : "Canada",
"State" : "Ontario",
"City" : "Toronto",
"Price" : 174,
"Quantity" : 22
},
{
"Color" : "red",
"Country" : "USA",
"State" : "California",
"City" : "Los Angeles",
"Price" : 166,
"Quantity" : 19
}
];
var pivot = new Flexmonster({
container: "pivotContainer",
toolbar: true,
report: {
dataSource: {
data: jsonData
},
slice: {
rows: [
{ uniqueName: "Color" },
{ uniqueName: "[Measures]" }
],
columns: [
{ uniqueName: "Geography" }
],
measures: [
{ uniqueName: "Price", aggregation: "sum" }
]
}
}
});
Try it on JSFiddle.
Note: if you use a JSON array of arrays you can also add the first object. In this case, you do not need to specify hierarchies in the first sub-array. Check out a live example.
It is possible to define only necessary types of fields and leave all others empty {}
. The type of these fields will be selected automatically. In the following example "Color"
was left undefined:
var jsonData = [
{
"Color": {},
"Price": {type: "number"}
},
{
"Color" : "green",
"Price" : 174,
"Country" : "Canada",
"City" : "Toronto",
"Discount" : 12
},
{
"Color" : "red",
"Price" : 166,
"Country" : "USA",
"City" : "Los Angeles",
"Discount" : 32
}
];
var pivot = new Flexmonster({
container: "pivotContainer",
toolbar: true,
report: {
dataSource: {
data: jsonData
}
}
});
Note that in the Field List only two fields will be available: "Color"
and "Price"
. All other fields ("Country"
, "City"
etc.) will be omitted because they were not mentioned in the first object of JSON array.
To make date fields be interpreted as a date, you must define the data type as a date. For example, "type": "date"
, "type": "date string"
, "type": "year/month/day"
or "type": "year/quarter/month/day"
. Additionally, data from these fields should have a special date format to be understood properly. The pivot table component supports the ISO 8601 date format, for example: "2016-03-20"
(just date) or "2016-03-20T14:48:00"
(date and time). Other formats aren’t officially supported and may have unexpected results.