none
New to ssrs unable to get correct syntax for IF AND statment.

    Question

  • I have a formula I tested in exel that works but I cannot seem to be able to figure out the correct way to use Iif.

    Formula:

    =IIF(AND(Fields!Assigned_TO_SVD.Value="PI LAB INFR",Fields!Assigned_TO_SVD.Value<>Fields!Assigning_SVD.Value,Fields!Assigning_SVD.Value<>"PI BIL HST INFR",Fields!Action_Type.Value=1),Fields!Action_Date.Value-Fields!Start_Date.Value,IIF(AND(Fields!Assigned_TO_SVD.Value="PI BILHSTINFR",Fields!Assigned_TO_SVD.Value<>Fields!Assigning_SVD.Value,Fields!Assigning_SVD.Value<>"PI LAB INFR",Fields!Action_Type.Value=1),Fields!Action_Date.Value-Fields!Start_Date.Value,"N/A"))

    I know this is not correct but not sure how to write it.

    Thanks in advance for any assistance.

    Wednesday, April 03, 2013 1:53 PM

All replies

  • HI, 

    Could you please write your condition Algorithm, I will write on SSRS Formula ! 

    Regards

    http://simplesqlserver.wordpress.com


    Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    Wednesday, April 03, 2013 2:06 PM
  • Hi 

    =IIF(Fields!Assigned_TO_SVD.Value="PI LAB INFR",
    iif(Fields!Assigned_TO_SVD.Value<>Fields!Assigning_SVD.Value,
    iif(Fields!Assigning_SVD.Value<>"PI BIL HST INFR",Fields!Action_Type.Value=1,Fields!Action_Date.Value-Fields!Start_Date.Value),
    "N/A"),
    "N/A")

    The above sample expression is based on your requirement posted. I took same part of that expression based on this you can write the remaining expression.


    Sreekanth Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.*****

    Wednesday, April 03, 2013 2:31 PM
  • Hi,

    Try This : 

    = IIF( Fields!Assigned_TO_SVD.Value="PI LAB INFR"
    
    AND Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value,
    
    AND Fields!Assigning_SVD.Value <> "PI BIL HST INFR",
    
    AND Fields!Action_Type.Value=1
    
    ,  Fields!Action_Date.Value-Fields!Start_Date.Value , 
    
     
    
    IIF( Fields!Assigned_TO_SVD.Value="PI BIL HST INFR",
    
    AND Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value,
    
    AND Fields!Assigning_SVD.Value <> "PI LAB INFR",
    
    AND Fields!Action_Type.Value=1,  
    
    Fields!Action_Date.Value - Fields!Start_Date.Value, "N/A" ) , "N/A" )

    Regards

    http://simplesqlserver.wordpress.com


    Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!


    • Edited by Djallal.E Wednesday, April 03, 2013 3:16 PM
    Wednesday, April 03, 2013 3:15 PM
  • Hi,

    Try This : 

    = IIF( Fields!Assigned_TO_SVD.Value="PI LAB INFR"
    
    AND Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value,
    
    AND Fields!Assigning_SVD.Value <> "PI BIL HST INFR",
    
    AND Fields!Action_Type.Value=1
    
    ,  Fields!Action_Date.Value-Fields!Start_Date.Value , 
    
     
    
    IIF( Fields!Assigned_TO_SVD.Value="PI BIL HST INFR",
    
    AND Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value,
    
    AND Fields!Assigning_SVD.Value <> "PI LAB INFR",
    
    AND Fields!Action_Type.Value=1,  
    
    Fields!Action_Date.Value - Fields!Start_Date.Value, "N/A" ) , "N/A" )

    Regards

    Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!


    Getting Error:

    The Value expression for the field ‘= IIF( Fields!Assigned_TO_SVD.Value="PI LAB INFR",

    AND Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value,

    AND Fields!Assigning_SVD.Value <> "PI BIL HST INFR",

    AND Fields!Action_Type.Value=1,

    Fields!Action_Date.Value-Fields!Start_Date.Value ,

    IIF( Fields!Assigned_TO_SVD.Value="PI BIL HST INFR",

    AND Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value,

    AND Fields!Assigning_SVD.Value <> "PI LAB INFR",

    AND Fields!Action_Type.Value=1, 

    Fields!Action_Date.Value - Fields!Start_Date.Value, "N/A" ), "N/A")’ contains an error: [BC30201] Expression expected.


    Wednesday, April 03, 2013 3:24 PM
  • Hi 

    try this 

    = IIF( Fields!Assigned_TO_SVD.Value="PI LAB INFR"

    AND (Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value)

    AND (Fields!Assigning_SVD.Value <> "PI BIL HST INFR")

    AND (Fields!Action_Type.Value=1)

    ,  Fields!Action_Date.Value-Fields!Start_Date.Value , 

     
    IIF( Fields!Assigned_TO_SVD.Value="PI BIL HST INFR"

    AND (Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value)

    AND (Fields!Assigning_SVD.Value <> "PI LAB INFR")

    AND (Fields!Action_Type.Value=1),  

    Fields!Action_Date.Value - Fields!Start_Date.Value, "N/A" ) )


    Sreekanth Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.*****


    • Edited by Sreekanth K Wednesday, April 03, 2013 3:33 PM
    Wednesday, April 03, 2013 3:31 PM
  • Hi

    Get same error I thought that was the issue when I got an error from the first formula.

    Wednesday, April 03, 2013 3:33 PM
  • Try this

    = IIF( Fields!Assigned_TO_SVD.Value="PI LAB INFR"

    AND (Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value)

    AND (Fields!Assigning_SVD.Value <> "PI BIL HST INFR")

    AND (Fields!Action_Type.Value=1)

    ,  Fields!Action_Date.Value-Fields!Start_Date.Value , 

     
    IIF( Fields!Assigned_TO_SVD.Value="PI BIL HST INFR"

    AND (Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value)

    AND (Fields!Assigning_SVD.Value <> "PI LAB INFR")

    AND (Fields!Action_Type.Value=1),  

    Fields!Action_Date.Value - Fields!Start_Date.Value, "N/A" ) )


    Sreekanth Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.*****

    Wednesday, April 03, 2013 3:35 PM
  • this will let the report run with out error but the result in the table is # Error.

    Maybe I should use DATEDIFF for ,  Fields!Action_Date.Value-Fields!Start_Date.Value , instead of subtracting them.

    Wednesday, April 03, 2013 3:42 PM
  • This should be work Fine : 

    = IIF( Fields!Assigned_TO_SVD.Value="PI LAB INFR"
    
    AND Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value 
    
    AND Fields!Assigning_SVD.Value <> "PI BIL HST INFR" 
     
    AND Fields!Action_Type.Value=1
    
    ,   DateDiff("d" , Fields!Action_Date.Value ,  Fields!Start_Date.Value ) ,  
    
     
    IIF( 
    
    Fields!Assigned_TO_SVD.Value = "PI BIL HST INFR" 
    
    AND Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value 
    
    AND Fields!Assigning_SVD.Value <> "PI LAB INFR" 
    
    AND Fields!Action_Type.Value = 1 ,   DateDiff("d" , Fields!Action_Date.Value ,  Fields!Start_Date.Value )    ,     "N/A" )  )

    Regards

    http://simplesqlserver.wordpress.com



    Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    Wednesday, April 03, 2013 3:44 PM
  • Yes, 

    Instead of "Fields!Action_Date.Value-Fields!Start_Date.Value" doing this in expression in reports. Try to do this in sql script and use the modified field in report directly instead of "Fields!Action_Date.Value-Fields!Start_Date.Value".


    Sreekanth Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.*****

    Wednesday, April 03, 2013 3:46 PM
  • So it works in SQL when I run the Statement but when I add it to the ssrs tool I get #Error not sure what is going on.

    Wednesday, April 03, 2013 4:52 PM
  • the IIF function evaluates both the TRUE and FALSE expression. You are getting an error because you have NULL dates in either Action_Date or Start_Date fields. You should use IsNothing() to check if the date is null before performing the datediff. However, this will make your IIF statement really long.

    Rather than do the expression in SSRS, it's better if you do a CASE statement in your SQL query for your dataset to calc the date difference.



    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    Wednesday, April 03, 2013 8:02 PM
  • Krootz,

    I have moved this to a case statment withing the SQL and althought when I run it threw PL SQL Developer the case statment runs fine and caculates. When I copy the SQL into the SSRS tool it outputs an #error. Any thoughts?

    Thursday, April 04, 2013 11:16 AM
  • So in your SQL, you have another field that either displays the difference in days or "N/A" right?

    In SSRS how are you using this field?


    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    Thursday, April 04, 2013 11:19 AM
  • You are correct.

    If I add it to a table with the related incident (UID) I get the error. If I add it to a table broken out but month and have it sum I get the error.

    this is the case stament I am using.

    CASE WHEN tosvd.serv_dept_sc = 'PI LAB INFR' AND actsvd.serv_dept_sc <> tosvd.serv_dept_sc AND actsvd.serv_dept_sc <> 'PI BIL HST INFR' AND act_type.act_type_id = '1'
      THEN incident.inc_resolve_act - act_reg.date_actioned
        When tosvd.serv_dept_sc = 'PI BIL HST INFR' AND actsvd.serv_dept_sc <> tosvd.serv_dept_sc AND actsvd.serv_dept_sc <> 'PI LAB INFR' AND act_type.act_type_id = '1'
      THEN incident.inc_resolve_act - act_reg.date_actioned
           END Time_to_Relief,

    Thursday, April 04, 2013 11:24 AM
  • Do you have blank dates? If you do, the result will be NULL and you can't sum. You mentioned PL SQL, are you using ORACLE?

    If you are you can use NVL(case statement...... end, 0) Time_to_Relief, this will force any nulls to become 0 and then you can sum.


    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z


    Thursday, April 04, 2013 11:33 AM
  • I am using oracle

    The reason I do not want the 0's is it will mess with averages. I will Try the NVL and see what I get. Thanks for the input.

    Thursday, April 04, 2013 11:41 AM
  • This should work without errors : 

    = IIF( Fields!Assigned_TO_SVD.Value="PI LAB INFR"  
    
    AND Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value 
    
    AND Fields!Assigning_SVD.Value <> "PI BIL HST INFR" 
     
    AND Fields!Action_Type.Value=1
    
    ,  
    
    IIF(( Fields!Action_Date.Value = "" OR Fields!Start_Date.Value = "" OR IsNothing(Fields!Action_Date.Value) OR IsNothing(Fields!Start_Date.Value) ), "" ,
     DateDiff("d" , 
      IIF(Fields!Action_Date.Value = "" OR IsNothing(Fields!Action_Date.Value), "01/01/1900" ,   Fields!Action_Date.Value ) , 
      IIF(Fields!Start_Date.Value = "" OR IsNothing(Fields!Start_Date.Value), "01/01/1900" ,   Fields!Start_Date.Value ) )
       )
     
    ,  
    
     
    IIF( 
    
    Fields!Assigned_TO_SVD.Value = "PI BIL HST INFR" 
    
    AND Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value 
    
    AND Fields!Assigning_SVD.Value <> "PI LAB INFR" 
    
    AND Fields!Action_Type.Value = 1 ,  
    
    
    IIF(( Fields!Action_Date.Value = "" OR Fields!Start_Date.Value = "" OR IsNothing(Fields!Action_Date.Value) OR IsNothing(Fields!Start_Date.Value) ),
      "",    DateDiff("d" , 
      IIF(Fields!Action_Date.Value = "" OR IsNothing(Fields!Action_Date.Value), "01/01/1900" ,   Fields!Action_Date.Value ) , 
      IIF(Fields!Start_Date.Value = "" OR IsNothing(Fields!Start_Date.Value), "01/01/1900" ,   Fields!Start_Date.Value ) )
         )
       ,     "N/A" )  )

    Regards

    http://simplesqlserver.wordpress.com



    Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    Friday, April 05, 2013 9:00 AM
  • This should work without errors : 

    = IIF( Fields!Assigned_TO_SVD.Value="PI LAB INFR"  
    
    AND Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value 
    
    AND Fields!Assigning_SVD.Value <> "PI BIL HST INFR" 
     
    AND Fields!Action_Type.Value=1
    
    ,  
    
    IIF(( Fields!Action_Date.Value = "" OR Fields!Start_Date.Value = "" OR IsNothing(Fields!Action_Date.Value) OR IsNothing(Fields!Start_Date.Value) ), "" ,
     DateDiff("d" , 
      IIF(Fields!Action_Date.Value = "" OR IsNothing(Fields!Action_Date.Value), "01/01/1900" ,   Fields!Action_Date.Value ) , 
      IIF(Fields!Start_Date.Value = "" OR IsNothing(Fields!Start_Date.Value), "01/01/1900" ,   Fields!Start_Date.Value ) )
       )
     
    ,  
    
     
    IIF( 
    
    Fields!Assigned_TO_SVD.Value = "PI BIL HST INFR" 
    
    AND Fields!Assigned_TO_SVD.Value <> Fields!Assigning_SVD.Value 
    
    AND Fields!Assigning_SVD.Value <> "PI LAB INFR" 
    
    AND Fields!Action_Type.Value = 1 ,  
    
    
    IIF(( Fields!Action_Date.Value = "" OR Fields!Start_Date.Value = "" OR IsNothing(Fields!Action_Date.Value) OR IsNothing(Fields!Start_Date.Value) ),
      "",    DateDiff("d" , 
      IIF(Fields!Action_Date.Value = "" OR IsNothing(Fields!Action_Date.Value), "01/01/1900" ,   Fields!Action_Date.Value ) , 
      IIF(Fields!Start_Date.Value = "" OR IsNothing(Fields!Start_Date.Value), "01/01/1900" ,   Fields!Start_Date.Value ) )
         )
       ,     "N/A" )  )

    Regards

    http://simplesqlserver.wordpress.com



    Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    This formula gives #ERROR as a result.
    Monday, April 15, 2013 2:54 PM