【9.0.103】【视图预览时字段显示异常，导致表格等展示也有问题】

登录

1.活字格预览效果

2.数据库查询结果

淡饭依恋 · 淡饭依恋

1.异常字段类型为 varbinary ，活字格不支持这种类型的数据

解决方案：
使用mysql的CAST函数，将字段类型转成字符串，使活字格支持

select
`人员`,
`统计项`,
`月份`,
CAST(`金额` AS CHAR) as '金额'
from ...

复制代码

Erik.Xue · Erik.Xue

本帖最后由 Erik.Xue 于 2024-9-5 09:23 编辑

调查了下：

1、首先需要把视图的那个金额字段，右键菜单，点击重新获取列类型：

2、然后需要将所有页面涉及绑定【金额】这个字段的地方都先清除绑定，然后重新绑定下。

目前活字格还没有支持（截止目前V10.0版本），可以在产品需求版块提个需求哦~

Erik.Xue · Erik.Xue

您好，请问视图中这个【金额】字段的来源字段是什么类型的？或者是怎么得到的这个字段的，最好附上视图SQL哈。

淡饭依恋 · 淡饭依恋

表格引用效果

Erik.Xue · Erik.Xue

本帖最后由 Erik.Xue 于 2024-9-3 12:09 编辑

大佬，需要说明下视图本身字段类型，最好附上视图SQL看下。

淡饭依恋 · 淡饭依恋

Erik.Xue 发表于 2024-9-3 11:46
您好，请问视图中这个【金额】字段的来源字段是什么类型的？或者是怎么得到的这个字段的，最好附上视图SQL ...

select
c.preSales as '人员',
'月度完成金额（元）' as '统计项',
DATE_FORMAT(c.serviceStartAt, '%Y-%m') as '月份',
c.contractAmount as '金额'
from itsm_service_contract c
where c.preSales is not null

union all

select
c.preSales as '人员',
'续签金额（元）' as '统计项',
DATE_FORMAT(c.serviceStartAt, '%Y-%m') as '月份',
c.contractAmountOld as '金额'
from itsm_service_contract c
where c.preSales is not null

union all

select
c.preSales as '人员',
'扩签金额（元）' as '统计项',
DATE_FORMAT(c.serviceStartAt, '%Y-%m') as '月份',
c.contractAmountExt as '金额'
from itsm_service_contract c
where c.preSales is not null

union all

select
c.preSales as '人员',
'新签金额（元）' as '统计项',
DATE_FORMAT(c.serviceStartAt, '%Y-%m') as '月份',
c.contractAmountNew as '金额'
from itsm_service_contract c
where c.preSales is not null

union all

select
c.preSales as '人员',
'月度目标完成率' as '统计项',
DATE_FORMAT(c.serviceStartAt, '%Y-%m') as '月份',
CONCAT(ROUND(IF(kpi.amount is null or kpi.amount = 0,0,sum(c.contractAmountNew)/kpi.amount)*100,2),'%') as '金额'
from itsm_service_contract c
left join (
select
user,
month,
sum(amount) as amount
from itsm_report_user_kpi_target
group by user,month
) kpi on kpi.user = c.preSales
and DATE_FORMAT(kpi.month, '%Y-%m') =DATE_FORMAT(c.serviceStartAt, '%Y-%m')
where c.preSales is not null
group by c.preSales,DATE_FORMAT(c.serviceStartAt, '%Y-%m')

union all

select
c.preSales as '人员',
'存量目标完成率' as '统计项',
DATE_FORMAT(c.serviceStartAt, '%Y-%m') as '月份',
CONCAT(ROUND(IF(kpi.amount is null or kpi.amount = 0,0,sum(c.contractAmountNew)/kpi.amount)*100,2),'%') as '金额'
from itsm_service_contract c
left join itsm_report_user_kpi_target kpi on kpi.user = c.preSales
and DATE_FORMAT(kpi.month, '%Y-%m') =DATE_FORMAT(c.serviceStartAt, '%Y-%m')
and kpi.type = '存量'
where c.preSales is not null
group by c.preSales,DATE_FORMAT(c.serviceStartAt, '%Y-%m')

union all

select
c.preSales as '人员',
'新量目标完成率' as '统计项',
DATE_FORMAT(c.serviceStartAt, '%Y-%m') as '月份',
CONCAT(ROUND(IF(kpi.amount is null or kpi.amount = 0,0,sum(c.contractAmountNew)/kpi.amount)*100,2),'%') as '金额'
from itsm_service_contract c
left join itsm_report_user_kpi_target kpi on kpi.user = c.preSales
and DATE_FORMAT(kpi.month, '%Y-%m') =DATE_FORMAT(c.serviceStartAt, '%Y-%m')
and kpi.type = '新量'
group by c.preSales,DATE_FORMAT(c.serviceStartAt, '%Y-%m')

union all

select
c.preSales as '人员',
'应移交合约数量（个）' as '统计项',
DATE_FORMAT(c.serviceStartAt, '%Y-%m') as '月份',
count(1) as '金额'
from itsm_service_contract c
where c.preSales is not null
group by c.preSales,DATE_FORMAT(c.serviceStartAt, '%Y-%m')

union all

select
c.preSales as '人员',
'已移交合约数量（个）' as '统计项',
DATE_FORMAT(c.serviceStartAt, '%Y-%m') as '月份',
count(1) as '金额'
from itsm_service_contract c
where c.preSales is not null
and status = '已审核'
group by c.preSales,DATE_FORMAT(c.serviceStartAt, '%Y-%m')

union all

select
c.preSales as '人员',
'合约移交率' as '统计项',
DATE_FORMAT(c.serviceStartAt, '%Y-%m') as '月份',
CONCAT(ROUND((sum(if(status = '已审核',1,0))/count(1))*100,2),'%') as '金额'
from itsm_service_contract c
where c.preSales is not null
group by c.preSales,DATE_FORMAT(c.serviceStartAt, '%Y-%m')

淡饭依恋 · 淡饭依恋

Erik.Xue 发表于 2024-9-3 12:08
大佬，需要说明下视图本身字段类型，最好附上视图SQL看下。

应该属于文本类型字段，因为不仅仅只是数字

Erik.Xue · Erik.Xue

这边尝试复现没有复现，需要提供一下这个【数据库连接】测试下。

		自动登录	找回密码
密码			立即注册

[已处理] 【9.0.103】【视图预览时字段显示异常，导致表格等展示也有问题】

本帖子中包含更多资源

8 个回复

本帖子中包含更多资源

本帖子中包含更多资源

本帖子中包含更多资源

Wyn高级认证

Wyn认证

讲师达人

悬赏达人

元老葡萄

活字格认证

活字格高级认证

微信认证勋章