locked
Can't get this expression to work RRS feed

  • Question

  • Coming from Crystal Reports I have trouble with expressions from time to time.  I have this one I just can't get to work and not really sure why.  

     

    =IIF(SUM(IIF(Fields!Work_Center.Value="INSPECT",1,0))>0 AND SUM(IIF(Fields!Work_Center.Value="INSPECT" AND Fields!Status.Value="O",1,0))>0,True,

    IIF(SUM(IIF(Fields!Work_Center.Value="40 WELD",1,0))>0 AND SUM(IIF(Fields!Work_Center.Value="40 WELD" AND Fields!Status.Value="O",1,0))=0, AND

    IIF(SUM(IIF(Fields!Work_Center.Value="41 FULLPEN",1,0))>0 AND SUM(IIF(Fields!Work_Center.Value="41 FULLPEN" AND Fields!Status.Value="O",1,0))=0,TRUE,FALSE)))

     

    What is going on with this?  Anyone?

    Tuesday, January 11, 2011 6:11 PM

Answers

  • Hi,

    Your query isn't well formatted, only the last part, I have reformatted it for you so that you can see the origin of the problem:

    IIF(=========> let's call this IIF1
     SUM(
     IIF(Fields!Work_Center.Value="40 WELD",1,0)
     )>0

     AND

     SUM(
     IIF(Fields!Work_Center.Value="40 WELD" AND Fields!Status.Value="O",1,0)
     )=0, =====================================================>This comma is in a wrong place!

     AND

     IIF(=====> let's call this IIF2
      SUM(
      IIF(Fields!Work_Center.Value="41 FULLPEN",1,0)
      )>0
      
      AND
      
      SUM(
      IIF(Fields!Work_Center.Value="41 FULLPEN" AND Fields!Status.Value="O",1,0)
      )=0
     ,TRUE,FALSE)==========> This is the complement of IIF2

    ======> here you need a comma, and value if true, value if false for IIF1

    )

     

    I hope this helps.


    MCITP: Business Intelligence Developer 2008, MCITP: Database Developer 2008, MCITP: Database Administrator 2008, MCPD: Enterprise Application Developer 3.5, MCTS: SharePoint 2010, Configuration Charter Member, MCTS: .NET Framework 3.5, Windows Workflow Foundation Applications, MCTS: Microsoft Windows Vista: Configuration, Microsoft® Certified Trainer 2010
    • Marked as answer by dchaos Tuesday, January 11, 2011 8:39 PM
    Tuesday, January 11, 2011 6:59 PM

All replies

  • Hi,

    Your query isn't well formatted, only the last part, I have reformatted it for you so that you can see the origin of the problem:

    IIF(=========> let's call this IIF1
     SUM(
     IIF(Fields!Work_Center.Value="40 WELD",1,0)
     )>0

     AND

     SUM(
     IIF(Fields!Work_Center.Value="40 WELD" AND Fields!Status.Value="O",1,0)
     )=0, =====================================================>This comma is in a wrong place!

     AND

     IIF(=====> let's call this IIF2
      SUM(
      IIF(Fields!Work_Center.Value="41 FULLPEN",1,0)
      )>0
      
      AND
      
      SUM(
      IIF(Fields!Work_Center.Value="41 FULLPEN" AND Fields!Status.Value="O",1,0)
      )=0
     ,TRUE,FALSE)==========> This is the complement of IIF2

    ======> here you need a comma, and value if true, value if false for IIF1

    )

     

    I hope this helps.


    MCITP: Business Intelligence Developer 2008, MCITP: Database Developer 2008, MCITP: Database Administrator 2008, MCPD: Enterprise Application Developer 3.5, MCTS: SharePoint 2010, Configuration Charter Member, MCTS: .NET Framework 3.5, Windows Workflow Foundation Applications, MCTS: Microsoft Windows Vista: Configuration, Microsoft® Certified Trainer 2010
    • Marked as answer by dchaos Tuesday, January 11, 2011 8:39 PM
    Tuesday, January 11, 2011 6:59 PM
  • Got it to work,  thanks much for the little lesson!  
    Tuesday, January 11, 2011 8:40 PM
  • Great! Glad I could help!

    Cheers!


    MCITP: Business Intelligence Developer 2008, MCITP: Database Developer 2008, MCITP: Database Administrator 2008, MCPD: Enterprise Application Developer 3.5, MCTS: SharePoint 2010, Configuration Charter Member, MCTS: .NET Framework 3.5, Windows Workflow Foundation Applications, MCTS: Microsoft Windows Vista: Configuration, Microsoft® Certified Trainer 2010
    Tuesday, January 11, 2011 9:16 PM