creating fixed length fields in Cognos

[lymi6977]

I am new at Cognos and working contract for a company using Cognos version 8.4.
I have used Crystal Reports since version 4, and I am seeing so many functional differences between the two.

I desperately need someone's expertise. I have searched on the Internet and cannot find a solution.

I need to create several fixed length fields.
The report will be exported for import into a legacy system.

Here are a few of the fields I need to convert to fixed lengths, as they appear in Crystal now (for those who know Crystal too) --- need to know if these functions are available in Cognos and if so, how to use them:
1st one --- {ARITEM.ARIACCTYP} & ToText({ARITEM.ARIACCOUNT},"0############") & "0PAID"
2nd one --- "CK" & ToText({ARITEM.ARISERIAL},"000000####") &
ToText(ToNumber(Replace(ToText({ARITEM.ARIPAIDAMT},2),".", "")),"0000000000") &
ToText({@Paid Date}, "MMddyy") & {ARITEM.ARIPAYEE}

Thanks so much!
Sheila Snyder
sheila.snyder@capitalbank-us.com

 

[blom0344]

ToText and ToNumber functions should be the CAST function in Cognos. The & can be substituted with ||

I have no idea what you mean with "0##########" Is this some sort of formatting?

Ties Blom

 

[lymi6977]

I believe the Crystal formula with the "0##########" is adding a leading zero and the "#" represents the length of the field.

With the cast function, what is the syntax for Cognos 8.4 to dictate the fixed length?

If I have a numeric field that I am converting to varchar, I would normally do this:
cast_varchar([fieldname])

Do you know what I would add to force this to always be 10 characters with leading zeros as filler?

Thanks!

 

[blom0344]

Within Report Studio you can acces all available Cognos (and Native ) functions. I do not think there is a Cognos function for either left or right padding, but you can work around this by using a case statement:

CASE 
WHEN CHARACTER_LENGTH([SOMEITEM]) = 1 THEN '000000000'||[SOMEITEM]
WHEN CHARACTER_LENGTH([SOMEITEM]) = 2 THEN '00000000'||[SOMEITEM]
WHEN CHARACTER_LENGTH([SOMEITEM]) = 3 THEN '0000000'||[SOMEITEM]
WHEN CHARACTER_LENGTH([SOMEITEM]) = 4 THEN '000000'||[SOMEITEM]
WHEN CHARACTER_LENGTH([SOMEITEM]) = 5 THEN '00000'||[SOMEITEM]
WHEN CHARACTER_LENGTH([SOMEITEM]) = 6 THEN '0000'||[SOMEITEM]
WHEN CHARACTER_LENGTH([SOMEITEM]) = 7 THEN '000'||[SOMEITEM]
WHEN CHARACTER_LENGTH([SOMEITEM]) = 8 THEN '00'||[SOMEITEM]
WHEN CHARACTER_LENGTH([SOMEITEM]) = 9 THEN '0'||[SOMEITEM]
ELSE [SOMEITEM]
END

Obviously, you need to cast the the original item first to a string. If you want to keep things readable, you can also use 2 dataitems , first using a CAST and then using this one in the above CASE construct.

Ties Blom

 

[lymi6977]

Perfect!
Thanks so much for your help!

 

[drlex]

For padding, could one just use the RIGHT function, together with a lead string?

RIGHT('000000000'||[SOMEITEM],8)

(Need to be sure that [SOMEITEM] is neither NULL, nor exceeds the limit.)

soi là, soi carré

 

[blom0344]

Yes, if you decide to use native functions (and make sure to publish the db function set) . We stick to using Cognos functions as much as possible to ensure database independency..

Ties Blom