Crystal公式转SSRS Expression报错，求正确转换方案

原Crystal公式 #

select {t_item_master.reorder_qty}
case 5:
  ({t_hu_detail.loaded_qty}*.5) 
case 3:
   ROUND({t_hu_detail.loaded_qty}/{t_item_master.reorder_qty}+.17)
case 4:
   ROUND({t_hu_detail.loaded_qty}/{t_item_master.reorder_qty}+.25)
case 6:
   ROUND({t_hu_detail.loaded_qty}/{t_item_master.reorder_qty}+.34)
case 7:
   ROUND({t_hu_detail.loaded_qty}/{t_item_master.reorder_qty}+.36)
case 8:
   ROUND({t_hu_detail.loaded_qty}/{t_item_master.reorder_qty}+.37)
case 9:
   ROUND({t_hu_detail.loaded_qty}/{t_item_master.reorder_qty}+.39)
default:
   ROUND({t_hu_detail.loaded_qty}/{t_item_master.reorder_qty})

用户报错的SSRS表达式 #

=SWITCH(Fields!reorder_qty.Value = 5,((Fields!loaded_qty.Value)*.5),
        Fields!reorder_qty.Value = 3,Round((Fields!loaded_qty.Value)/(Fields!reorder_qty.Value)+.17),
    Fields!reorder_qty.Value = 4,Round((Fields!loaded_qty.Value)/(Fields!reorder_qty.Value)+.25),
    Fields!reorder_qty.Value = 6,Round((Fields!loaded_qty.Value)/(Fields!reorder_qty.Value)+.34),
    Fields!reorder_qty.Value = 7,Round((Fields!loaded_qty.Value)/(Fields!reorder_qty.Value)+.36),
    Fields!reorder_qty.Value = 8,Round((Fields!loaded_qty.Value)/(Fields!reorder_qty.Value)+.37),
    Fields!reorder_qty.Value = 9,Round((Fields!loaded_qty.Value)/(Fields!reorder_qty.Value)+.39),
    Round((Fields!loaded_qty.Value)/(Fields!reorder_qty.Value)))

错误原因 #

SSRS的SWITCH函数要求参数必须是条件表达式, 对应结果的成对结构，用户最后一行仅写了默认结果，缺少对应的条件判断，导致语法不合法。

修正后的SSRS表达式 #

=SWITCH(
    Fields!reorder_qty.Value = 5, Fields!loaded_qty.Value * 0.5,
    Fields!reorder_qty.Value = 3, Round(Fields!loaded_qty.Value / Fields!reorder_qty.Value + 0.17),
    Fields!reorder_qty.Value = 4, Round(Fields!loaded_qty.Value / Fields!reorder_qty.Value + 0.25),
    Fields!reorder_qty.Value = 6, Round(Fields!loaded_qty.Value / Fields!reorder_qty.Value + 0.34),
    Fields!reorder_qty.Value = 7, Round(Fields!loaded_qty.Value / Fields!reorder_qty.Value + 0.36),
    Fields!reorder_qty.Value = 8, Round(Fields!loaded_qty.Value / Fields!reorder_qty.Value + 0.37),
    Fields!reorder_qty.Value = 9, Round(Fields!loaded_qty.Value / Fields!reorder_qty.Value + 0.39),
    True, Round(Fields!loaded_qty.Value / Fields!reorder_qty.Value)
)

额外说明 #

• 添加True作为默认分支的条件，确保所有未匹配前面case的情况都执行默认逻辑
• 移除了冗余的括号，让表达式更简洁
• 若存在reorder_qty为0或空值的情况，需额外添加判断避免零除错误，例如在最前面增加：

  Fields!reorder_qty.Value Is Nothing Or Fields!reorder_qty.Value = 0, 0,
  

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