交叉表函数

二、交叉表函数使用说明

附:交叉表结构解析

♦列分组—colGroupFields

♦行分组—rowGroupFields

♦表头—headerFields

♦数据集—dataSet

♦总计表头信息—summaryDisplayFields

♦总计数据集—summaryDataSet

2.1 通过交叉表 Id 查询交叉表(默认加载交叉表首页 20 条数据) Fx.BI.queryPivotViewDataById(String viewId)

2.1.1 函数用途

通过viewId查询交叉表数据

2.1.2 参数说明

参数 类型 是否必须 说明 参数示例
viewId String 交叉表ID
 “BI_62973467376efe000172be38”

2.1.3 函数举例

def(isError,data,errorMsg) = Fx.BI.queryPivotViewDataById("BI_62973467376efe000172be38")
if(!isError){
  log.info("分页信息:"+data["page"])
  log.info("表头信息:"+data["headerFields"])
  log.info("数据集:"+data["dataSet"])
  log.info("列分组:"+data["colGroupFields"])
  log.info("行分组:"+data["rowGroupFields"])
  log.info("统计列:"+data["statFields"])
  log.info("summaryDataSet:"+data["summaryDataSet"])
  log.info("summaryDisplayFields:"+data["summaryDisplayFields"])
}else{
  log.info("查询失败:"+errorMsg)
}

2.1.4 返回值说明

参数 类型 说明
code Int 函数状态码:200:成功403:没有权限500:系统异常
message String 返回消息
page Object 分页信息
headerFields Object 表头信息
dataSet Object 结果数据集
colGroupFields Object 列分组
rowGroupFields Object 行分组
statFields Object 统计列
summaryDataSet Object 总计数据集
summaryDisplayFields Object 总计表头信息

2.1.5 返回值示例

图表数据返回的结构统一为二维表,成对映射

{
    "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": "总计",
                    "formattedValue": "总计"
                },
                {
                    "value": "10.0",
                    "formattedValue": "10.000"
                },
                {
                    "value": "10.0",
                    "formattedValue": "10.000"
                }
            ]
        ],
        "headerFields": [
            [
                {
                    "value": "部门",
                    "fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
                    "formattedValue": null,
                    "fieldName": "部门",
                    "columnName": "object_oz2i0__c_field_6ejpc__c$0",
                    "formatStr": ""
                },
                {
                    "value": "1001",
                    "fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
                    "formattedValue": "销售部门",
                    "fieldName": "部门",
                    "columnName": null,
                    "formatStr": null
                },
                {
                    "value": "总计",
                    "fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
                    "formattedValue": "总计",
                    "fieldName": "部门",
                    "columnName": null,
                    "formatStr": null
                }
            ],
            [
                {
                    "value": "客户名称",
                    "fieldID": "BI_3f378638510641067008767e02cf6563",
                    "formattedValue": null,
                    "fieldName": "客户名称",
                    "columnName": "object_oz2i0__c_field_21fnw__c$0",
                    "formatStr": ""
                },
                {
                    "value": "金额",
                    "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                    "formattedValue": null,
                    "fieldName": "金额",
                    "columnName": "object_oz2i0__c_field_6rfqc__c$0",
                    "formatStr": "#,##0.000"
                },
                {
                    "value": "金额",
                    "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                    "formattedValue": null,
                    "fieldName": "金额",
                    "columnName": "object_oz2i0__c_field_6rfqc__c$0",
                    "formatStr": "#,##0.000"
                }
            ]
        ],
        "colGroupFields": [
            {
                "fieldId": "BI_3f378638510641067008767e02cf6563",
                "fieldName": "客户名称",
                "formatStr": "",
                "columnName": "object_oz2i0__c_field_21fnw__c$0"
            }
        ],
        "rowGroupFields": [
            {
                "fieldId": "BI_1416823ffb7298cc11838cedb4b59fdf",
                "fieldName": "部门",
                "formatStr": "",
                "columnName": "object_oz2i0__c_field_6ejpc__c$0"
            }
        ],
        "statFields": [
            {
                "columnName": null,
                "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                "fieldName": "金额",
                "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": "求和"
                }
            ]
        },
        "summaryDisplayFields": [
            {
                "columnName": null,
                "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                "fieldName": "金额",
                "crmFieldName": "field_6rfqC__c",
                "crmObjName": "object_oz2i0__c",
                "dbFieldName": "field_6rfqC__c",
                "dbObjName": "object_oz2i0__c"
            }
        ]
    }
}

2.2 通过 viewId 和分页信息 page 查询交叉表 Fx.BI.queryPivotViewDataByPage(String viewId, Integer pageNumber, Integer pageSize)

**

2.2.1 函数用途

通过viewId和分页信息page查询交叉表,用户可以自定义查询想要查询的页码,并规定每页条数

2.2.2 参数说明

参数 类型 是否必须 说明 参数示例
viewId String 交叉表ID
“BI_62973467376efe000172be38”
pageNumber Integer 页码 1
pageSize Integer 每页规定条数 1

2.2.3 函数举例

==查询第一页,每页一条数据的交叉表数据=======
=============================================================================================
def(isError,data,errorMsg) = Fx.BI.queryPivotViewDataByPage("BI_62973467376efe000172be38",1,1)
if(!isError){
  log.info("分页信息:"+data["page"])
  log.info("表头信息:"+data["headerFields"])
  log.info("数据集:"+data["dataSet"])
  log.info("列分组:"+data["colGroupFields"])
  log.info("行分组:"+data["rowGroupFields"])
  log.info("统计列:"+data["statFields"])
  log.info("总计数据集:"+data["summaryDataSet"])
  log.info("总计表头信息:"+data["summaryDisplayFields"])
}else{
  log.info("查询失败:"+errorMsg)
}

2.2.4 返回值说明

参数 类型 说明
code Int 函数状态码:200:成功403:没有权限500:系统异常
message String 返回消息
page Object 分页信息
headerFields Object 表头信息
dataSet Object 结果数据集
colGroupFields Object 列分组
rowGroupFields Object 行分组
statFields Object 统计列
summaryDataSet Object 总计数据集
summaryDisplayFields Object 总计表头信息

2.2.5 返回值示例

图表数据返回的结构统一为二维表,成对映射

{
    "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": "部门",
                    "fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
                    "formattedValue": null,
                    "fieldName": "部门",
                    "columnName": "object_oz2i0__c_field_6ejpc__c$0",
                    "formatStr": ""
                },
                {
                    "value": "1001",
                    "fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
                    "formattedValue": "销售部门",
                    "fieldName": "部门",
                    "columnName": null,
                    "formatStr": null
                },
                {
                    "value": "总计",
                    "fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
                    "formattedValue": "总计",
                    "fieldName": "部门",
                    "columnName": null,
                    "formatStr": null
                }
            ],
            [
                {
                    "value": "客户名称",
                    "fieldID": "BI_3f378638510641067008767e02cf6563",
                    "formattedValue": null,
                    "fieldName": "客户名称",
                    "columnName": "object_oz2i0__c_field_21fnw__c$0",
                    "formatStr": ""
                },
                {
                    "value": "金额",
                    "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                    "formattedValue": null,
                    "fieldName": "金额",
                    "columnName": "object_oz2i0__c_field_6rfqc__c$0",
                    "formatStr": "#,##0.000"
                },
                {
                    "value": "金额",
                    "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                    "formattedValue": null,
                    "fieldName": "金额",
                    "columnName": "object_oz2i0__c_field_6rfqc__c$0",
                    "formatStr": "#,##0.000"
                }
            ]
        ],
        "colGroupFields": [
            {
                "fieldId": "BI_3f378638510641067008767e02cf6563",
                "fieldName": "客户名称",
                "formatStr": "",
                "columnName": "object_oz2i0__c_field_21fnw__c$0"
            }
        ],
        "rowGroupFields": [
            {
                "fieldId": "BI_1416823ffb7298cc11838cedb4b59fdf",
                "fieldName": "部门",
                "formatStr": "",
                "columnName": "object_oz2i0__c_field_6ejpc__c$0"
            }
        ],
        "statFields": [
            {
                "columnName": null,
                "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                "fieldName": "金额",
                "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": "求和"
                }
            ]
        },
        "summaryDisplayFields": [
            {
                "columnName": null,
                "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                "fieldName": "金额",
                "crmFieldName": "field_6rfqC__c",
                "crmObjName": "object_oz2i0__c",
                "dbFieldName": "field_6rfqC__c",
                "dbObjName": "object_oz2i0__c"
            }
        ]
    }
}

2.3 通过 viewId、filterList 和 defaultFilterOptionId 查询交叉表数据 Fx.BI.queryPivotViewDataByFilter(String viewId, String defaultFilterOptionId, List filterList)

**

2.3.1 函数用途

通过viewId和分页信息page查询交叉表,用户可以自定义查询想要查询的页码,并规定每页条数

2.3.2 参数说明

参数 类型 是否必须 说明 参数示例
viewId String 交叉表ID
“BI_62973467376efe000172be38” 
defaultFilterOptionId String 场景Id
“BI_59a3e59333b39e09b44e9db2”
filterList Integer 筛选条件
[["filterId":"BI_62c68112deef7b0001c1f756","value1":"[{"id":1001,"type":"g"}]","value2":""]] 

2.3.3 filterList结构说明

参数 类型 是否必须 说明 参数示例
filterId String 筛选ID
“BI_62c68112deef7b0001c1f756” 
value1 String 筛选值
"[{"id":1001,"type":"g"}]" 
value2 Integer 筛选值
“” 
dateRangeId Integer 日期筛选需要用到的枚举值 16 (注意:枚举值见-》6、使用filterList注意事项:日期筛选case和枚举项)

2.3.4 函数举例

==查询特定的场景和筛选的交叉表数据=======
=============================================================================================
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("分页信息:"+data["page"])
  log.info("表头信息:"+data["headerFields"])
  log.info("数据集:"+data["dataSet"])
  log.info("列分组:"+data["colGroupFields"])
  log.info("行分组:"+data["rowGroupFields"])
  log.info("统计列:"+data["statFields"])
  log.info("总计数据集:"+data["summaryDataSet"])
  log.info("总计表头信息:"+data["summaryDisplayFields"])
}else{
  log.info("查询失败:"+errorMsg)
}

2.3.5 返回值说明

参数 类型 说明
code Int 函数状态码:200:成功403:没有权限500:系统异常
message String 返回消息
page Object 分页信息
headerFields Object 表头信息
dataSet Object 结果数据集
colGroupFields Object 列分组
rowGroupFields Object 行分组
statFields Object 统计列
summaryDataSet Object 总计数据集
summaryDisplayFields Object 总计表头信息

2.3.6 返回值示例

图表数据返回的结构统一为二维表,成对映射

{
    "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": "总计",
                    "formattedValue": "总计"
                },
                {
                    "value": "10.0",
                    "formattedValue": "10.000"
                },
                {
                    "value": "10.0",
                    "formattedValue": "10.000"
                }
            ]
        ],
        "headerFields": [
            [
                {
                    "value": "部门",
                    "fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
                    "formattedValue": null,
                    "fieldName": "部门",
                    "columnName": "object_oz2i0__c_field_6ejpc__c$0",
                    "formatStr": ""
                },
                {
                    "value": "1001",
                    "fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
                    "formattedValue": "销售部门",
                    "fieldName": "部门",
                    "columnName": null,
                    "formatStr": null
                },
                {
                    "value": "总计",
                    "fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
                    "formattedValue": "总计",
                    "fieldName": "部门",
                    "columnName": null,
                    "formatStr": null
                }
            ],
            [
                {
                    "value": "客户名称",
                    "fieldID": "BI_3f378638510641067008767e02cf6563",
                    "formattedValue": null,
                    "fieldName": "客户名称",
                    "columnName": "object_oz2i0__c_field_21fnw__c$0",
                    "formatStr": ""
                },
                {
                    "value": "金额",
                    "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                    "formattedValue": null,
                    "fieldName": "金额",
                    "columnName": "object_oz2i0__c_field_6rfqc__c$0",
                    "formatStr": "#,##0.000"
                },
                {
                    "value": "金额",
                    "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                    "formattedValue": null,
                    "fieldName": "金额",
                    "columnName": "object_oz2i0__c_field_6rfqc__c$0",
                    "formatStr": "#,##0.000"
                }
            ]
        ],
        "colGroupFields": [
            {
                "fieldId": "BI_3f378638510641067008767e02cf6563",
                "fieldName": "客户名称",
                "formatStr": "",
                "columnName": "object_oz2i0__c_field_21fnw__c$0"
            }
        ],
        "rowGroupFields": [
            {
                "fieldId": "BI_1416823ffb7298cc11838cedb4b59fdf",
                "fieldName": "部门",
                "formatStr": "",
                "columnName": "object_oz2i0__c_field_6ejpc__c$0"
            }
        ],
        "statFields": [
            {
                "columnName": null,
                "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                "fieldName": "金额",
                "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": "求和"
                }
            ]
        },
        "summaryDisplayFields": [
            {
                "columnName": null,
                "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                "fieldName": "金额",
                "crmFieldName": "field_6rfqC__c",
                "crmObjName": "object_oz2i0__c",
                "dbFieldName": "field_6rfqC__c",
                "dbObjName": "object_oz2i0__c"
            }
        ]
    }
}

2.4 通过 viewId、分页信息 page、filterList 和 defaultFilterOptionId 查询交叉表数据 Fx.BI.queryPivotReportData(String viewId, Integer pageNumber, Integer pageSize, String defaultFilterOptionId, List filterList)

**

2.4.1 函数用途

通过viewId和筛选条件filterList、场景defaultFilterOptionId,查询交叉表

2.4.2 参数说明

参数 类型 是否必须 说明 参数示例
viewId String 交叉表ID
“BI_62973467376efe000172be38” 
defaultFilterOptionId String 场景Id
“BI_59a3e59333b39e09b44e9db2” 
filterList Integer 筛选条件
[["filterId":"BI_62c68112deef7b0001c1f756","value1":"[{"id":1001,"type":"g"}]","value2":""]] 
pageNumber Integer 页码 1
pageSize Integer 每页的条数 1

2.4.3 filterList结构说明

参数 类型 是否必须 说明 参数示例
filterId String 筛选ID
“BI_62c68112deef7b0001c1f756” 
value1 String 筛选值
"[{"id":1001,"type":"g"}]” 
value2 Integer 筛选值
“” 
dateRangeId Integer 日期筛选需要用到的枚举值 16 (注意:枚举值见-》6、使用filterList注意事项:日期筛选case和枚举项)

2.4.4 函数举例

==通过分页信息page、viewId、场景Id、筛选器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("分页信息:"+data["page"])
  log.info("表头信息:"+data["headerFields"])
  log.info("数据集:"+data["dataSet"])
  log.info("列分组:"+data["colGroupFields"])
  log.info("行分组:"+data["rowGroupFields"])
  log.info("统计列:"+data["statFields"])
  log.info("总计数据集:"+data["summaryDataSet"])
  log.info("总计表头信息:"+data["summaryDisplayFields"])
}else{
  log.info("查询失败:"+errorMsg)
}

2.4.5 返回值说明

参数 类型 说明
code Int 函数状态码:200:成功403:没有权限500:系统异常
message String 返回消息
page Object 分页信息
headerFields Object 表头信息
dataSet Object 结果数据集
colGroupFields Object 列分组
rowGroupFields Object 行分组
statFields Object 统计列
summaryDataSet Object 总计数据集
summaryDisplayFields Object 总计表头信息

2.4.6 返回值示例

图表数据返回的结构统一为二维表,成对映射

{
    "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": "部门",
                    "fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
                    "formattedValue": null,
                    "fieldName": "部门",
                    "columnName": "object_oz2i0__c_field_6ejpc__c$0",
                    "formatStr": ""
                },
                {
                    "value": "1001",
                    "fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
                    "formattedValue": "销售部门",
                    "fieldName": "部门",
                    "columnName": null,
                    "formatStr": null
                },
                {
                    "value": "总计",
                    "fieldID": "BI_1416823ffb7298cc11838cedb4b59fdf",
                    "formattedValue": "总计",
                    "fieldName": "部门",
                    "columnName": null,
                    "formatStr": null
                }
            ],
            [
                {
                    "value": "客户名称",
                    "fieldID": "BI_3f378638510641067008767e02cf6563",
                    "formattedValue": null,
                    "fieldName": "客户名称",
                    "columnName": "object_oz2i0__c_field_21fnw__c$0",
                    "formatStr": ""
                },
                {
                    "value": "金额",
                    "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                    "formattedValue": null,
                    "fieldName": "金额",
                    "columnName": "object_oz2i0__c_field_6rfqc__c$0",
                    "formatStr": "#,##0.000"
                },
                {
                    "value": "金额",
                    "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                    "formattedValue": null,
                    "fieldName": "金额",
                    "columnName": "object_oz2i0__c_field_6rfqc__c$0",
                    "formatStr": "#,##0.000"
                }
            ]
        ],
        "colGroupFields": [
            {
                "fieldId": "BI_3f378638510641067008767e02cf6563",
                "fieldName": "客户名称",
                "formatStr": "",
                "columnName": "object_oz2i0__c_field_21fnw__c$0"
            }
        ],
        "rowGroupFields": [
            {
                "fieldId": "BI_1416823ffb7298cc11838cedb4b59fdf",
                "fieldName": "部门",
                "formatStr": "",
                "columnName": "object_oz2i0__c_field_6ejpc__c$0"
            }
        ],
        "statFields": [
            {
                "columnName": null,
                "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                "fieldName": "金额",
                "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": "求和"
                }
            ]
        },
        "summaryDisplayFields": [
            {
                "columnName": null,
                "fieldID": "BI_0f46eb539465b5746c1798ad9789c83a",
                "fieldName": "金额",
                "crmFieldName": "field_6rfqC__c",
                "crmObjName": "object_oz2i0__c",
                "dbFieldName": "field_6rfqC__c",
                "dbObjName": "object_oz2i0__c"
            }
        ]
    }
}
2026-09-18
0 0