Search code examples
reporting-servicesoracle10gssrs-2008

SSRS: IF Else inside Switch not working

  • Here im trying to do switch then if else in it. I dont know where i missed but this doesnt work.
  • for first condition in switch, 'WEEKLY', WW2 and WW1 is in number form but of text/string datatype. so after cast them to integer/number datatype then those are use in iff operation.
  • for second condition in switch, 'MONTHLY', WW2 and WW1 is month name. so the objective is to get month number from month name. After that, then WW1,WW2 are use in iff operation.

=SWITCH(Parameters!date_range_type.Value = "WEEKLY", IIF((cInt(Parameters!WW2.Value) - cInt(Parameters!WW1.Value)) > 10, Parameters!WW1.Value,IIF(cInt(Parameters!WW2.Value) < 11,1,cInt(Parameters!WW2.Value) - 10)), Parameters!date_range_type.Value = "MONTHLY", IIF((cInt(MONTH(datepart("YYYY",today())& "-" & Parameters!WW2.Value & "-01")) - cInt(MONTH(datepart("YYYY",today())& "-" & Parameters!WW1.Value & "-01"))) > 10, MONTH(datepart("YYYY",today())& "-" & Parameters!WW1.Value & "-01"),IIF(cInt(MONTH(datepart("YYYY",today())& "-" & Parameters!WW2.Value & "-01")) < 11,1,cInt(MONTH(datepart("YYYY",today())& "-" & Parameters!WW2.Value & "-01")) - 10)) )

if i run report for 'WEEKLY' only, means doesnt need switch and the other iif condition, it working fine. so does when run on 'MONTHLY' only.

EDIT

this is how it looks like if i use iif only..

IIF(Parameters!date_range_type.Value = "MONTHLY",( IIF((cInt(MONTH(datepart("YYYY",today())& "-" & Parameters!WW2.Value & "-01")) - cInt(MONTH(datepart("YYYY",today())& "-" & Parameters!WW1.Value & "-01"))) > 10,MONTH(datepart("YYYY",today())& "-" & Parameters!WW1.Value & "-01"),IIF(cInt(MONTH(datepart("YYYY",today())& "-" & Parameters!WW2.Value & "-01")) < 11,1,cInt(MONTH(datepart("YYYY",today())& "-" & Parameters!WW2.Value & "-01")) - 10)) ), ( IIF(Parameters!date_range_type.Value = "WEEKLY",( IIF((cInt(Parameters!WW2.Value) - cInt(Parameters!WW1.Value)) > 10, Parameters!WW1.Value, IIF(cInt(Parameters!WW2.Value) < 11,1,cInt(Parameters!WW2.Value) - 10)) ), "0") ) )

Solution

  • End up using sql function for the job. this is the same logic as above SSRS code.

    CREATE OR REPLACE FUNCTION ww3_param (ww1 IN NUMBER, ww2 IN NUMBER) RETURN VARCHAR2 IS ret_val VARCHAR2(10); BEGIN IF (ww2 - ww1) > 10 THEN ret_val := to_char(ww1); ELSE IF ww2 < 10 THEN ret_val := '1'; ELSE ret_val := to_char(ww2 - 10);
    END IF; END IF;
    RETURN ret_val; END;

    EDIT

    This is the query for the dataset:

    select case when :date_range_type = 'MONTHLY' then ww3_param(to_char(to_date(:WW1,'MON'),'MM'),to_char(to_date(:WW2,'MON'),'MM')) when :date_range_type = 'WEEKLY' then ww3_param(to_number(:WW1),to_number(:WW2)) end as ww3_param_value from dual