Topic
  • 6 replies
  • Latest Post - ‏2013-07-26T15:56:07Z by bgdecker
bgdecker
bgdecker
24 Posts

Pinned topic Web Query Define Field

‏2013-07-25T20:24:53Z |

I'm new to Web Query; I only got the program a couple of weeks ago.  Now they want me to build a report for a client.  I'm having a problem with a Define field.  The field should return a sum of three fields if the IF statement is true.

IF ( TXDIS5.TXDIS5.YEARTMP LT 2012 ) AND ( TXDIS5.TXDIS5.FNDCODETMP EQ 'M&O' ) THEN TXDIS5.TXDIS5.TAXCOLTMP + TXDIS5.TXDIS5.PNICOLTMP + TXDIS5.TXDIS5.FEECOLTMP ELSE 1

I've put the Define field in the report and can see one row with amounts in each of the fields in the formula; but the field returns 0.00.  What am I doing wrong?

  • GeneCobb
    GeneCobb
    371 Posts

    Re: Web Query Define Field

    ‏2013-07-25T20:56:57Z  

    Hi Brandy - Looks like that should work. I am wondering if the ampersand (&) in your IF logic is screwing things up. Could you try removing that AND condition to see if its the culprit?

  • bgdecker
    bgdecker
    24 Posts

    Re: Web Query Define Field

    ‏2013-07-25T21:19:09Z  
    • GeneCobb
    • ‏2013-07-25T20:56:57Z

    Hi Brandy - Looks like that should work. I am wondering if the ampersand (&) in your IF logic is screwing things up. Could you try removing that AND condition to see if its the culprit?

    Gene

    Changed formula to: IF ( TXDIS5.TXDIS5.YEARTMP LT 2012 ) THEN TXDIS5.TXDIS5.TAXCOLTMP + TXDIS5.TXDIS5.PNICOLTMP + TXDIS5.TXDIS5.FEECOLTMP ELSE 1

    No change in results; still returns 0.00.  Why won't the field return 1?

    Brandy

  • GeneCobb
    GeneCobb
    371 Posts

    Re: Web Query Define Field

    ‏2013-07-25T21:34:31Z  
    • bgdecker
    • ‏2013-07-25T21:19:09Z

    Gene

    Changed formula to: IF ( TXDIS5.TXDIS5.YEARTMP LT 2012 ) THEN TXDIS5.TXDIS5.TAXCOLTMP + TXDIS5.TXDIS5.PNICOLTMP + TXDIS5.TXDIS5.FEECOLTMP ELSE 1

    No change in results; still returns 0.00.  Why won't the field return 1?

    Brandy

    Brandy - what is the format of the define field? What happens if your expression/formula is just this:

    TXDIS5.TXDIS5.TAXCOLTMP + TXDIS5.TXDIS5.PNICOLTMP + TXDIS5.TXDIS5.FEECOLTMP

  • bgdecker
    bgdecker
    24 Posts

    Re: Web Query Define Field

    ‏2013-07-26T14:38:14Z  
    • GeneCobb
    • ‏2013-07-25T21:34:31Z

    Brandy - what is the format of the define field? What happens if your expression/formula is just this:

    TXDIS5.TXDIS5.TAXCOLTMP + TXDIS5.TXDIS5.PNICOLTMP + TXDIS5.TXDIS5.FEECOLTMP

    Gene

    The format is P15.2 and the three fields below are also P15.2.  Only one file in the report.  Using the formula below I get the right number.  I even scraped the original report and started over.  No change in the results.

    Thank you for your help so far.

    Brandy

  • GeneCobb
    GeneCobb
    371 Posts

    Re: Web Query Define Field

    ‏2013-07-26T15:10:58Z  
    • bgdecker
    • ‏2013-07-26T14:38:14Z

    Gene

    The format is P15.2 and the three fields below are also P15.2.  Only one file in the report.  Using the formula below I get the right number.  I even scraped the original report and started over.  No change in the results.

    Thank you for your help so far.

    Brandy

    ok..I wonder then if there is something wrong with the field TXDIS5.TXDIS5.YEARTMP. Is it a numeric field?

  • bgdecker
    bgdecker
    24 Posts

    Re: Web Query Define Field

    ‏2013-07-26T15:56:07Z  
    • GeneCobb
    • ‏2013-07-26T15:10:58Z

    ok..I wonder then if there is something wrong with the field TXDIS5.TXDIS5.YEARTMP. Is it a numeric field?

    Gene

    Yes, it is a nurmeric field.  I think I need to have the administrator make sure that Web Query is up to date.  While looking through the forum I ran across a couple of posts that are simular to other problems I'm having.  In particular: Drop Down List not functioning on Web Query reports, and Cant Create Synonym.  Let me get that done and see if this problem goes away.

     

    Thank you for your help.

    Brandy