When using BI analysis, if you want to further analyze the result data, you can obtain the result data through the connection function. At present, it mainly supports reports, single-dimensional statistical charts, report crosstabs, and table spelling to obtain result data. For specific usage scenarios and usage methods, see the following instructions:
1. Instructions for the use of the report statistics chart function
1.1 By default, load 20 pieces of data on the first page of the chart Fx.BI.loadViewData(<String viewId>)
1.1.1 Function purpose
Query chart data by viewId
1.1.2 Parameter description
| parameter | type | required | description | parameter example |
| :----- | :----- | :------- | :----- | :--------------- ---------- |
| viewId | String | Yes | Chart ID | "BI_62174baf180c0b000183a7a6" |
1.1.3 Return value description
| parameter | type | description |
| :------------ | :----- | :------------------------ ------------------- |
| code | Int | Function status code: 200: success, 403: no permission, 500: system exception |
| message | String | return message |
| page | Object | Paging information |
| displayFields | Object | header information |
| dataSet | Object | result dataset |
1.1.4 Return value example
The structure returned by the chart data is unified into a two-dimensional table, the table header and data set are returned separately, and paired mapping
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"page": {
"pageSize": 20,
"pageNumber": 1,
"pageCount": 1,
"totalCount": 3
},
"displayFields": [
{
"fieldId": "BI_b76b2e080ba679a4d609be4149fa0cf4",
"fieldName": "Customer Name",
"formatStr": null,
"apiName": "name",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_name$0"
},
{
"fieldId": "BI_0e3221e80e82809cb750316a81fb8bec",
"fieldName": "Creator",
"formatStr": "",
"apiName": "created_by",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_created_by$0"
},
{
"fieldId": "BI_bacc9e515603edf1e4d765fe3970a043",
"fieldName": "Customer Number",
"formatStr": null,
"apiName": "account_no",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_account_no$0"
},
{
"fieldId": "BI_39346e9a9deba83f255db6867dea0f4e",
"fieldName": "Creation Time",
"formatStr": "yyyy-MM-dd HH:mm",
"apiName": "create_time",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_create_time$0"
},
{
"fieldId": "BI_92f9670f6a4ed14d17cebabf8501c4f9",
"fieldName": "Last Modifier",
"formatStr": "",
"apiName": "last_modified_by",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_last_modified_by$0"
},
{
"fieldId": "BI_86142383c45f86fa2be8c6884dce6eeb",
"fieldName": "Customer Level",
"formatStr": "",
"apiName": "account_path",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_account_path$0"
},
{
"fieldId": "BI_2fa0b164273097ce020c607c71b28dc8",
"fieldName": "Customer Industry",
"formatStr": "",
"apiName": "field_IGh6L__c",
"objectDescribeApiName": "AccountObj",
"columnName": "customer_udef_field_high6l__c$0"
},
{
"fieldId": "BI_49ccf044b0b6b9b720da8006f37b8474",
"fieldName": "Account Date",
"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": "Test Client",
"formattedValue": "Test Client",
"formattedShowValue": "Test Client"
},
{
"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": "Expense reimbursement user 1",
"formattedValue": "Expense reimbursement user 1",
"formattedShowValue": "Expense reimbursement user 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": "Expense reimbursement customer superior customer/expense reimbursement user 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 Report header information and report data information display
Explanation table for each field in the report header
| Field | Field Meaning | Return Sample |
| :-------------------- | :----------- | :------------- ------------------------------------- |
| objectDescribeApiName | pass object description | "objectDescribeApiName": "AccountObj" |
| formatStr | format information | "formatStr": null |
| fieldName | field display name | "fieldName": "customer name" |
| fieldID | Field primary key | "fieldId": "BI_b76b2e080ba679a4d609be4149fa0cf4" |
| columnName | unique column name | "columnName": "biz_account_name$0" |
| apiName | pass field description | "apiName": "name" |
Report data detailed information field description table
| Field | Field Meaning | Return Sample |
| :---------------- | :----------- | :---------------- ----------- |
| value | field value | "value": "jjjj" |
| formattedValue | field display name | "formattedValue": "jjjj" |
| formattedShowValue | floating value | "formattedShowValue": "jjjj" |
1.1.6 Function DEMO1
/********************Load chart data************************** */
++++++++++++++++++++++++++++++++++++++++++++++++++Fx .BI.getFiltersResult(<String viewId>) method++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++
==================================================== ==================================================== ==================================================== ==============
def(isError4,data4,errorMsg4) = Fx.BI.loadViewData("BI_62174baf180c0b000183a7a6")
if(!isError4){
def page = data4["page"]
log.info("Page 1 data set: "+data4["dataSet"])
==================================================== ==================================================== ==================================================== ===============
+++++++++++ If you want to query all data sets, you need to combine the Fx.BI.pageLoadViewData(<String viewId>,<Integer pageNumber>,<Integer pageSize>) method, and combine the two parts of the code+ ++++++++++++++++++++
==================================================== ==================================================== ==================================================== ===============
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("The "+i+" page data set: "+dataTmp["dataSet"])
/* After getting the DataSet, the business can be processed by itself*/
}
}
}
}
1.2 Load the i-th page chart data Fx.BI.pageLoadViewData(<String viewId>,<Integer pageNumber>,<Integer pageSize>)
1.2.1 Function purpose
Query the chart data on page i
Note: This function can query the data sets of each page when the number of pages is known. Usually, it can cooperate with the method Fx.BI.loadViewData(<String viewId>) to search all page data sets. See code DEMO1 for details
1.2.2 Parameter description
| parameter | type | required | description | parameter example |
| :--------- | :------ | :------ | :--------- | :--------- ------------------- |
| viewId | String | Yes | Chart ID | "BI_62174baf180c0b000183a7a6" |
| pageNumber | Integer | yes | page number | 1 |
| pageSize | Integer | yes | number of single page records | 20 |
1.2.3 Return value description
| parameter | type | description |
| :------------ | :----- | :------------------------ ------------------- |
| parameter | type | description |
| code | Int | Function status code: 200: success, 403: no permission, 500: system exception |
| message | String | return message |
| page | Object | Paging information |
| displayFields | Object | header information |
| dataSet | Object | result dataset |
1.2.4 Return value example
The structure returned by the chart data is unified into a two-dimensional table, the table header and data set are returned separately, and paired mapping
{
"code": 200,
"message": "success",
"i18nCode": null,
"data": {
"page": {
"pageSize": 20,
"pageNumber": 1,
"pageCount": 1,
"totalCount": 3
},
"displayFields": [
{
"fieldId": "BI_b76b2e080ba679a4d609be4149fa0cf4",
"fieldName": "Customer Name",
"formatStr": null,
"apiName": "name",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_name$0"
},
{
"fieldId": "BI_0e3221e80e82809cb750316a81fb8bec",
"fieldName": "Creator",
"formatStr": "",
"apiName": "created_by",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_created_by$0"
},
{
"fieldId": "BI_bacc9e515603edf1e4d765fe3970a043",
"fieldName": "Customer Number",
"formatStr": null,
"apiName": "account_no",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_account_no$0"
},
{
"fieldId": "BI_39346e9a9deba83f255db6867dea0f4e",
"fieldName": "Creation Time",
"formatStr": "yyyy-MM-dd HH:mm",
"apiName": "create_time",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_create_time$0"
},
{
"fieldId": "BI_92f9670f6a4ed14d17cebabf8501c4f9",
"fieldName": "Last Modifier",
"formatStr": "",
"apiName": "last_modified_by",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_last_modified_by$0"
},
{
"fieldId": "BI_86142383c45f86fa2be8c6884dce6eeb",
"fieldName": "Customer Level",
"formatStr": "",
"apiName": "account_path",
"objectDescribeApiName": "AccountObj",
"columnName": "biz_account_account_path$0"
},
{
"fieldId": "BI_2fa0b164273097ce020c607c71b28dc8",
"fieldName": "Customer Industry",
"formatStr": "",
"apiName": "field_IGh6L__c",
"objectDescribeApiName": "AccountObj",
"columnName": "customer_udef_field_high6l__c$0"
},
{
"fieldId": "BI_49ccf044b0b6b9b720da8006f37b8474",
"fieldName": "Account Date",
"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": "Test Client",
"formattedValue": "Test Client",
"formattedShowValue": "Test Client"
},
{
"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": "Expense reimbursement user 1",
"formattedValue": "Expense reimbursement user 1",
"formattedShowValue": "Expense reimbursement user 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": "Expense reimbursement customer superior customer/expense reimbursement user 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 Report header information and report data information display
Explanation table for each field in the report header
| Field | Field Meaning | Return Sample |
| :-------------------- | :----------- | :------------- ------------------------------------- |
| fieldID | Field primary key | "fieldId": "BI_b76b2e080ba679a4d609be4149fa0cf4" |
| fieldName | field display name | "fieldName": "customer name" |
| formatStr | format information | "formatStr": null |
| apiName | pass field description | "apiName": "name" |
| objectDescribeApiName | pass object description | "objectDescribeApiName": "AccountObj" |
| columnName | unique column name | "columnName": "biz_account_name$0" |
Report data detailed information field description table
| Field | Field Meaning | Return Sample |
| :---------------- | :----------- | :---------------- ----------- |
| value | field value | "value": "jjjj" |
| formattedValue | field display name | "formattedValue": "jjjj" |
| formattedShowValue | floating value | "formattedShowValue": "jjjj" |
1.2.6 Function DEMO2
/**Query the data set of page i**/
++++++++++++++++++++++Fx.BI.pageLoadViewData(<String viewId>,<Integer pageNumber>,<Integer pageSize>) method++++++++ ++++++++++++++++++++++++
==================================================== ==================================================== =====================================
def (isErrorTmp,dataTmp,errorMsgTmp) = Fx.BI.pageLoadViewData("BI_62174baf180c0b000183a7a6",i,pageSize)
log.info("The "+i+" page data set: "+dataTmp["dataSet"])
/* After getting the DataSet, the business can be processed by itself*/
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","dateRangeId":"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 Get the data range and scene of the chart Fx.BI.getFiltersResult(<String viewId>)
1.5.1 Function usage
Query the data range and scene of the chart according to the specified conditions
1.5.2 Parameter description
| parameter | type | required | description |
|---|---|---|---|
| viewId | String | Yes | View ID |
1.5.3 Return Value Description
| parameter | type | description |
| :-------------- | :----- | :----------------------- ----------------------------------- |
| code | Int | Function status code: 200: Success 403: No permission 404: Not found 500: System exception |
| message | String | return message |
| filterGroups | Object | Data range information |
| labelAndOptions | Object | scene information |
1.5.4 Return value example
The structure returned by the chart data is unified into a two-dimensional table, the table header and data set are returned separately, and paired mapping
{
"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": "Scene",
"defaultFilterOptions": [
{
"optionName": "All",
"isDefault": 1,
"optionID": "BI_59a3e59333b39e09b44e9db1"
},
{
"optionName": "I am in charge",
"isDefault": 0,
"optionID": "BI_59a3e59333b39e09b44e9db2"
},
{
"optionName": "My joint follow-up",
"isDefault": 0,
"optionID": "BI_171714538838884352"
},
{
"optionName": "I serve",
"isDefault": 0,
"optionID": "BI_171714538872438784"
},
{
"optionName": "My Participation",
"isDefault": 0,
"optionID": "BI_59a3e59333b39e09b44e9db3"
},
{
"optionName": "I am in charge of the department",
"isDefault": 0,
"optionID": "BI_59a3e59333b39e09b44e9db4"
},
{
"optionName": "My subordinate is in charge",
"isDefault": 0,
"optionID": "BI_59a3e59333b39e09b44e9db5"
},
{
"optionName": "My subordinates jointly follow up",
"isDefault": 0,
"optionID": "BI_171714538939547648"
},
{
"optionName": "served by my subordinates",
"isDefault": 0,
"optionID": "BI_171714538973102080"
},
{
"optionName": "Participated by my subordinates",
"isDefault": 0,
"optionID": "BI_59a3e59333b39e09b44e9db6"
},
{
"optionName": "Shared with me",
"isDefault": 0,
"optionID": "BI_59a3e59333b39e09b44e9db7"
}
]
}
]
}
}
1.5.5 Comparison display of return value codes and example images
| Field | Field Meaning | Return Sample |
| :---------- | :----------- | :-------------------- --------------------------------------- |
| dataRangeId | required for date filtering | "dateRangeId": null |
| filterId | Filter primary key | "filterId": "BI_62174baf180c0b000183a7a9", |
| value1 | default filter range | "value1": "[{"id":999999,"type":"c"}]", "value1": "[{"id": 999999,"type":"c"}]", |
| value2 | default filter range | "value2": "", |
Scene information (labelAndOptions)
| Field | Field Meaning | Return Sample |
| :--------- | :------------------- | :---------------- ------------------------ |
| optionName | scene option name | "optionName": "all" |
| isDefault | Whether it is default (1: default) | "isDefault": 1 |
| optionID | scene option ID | "optionID": "BI_59a3e59333b39e09b44e9db1" |
1.5.6 Function DEMO5
/* load chart filter */
++++++++++++++++++++++++++++++++++++++++++++++ Fx.BI. getFiltersResult(<String viewId>) method++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++
==================================================== ==================================================== ===================================================
def(isError5,data5,errorMsg5) = Fx.BI.getFiltersResult("BI_62174baf180c0b000183a7a6")
if(!isError5){
log.info("Filter information:"+data5["filterGroups"])
log.info("Scene information:"+data5["labelAndOptions"])
/*The filter value can be modified according to the FilterId to query, and the structure of the value can refer to the parameter structure of the report running state*/
}
1.6 Precautions for using filterList: date filter case and enumeration items
Note: If the filter range is date filter, you need to add a condition to filterList, if the date filter does not add this condition, it will fail
For example: "dataRangeId": 6, which represents the current year, the required enumeration value and introduction are shown in the figure below
2. Instructions for using the crosstab function
Attachment: Crosstab Structure Analysis
♦Column grouping—colGroupFields
♦Row grouping—rowGroupFields
♦Header—headerFields
♦Dataset—dataSet
♦Total header information—summaryDisplayFields
♦Total data set—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
♦数据集—dataSe
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
}
}
}