Crystal Reports多条件字段高亮配置技术求助

需求说明 #

• 无调整情况：数值处于特定区间时标记为黄色，提示接近下一非调整奖金等级，该判断无条件执行
• 含调整情况：仅当{Productivity_Bonuses.Bonus_Percent}≠1时执行，数值处于对应调整区间时标记为橙色（需覆盖黄色标记），提示接近下一调整奖金等级（门槛随休假调整）

可行公式组合方案 #

核心逻辑是优先判断调整情况，满足则直接返回橙色；不满足时再判断非调整情况，返回黄色或无颜色，确保橙色覆盖黄色。

合并后的高亮公式 #

// 先判断调整情况（优先级更高）
IF NOT(ISNULL({Productivity_Bonuses.Bonus_Percent})) AND {Productivity_Bonuses.Bonus_Percent} <> 1 THEN
    IF ISNULL({Productivity_Bonuses.Credits}) or {Productivity_Bonuses.Credits} = 0 THEN 
        if (({Productivity_Bonuses.Revenue} < 18000 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent} 
        and {Productivity_Bonuses.Revenue} >= 17500 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent}) 
        or ({Productivity_Bonuses.Revenue} < 16000 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent}  
        and {Productivity_Bonuses.Revenue} >= 15500 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent}) 
        or ({Productivity_Bonuses.Revenue} < 14500 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent} 
        and {Productivity_Bonuses.Revenue} >= 14000 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent})) 
            then color(245, 176, 65)
        else 
            // 调整情况不满足时，判断非调整情况
            IF ISNULL({Productivity_Bonuses.Credits}) or {Productivity_Bonuses.Credits} = 0 THEN 
                if (({Productivity_Bonuses.Revenue} < 18000 * {MPC_OPData.FullTime_Equivalency} 
                and {Productivity_Bonuses.Revenue} >= 17500 * {MPC_OPData.FullTime_Equivalency}) 
                or ({Productivity_Bonuses.Revenue} < 16000 * {MPC_OPData.FullTime_Equivalency} 
                and {Productivity_Bonuses.Revenue} >= 15500 * {MPC_OPData.FullTime_Equivalency}) 
                or ({Productivity_Bonuses.Revenue} < 14500 * {MPC_OPData.FullTime_Equivalency}
                and {Productivity_Bonuses.Revenue} >= 14000 * {MPC_OPData.FullTime_Equivalency})) 
                    then crYellow
                else crNoColor
            ELSE 
                if (({Productivity_Bonuses.Revenue} < 18000 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100)
                and {Productivity_Bonuses.Revenue} >= 17500 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100))
                or ({Productivity_Bonuses.Revenue} < 16000 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100)
                and {Productivity_Bonuses.Revenue} >= 15500 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100))
                or ({Productivity_Bonuses.Revenue} < 14500 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100)
                and {Productivity_Bonuses.Revenue} >= 14000 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100)))
                    then crYellow
                else crNoColor
ELSE
    // Bonus_Percent为1或空时，直接判断非调整情况
    IF ISNULL({Productivity_Bonuses.Credits}) or {Productivity_Bonuses.Credits} = 0 THEN 
        if (({Productivity_Bonuses.Revenue} < 18000 * {MPC_OPData.FullTime_Equivalency} 
        and {Productivity_Bonuses.Revenue} >= 17500 * {MPC_OPData.FullTime_Equivalency}) 
        or ({Productivity_Bonuses.Revenue} < 16000 * {MPC_OPData.FullTime_Equivalency} 
        and {Productivity_Bonuses.Revenue} >= 15500 * {MPC_OPData.FullTime_Equivalency}) 
        or ({Productivity_Bonuses.Revenue} < 14500 * {MPC_OPData.FullTime_Equivalency}
        and {Productivity_Bonuses.Revenue} >= 14000 * {MPC_OPData.FullTime_Equivalency})) 
            then crYellow
        else crNoColor
    ELSE 
        if (({Productivity_Bonuses.Revenue} < 18000 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100)
        and {Productivity_Bonuses.Revenue} >= 17500 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100))
        or ({Productivity_Bonuses.Revenue} < 16000 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100)
        and {Productivity_Bonuses.Revenue} >= 15500 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100))
        or ({Productivity_Bonuses.Revenue} < 14500 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100)
        and {Productivity_Bonuses.Revenue} >= 14000 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100)))
            then crYellow
        else crNoColor

逻辑说明 #

1. 先处理调整场景：仅当{Productivity_Bonuses.Bonus_Percent}≠1时触发，若满足调整区间条件直接返回橙色，不满足则进入非调整场景判断
2. 非调整场景无条件执行：满足区间返回黄色，否则返回crNoColor避免黑色高亮问题
3. 嵌套结构确保橙色优先级高于黄色，覆盖原有黄色标记逻辑

原代码参考 #

非调整情况判断代码 #

IF ISNULL({Productivity_Bonuses.Credits}) or {Productivity_Bonuses.Credits} = 0 THEN 
if (({Productivity_Bonuses.Revenue} < 18000 * {MPC_OPData.FullTime_Equivalency} 
and {Productivity_Bonuses.Revenue} >= 17500 * {MPC_OPData.FullTime_Equivalency}) 
or ({Productivity_Bonuses.Revenue} < 16000 * {MPC_OPData.FullTime_Equivalency} 
and {Productivity_Bonuses.Revenue} >= 15500 * {MPC_OPData.FullTime_Equivalency}) 
or ({Productivity_Bonuses.Revenue} < 14500 * {MPC_OPData.FullTime_Equivalency}
and {Productivity_Bonuses.Revenue} >= 14000 * {MPC_OPData.FullTime_Equivalency})) 
    then crYellow
else crNoColor
ELSE //ISNULL({Productivity_Bonuses.Credits}) is false and {Productivity_Bonuses.Credits} <> 0
if (({Productivity_Bonuses.Revenue} < 18000 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100)
and {Productivity_Bonuses.Revenue} >= 17500 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100))
or ({Productivity_Bonuses.Revenue} < 16000 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100)
and {Productivity_Bonuses.Revenue} >= 15500 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100))
or ({Productivity_Bonuses.Revenue} < 14500 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100)
and {Productivity_Bonuses.Revenue} >= 14000 * {MPC_OPData.FullTime_Equivalency} * ((100 - {Productivity_Bonuses.Credits}) / 100)))
    then crYellow
else crNoColor

调整情况判断代码 #

IF ISNULL({Productivity_Bonuses.Credits}) or {Productivity_Bonuses.Credits} = 0 THEN 
if (({Productivity_Bonuses.Revenue} < 18000 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent} 
and {Productivity_Bonuses.Revenue} >= 17500 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent}) 
or ({Productivity_Bonuses.Revenue} < 16000 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent}  
and {Productivity_Bonuses.Revenue} >= 15500 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent}) 
or ({Productivity_Bonuses.Revenue} < 14500 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent} 
and {Productivity_Bonuses.Revenue} >= 14000 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent})) 
    then color(245, 176, 65)
else crNoColor
ELSE 
if (({Productivity_Bonuses.Revenue} < 18000 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent} * ((100 - {Productivity_Bonuses.Credits}) / 100)
and {Productivity_Bonuses.Revenue} >= 17500 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent} * ((100 - {Productivity_Bonuses.Credits}) / 100))
or ({Productivity_Bonuses.Revenue} < 16000 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent} * ((100 - {Productivity_Bonuses.Credits}) / 100)
and {Productivity_Bonuses.Revenue} >= 15500 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent} * ((100 - {Productivity_Bonuses.Credits}) / 100))
or ({Productivity_Bonuses.Revenue} < 14500 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent} * ((100 - {Productivity_Bonuses.Credits}) / 100)
and {Productivity_Bonuses.Revenue} >= 14000 * {MPC_OPData.FullTime_Equivalency} * {Productivity_Bonuses.Bonus_Percent} * ((100 - {Productivity_Bonuses.Credits}) / 100)))
    then color(245, 176, 65)
else crNoColor

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