Calculated Field Creation (Subtraction of VARCHAR data possible?)

[pjewett]

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

[MFGF]

Wow! It's been so long since I've used Impromptu I have plain forgotten the list of functions! Yes - your general approach is right - you need to use a function to convert the varchar strings into numbers, then subtract these results. I'm pretty sure cast() is not an available Impromptu function, but something like String-to-Integer() or String-to-Number() maybe? Perhaps these are PowerHouse functions, though - it's been so many decades I since I used them they have blurred together into indistinction :) If you look through the list of functions available when creating your calculation, are there any that look similar to these?

Cheers!

MF.

[pjewett]

:(  Yea I hear that a lot!  Long story behind our continued use of Impromptu.  Anyway thanks that worked! I actually became distracted with altering the statement that creates that table each night so that those fields were integer and that worked..mostly. But some of our existing reporting didn't like that change and I thought perhaps I would have to exit that table from our catalog and reimport it to fix that issue but seemed to heavy handed for this task.

I'm good to go! Thanks very much sir!

[MFGF]

Oh good! Glad you managed to get it working :) Thanks for the update!

MF.