IIf not working properly?

8 posts, 0 answers
  1. klaudyuxxx
    klaudyuxxx avatar
    4 posts
    Member since:
    Jan 2013

    Posted 22 Mar 2013 Link to this post

    I need to exec a stored procedure. Some of the parameters are strings (but can also be null) and need to have this format: < 'IntVal1,IntVal2,IntVal3' >.
    IntVals are basically city IDs.
    I managed to format them with Join function:  Join(",",Parameters.Cities.Value).
    The problem I'm facing is when the parameter City is null (I sometimes need it to be null). The Join function outputs an error in that case. To avoid calling the Join function when the param City is null I tried the following:
    @queryParamCity   =   IIf  (Parameters.Cities.Value,  Join(",",Parameters.Cities.Value),  null)
    or
    @queryParamCity   =   IIf  (Parameters.Cities.Value IS NOT null,  Join(",",Parameters.Cities.Value),  null)
    or 
    @queryParamCity   =   IIf(Parameters.Cities.Value is null,null,Join(",",Parameters.Cities.Value))
    .....but the Join function is still being called even if the City parameter is null, so it generates an error and the execution is stopped.

    So, no matter what is the value of the expression, the other two member of the IIf function are called.
  2. klaudyuxxx
    klaudyuxxx avatar
    4 posts
    Member since:
    Jan 2013

    Posted 22 Mar 2013 Link to this post

    The reporting engine first evaluates the function parameters and then passes the result values to the function. This means that you need to make sure the parameters of the function are valid statements prior to executing the function.

    Found my answer...But not a solution
  3. DevCraft banner
  4. klaudyuxxx
    klaudyuxxx avatar
    4 posts
    Member since:
    Jan 2013

    Posted 22 Mar 2013 Link to this post

    and the solution
    = IIf(Parameters.Cities.Value is null,null,Join(",", IsNull(Parameters.Cities.Value, Parameters.fake.Value)))

    Where the 'fake' param is a string with Value < ="" >
  5. erwin
    erwin avatar
    358 posts
    Member since:
    Dec 2006

    Posted 02 Apr 2013 Link to this post

    Another option would be to use COALESCE in the SQL Query.

    Update: sorry didn't notice at first, you are using Parameters, so COALESCE is not an option.
  6. Gaurav
    Gaurav avatar
    35 posts
    Member since:
    Nov 2012

    Posted 25 Oct 2013 Link to this post

    What will happen if the logic is like : -
    IIF(A=0,0,B/A)
    As stated
    The reporting engine first evaluates the function parameters and then passes the result values to the function. This means that you need to make sure the parameters of the function are valid statements prior to executing the function
    Now as per my understanding by the statement :-
    Function evaluate B/A first ?  Am i wrong ?  Than how can i handle Device by zero conditions ?
  7. Alexandru
    Alexandru avatar
    3 posts
    Member since:
    Sep 2012

    Posted 25 Oct 2013 Link to this post

    null
  8. klaudyuxxx
    klaudyuxxx avatar
    4 posts
    Member since:
    Jan 2013

    Posted 25 Oct 2013 Link to this post

    try something like this
    =IIf (A=0, 0, B / IIf(A=0,1,A))
  9. Gaurav
    Gaurav avatar
    35 posts
    Member since:
    Nov 2012

    Posted 10 Nov 2013 Link to this post

     @Alexandru

    Null ??
Back to Top
DevCraft banner