BO Webi技术问题：如何显示空白而非计算值

问题场景 #

计算两个日期时间字段的差值时，若其中一个字段为空，当前变量仍会输出计算结果，需求改为这种场景下显示空白。

现有变量公式 #

CT Time Diff (Started to Completed) #

=(((DaysBetween([CT Exam Started Date & Time];[CT Completed Date & Time]))*1440)+(ToNumber(Substr(FormatDate([CT Completed Date & Time];"HH:mm:ss");1 ;2))-ToNumber(Substr(FormatDate([CT Exam Started Date & Time];"HH:mm:ss");1 ;2)))*60+(ToNumber(Substr(FormatDate([CT Completed Date & Time];"HH:mm:ss");4 ;2))-ToNumber(Substr(FormatDate([CT Exam Started Date & Time];"HH:mm:ss");4 ;2))))

CT Started to Completed Time (MM) #

=If (([CT Time Diff (Started to Completed)]) >= 0) Then [CT Time Diff (Started to Completed)]

解决方案 #

核心是通过IsNull()函数判断字段是否为空，只要任意一个日期时间字段为空，就返回空白字符串""，否则执行差值计算。

方案1：修改上层变量（推荐） #

直接调整CT Started to Completed Time (MM)，在原有逻辑前增加空值判断：

=If (IsNull([CT Exam Started Date & Time]) Or IsNull([CT Completed Date & Time])) Then ""
Else If ([CT Time Diff (Started to Completed)] >= 0) Then [CT Time Diff (Started to Completed)]

方案2：修改底层变量 #

在CT Time Diff (Started to Completed)中加入空值判断，所有依赖它的变量会自动继承空值处理逻辑：

=If (IsNull([CT Exam Started Date & Time]) Or IsNull([CT Completed Date & Time])) Then ""
Else (((DaysBetween([CT Exam Started Date & Time];[CT Completed Date & Time]))*1440)+(ToNumber(Substr(FormatDate([CT Completed Date & Time];"HH:mm:ss");1 ;2))-ToNumber(Substr(FormatDate([CT Exam Started Date & Time];"HH:mm:ss");1 ;2)))*60+(ToNumber(Substr(FormatDate([CT Completed Date & Time];"HH:mm:ss");4 ;2))-ToNumber(Substr(FormatDate([CT Exam Started Date & Time];"HH:mm:ss");4 ;2))))

说明 #

• IsNull()函数用于检测字段是否为空，Or逻辑确保任意一个字段为空时都返回空白
• 空白字符串""在Webi中会显示为空白单元格，符合需求

内容的提问来源于stack exchange，提问作者jsisock79