在使用BI分析时，如果想要对结果数据做进一步的分析，可以通过接函数获取结果数据。目前主要支持了报表、单维度统计图、报表交叉表、拼表获取结果数据。具体的使用场景和使用方法见如下说明：
一、报表统计图函数使用说明
- 默认加载图表首页20条数据
Fx.BI.loadViewData(<string></string>) - 加载第i页图表数据
Fx.BI.pageLoadViewData(<String viewId>,<Integer pageNumber>,<Integer pageSize>) - 条件查询图表数据（默认返回首页20条）
Fx.BI.queryViewData(<String viewId>,<String defaultFilterOptionID>,<List filters>) - 条件查询第i页数据集
Fx.BI.pageQueryViewData(<String viewId>,<Integer pageNumber>,<Integer pageSize>,<String defaultFilterOptionID>,<List filters>) - 获取图表的数据范围以及场景
Fx.BI.getFiltersResult(<String viewId>) - 使用filterList注意事项：日期筛选case和枚举项
二、交叉表函数使用说明
- 通过交叉表Id查询交叉表（默认加载交叉表首页20条数据）
Fx.BI.queryPivotViewDataById(<String viewId>) - 通过viewId和分页信息page查询交叉表
Fx.BI.queryPivotViewDataByPage(<String viewId>,<Integer pageNumber>,<Integer pageSize>) - 通过viewId，filterList和defaultFilterOptionId查询交叉表数据
Fx.BI.queryPivotViewDataByFilter(<String viewId>,<String defaultFilterOptionId >,<List filterList>) - 通过viewId，分页信息page，filterList和defaultFilterOptionId、查询交叉表数据
Fx.BI.queryPivotReportData(<String viewId>,<Integer pageNumber>,<Integer pageSize>,<String defaultFilterOptionId >,<List filterList>)
三、拼表函数使用说明
- 通过图表Id查询拼表（默认加载拼表首页20条数据）
Fx.BI.queryLwtViewDataById(<String viewId>) - 通过viewId和分页信息page查询拼表
Fx.BI.queryLwtViewDataByPage(<String viewId>,<Integer pageNumber>,<Integer pageSize>) - 通过viewId，filterList查询拼表数据
Fx.BI.queryLwtViewDataByFilter(<String viewId>,<List filterList>) - 通过viewId，分页信息page，filterList查询拼表数据
Fx.BI.queryLwtViewData(<String viewId>,<Integer pageNumber>,<Integer pageSize>，<List filterList>)
一、报表统计图函数使用说明
1.1 默认加载图表首页20条数据 Fx.BI.loadViewData(<String viewId>)
1.1.1 函数用途
通过viewId查询图表数据
1.1.2 参数说明
| 参数 | 类型 | 是否必须 | 说明 | 参数示例 |
|---|---|---|---|---|
| viewId | String | 是 | 图表ID | “BI_62174baf180c0b000183a7a6” |
1.1.3 返回值说明
| 参数 | 类型 | 说明 |
|---|---|---|
| code | Int | 函数状态码：200：成功403：没有权限500：系统异常 |
| message | String | 返回消息 |
| page | Object | 分页信息 |
| dispalyFields | Object | 表头信息 |
| dataSet | Object | 结果数据集 |
1.1.4 返回值示例
图表数据返回的结构统一为二维表，表头和数据集分开返回，成对映射
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"page": {
"pageSize": 20,
"pageNumber": 1,
"pageCount": 1,
"totalCount": 3
},
"displayFields": [
{
"fieldId": "BI_b76b2e080ba679a4d609be4149fa0cf4",
"fieldName": "客户名称",
"formatStr": null,
"apiName": "name",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_name$0"
},
{
"fieldId": "BI_0e3221e80e82809cb750316a81fb8bec",
"fieldName": "创建人",
"formatStr": "",
"apiName": "created_by",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_created_by$0"
},
{
"fieldId": "BI_bacc9e515603edf1e4d765fe3970a043",
"fieldName": "客户编号",
"formatStr": null,
"apiName": "account_no",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_account_no$0"
},
{
"fieldId": "BI_39346e9a9deba83f255db6867dea0f4e",
"fieldName": "创建时间",
"formatStr": "yyyy-MM-dd HH:mm",
"apiName": "create_time",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_create_time$0"
},
{
"fieldId": "BI_92f9670f6a4ed14d17cebabf8501c4f9",
"fieldName": "最后修改人",
"formatStr": "",
"apiName": "last_modified_by",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_last_modified_by$0"
},
{
"fieldId": "BI_86142383c45f86fa2be8c6884dce6eeb",
"fieldName": "客户层级",
"formatStr": "",
"apiName": "account_path",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_account_path$0"
},
{
"fieldId": "BI_2fa0b164273097ce020c607c71b28dc8",
"fieldName": "客户行业",
"formatStr": "",
"apiName": "field_IGh6L__c",
"objectDescribeApiName": "AccountObj",
"columnName": "customer_udef_field_igh6l__c$0"
},
{
"fieldId": "BI_49ccf044b0b6b9b720da8006f37b8474",
"fieldName": "到账日期",
"formatStr": "yyyy-MM-dd",
"apiName": "field_rewsS__c",
"objectDescribeApiName": "AccountObj",
"columnName": "customer_udef_field_rewss__c$0"
},
{
"fieldId": "BI_4865460605f3905d910d1bffa401a426",
"fieldName": "value0",
"formatStr": "",
"apiName": "value0",
"objectDescribeApiName": "AccountObj",
"columnName": "customer_udef_value0$0"
}
],
"dataSet": [
[
{
"value": "jjjj",
"formattedValue": "jjjj",
"formattedShowValue": "jjjj"
},
{
"value": "1067",
"formattedValue": "bi",
"formattedShowValue": "1067"
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "1.64610619939E12",
"formattedValue": "2022-03-01 11:43",
"formattedShowValue": "1.64610619939E12"
},
{
"value": "1067",
"formattedValue": "bi",
"formattedShowValue": "1067"
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "1646841600000",
"formattedValue": "2022-03-10",
"formattedShowValue": "1646841600000"
},
{
"value": "621d9656a5db41000156aae0",
"formattedValue": "621d9656a5db41000156aae0",
"formattedShowValue": "621d9656a5db41000156aae0"
}
],
[
{
"value": "测试客户",
"formattedValue": "测试客户",
"formattedShowValue": "测试客户"
},
{
"value": "1067",
"formattedValue": "bi",
"formattedShowValue": "1067"
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "1.646135527393E12",
"formattedValue": "2022-03-01 19:52",
"formattedShowValue": "1.646135527393E12"
},
{
"value": "1067",
"formattedValue": "bi",
"formattedShowValue": "1067"
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "1646496000000",
"formattedValue": "2022-03-06",
"formattedShowValue": "1646496000000"
},
{
"value": "621e08e6a5db4100016247ec",
"formattedValue": "621e08e6a5db4100016247ec",
"formattedShowValue": "621e08e6a5db4100016247ec"
}
],
[
{
"value": "费用报销用户1",
"formattedValue": "费用报销用户1",
"formattedShowValue": "费用报销用户1"
},
{
"value": "1067",
"formattedValue": "bi",
"formattedShowValue": "1067"
},
{
"value": "1",
"formattedValue": "1",
"formattedShowValue": "1"
},
{
"value": "1.645172412386E12",
"formattedValue": "2022-02-18 16:20",
"formattedShowValue": "1.645172412386E12"
},
{
"value": "1067",
"formattedValue": "bi",
"formattedShowValue": "1067"
},
{
"value": "620f56eacb42760001e9d6c1.620f56bccb42760001e9d174",
"formattedValue": "费用报销客户上级客户/费用报销用户1",
"formattedShowValue": "620f56eacb42760001e9d6c1.620f56bccb42760001e9d174"
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "1645113600000",
"formattedValue": "2022-02-18",
"formattedShowValue": "1645113600000"
},
{
"value": "620f56bccb42760001e9d174",
"formattedValue": "620f56bccb42760001e9d174",
"formattedShowValue": "620f56bccb42760001e9d174"
}
]
]
}
}
1.1.5 报表表头信息以及报表数据信息展示
报表头各字段说明表
| 字段 | 字段含义 | 返回样例 |
|---|---|---|
| objectDescribeApiName | pass对象描述 | "objectDescribeApiName": "AccountObj" |
| formatStr | 格式化信息 | "formatStr": null |
| fieldName | 字段展示名称 | "fieldName": "客户名称" |
| fieldID | 字段主键 | "fieldId": "BI_b76b2e080ba679a4d609be4149fa0cf4" |
| columName | 唯一列名 | "columnName": "biz_account_name$0" |
| apiName | pass字段描述 | "apiName": "name" |
报表数据详细信息字段说明表
| 字段 | 字段含义 | 返回样例 |
|---|---|---|
| value | 字段值 | "value": "jjjj" |
| formattedValue | 字段展示名称 | "formattedValue": "jjjj" |
| formattedShowValue | 悬浮值 | "formattedShowValue": "jjjj" |
1.1.6 函数DEMO1
/*********************加载图表数据**************************/
+++++++++++++++++++++++++++++++++++++++++++++++++Fx.BI.getFiltersResult(<String viewId>)方法+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
===================================================================================================================================================================
def(isError4,data4,errorMsg4) = Fx.BI.loadViewData("BI_62174baf180c0b000183a7a6")
if(!isError4){
def page = data4["page"]
log.info("第1页数据集："+data4["dataSet"])
====================================================================================================================================================================
+++++++++++如果要查询所有数据集，要联合 Fx.BI.pageLoadViewData(<String viewId>,<Integer pageNumber>,<Integer pageSize>)方法，结合这两部分的代码+++++++++++++++++++++
====================================================================================================================================================================
if(page!=null){
def pageCount = page["pageCount"] as Integer
def pageSize = page["pageSize"] as Integer
if(pageCount>1){
Range range = Ranges.of(2,pageCount.intValue())
range.each { i ->
def (isErrorTmp,dataTmp,errorMsgTmp) = Fx.BI.pageLoadViewData("BI_62174baf180c0b000183a7a6",i,pageSize)
log.info("第"+i+"页数据集:"+dataTmp["dataSet"])
/*拿到DataSet就可以业务自己后续处理了*/
}
}
}
}
1.2 加载第i页图表数据 Fx.BI.pageLoadViewData(<String viewId>,<Integer pageNumber>,<Integer pageSize>)
1.2.1 函数用途
查询第i页图表数据
注意：此函数可以在已知有多少页的情况下进行查询每页的数据集，通常可以配合方法Fx.BI.loadViewData(<String viewId>)进行查找所有页数据集，详细见代码DEMO1
1.2.2 参数说明
| 参数 | 类型 | 是否必须 | 说明 | 参数示例 |
|---|---|---|---|---|
| viewId | String | 是 | 图表ID | “BI_62174baf180c0b000183a7a6” |
| pageNumber | Integer | 是 | 页码 | 1 |
| pageSize | Integer | 是 | 单页记录数 | 20 |
1.2.3 返回值说明
| 参数 | 类型 | 说明 |
|---|---|---|
| 参数 | 类型 | 说明 |
| code | Int | 函数状态码：200：成功403：没有权限500：系统异常 |
| message | String | 返回消息 |
| page | Object | 分页信息 |
| dispalyFields | Object | 表头信息 |
| dataSet | Object | 结果数据集 |
1.2.4 返回值示例
图表数据返回的结构统一为二维表，表头和数据集分开返回，成对映射
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"page": {
"pageSize": 20,
"pageNumber": 1,
"pageCount": 1,
"totalCount": 3
},
"displayFields": [
{
"fieldId": "BI_b76b2e080ba679a4d609be4149fa0cf4",
"fieldName": "客户名称",
"formatStr": null,
"apiName": "name",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_name$0"
},
{
"fieldId": "BI_0e3221e80e82809cb750316a81fb8bec",
"fieldName": "创建人",
"formatStr": "",
"apiName": "created_by",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_created_by$0"
},
{
"fieldId": "BI_bacc9e515603edf1e4d765fe3970a043",
"fieldName": "客户编号",
"formatStr": null,
"apiName": "account_no",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_account_no$0"
},
{
"fieldId": "BI_39346e9a9deba83f255db6867dea0f4e",
"fieldName": "创建时间",
"formatStr": "yyyy-MM-dd HH:mm",
"apiName": "create_time",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_create_time$0"
},
{
"fieldId": "BI_92f9670f6a4ed14d17cebabf8501c4f9",
"fieldName": "最后修改人",
"formatStr": "",
"apiName": "last_modified_by",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_last_modified_by$0"
},
{
"fieldId": "BI_86142383c45f86fa2be8c6884dce6eeb",
"fieldName": "客户层级",
"formatStr": "",
"apiName": "account_path",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_account_path$0"
},
{
"fieldId": "BI_2fa0b164273097ce020c607c71b28dc8",
"fieldName": "客户行业",
"formatStr": "",
"apiName": "field_IGh6L__c",
"objectDescribeApiName": "AccountObj",
"columnName": "customer_udef_field_igh6l__c$0"
},
{
"fieldId": "BI_49ccf044b0b6b9b720da8006f37b8474",
"fieldName": "到账日期",
"formatStr": "yyyy-MM-dd",
"apiName": "field_rewsS__c",
"objectDescribeApiName": "AccountObj",
"columnName": "customer_udef_field_rewss__c$0"
},
{
"fieldId": "BI_4865460605f3905d910d1bffa401a426",
"fieldName": "value0",
"formatStr": "",
"apiName": "value0",
"objectDescribeApiName": "AccountObj",
"columnName": "customer_udef_value0$0"
}
],
"dataSet": [
[
{
"value": "jjjj",
"formattedValue": "jjjj",
"formattedShowValue": "jjjj"
},
{
"value": "1067",
"formattedValue": "bi",
"formattedShowValue": "1067"
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "1.64610619939E12",
"formattedValue": "2022-03-01 11:43",
"formattedShowValue": "1.64610619939E12"
},
{
"value": "1067",
"formattedValue": "bi",
"formattedShowValue": "1067"
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "1646841600000",
"formattedValue": "2022-03-10",
"formattedShowValue": "1646841600000"
},
{
"value": "621d9656a5db41000156aae0",
"formattedValue": "621d9656a5db41000156aae0",
"formattedShowValue": "621d9656a5db41000156aae0"
}
],
[
{
"value": "测试客户",
"formattedValue": "测试客户",
"formattedShowValue": "测试客户"
},
{
"value": "1067",
"formattedValue": "bi",
"formattedShowValue": "1067"
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "1.646135527393E12",
"formattedValue": "2022-03-01 19:52",
"formattedShowValue": "1.646135527393E12"
},
{
"value": "1067",
"formattedValue": "bi",
"formattedShowValue": "1067"
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "1646496000000",
"formattedValue": "2022-03-06",
"formattedShowValue": "1646496000000"
},
{
"value": "621e08e6a5db4100016247ec",
"formattedValue": "621e08e6a5db4100016247ec",
"formattedShowValue": "621e08e6a5db4100016247ec"
}
],
[
{
"value": "费用报销用户1",
"formattedValue": "费用报销用户1",
"formattedShowValue": "费用报销用户1"
},
{
"value": "1067",
"formattedValue": "bi",
"formattedShowValue": "1067"
},
{
"value": "1",
"formattedValue": "1",
"formattedShowValue": "1"
},
{
"value": "1.645172412386E12",
"formattedValue": "2022-02-18 16:20",
"formattedShowValue": "1.645172412386E12"
},
{
"value": "1067",
"formattedValue": "bi",
"formattedShowValue": "1067"
},
{
"value": "620f56eacb42760001e9d6c1.620f56bccb42760001e9d174",
"formattedValue": "费用报销客户上级客户/费用报销用户1",
"formattedShowValue": "620f56eacb42760001e9d6c1.620f56bccb42760001e9d174"
},
{
"value": "null",
"formattedValue": "--",
"formattedShowValue": null
},
{
"value": "1645113600000",
"formattedValue": "2022-02-18",
"formattedShowValue": "1645113600000"
},
{
"value": "620f56bccb42760001e9d174",
"formattedValue": "620f56bccb42760001e9d174",
"formattedShowValue": "620f56bccb42760001e9d174"
}
]
]
}
}
1.2.5 报表表头信息以及报表数据信息展示
报表头各字段说明表
| 字段 | 字段含义 | 返回样例 |
|---|---|---|
| fieldID | 字段主键 | "fieldId": "BI_b76b2e080ba679a4d609be4149fa0cf4" |
| fieldName | 字段展示名称 | "fieldName": "客户名称" |
| formatStr | 格式化信息 | "formatStr": null |
| apiName | pass字段描述 | "apiName": "name" |
| objectDescribeApiName | pass对象描述 | "objectDescribeApiName": "AccountObj" |
| columName | 唯一列名 | "columnName": "biz_account_name$0" |
报表数据详细信息字段说明表
| 字段 | 字段含义 | 返回样例 |
|---|---|---|
| value | 字段值 | "value": "jjjj" |
| formattedValue | 字段展示名称 | "formattedValue": "jjjj" |
| formattedShowValue | 悬浮值 | "formattedShowValue": "jjjj" |
1.2.6 函数DEMO2
/**查询第i页数据集**/
+++++++++++++++++++++Fx.BI.pageLoadViewData(<String viewId>,<Integer pageNumber>,<Integer pageSize>)方法++++++++++++++++++++++++++++++
======================================================================================================================================
def (isErrorTmp,dataTmp,errorMsgTmp) = Fx.BI.pageLoadViewData("BI_62174baf180c0b000183a7a6",i,pageSize)
log.info("第"+i+"页数据集:"+dataTmp["dataSet"])
/*拿到DataSet就可以业务自己后续处理了*/
1.3 条件查询图表数据（默认返回首页20条） Fx.BI.queryViewData(<String viewId>,<String defaultFilterOptionID>,<List filters>)
1.3.1 函数用途
注意：此函数只能查询一页的数据，如果要查询所有数据，需要联合这个函数Fx.BI.pageQueryViewData(<String viewId>,<Integer pageNum>,<Integer pageSize>,<String defaultFilterOptionID>,<List filters>)，看下方示例代码
1.3.2 参数说明
| 参数 | 类型 | 是否必须 | 说明 | 参数示例 |
|---|---|---|---|---|
| 参数 | 类型 | 是否必须 | 说明 | 参数示例 |
| viewId | String | 是 | 图表ID | “BI_62174baf180c0b000183a7a6” |
| defaultFilterOptionID | String | 是 | 场景ID | “BI_59a3e59333b39e09b44e9db1” |
| filters | List | 否 | 筛选条件（包括数据范围和场景筛选） | ["filterId":"BI_62174baf180c0b000183a7aa","dataRangeId":"6"] |
1.3.3 返回值说明
| 参数 | 类型 | 说明 |
|---|---|---|
| code | Int | 函数状态码：200：成功403：没有权限500：系统异常 |
| message | String | 返回消息 |
| page | Object | 分页信息 |
| dispalyFields | Object | 表头信息 |
| dataSet | Object | 结果数据集 |
1.3.4 返回值示例
图表数据返回的结构统一为二维表，表头和数据集分开返回，成对映射
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"page": {
"pageSize": 20,
"pageNumber": 20,
"pageCount": 1,
"totalCount": 2
},
"displayFields": [
{
"fieldId": "BI_b76b2e080ba679a4d609be4149fa0cf4",
"fieldName": "客户名称",
"apiName": "name",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_name$0"
},
{
"fieldId": "BI_bacc9e515603edf1e4d765fe3970a043",
"fieldName": "客户编号",
"apiName": "account_no",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_account_no$0"
}
],
"dataSet": [
[
{
"value": "620f56eacb42760001e9d6c1.620f56bccb42760001e9d174",
"formattedValue": "费用报销客户上级客户/费用报销用户1",
"formattedShowValue": "620f56eacb42760001e9d6c1.620f56bccb42760001e9d174"
},
{
"value": "1645113600000",
"formattedValue": "2022-02-18",
"formattedShowValue": "1645113600000"
}
]
]
}
}
报表头各字段说明表
| 字段 | 字段含义 | 返回样例 |
|---|---|---|
| fieldID | 字段主键 | "fieldId": "BI_b76b2e080ba679a4d609be4149fa0cf4" |
| fieldName | 字段展示名称 | "fieldName": "客户名称" |
| apiName | pass字段描述 | "apiName": "name" |
| objectDescribeApiName | pass对象描述 | "objectDescribeApiName": "AccountObj" |
| columName | 唯一列名 | "columnName": "biz_account_name$0" |
报表数据详细信息字段说明表
| 字段 | 字段含义 | 返回样例 |
|---|---|---|
| value | 字段值 | "value": "620f56eacb42760001e9d6c1.620f56bccb42760001e9d174", |
| formattedValue | 字段展示名称 | "formattedValue": "费用报销客户上级客户/费用报销用户1", |
| formattedShowValue | 悬浮值 | "formattedShowValue": "620f56eacb42760001e9d6c1.620f56bccb42760001e9d174" |
1.3.5 函数DEMO3
/**条件查询*/
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++Fx.BI.queryViewData(<String viewId>,<String optionId>,<List filters>)方法，按条件查询表中数据（只能查询一页数据）++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
==========================================================================================================================================================================================================================================
List filters = [["filterId":"BI_62174baf180c0b000183a7aa","dateRangeId":"6"]]
def(isError6,data6,errorMsg6) = Fx.BI.queryViewData("BI_62174baf180c0b000183a7a6","BI_59a3e59333b39e09b44e9db1",filters)
if(!isError6){
def page6 = data6["page"]
log.info("第1页数据集："+data6["dataSet"])
===========================================================================================================================================================================================================================================
++++++++++++++++++++++++++++++如果要查询所有数据联合下方的Fx.BI.pageQueryViewData(<String viewId>,<Integer pageNumber>,<Integer pageSize>,<String defaultFilterOptionID>,<List filters>)方法+++++++++++++++++++++++++++++++++++++++++++++++
===========================================================================================================================================================================================================================================
if(page6!=null){
def pageCount6 = page6["pageCount"] as Integer
def pageSize6 = page6["pageSize"] as Integer
if(pageCount6>1){
Range range6 = Ranges.of(2,pageCount6.intValue())
range6.each { i ->
def (isErrorTmp,dataTmp,errorMsgTmp) = Fx.BI.pageQueryViewData("BI_62174baf180c0b000183a7a6",i,pageSize6,"BI_59a3e59333b39e09b44e9db1",filters)
log.info("第"+i+"页数据集:"+dataTmp["dataSet"])
/*拿到DataSet就可以业务自己后续处理了*/
}
}
}
}else{
log.info("条件查询失败:"+errorMsg6)
}
1.4 条件查询第i页数据集 Fx.BI.pageQueryViewData(<String viewId>,<Integer pageNumber>,<Integer pageSize>,<String defaultFilterOptionID>,<List filters>)
1.4.1 函数用途
按指定条件和指定页数查询图表数据
注意：此函数可以在已知有多少页的情况下进行查询每页的数据集，通常可以配合方法Fx.BI.queryViewData(<String viewId>,<String defaultFilterOptionID>,<List filters>) 进行查找所有页数据集，详细见代码DEMO3
1.4.2 参数说明
| 参数 | 类型 | 是否必须 | 说明 |
|---|---|---|---|
| viewId | String | 是 | 图表ID |
| pageNumber | Integer | 是 | 页码 |
| pageSize | Integer | 是 | 单页记录数 |
| defaultFilterOptionID | String | 是 | 权限场景 |
| filters | List | 是 | 筛选条件 |
1.4.3 返回值说明
| 参数 | 类型 | 说明 |
|---|---|---|
| code | Int | 函数状态码：200：成功403：没有权限500：系统异常 |
| message | String | 返回消息 |
| page | Object | 分页信息 |
| dispalyFields | Object | 表头信息 |
| dataSet | Object | 结果数据集 |
1.4.4 返回值示例
图表数据返回的结构统一为二维表，表头和数据集分开返回，成对映射
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"page": {
"pageSize": 20,
"pageNumber": 20,
"pageCount": 1,
"totalCount": 2
},
"displayFields": [
{
"fieldId": "BI_b76b2e080ba679a4d609be4149fa0cf4",
"fieldName": "客户名称",
"apiName": "name",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_name$0"
},
{
"fieldId": "BI_bacc9e515603edf1e4d765fe3970a043",
"fieldName": "客户编号",
"apiName": "account_no",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_account_no$0"
}
],
"dataSet": [
[
{
"value": "620f56eacb42760001e9d6c1.620f56bccb42760001e9d174",
"formattedValue": "费用报销客户上级客户/费用报销用户1",
"formattedShowValue": "620f56eacb42760001e9d6c1.620f56bccb42760001e9d174"
},
{
"value": "1645113600000",
"formattedValue": "2022-02-18",
"formattedShowValue": "1645113600000"
}
]
]
}
}
报表头各字段说明表
| 字段 | 字段含义 | 返回样例 |
|---|---|---|
| 字段 | 字段含义 | 返回样例 |
| fieldID | 字段主键 | "fieldId": "BI_b76b2e080ba679a4d609be4149fa0cf4" |
| fieldName | 字段展示名称 | "fieldName": "客户名称" |
| apiName | pass字段描述 | "apiName": "name" |
| objectDescribeApiName | pass对象描述 | "objectDescribeApiName": "AccountObj" |
| columName | 唯一列名 | "columnName": "biz_account_name$0" |
报表数据详细信息字段说明表
| 字段 | 字段含义 | 返回样例 |
|---|---|---|
| value | 字段值 | "value": "620f56eacb42760001e9d6c1.620f56bccb42760001e9d174", |
| formattedValue | 字段展示名称 | "formattedValue": "费用报销客户上级客户/费用报销用户1", |
| formattedShowValue | 悬浮值 | "formattedShowValue": "620f56eacb42760001e9d6c1.620f56bccb42760001e9d174" |
1.4.5 函数DEMO4
===========================================================================================================================================================================================================================================
+++++++++++++++++++++++++++++++++++++++++++++++++Fx.BI.pageQueryViewData(<String viewId>,<Integer pageNumber>,<Integer pageSize>,<String defaultFilterOptionID>,<List filters>)方法++++++++++++++++++++++++++++++++++++++++++++++++++++++++
===========================================================================================================================================================================================================================================
/**条件查询第i页的图表数据集*/
def (isErrorTmp,dataTmp,errorMsgTmp) = Fx.BI.pageQueryViewData("BI_62174baf180c0b000183a7a6",i,pageSize6,"BI_59a3e59333b39e09b44e9db1",filters)
log.info("第"+i+"页数据集:"+dataTmp["dataSet"])
/*拿到DataSet就可以业务自己后续处理了*/
1.5 获取图表的数据范围以及场景 Fx.BI.getFiltersResult(<String viewId>)
1.5.1 函数用途
按指定条件查询图表的数据范围以及场景
1.5.2 参数说明
| 参数 | 类型 | 是否必须 | 说明 |
|---|---|---|---|
| viewId | String | 是 | 图表ID |
1.5.3 返回值说明
| 参数 | 类型 | 说明 |
|---|---|---|
| code | Int | 函数状态码：200：成功403：没有权限404：未找到500：系统异常 |
| message | String | 返回消息 |
| filterGroups | Object | 数据范围信息 |
| labelAndOptions | Object | 场景信息 |
1.5.4 返回值示例
图表数据返回的结构统一为二维表，表头和数据集分开返回，成对映射
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"filterGroups": [
{
"filters": [
{
"filterId": "BI_62174baf180c0b000183a7a9",
"value1": "[{\"id\":999999,\"type\":\"c\"}]",
"value2": "",
"dateRangeId": null
},
{
"filterId": "BI_62174baf180c0b000183a7aa",
"value1": "2022-01-01",
"value2": "2022-12-31",
"dateRangeId": null
},
{
"filterId": "BI_62174baf180c0b000183a7ab",
"value1": "2022-01-01 00:00",
"value2": "2022-12-31 23:59",
"dateRangeId": null
},
{
"filterId": "BI_62174baf180c0b000183a7ac",
"value1": "[{\"id\":999999,\"type\":\"c\"}]",
"value2": "",
"dateRangeId": null
}
]
}
],
"labelAndOptions": [
{
"defaultFilterOptionID": null,
"defaultFilterOptionType": "UDF",
"label": "场景",
"defaultFilterOptions": [
{
"optionName": "全部",
"isDefault": 1,
"optionID": "BI_59a3e59333b39e09b44e9db1"
},
{
"optionName": "我负责的",
"isDefault": 0,
"optionID": "BI_59a3e59333b39e09b44e9db2"
},
{
"optionName": "我联合跟进的",
"isDefault": 0,
"optionID": "BI_171714538838884352"
},
{
"optionName": "我服务的",
"isDefault": 0,
"optionID": "BI_171714538872438784"
},
{
"optionName": "我参与的",
"isDefault": 0,
"optionID": "BI_59a3e59333b39e09b44e9db3"
},
{
"optionName": "我负责部门的",
"isDefault": 0,
"optionID": "BI_59a3e59333b39e09b44e9db4"
},
{
"optionName": "我下属负责的",
"isDefault": 0,
"optionID": "BI_59a3e59333b39e09b44e9db5"
},
{
"optionName": "我下属联合跟进的",
"isDefault": 0,
"optionID": "BI_171714538939547648"
},
{
"optionName": "我下属服务的",
"isDefault": 0,
"optionID": "BI_171714538973102080"
},
{
"optionName": "我下属参与的",
"isDefault": 0,
"optionID": "BI_59a3e59333b39e09b44e9db6"
},
{
"optionName": "共享给我的",
"isDefault": 0,
"optionID": "BI_59a3e59333b39e09b44e9db7"
}
]
}
]
}
}
1.5.5 返回值代码与示例图对照展示
| 字段 | 字段含义 | 返回样例 |
|---|---|---|
| dataRangeId | 日期筛选需要 | "dateRangeId": null |
| filterId | 筛选器主键 | "filterId": "BI_62174baf180c0b000183a7a9", |
| value1 | 默认筛选范围 | "value1": "[{\"id\":999999,\"type\":\"c\"}]","value1": "[{\"id\":999999,\"type\":\"c\"}]", |
| value2 | 默认筛选范围 | "value2": "", |
场景信息（labelAndOptions）
| 字段 | 字段含义 | 返回样例 |
|---|---|---|
| optionName | 场景选项名称 | "optionName": "全部" |
| isDefault | 是否默认（1：默认 ） | "isDefault": 1 |
| optionID | 场景选项ID | "optionID": "BI_59a3e59333b39e09b44e9db1" |
1.5.6 函数DEMO5
/*加载图表筛选器*/
++++++++++++++++++++++++++++++++++++++++++++++ Fx.BI.getFiltersResult(<String viewId>)方法++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
=====================================================================================================================================================
def(isError5,data5,errorMsg5) = Fx.BI.getFiltersResult("BI_62174baf180c0b000183a7a6")
if(!isError5){
log.info("筛选器信息:"+data5["filterGroups"])
log.info("场景信息:"+data5["labelAndOptions"])
/*可根据FilterId修改筛选器值进行查询，值的结构可参考报表运行态传参结构*/
}
1.6 使用filterList注意事项：日期筛选case和枚举项
注意：如果筛选范围为日期筛选的话，需要在filterList添加一个条件，如果日期筛选不加这个条件是会失败的
例如：”dataRangeId“：6，代表本年度，需要的枚举值和介绍见下图
二、交叉表函数使用说明
附：交叉表结构解析
♦列分组—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"
}
]
}
}
三、拼表函数使用说明
附：拼表结构解析
♦表头—displayFields
♦数据集—dataSet
3.1 通过图表Id查询拼表（默认加载拼表首页20条数据） Fx.BI.queryLwtViewDataById(<String viewId>)
3.1.1 函数用途
通过viewId查询拼表数据
3.1.2 参数说明
| 参数 | 类型 | 是否必须 | 说明 | 参数示例 |
|---|---|---|---|---|
| viewId | String | 是 | 拼表ID | “BI_lwt_1657506871248” |
3.1.3 函数举例
def(isError,data,errorMsg) = Fx.BI.queryLwtViewDataById("BI_lwt_1657506871248")
if(!isError){
log.info("分页信息:"+data["page"])
log.info("表头信息:"+data["displayFields"])
log.info("数据集:"+data["dataSet"])
}else{
log.info("查询失败:"+errorMsg)
}
3.1.4 返回值说明
| 参数 | 类型 | 说明 |
|---|---|---|
| code | Int | 函数状态码：200：成功403：没有权限500：系统异常 |
| message | String | 返回消息 |
| page | Object | 分页信息 |
| displayFields | Object | 表头信息 |
| dataSet | Object | 结果数据集 |
3.1.5 返回值示例
图表数据返回的结构统一为二维表，成对映射
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"displayFields": [
[
{
"fieldId": "BI_lwt_calc_6ad70579d41f4901aee273eb7e9aeb0b",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_b421df3270824345b6c5dc13f2fea14e",
"fieldName": "年",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_f289f266427f4cedba1a746c6b058298",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_bba3a46afcf14f158855fa733de92205",
"fieldName": "季",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_83cb73bcc82f4f2897e79799d0ff6d9f",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_a010a7cbf99646ae89eef3e6deb64557",
"fieldName": "月",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_7e11d20cdabb47fb9ed0fe6291ee7e2b",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
}
],
[
{
"fieldId": "BI_5be1388756fc11448c4567dc",
"fieldName": "产品线",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "MultiSelectEnum",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "product_line",
"linkObjName": "ProductObj"
},
{
"fieldId": "BI_5be3b0ff2563710001ab1b62",
"fieldName": "销售数量(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "quantity",
"linkObjName": ""
},
{
"fieldId": "BI_6178cf99ea585200014b0ef9",
"fieldName": "订单总金额(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "subtotal",
"linkObjName": ""
},
{
"fieldId": "BI_5be3b0ff2563710001ab1b62",
"fieldName": "销售数量(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "quantity",
"linkObjName": ""
},
{
"fieldId": "BI_6178cf99ea585200014b0ef9",
"fieldName": "订单总金额(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "subtotal",
"linkObjName": ""
},
{
"fieldId": "BI_5be3b0ff2563710001ab1b62",
"fieldName": "销售数量(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "quantity",
"linkObjName": ""
},
{
"fieldId": "BI_6178cf99ea585200014b0ef9",
"fieldName": "订单总金额(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "subtotal",
"linkObjName": ""
}
]
],
"dataSet": [
[
{
"value": "产品线1",
"displayValue": "产品线1",
"dataFormatStr": ""
},
{
"value": "2625.0",
"displayValue": "2625",
"dataFormatStr": "0.00"
},
{
"value": "26250.0",
"displayValue": "2.63万",
"dataFormatStr": "0.00"
},
{
"value": "66934.0",
"displayValue": "66934",
"dataFormatStr": "0.00"
},
{
"value": "669340.0",
"displayValue": "66.93万",
"dataFormatStr": "0.00"
},
{
"value": "0.0",
"displayValue": "0",
"dataFormatStr": "0.00"
},
{
"value": "0.0",
"displayValue": "0.00",
"dataFormatStr": "0.00"
}
],
[
{
"value": "--",
"displayValue": "--",
"dataFormatStr": ""
},
{
"value": "23464.0",
"displayValue": "23464",
"dataFormatStr": "0.00"
},
{
"value": "234640.0",
"displayValue": "23.46万",
"dataFormatStr": "0.00"
},
{
"value": "143423.0",
"displayValue": "143423",
"dataFormatStr": "0.00"
},
{
"value": "1434230.0",
"displayValue": "143.42万",
"dataFormatStr": "0.00"
},
{
"value": "18389.0",
"displayValue": "18389",
"dataFormatStr": "0.00"
},
{
"value": "183890.0",
"displayValue": "18.39万",
"dataFormatStr": "0.00"
}
],
[
{
"value": "--",
"displayValue": "--",
"dataFormatStr": ""
},
{
"value": "23464.0",
"displayValue": "23464",
"dataFormatStr": "0.00"
},
{
"value": "234640.0",
"displayValue": "23.46万",
"dataFormatStr": "0.00"
},
{
"value": "143423.0",
"displayValue": "143423",
"dataFormatStr": "0.00"
},
{
"value": "1434230.0",
"displayValue": "143.42万",
"dataFormatStr": "0.00"
},
{
"value": "240947.0",
"displayValue": "240947",
"dataFormatStr": "0.00"
},
{
"value": "2409470.0",
"displayValue": "240.95万",
"dataFormatStr": "0.00"
}
],
[
{
"value": "--",
"displayValue": "--",
"dataFormatStr": ""
},
{
"value": "23464.0",
"displayValue": "23464",
"dataFormatStr": "0.00"
},
{
"value": "234640.0",
"displayValue": "23.46万",
"dataFormatStr": "0.00"
},
{
"value": "547561.0",
"displayValue": "547561",
"dataFormatStr": "0.00"
},
{
"value": "5475610.0",
"displayValue": "547.56万",
"dataFormatStr": "0.00"
},
{
"value": "18389.0",
"displayValue": "18389",
"dataFormatStr": "0.00"
},
{
"value": "183890.0",
"displayValue": "18.39万",
"dataFormatStr": "0.00"
}
],
[
{
"value": "--",
"displayValue": "--",
"dataFormatStr": ""
},
{
"value": "23464.0",
"displayValue": "23464",
"dataFormatStr": "0.00"
},
{
"value": "234640.0",
"displayValue": "23.46万",
"dataFormatStr": "0.00"
},
{
"value": "547561.0",
"displayValue": "547561",
"dataFormatStr": "0.00"
},
{
"value": "5475610.0",
"displayValue": "547.56万",
"dataFormatStr": "0.00"
},
{
"value": "240947.0",
"displayValue": "240947",
"dataFormatStr": "0.00"
},
{
"value": "2409470.0",
"displayValue": "240.95万",
"dataFormatStr": "0.00"
}
]
],
"page": {
"pageSize": 20,
"pageNumber": 1,
"pageCount": 1,
"totalCount": 5
}
}
}
3.2 通过viewId和分页信息page查询拼表 Fx.BI.queryLwtViewDataByPage(<String viewId>,<Integer pageNumber>,<Integer pageSize>)
3.2.1 函数用途
通过viewId和分页信息page查询拼表,用户可以自定义查询想要查询的页码，并规定每页条数
3.2.2 参数说明
| 参数 | 类型 | 是否必须 | 说明 | 参数示例 |
|---|---|---|---|---|
| viewId | String | 是 | 交叉表ID | “BI_lwt_1657506871248” |
| pageNumber | Integer | 是 | 页码 | 1 |
| pageSize | Integer | 是 | 每页规定条数 | 1 |
3.2.3 函数举例
==========================查询第一页，每页一条数据的交叉表数据===============================
=============================================================================================
def(isError,data,errorMsg) = Fx.BI.queryLwtViewDataByPage("BI_lwt_1657506871248",1,1)
if(!isError){
log.info("分页信息:"+data["page"])
log.info("表头信息:"+data["displayFields"])
log.info("数据集:"+data["dataSet"])
}else{
log.info("查询失败:"+errorMsg)
}
3.2.4 返回值说明
| 参数 | 类型 | 说明 |
|---|---|---|
| code | Int | 函数状态码：200：成功403：没有权限500：系统异常 |
| message | String | 返回消息 |
| page | Object | 分页信息 |
| displayFields | Object | 表头信息 |
| dataSet | Object | 结果数据集 |
3.2.5 返回值示例
图表数据返回的结构统一为二维表，成对映射
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"displayFields": [
[
{
"fieldId": "BI_lwt_calc_390f421f5d51471d889f27fe3bc1849b",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_c51e947793a4448e964e21a19855d637",
"fieldName": "年",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_ba0dcd60e4ca404b9f0c49278a776718",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_ccb483314eae4008a83cce58bf28b36f",
"fieldName": "季",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_a62e283869af4b29885c583f1ddf6934",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_e6f0d1ba2c3c428e9ab9818da6a00ae3",
"fieldName": "月",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_3d6e6fb55d924a61bd6ebec94bab1c84",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
}
],
[
{
"fieldId": "BI_5be1388756fc11448c4567dc",
"fieldName": "产品线",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "MultiSelectEnum",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "product_line",
"linkObjName": "ProductObj"
},
{
"fieldId": "BI_5be3b0ff2563710001ab1b62",
"fieldName": "销售数量(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "quantity",
"linkObjName": ""
},
{
"fieldId": "BI_6178cf99ea585200014b0ef9",
"fieldName": "订单总金额(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "subtotal",
"linkObjName": ""
},
{
"fieldId": "BI_5be3b0ff2563710001ab1b62",
"fieldName": "销售数量(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "quantity",
"linkObjName": ""
},
{
"fieldId": "BI_6178cf99ea585200014b0ef9",
"fieldName": "订单总金额(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "subtotal",
"linkObjName": ""
},
{
"fieldId": "BI_5be3b0ff2563710001ab1b62",
"fieldName": "销售数量(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "quantity",
"linkObjName": ""
},
{
"fieldId": "BI_6178cf99ea585200014b0ef9",
"fieldName": "订单总金额(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "subtotal",
"linkObjName": ""
}
]
],
"dataSet": [
[
{
"value": "产品线1",
"displayValue": "产品线1",
"dataFormatStr": ""
},
{
"value": "2625.0",
"displayValue": "2625",
"dataFormatStr": "0.00"
},
{
"value": "26250.0",
"displayValue": "2.63万",
"dataFormatStr": "0.00"
},
{
"value": "66934.0",
"displayValue": "66934",
"dataFormatStr": "0.00"
},
{
"value": "669340.0",
"displayValue": "66.93万",
"dataFormatStr": "0.00"
},
{
"value": "0.0",
"displayValue": "0",
"dataFormatStr": "0.00"
},
{
"value": "0.0",
"displayValue": "0.00",
"dataFormatStr": "0.00"
}
]
],
"page": {
"pageSize": 1,
"pageNumber": 1,
"pageCount": 5,
"totalCount": 5
}
}
}
3.3 通过viewId，filterList查询拼表数据 Fx.BI.queryLwtViewDataByFilter(<String viewId>,<List filterList>)
3.3.1 函数用途
通过viewId和筛选范围进行查询拼表
3.3.2 参数说明
| 参数 | 类型 | 是否必须 | 说明 | 参数示例 |
|---|---|---|---|---|
| viewId | String | 是 | 交叉表ID | “BI_62973467376efe000172be38” |
| filterList | Integer | 是 | 筛选条件 | List filterList = [["filterId":"BI_62cb8af6741ac20001debf72","value1":"","value2":"","dateRangeId":6]] |
| 参数 | 类型 | 是否必须 | 说明 | 参数示例 |
|---|---|---|---|---|
| filterId | String | 是 | 拼表ID | “BI_lwt_1657506871248” |
| value1 | String | 是 | 筛选值 | “” |
| value2 | Integer | 是 | 筛选值 | ”" |
| dateRangeId | Integer | 否 | 枚举值 | 6 （注意：枚举值见-》6、使用filterList注意事项：日期筛选case和枚举项） |
3.3.3 函数举例
==========================查询筛选的拼表数据===============================
=============================================================================================
List filterList = [["filterId":"BI_62cb8af6741ac20001debf72","value1":"","value2":"","dateRangeId":6]];
def(isError,data,errorMsg) = Fx.BI.queryLwtViewDataByFilter("BI_lwt_1657506871248",filterList)
if(!isError){
log.info("分页信息:"+data["page"])
log.info("表头信息:"+data["displayFields"])
log.info("数据集:"+data["dataSet"])
}else{
log.info("查询失败:"+errorMsg)
}
3.3.4 返回值说明
| 参数 | 类型 | 说明 |
|---|---|---|
| code | Int | 函数状态码：200：成功403：没有权限500：系统异常 |
| message | String | 返回消息 |
| page | Object | 分页信息 |
| displayFields | Object | 表头信息 |
| dataSet | Object | 结果数据集 |
3.3.5 返回值示例
图表数据返回的结构统一为二维表，成对映射
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"displayFields": [
[
{
"fieldId": "BI_lwt_calc_d9cad9232d464077b5eafcd13f60183f",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_228e9fbb9359411faa3f5ee2bb13649b",
"fieldName": "年",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_d2530cb668b64b06ae54a4232b0ccc87",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_399493e3de2c4d299c10d705f188eb49",
"fieldName": "季",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_f3b44d6bb3324183bf164596ea0dbd5c",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_40095e45357947b792fe855350dd84be",
"fieldName": "月",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_8f6ddee4bf9d49c99902f30b769801ea",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
}
],
[
{
"fieldId": "BI_5be1388756fc11448c4567dc",
"fieldName": "产品线",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "MultiSelectEnum",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "product_line",
"linkObjName": "ProductObj"
},
{
"fieldId": "BI_5be3b0ff2563710001ab1b62",
"fieldName": "销售数量(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "quantity",
"linkObjName": ""
},
{
"fieldId": "BI_6178cf99ea585200014b0ef9",
"fieldName": "订单总金额(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "subtotal",
"linkObjName": ""
},
{
"fieldId": "BI_5be3b0ff2563710001ab1b62",
"fieldName": "销售数量(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "quantity",
"linkObjName": ""
},
{
"fieldId": "BI_6178cf99ea585200014b0ef9",
"fieldName": "订单总金额(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "subtotal",
"linkObjName": ""
},
{
"fieldId": "BI_5be3b0ff2563710001ab1b62",
"fieldName": "销售数量(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "quantity",
"linkObjName": ""
},
{
"fieldId": "BI_6178cf99ea585200014b0ef9",
"fieldName": "订单总金额(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "subtotal",
"linkObjName": ""
}
]
],
"dataSet": [
[
{
"value": "产品线3",
"displayValue": "产品线3",
"dataFormatStr": ""
},
{
"value": "73976.0",
"displayValue": "73976",
"dataFormatStr": "0.00"
},
{
"value": "739760.0",
"displayValue": "73.98万",
"dataFormatStr": "0.00"
},
{
"value": "19194.0",
"displayValue": "19194",
"dataFormatStr": "0.00"
},
{
"value": "191940.0",
"displayValue": "19.19万",
"dataFormatStr": "0.00"
},
{
"value": "0.0",
"displayValue": "0",
"dataFormatStr": "0.00"
},
{
"value": "0.0",
"displayValue": "0.00",
"dataFormatStr": "0.00"
}
],
[
{
"value": "产品线2",
"displayValue": "产品线2",
"dataFormatStr": ""
},
{
"value": "199451.0",
"displayValue": "199451",
"dataFormatStr": "0.00"
},
{
"value": "1994510.0",
"displayValue": "199.45万",
"dataFormatStr": "0.00"
},
{
"value": "47691.0",
"displayValue": "47691",
"dataFormatStr": "0.00"
},
{
"value": "476910.0",
"displayValue": "47.69万",
"dataFormatStr": "0.00"
},
{
"value": "0.0",
"displayValue": "0",
"dataFormatStr": "0.00"
},
{
"value": "0.0",
"displayValue": "0.00",
"dataFormatStr": "0.00"
}
],
[
{
"value": "产品线1",
"displayValue": "产品线1",
"dataFormatStr": ""
},
{
"value": "269469.0",
"displayValue": "269469",
"dataFormatStr": "0.00"
},
{
"value": "6041360.0",
"displayValue": "604.14万",
"dataFormatStr": "0.00"
},
{
"value": "66934.0",
"displayValue": "66934",
"dataFormatStr": "0.00"
},
{
"value": "669340.0",
"displayValue": "66.93万",
"dataFormatStr": "0.00"
},
{
"value": "0.0",
"displayValue": "0",
"dataFormatStr": "0.00"
},
{
"value": "0.0",
"displayValue": "0.00",
"dataFormatStr": "0.00"
}
],
[
{
"value": "--",
"displayValue": "--",
"dataFormatStr": ""
},
{
"value": "557336.0",
"displayValue": "557336",
"dataFormatStr": "0.00"
},
{
"value": "5573415.0",
"displayValue": "557.34万",
"dataFormatStr": "0.00"
},
{
"value": "143423.0",
"displayValue": "143423",
"dataFormatStr": "0.00"
},
{
"value": "1434230.0",
"displayValue": "143.42万",
"dataFormatStr": "0.00"
},
{
"value": "18389.0",
"displayValue": "18389",
"dataFormatStr": "0.00"
},
{
"value": "183890.0",
"displayValue": "18.39万",
"dataFormatStr": "0.00"
}
],
[
{
"value": "--",
"displayValue": "--",
"dataFormatStr": ""
},
{
"value": "557336.0",
"displayValue": "557336",
"dataFormatStr": "0.00"
},
{
"value": "5573415.0",
"displayValue": "557.34万",
"dataFormatStr": "0.00"
},
{
"value": "143423.0",
"displayValue": "143423",
"dataFormatStr": "0.00"
},
{
"value": "1434230.0",
"displayValue": "143.42万",
"dataFormatStr": "0.00"
},
{
"value": "240947.0",
"displayValue": "240947",
"dataFormatStr": "0.00"
},
{
"value": "2409470.0",
"displayValue": "240.95万",
"dataFormatStr": "0.00"
}
],
[
{
"value": "--",
"displayValue": "--",
"dataFormatStr": ""
},
{
"value": "557336.0",
"displayValue": "557336",
"dataFormatStr": "0.00"
},
{
"value": "5573415.0",
"displayValue": "557.34万",
"dataFormatStr": "0.00"
},
{
"value": "547561.0",
"displayValue": "547561",
"dataFormatStr": "0.00"
},
{
"value": "5475610.0",
"displayValue": "547.56万",
"dataFormatStr": "0.00"
},
{
"value": "18389.0",
"displayValue": "18389",
"dataFormatStr": "0.00"
},
{
"value": "183890.0",
"displayValue": "18.39万",
"dataFormatStr": "0.00"
}
],
[
{
"value": "--",
"displayValue": "--",
"dataFormatStr": ""
},
{
"value": "557336.0",
"displayValue": "557336",
"dataFormatStr": "0.00"
},
{
"value": "5573415.0",
"displayValue": "557.34万",
"dataFormatStr": "0.00"
},
{
"value": "547561.0",
"displayValue": "547561",
"dataFormatStr": "0.00"
},
{
"value": "5475610.0",
"displayValue": "547.56万",
"dataFormatStr": "0.00"
},
{
"value": "240947.0",
"displayValue": "240947",
"dataFormatStr": "0.00"
},
{
"value": "2409470.0",
"displayValue": "240.95万",
"dataFormatStr": "0.00"
}
],
[
{
"value": "--",
"displayValue": "--",
"dataFormatStr": ""
},
{
"value": "2436277.0",
"displayValue": "2436277",
"dataFormatStr": "0.00"
},
{
"value": "24380370.0",
"displayValue": "2,438.04万",
"dataFormatStr": "0.00"
},
{
"value": "143423.0",
"displayValue": "143423",
"dataFormatStr": "0.00"
},
{
"value": "1434230.0",
"displayValue": "143.42万",
"dataFormatStr": "0.00"
},
{
"value": "18389.0",
"displayValue": "18389",
"dataFormatStr": "0.00"
},
{
"value": "183890.0",
"displayValue": "18.39万",
"dataFormatStr": "0.00"
}
],
[
{
"value": "--",
"displayValue": "--",
"dataFormatStr": ""
},
{
"value": "2436277.0",
"displayValue": "2436277",
"dataFormatStr": "0.00"
},
{
"value": "24380370.0",
"displayValue": "2,438.04万",
"dataFormatStr": "0.00"
},
{
"value": "143423.0",
"displayValue": "143423",
"dataFormatStr": "0.00"
},
{
"value": "1434230.0",
"displayValue": "143.42万",
"dataFormatStr": "0.00"
},
{
"value": "240947.0",
"displayValue": "240947",
"dataFormatStr": "0.00"
},
{
"value": "2409470.0",
"displayValue": "240.95万",
"dataFormatStr": "0.00"
}
],
[
{
"value": "--",
"displayValue": "--",
"dataFormatStr": ""
},
{
"value": "2436277.0",
"displayValue": "2436277",
"dataFormatStr": "0.00"
},
{
"value": "24380370.0",
"displayValue": "2,438.04万",
"dataFormatStr": "0.00"
},
{
"value": "547561.0",
"displayValue": "547561",
"dataFormatStr": "0.00"
},
{
"value": "5475610.0",
"displayValue": "547.56万",
"dataFormatStr": "0.00"
},
{
"value": "18389.0",
"displayValue": "18389",
"dataFormatStr": "0.00"
},
{
"value": "183890.0",
"displayValue": "18.39万",
"dataFormatStr": "0.00"
}
],
[
{
"value": "--",
"displayValue": "--",
"dataFormatStr": ""
},
{
"value": "2436277.0",
"displayValue": "2436277",
"dataFormatStr": "0.00"
},
{
"value": "24380370.0",
"displayValue": "2,438.04万",
"dataFormatStr": "0.00"
},
{
"value": "547561.0",
"displayValue": "547561",
"dataFormatStr": "0.00"
},
{
"value": "5475610.0",
"displayValue": "547.56万",
"dataFormatStr": "0.00"
},
{
"value": "240947.0",
"displayValue": "240947",
"dataFormatStr": "0.00"
},
{
"value": "2409470.0",
"displayValue": "240.95万",
"dataFormatStr": "0.00"
}
]
],
"page": {
"pageSize": 20,
"pageNumber": 1,
"pageCount": 1,
"totalCount": 11
}
}
}
3.4 通过viewId，分页信息page，filterList查询拼表数据 Fx.BI.queryLwtViewData(<String viewId>,<Integer pageNumber>,<Integer pageSize>，<List filterList>)
3.4.1 函数用途
通过viewId和筛选条件filterList、场景defaultFilterOptionId，查询交叉表
3.4.2 参数说明
| 参数 | 类型 | 是否必须 | 说明 | 参数示例 |
|---|---|---|---|---|
| viewId | String | 是 | 交叉表ID | “BI_lwt_1657506871248” |
| filterList | Integer | 是 | 筛选条件 | List filterList = [["filterId":"BI_62cb8af6741ac20001debf72","value1":"","value2":"","dateRangeId":6]] |
| pageNumber | Integer | 是 | 页码 | 1 |
| pageSize | Integer | 是 | 每页的条数 | 1 |
3.4.3 filterList结构说明
| 参数 | 类型 | 是否必须 | 说明 | 参数示例 |
|---|---|---|---|---|
| filterId | String | 是 | 拼表ID | “BI_62973467376efe000172be38” |
| value1 | String | 是 | 筛选范围 | “” |
| value2 | Integer | 是 | 筛选范围 | “” |
| dateRangeId | Integer | 否 | 日期筛选需要用到的枚举值 | 6 （注意：枚举值见-》6、使用filterList注意事项：日期筛选case和枚举项） |
3.4.4 函数举例
==========================通过分页信息page、viewId、筛选器filterList查询的拼表数据===============================
=================================================================================================================
List filterList = [["filterId":"BI_62cb8af6741ac20001debf72","value1":"","value2":"","dateRangeId":6]];
def(isError,data,errorMsg) = Fx.BI.queryLwtViewData("BI_lwt_1657506871248",1,1,filterList)
if(!isError){
log.info("分页信息:"+data["page"])
log.info("表头信息:"+data["displayFields"])
log.info("数据集:"+data["dataSet"])
}else{
log.info("查询失败:"+errorMsg)
3.4.5 返回值说明
| 参数 | 类型 | 说明 |
|---|---|---|
| code | Int | 函数状态码：200：成功403：没有权限500：系统异常 |
| message | String | 返回消息 |
| page | Object | 分页信息 |
| displayFields | Object | 表头信息 |
| dataSet | Object | 结果数据集 |
3.4.6 返回值示例
图表数据返回的结构统一为二维表，成对映射
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"displayFields": [
[
{
"fieldId": "BI_lwt_calc_ddc948d7b23d4a4c9da0f4d27b79d465",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_86273a3178fe4ceab71538462ee13962",
"fieldName": "年",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_35fc27cbe17444d6951fc4a3f82519b6",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_8c5755a7669a41f5ba57e89afc17b459",
"fieldName": "季",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_208110f605e94df2af1333c12c4a89bd",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_e5a5ac5d61924ce0a81699c9668c7b1b",
"fieldName": "月",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
},
{
"fieldId": "BI_lwt_calc_be4b6adce187454ebd35d2a3289eb55c",
"fieldName": "",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "String",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "",
"linkObjName": ""
}
],
[
{
"fieldId": "BI_5be1388756fc11448c4567dc",
"fieldName": "产品线",
"formatStr": "",
"objName": "",
"objectNodeName": null,
"fieldType": "MultiSelectEnum",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "product_line",
"linkObjName": "ProductObj"
},
{
"fieldId": "BI_5be3b0ff2563710001ab1b62",
"fieldName": "销售数量(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "quantity",
"linkObjName": ""
},
{
"fieldId": "BI_6178cf99ea585200014b0ef9",
"fieldName": "订单总金额(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "subtotal",
"linkObjName": ""
},
{
"fieldId": "BI_5be3b0ff2563710001ab1b62",
"fieldName": "销售数量(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "quantity",
"linkObjName": ""
},
{
"fieldId": "BI_6178cf99ea585200014b0ef9",
"fieldName": "订单总金额(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "subtotal",
"linkObjName": ""
},
{
"fieldId": "BI_5be3b0ff2563710001ab1b62",
"fieldName": "销售数量(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "quantity",
"linkObjName": ""
},
{
"fieldId": "BI_6178cf99ea585200014b0ef9",
"fieldName": "订单总金额(求和)",
"formatStr": "",
"objName": "订单产品",
"objectNodeName": null,
"fieldType": "Number",
"joinType": "outer",
"refObjName": "",
"dbFieldName": "subtotal",
"linkObjName": ""
}
]
],
"dataSet": [
[
{
"value": "产品线3",
"displayValue": "产品线3",
"dataFormatStr": ""
},
{
"value": "73976.0",
"displayValue": "73976",
"dataFormatStr": "0.00"
},
{
"value": "739760.0",
"displayValue": "73.98万",
"dataFormatStr": "0.00"
},
{
"value": "19194.0",
"displayValue": "19194",
"dataFormatStr": "0.00"
},
{
"value": "191940.0",
"displayValue": "19.19万",
"dataFormatStr": "0.00"
},
{
"value": "0.0",
"displayValue": "0",
"dataFormatStr": "0.00"
},
{
"value": "0.0",
"displayValue": "0.00",
"dataFormatStr": "0.00"
}
]
],
"page": {
"pageSize": 1,
"pageNumber": 1,
"pageCount": 11,
"totalCount": 11
}
}
}