Topic
  • 3 replies
  • Latest Post - ‏2013-06-19T16:43:49Z by OpherB
pjewett
pjewett
2 Posts

Pinned topic Calculated Field Creation (Subtraction of VARCHAR data possible?)

‏2013-06-05T16:55:48Z |

Hi All

(Using Cognos 7.3 Admin)

Not exactly a newbie to Cognos anymore really but have not created "Calculated Fields" yet but need to and have run into a problem.

I need to create a simple calculated field that will subtract a "received quantity" field from an "order quantity field" in one of our db's. However this information is ingested each night from our financial db into a table in our "biblio" db and the fields are varchar. I assume this is why I'm not seeing a way to subtract these fields in the "available components" list when I try to create the calculated field.

Is there a way I can "cast" these fields as numbers or otherwise "fool" Cognos into seeing them as numbers so I can subtract them?

Trying to avoid changing the table itself as it would involve changes in sprocs & other reporting.

Any help would be MUCH appreciated.

Thanks

  • OpherB
    OpherB
    5 Posts

    Re: Calculated Field Creation (Subtraction of VARCHAR data possible?)

    ‏2013-06-17T23:39:10Z  

    Been a long time since I've used Impromptu, but there should be a function with a name like "char_to_num" that will do the conversion for you. You'll have to do both values before performing the subtraction.

    Good luck,
    Opher

     

  • pkandima
    pkandima
    2 Posts

    Re: Calculated Field Creation (Subtraction of VARCHAR data possible?)

    ‏2013-06-18T09:55:34Z  

    I am not having much knowledge on cognos 7.3

    but i think we can subtract the values

    syntax is substract (abcd, from 1 to 2)

    the result will be ab

     

    hope this helps

    Thanks

  • OpherB
    OpherB
    5 Posts

    Re: Calculated Field Creation (Subtraction of VARCHAR data possible?)

    ‏2013-06-19T16:43:49Z  
    • pkandima
    • ‏2013-06-18T09:55:34Z

    I am not having much knowledge on cognos 7.3

    but i think we can subtract the values

    syntax is substract (abcd, from 1 to 2)

    the result will be ab

     

    hope this helps

    Thanks

    How will that help him "...subtract a "received quantity" field from an "order quantity field" in one of our db's"