reporting-servicescrystal-reportsssrs-expression

I need to convert this Crystal formula into SSRS Expression:


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})

I tried to do that and got as below expression but getting error.

=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)))

Solution

  • It's looks like you are just missing the expression for the last line that is usually used as an ELSE statement for a default value when all other conditions are not met. You need to add the True for the expression - SSRS doesn't assume the last line is a default value.

    =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),
            True, Round(Fields!loaded_qty.Value / Fields!reorder_qty.Value)
            )
    

    You don't need to enclose each field in parenthesis in SSRS like the Crystal report code had.