A slice object defines which fields should be placed into the rows, columns, and measures. Besides, it contains information about the filtering, sorting, expands, drills and report filtering features.
Learn more about a slice object in the following sections:
| Name | Type | Description |
|---|---|---|
columns | Array of Hierarchy Objects | optional Specifies the hierarchies to be selected to the columns of the slice. |
drills | Drills Object | optional Stores all drill-down configurations of the multi-level hierarchies. |
expands | Expands Object | optional Stores the information about the hierarchies which should be expanded on the grid. |
measures | Array of Measure Objects | Specifies the measures to be selected to the slice. |
reportFilters | Array of Hierarchy Objects | optional Specifies the report filters to be selected to the slice. Report filters are applied to the whole report. |
rows | Array of Hierarchy Objects | optional Specifies the hierarchies to be selected to the rows of the slice. |
sorting | Sorting Object | optional Describes how the values for the specific hierarchies in the rows and/or columns are sorted on the grid. |
A Hierarchy Object. Each field of your data is represented by a hierarchy in a pivot table. Multiple fields can be combined into a single hierarchy. It is described by the following properties:
| Name | Type | Description |
|---|---|---|
uniqueName | String | An identifier of the hierarchy. |
caption | String | optional A caption of the hierarchy. |
filter | Object | optionalContains the following details about filtering that should be applied to the hierarchy members: |
| Array of strings | An array of hierarchy members to be shown on the grid if filtering by members is applied. |
| Boolean | optional Specifies whether to display all items defined in members (false) or to display all items except the ones defined in members(true). Default value: false. |
| String | optional Available only for the hierarchy members in the rows and columns. Used for Top X filtering. Specifies a measure on which filtering is based. |
| Number | optional Available only for the hierarchy members in the rows and columns. Used for Top X filtering. Sets the number of elements for the Top X ("type": "top") or Bottom X ("type": "bottom") filter. Default value: 10. |
| String | optional Available only for the hierarchy members in the rows and columns. Sets a type of filtering. Possible values are "none" (no filtering is applied to the hierarchy), "members" (filtering by members is applied), "top" (the Top X filter is applied), "bottom" (the Bottom X filter is applied). |
sort | String | optional Sets a type of sorting for the hierarchy members. Possible values: "asc", "desc" and "unsorted". Default value: "unsorted". |
A Drills Object. This object stores all drill-down configurations of the multi-level hierarchies. It has the following properties:
| Name | Type | Description |
|---|---|---|
| Boolean | optional Defines whether all levels of all hierarchies are drilled down (true) or drilled up (false) on the grid. Default value: false. |
| Array of objects | optional Stores all the columns which should be drilled down on the grid. |
| Array of objects | optional Stores all the rows which should be drilled down on the grid. |
An example of a drills object:
"drills": {
"drillAll": false,
"rows": [{
"tuple": [
"Business Type.Warehouse"
]
}]
}
The result of this code: the member "Warehouse" of the "Business Type" hierarchy from rows is drilled down on the grid. See the full code in the CodePen demo.
To drill down all the hierarchies, set a "drillAll" property to true.
When a user drills up & down the hierarchies on the grid, the changes are saved within a report and can be restored later.
An Expands Object. This object stores the information about the hierarchies which should be expanded on the grid. It has the following properties:
| Name | Type | Description |
|---|---|---|
| Boolean | optional Specifies whether all the hierarchies which are defined in the current slice should be expanded (true) or collapsed (false) on the grid. Default value: false. |
| Array of objects | optional Stores all the columns which should be expanded on the grid. |
| Array of objects | optional Stores all the rows which should be expanded on the grid. |
An example of the expands object:
"expands": {
"expandAll": false,
"rows": [{
"tuple": [
"Country.Australia"
]
},
{
"tuple": [
"Country.Canada"
]
}
]
}
The result of this code: only "Australia" and "Canada" members of the Country hierarchy from rows are expanded on the grid. See the full code in the CodePen demo.
To expand all the hierarchies, set a "expandAll" to true.
When a user expands the hierarchies on the grid, the changes are saved within a report and can be restored later.
A Measure Object. This object describes the fields selected to the measures. It has the following properties:
| Name | Type | Description |
|---|---|---|
uniqueName | String | An identifier of the measure. |
active | Boolean | optional Specifies whether this particular measure should be selected to the report (true) or not (false). |
aggregation | String | optionalAn identifier of an aggregation that should be applied to this measure. Possible values: "sum", "count", "distinctcount", "average", "product", "min", "max", "percent", "percentofcolumn", "percentofrow", "index", "difference", "%difference". If the measure is calculated and defined by "formula", this property is set to "none". Default value: "sum". |
availableAggregations | Array of strings | optional Stores strings of the aggregation functions. Specify here the functions that can be applied to the current measure. If the measure is calculated, this property is set to []. |
caption | String | optional A caption of the measure. |
formula | String | optional Defines a formula for the calculated measure. The following arithmetic, assignment, relational, logical operators and a conditional statement can be used in the formula: +, -, *, /, ^, =, <, >, <=, >=, ==, !=, or, and, if. The functions that can be applied to the measure: abs, max, min. Other measures can be addressed by using their uniqueName and aggregation properties. Examples of such addressing: sum("Price") or max("Order"). |
individual | Boolean | optional Specifies a type of the values which are used to calculate the formula: raw (true) or aggregated (false) ones. Default value: false. See the demo to understand the difference. |
format | String | optional A name of the number format object applied to the measure. |
grandTotalCaption | String | optional A caption of a grand total for this measure. |
You can define a slice with measures only:
{
"dataSource": {
"data": jsonData
},
"slice": {
"measures": [{
"uniqueName": "Price",
"aggregation": "sum",
"active": true
}]
}
}
Specify where the measures should be displayed by adding "uniqueName": "Measures" to rows or columns. Measures are put to columns by default.
An example of a slice inside of the report object:
{
"dataSource": {
"dataSourceType": "csv",
"filename": "https://cdn.webdatarocks.com/data/data.csv"
},
"slice": {
"rows": [{
"uniqueName": "Measures"
},
{
"uniqueName": "Category",
"filter": {
"members": [
"Category.Accessories"
],
"negation": true
}
}
],
"columns": [{
"uniqueName": "Destination"
}],
"measures": [{
"uniqueName": "Price",
"aggregation": "sum"
},
{
"uniqueName": "Quantity",
"aggregation": "sum"
}
],
"sorting": {
"row": {
"type": "desc",
"tuple": [],
"measure": "Price"
}
}
}
}
See the full code in the CodePen example.
A Sorting Object is described by the following properties:
| Name | Type | Description |
|---|---|---|
column | Object | optional Defines sorting for the specified column. It has the following properties: |
| Array of strings | Identifies the column members to which sorting should be applied. |
| String | Specifies a measure on which sorting is based. |
| String | A type of sorting for the column. Possible values: "asc" and "desc". |
row | Object | optional Defines sorting for the specified row. It has the following properties: |
| Array of strings | Identifies the row members to which sorting should be applied. |
| String | Specifies a measure on which sorting is based. |
| String | A type of sorting for the row. Possible values: "asc" and "desc". |
Define the sorting object within a report:
"sorting": {
"column": {
"type": "desc",
"tuple": [],
"measure": "Price"
}
}
Likewise, you can define sorting for the members of the columns and the rows. An example of sorting for the members of the "Category" hierarchy in rows:
"rows": [{
"uniqueName": "Category",
"filter": {
"members": [
"Category.Accessories"
],
"negation": true
},
"sort": "desc"
}]
If the slice is not defined, WebDataRocks specifies a default slice for your report. It has the following structure: the first hierarchy from the data is put into rows, the first measure – into columns. To disable the default slice, set a showDefaultSlice property in the Options Object to false.
Example
Suppose you have the following JSON data definition:
var jsonData = [{
"Category": "Accessories",
"Size": "262 oz",
"Color": "red",
"Destination": "Australia",
"Business Type": "Specialty Bike Shop",
"Country": "Australia",
"Price": 174,
"Quantity": 225,
"Discount": 23
},
{
"Category": "Accessories",
"Size": "214 oz",
"Color": "yellow",
"Destination": "Canada",
"Business Type": "Specialty Bike Shop",
"Country": "Canada",
"Price": 502,
"Quantity": 90,
"Discount": 17
}
]
In this case, "Category" is the first hierarchy and "Price" is the first measure. Consequently, these hierarchies go to rows and columns correspondingly. For this particular dataset, the default slice looks the following way:
{
"dataSource": {
"data": jsonData
},
"slice": {
"rows": [{
"uniqueName": "Category"
}],
"columns": [{
"uniqueName": "Measures"
}],
"measures": [{
"uniqueName": "Price"
}]
}
}
To see how the default slice is set in the report, check out the CodePen demo.
To concentrate on the particular subsets of data, use the report filters – they are applied to the whole report. Define a reportFilters property in the slice as follows:
{
"dataSource": {
"dataSourceType": "csv",
"filename": "https://cdn.webdatarocks.com/data/data.csv"
},
"slice": {
"reportFilters": [{
"uniqueName": "Country"
},
{
"uniqueName": "Business Type",
"filter": {
"members": [
"Business Type.Warehouse"
]
}
}
]
"rows": [{
"uniqueName": "Measures"
},
{
"uniqueName": "Category",
"filter": {
"members": [
"Category.Accessories"
],
"negation": true
}
}
],
"columns": [{
"uniqueName": "Destination"
}],
"measures": [{
"uniqueName": "Price",
"aggregation": "sum"
},
{
"uniqueName": "Quantity",
"aggregation": "sum"
}
]
}
}
See how to set report filters in the CodePen example.
To create a custom calculated value for the report, define a formula for the measure. After creation, all the calculated values are stored within a report.
Example
Suppose you want to calculate the total revenue of sales. Here is how the calculated value may look like in this case:
"measures": [{
"uniqueName": "Revenue",
"formula": "sum('Price') * sum('Quantity')",
"caption": "Revenue",
"active": true
}]
Create as many calculated values as you need. There is no limit on their number.
The Field List is a multi-purpose element of a pivot table: here you can arrange the fields in the rows, columns, measures, and report filters, define the calculated values and select the aggregations for the measures.
An example of the slice definition:
Sorting, filtering, drilling up/down, expanding/collapsing features are available right on the grid.
You can change the slice by using setReport() API call. To get the slice as a part of the report use getReport() API call.