二、交叉表函数使用说明
附:交叉表结构解析
♦列分组—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"
}
]
}
}