Published at: 2026-09-17
Crosstab report functions
Appendix: Crosstab report structure
♦Column groups — colGroupFields
♦Row groups — rowGroupFields
♦Header — headerFields
♦Data set — dataSet
♦Summary header information — summaryDisplayFields
♦Summary data set — summaryDataSet
2.1 Query a crosstab report by crosstab report ID (loads the first 20 records by default): Fx.BI.queryPivotViewDataById()
2.1.1 Purpose
Query crosstab report data by viewId.
2.1.2 Parameters
| Parameter | Type | Required | Description | Example |
|---|---|---|---|---|
| viewId | String | Yes | Crosstab report ID | <pre> “BI_62973467376efe000172be38”</pre> |
2.1.3 Example
def(isError,data,errorMsg) = Fx.BI.queryPivotViewDataById("BI_62973467376efe000172be38")
if(!isError){
log.info("Pagination information: "+data["page"])
log.info("Header information: "+data["headerFields"])
log.info("Data set: "+data["dataSet"])
log.info("Column groups: "+data["colGroupFields"])
log.info("Row groups: "+data["rowGroupFields"])
log.info("Statistic columns: "+data["statFields"])
log.info("summaryDataSet: "+data["summaryDataSet"])
log.info("summaryDisplayFields: "+data["summaryDisplayFields"])
}else{
log.info("Query failed: "+errorMsg)
}
2.1.4 Return values
| Parameter | Type | Description |
|---|---|---|
| code | Int | Function status code. 200: success. 403: no permission. 500: system error. |
| message | String | Returned message |
| page | Object | Pagination information |
| headerFields | Object | Header information |
| dataSet | Object | Result data set |
| colGroupFields | Object | Column groups |
| rowGroupFields | Object | Row groups |
| statFields | Object | Statistic columns |
| summaryDataSet | Object | Summary data set |
| summaryDisplayFields | Object | Summary header information |
2.1.5 Return value example
Chart data is returned in a unified two-dimensional table structure and maps in pairs.
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"page": {
"pageSize": 20,
"pageNumber": 1,
"pageCount": 1,
"totalCount": 2
},
"dataSet": [
[
{
"value": "--",
"formattedValue": "--"
},
{
"value": "10.0",
"formattedValue": "10.000"
},
{
"value": "10.0",
"formattedValue": "10.000"
}
],
[
{
"value": "Total",
"formattedValue": "Total"
},
{
"value": "10.0",
"formattedValue": "10.000"
},
{
"value": "10.0",
"formattedValue": "10.000"
}
]
],
"headerFields": [
[
{
"value": "Department",
"fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
"formattedValue": null,
"fieldName": "Department",
"columnName": "object_oz2i0__c_field_6ejpc__c$0",
"formatStr": ""
},
{
"value": "1001",
"fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
"formattedValue": "Sales Department",
"fieldName": "Department",
"columnName": null,
"formatStr": null
},
{
"value": "Total",
"fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
"formattedValue": "Total",
"fieldName": "Department",
"columnName": null,
"formatStr": null
}
],
[
{
"value": "Account Name",
"fieldID": "BI_3f378638510641067008767e02cf6563",
"formattedValue": null,
"fieldName": "Account Name",
"columnName": "object_oz2i0__c_field_21fnw__c$0",
"formatStr": ""
},
{
"value": "Amount",
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"formattedValue": null,
"fieldName": "Amount",
"columnName": "object_oz2i0__c_field_6rfqc__c$0",
"formatStr": "#,##0.000"
},
{
"value": "Amount",
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"formattedValue": null,
"fieldName": "Amount",
"columnName": "object_oz2i0__c_field_6rfqc__c$0",
"formatStr": "#,##0.000"
}
]
],
"colGroupFields": [
{
"fieldId": "BI_3f378638510641067008767e02cf6563",
"fieldName": "Account Name",
"formatStr": "",
"columnName": "object_oz2i0__c_field_21fnw__c$0"
}
],
"rowGroupFields": [
{
"fieldId": "BI_1416823ffb7298cc11838cedb4b59fdf",
"fieldName": "Department",
"formatStr": "",
"columnName": "object_oz2i0__c_field_6ejpc__c$0"
}
],
"statFields": [
{
"columnName": null,
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"fieldName": "Amount",
"crmFieldName": "field_6rfqC__c",
"crmObjName": "object_oz2i0__c",
"dbFieldName": "field_6rfqC__c",
"dbObjName": "object_oz2i0__c"
}
],
"summaryDataSet": {
"object_oz2i0__c_field_6rfqc__c$0": [
{
"value": "10.0",
"formattedValue": "10.000",
"formattedShowValue": "10.0",
"aggType": "2",
"aggTypeCaption": "Sum"
}
]
},
"summaryDisplayFields": [
{
"columnName": null,
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"fieldName": "Amount",
"crmFieldName": "field_6rfqC__c",
"crmObjName": "object_oz2i0__c",
"dbFieldName": "field_6rfqC__c",
"dbObjName": "object_oz2i0__c"
}
]
}
}
2.2 Query a crosstab report by viewId and pagination information: Fx.BI.queryPivotViewDataByPage(,,)
2.2.1 Purpose
Query a crosstab report by viewId and pagination information. You can specify the page number to query and the number of records per page.
2.2.2 Parameters
| Parameter | Type | Required | Description | Example |
|---|---|---|---|---|
| viewId | String | Yes | Crosstab report ID | <pre>“BI_62973467376efe000172be38”</pre> |
| pageNumber | Integer | Yes | Page number | 1 |
| pageSize | Integer | Yes | Records per page | 1 |
2.2.3 Example
==Query page 1 with one record per page=======
=============================================================================================
def(isError,data,errorMsg) = Fx.BI.queryPivotViewDataByPage("BI_62973467376efe000172be38",1,1)
if(!isError){
log.info("Pagination information: "+data["page"])
log.info("Header information: "+data["headerFields"])
log.info("Data set: "+data["dataSet"])
log.info("Column groups: "+data["colGroupFields"])
log.info("Row groups: "+data["rowGroupFields"])
log.info("Statistic columns: "+data["statFields"])
log.info("Summary data set: "+data["summaryDataSet"])
log.info("Summary header information: "+data["summaryDisplayFields"])
}else{
log.info("Query failed: "+errorMsg)
}
2.2.4 Return values
| Parameter | Type | Description |
|---|---|---|
| code | Int | Function status code. 200: success. 403: no permission. 500: system error. |
| message | String | Returned message |
| page | Object | Pagination information |
| headerFields | Object | Header information |
| dataSet | Object | Result data set |
| colGroupFields | Object | Column groups |
| rowGroupFields | Object | Row groups |
| statFields | Object | Statistic columns |
| summaryDataSet | Object | Summary data set |
| summaryDisplayFields | Object | Summary header information |
2.2.5 Return value example
Chart data is returned in a unified two-dimensional table structure and maps in pairs.
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"page": {
"pageSize": 1,
"pageNumber": 1,
"pageCount": 2,
"totalCount": 2
},
"dataSet": [
[
{
"value": "--",
"formattedValue": "--"
},
{
"value": "10.0",
"formattedValue": "10.000"
},
{
"value": "10.0",
"formattedValue": "10.000"
}
]
],
"headerFields": [
[
{
"value": "Department",
"fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
"formattedValue": null,
"fieldName": "Department",
"columnName": "object_oz2i0__c_field_6ejpc__c$0",
"formatStr": ""
},
{
"value": "1001",
"fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
"formattedValue": "Sales Department",
"fieldName": "Department",
"columnName": null,
"formatStr": null
},
{
"value": "Total",
"fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
"formattedValue": "Total",
"fieldName": "Department",
"columnName": null,
"formatStr": null
}
],
[
{
"value": "Account Name",
"fieldID": "BI_3f378638510641067008767e02cf6563",
"formattedValue": null,
"fieldName": "Account Name",
"columnName": "object_oz2i0__c_field_21fnw__c$0",
"formatStr": ""
},
{
"value": "Amount",
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"formattedValue": null,
"fieldName": "Amount",
"columnName": "object_oz2i0__c_field_6rfqc__c$0",
"formatStr": "#,##0.000"
},
{
"value": "Amount",
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"formattedValue": null,
"fieldName": "Amount",
"columnName": "object_oz2i0__c_field_6rfqc__c$0",
"formatStr": "#,##0.000"
}
]
],
"colGroupFields": [
{
"fieldId": "BI_3f378638510641067008767e02cf6563",
"fieldName": "Account Name",
"formatStr": "",
"columnName": "object_oz2i0__c_field_21fnw__c$0"
}
],
"rowGroupFields": [
{
"fieldId": "BI_1416823ffb7298cc11838cedb4b59fdf",
"fieldName": "Department",
"formatStr": "",
"columnName": "object_oz2i0__c_field_6ejpc__c$0"
}
],
"statFields": [
{
"columnName": null,
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"fieldName": "Amount",
"crmFieldName": "field_6rfqC__c",
"crmObjName": "object_oz2i0__c",
"dbFieldName": "field_6rfqC__c",
"dbObjName": "object_oz2i0__c"
}
],
"summaryDataSet": {
"object_oz2i0__c_field_6rfqc__c$0": [
{
"value": "10.0",
"formattedValue": "10.000",
"formattedShowValue": "10.0",
"aggType": "2",
"aggTypeCaption": "Sum"
}
]
},
"summaryDisplayFields": [
{
"columnName": null,
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"fieldName": "Amount",
"crmFieldName": "field_6rfqC__c",
"crmObjName": "object_oz2i0__c",
"dbFieldName": "field_6rfqC__c",
"dbObjName": "object_oz2i0__c"
}
]
}
}
2.3 Query crosstab report data by viewId, filterList, and defaultFilterOptionId: Fx.BI.queryPivotViewDataByFilter(,,)
- )
2.3.1 Purpose
Query crosstab report data by viewId, filterList, and defaultFilterOptionId.
2.3.2 Parameters
| Parameter | Type | Required | Description | Example |
|---|---|---|---|---|
| viewId | String | Yes | Crosstab report ID | <pre>“BI_62973467376efe000172be38” </pre> |
| defaultFilterOptionId | String | Yes | Scenario ID | <pre>“BI_59a3e59333b39e09b44e9db2”</pre> |
| filterList | Integer | Yes | Filter conditions | <pre>[["filterId":"BI_62c68112deef7b0001c1f756","value1":"[{"id":1001,"type":"g"}]","value2":""]] </pre> |
2.3.3 filterList structure
| Parameter | Type | Required | Description | Example |
|---|---|---|---|---|
| filterId | String | Yes | Filter ID | <pre>“BI_62c68112deef7b0001c1f756” </pre> |
| value1 | String | Yes | Filter value | <pre>"[{"id":1001,"type":"g"}]" </pre> |
| value2 | Integer | Yes | Filter value | <pre>“” </pre> |
| dateRangeId | Integer | No | Enumeration value required for date filters | 16 (Note: For enumeration values, see “1.6 Notes on using filterList: date filter cases and enumeration values”) |
2.3.4 Example
==Query crosstab report data for a specific scenario and filters=======
=============================================================================================
List filterList = [["filterId":"BI_62c68112deef7b0001c1f756","value1":"[{"id":1001,"type":"g"}]","value2":""]]
def(isError,data,errorMsg) = Fx.BI.queryPivotViewDataByFilter("BI_62973467376efe000172be38","BI_59a3e59333b39e09b44e9db2",filterList)
if(!isError){
log.info("Pagination information: "+data["page"])
log.info("Header information: "+data["headerFields"])
log.info("Data set: "+data["dataSet"])
log.info("Column groups: "+data["colGroupFields"])
log.info("Row groups: "+data["rowGroupFields"])
log.info("Statistic columns: "+data["statFields"])
log.info("Summary data set: "+data["summaryDataSet"])
log.info("Summary header information: "+data["summaryDisplayFields"])
}else{
log.info("Query failed: "+errorMsg)
}
2.3.5 Return values
| Parameter | Type | Description |
|---|---|---|
| code | Int | Function status code. 200: success. 403: no permission. 500: system error. |
| message | String | Returned message |
| page | Object | Pagination information |
| headerFields | Object | Header information |
| dataSet | Object | Result data set |
| colGroupFields | Object | Column groups |
| rowGroupFields | Object | Row groups |
| statFields | Object | Statistic columns |
| summaryDataSet | Object | Summary data set |
| summaryDisplayFields | Object | Summary header information |
2.3.6 Return value example
Chart data is returned in a unified two-dimensional table structure and maps in pairs.
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"page": {
"pageSize": 20,
"pageNumber": 1,
"pageCount": 1,
"totalCount": 2
},
"dataSet": [
[
{
"value": "--",
"formattedValue": "--"
},
{
"value": "10.0",
"formattedValue": "10.000"
},
{
"value": "10.0",
"formattedValue": "10.000"
}
],
[
{
"value": "Total",
"formattedValue": "Total"
},
{
"value": "10.0",
"formattedValue": "10.000"
},
{
"value": "10.0",
"formattedValue": "10.000"
}
]
],
"headerFields": [
[
{
"value": "Department",
"fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
"formattedValue": null,
"fieldName": "Department",
"columnName": "object_oz2i0__c_field_6ejpc__c$0",
"formatStr": ""
},
{
"value": "1001",
"fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
"formattedValue": "Sales Department",
"fieldName": "Department",
"columnName": null,
"formatStr": null
},
{
"value": "Total",
"fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
"formattedValue": "Total",
"fieldName": "Department",
"columnName": null,
"formatStr": null
}
],
[
{
"value": "Account Name",
"fieldID": "BI_3f378638510641067008767e02cf6563",
"formattedValue": null,
"fieldName": "Account Name",
"columnName": "object_oz2i0__c_field_21fnw__c$0",
"formatStr": ""
},
{
"value": "Amount",
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"formattedValue": null,
"fieldName": "Amount",
"columnName": "object_oz2i0__c_field_6rfqc__c$0",
"formatStr": "#,##0.000"
},
{
"value": "Amount",
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"formattedValue": null,
"fieldName": "Amount",
"columnName": "object_oz2i0__c_field_6rfqc__c$0",
"formatStr": "#,##0.000"
}
]
],
"colGroupFields": [
{
"fieldId": "BI_3f378638510641067008767e02cf6563",
"fieldName": "Account Name",
"formatStr": "",
"columnName": "object_oz2i0__c_field_21fnw__c$0"
}
],
"rowGroupFields": [
{
"fieldId": "BI_1416823ffb7298cc11838cedb4b59fdf",
"fieldName": "Department",
"formatStr": "",
"columnName": "object_oz2i0__c_field_6ejpc__c$0"
}
],
"statFields": [
{
"columnName": null,
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"fieldName": "Amount",
"crmFieldName": "field_6rfqC__c",
"crmObjName": "object_oz2i0__c",
"dbFieldName": "field_6rfqC__c",
"dbObjName": "object_oz2i0__c"
}
],
"summaryDataSet": {
"object_oz2i0__c_field_6rfqc__c$0": [
{
"value": "10.0",
"formattedValue": "10.000",
"formattedShowValue": "10.0",
"aggType": "2",
"aggTypeCaption": "Sum"
}
]
},
"summaryDisplayFields": [
{
"columnName": null,
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"fieldName": "Amount",
"crmFieldName": "field_6rfqC__c",
"crmObjName": "object_oz2i0__c",
"dbFieldName": "field_6rfqC__c",
"dbObjName": "object_oz2i0__c"
}
]
}
}
2.4 Query crosstab report data by viewId, pagination information, filterList, and defaultFilterOptionId: Fx.BI.queryPivotReportData(,,,,)
- )
2.4.1 Purpose
Query a crosstab report by viewId, filterList, and the scenario defaultFilterOptionId.
2.4.2 Parameters
| Parameter | Type | Required | Description | Example |
|---|---|---|---|---|
| viewId | String | Yes | Crosstab report ID | <pre>“BI_62973467376efe000172be38” </pre> |
| defaultFilterOptionId | String | Yes | Scenario ID | <pre>“BI_59a3e59333b39e09b44e9db2” </pre> |
| filterList | Integer | Yes | Filter conditions | <pre>[["filterId":"BI_62c68112deef7b0001c1f756","value1":"[{"id":1001,"type":"g"}]","value2":""]] </pre> |
| pageNumber | Integer | Yes | Page number | 1 |
| pageSize | Integer | Yes | Records per page | 1 |
2.4.3 filterList structure
| Parameter | Type | Required | Description | Example |
|---|---|---|---|---|
| filterId | String | Yes | Filter ID | <pre>“BI_62c68112deef7b0001c1f756” </pre> |
| value1 | String | Yes | Filter value | <pre>"[{"id":1001,"type":"g"}]” </pre> |
| value2 | Integer | Yes | Filter value | <pre>“” </pre> |
| dateRangeId | Integer | No | Enumeration value required for date filters | 16 (Note: For enumeration values, see “1.6 Notes on using filterList: date filter cases and enumeration values”) |
2.4.4 Example
==Query crosstab report data by pagination information, viewId, scenario ID, and filterList=======
=============================================================================================
List filterList = [["filterId":"BI_62c68112deef7b0001c1f756","value1":"[{"id":1001,"type":"g"}]","value2":""]]
def(isError,data,errorMsg) = Fx.BI.queryPivotReportData("BI_62973467376efe000172be38",1,1,"BI_59a3e59333b39e09b44e9db2",filterList)
if(!isError){
log.info("Pagination information: "+data["page"])
log.info("Header information: "+data["headerFields"])
log.info("Data set: "+data["dataSet"])
log.info("Column groups: "+data["colGroupFields"])
log.info("Row groups: "+data["rowGroupFields"])
log.info("Statistic columns: "+data["statFields"])
log.info("Summary data set: "+data["summaryDataSet"])
log.info("Summary header information: "+data["summaryDisplayFields"])
}else{
log.info("Query failed: "+errorMsg)
}
2.4.5 Return values
| Parameter | Type | Description |
|---|---|---|
| code | Int | Function status code. 200: success. 403: no permission. 500: system error. |
| message | String | Returned message |
| page | Object | Pagination information |
| headerFields | Object | Header information |
| dataSet | Object | Result data set |
| colGroupFields | Object | Column groups |
| rowGroupFields | Object | Row groups |
| statFields | Object | Statistic columns |
| summaryDataSet | Object | Summary data set |
| summaryDisplayFields | Object | Summary header information |
2.4.6 Return value example
Chart data is returned in a unified two-dimensional table structure and maps in pairs.
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"page": {
"pageSize": 1,
"pageNumber": 1,
"pageCount": 2,
"totalCount": 2
},
"dataSet": [
[
{
"value": "--",
"formattedValue": "--"
},
{
"value": "10.0",
"formattedValue": "10.000"
},
{
"value": "10.0",
"formattedValue": "10.000"
}
]
],
"headerFields": [
[
{
"value": "Department",
"fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
"formattedValue": null,
"fieldName": "Department",
"columnName": "object_oz2i0__c_field_6ejpc__c$0",
"formatStr": ""
},
{
"value": "1001",
"fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
"formattedValue": "Sales Department",
"fieldName": "Department",
"columnName": null,
"formatStr": null
},
{
"value": "Total",
"fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
"formattedValue": "Total",
"fieldName": "Department",
"columnName": null,
"formatStr": null
}
],
[
{
"value": "Account Name",
"fieldID": "BI_3f378638510641067008767e02cf6563",
"formattedValue": null,
"fieldName": "Account Name",
"columnName": "object_oz2i0__c_field_21fnw__c$0",
"formatStr": ""
},
{
"value": "Amount",
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"formattedValue": null,
"fieldName": "Amount",
"columnName": "object_oz2i0__c_field_6rfqc__c$0",
"formatStr": "#,##0.000"
},
{
"value": "Amount",
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"formattedValue": null,
"fieldName": "Amount",
"columnName": "object_oz2i0__c_field_6rfqc__c$0",
"formatStr": "#,##0.000"
}
]
],
"colGroupFields": [
{
"fieldId": "BI_3f378638510641067008767e02cf6563",
"fieldName": "Account Name",
"formatStr": "",
"columnName": "object_oz2i0__c_field_21fnw__c$0"
}
],
"rowGroupFields": [
{
"fieldId": "BI_1416823ffb7298cc11838cedb4b59fdf",
"fieldName": "Department",
"formatStr": "",
"columnName": "object_oz2i0__c_field_6ejpc__c$0"
}
],
"statFields": [
{
"columnName": null,
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"fieldName": "Amount",
"crmFieldName": "field_6rfqC__c",
"crmObjName": "object_oz2i0__c",
"dbFieldName": "field_6rfqC__c",
"dbObjName": "object_oz2i0__c"
}
],
"summaryDataSet": {
"object_oz2i0__c_field_6rfqc__c$0": [
{
"value": "10.0",
"formattedValue": "10.000",
"formattedShowValue": "10.0",
"aggType": "2",
"aggTypeCaption": "Sum"
}
]
},
"summaryDisplayFields": [
{
"columnName": null,
"fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
"fieldName": "Amount",
"crmFieldName": "field_6rfqC__c",
"crmObjName": "object_oz2i0__c",
"dbFieldName": "field_6rfqC__c",
"dbObjName": "object_oz2i0__c"
}
]
}
}