I need to convert this Crystal formula into SSRS Expression:

Question

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

That's actually pretty close for a complicated expression. – Hannover Fist Jul 20 '22 at 16:38 — Hannover Fist, Jul 20 '22 at 16:38

score 0 · Accepted Answer · answered Jul 19 '22 at 18:24

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.

@RichaBharti If this answer solved your problem, then please mark it as accepted. This helps other people find answers to similar question and also credits the person who helped, you will also gain reputation by doing this. To mark as answered, click the check mark to the left of the answer under the vote up/down buttons — Alan Schofield, Jul 20 '22 at 09:17

I need to convert this Crystal formula into SSRS Expression:

1 Answers1