Format Change for Integer type column

[chinnucognos]

Hello everyone,


I have a requirement which i need to convert existing TELEPHONE_NUMBER( datatype=Int64) query item having data as "4,567,567,335" to convert data as  "456-756-7335".
Can you guys help on this.

thanks in advance

[BigChris]

Hi - could you cast it as a string then format it using substrings etc.?

[MFGF]

Hello everyone,


I have a requirement which i need to convert existing TELEPHONE_NUMBER( datatype=Int64) query item having data as "4,567,567,335" to convert data as  "456-756-7335".
Can you guys help on this.

thanks in advance

You could perhaps use formatting for this? Divide the item by 10000 in a query calculation, then in the properties of the calculation set the data format to Number and set the decimal separator to - and the thousands separator to -

Would this work?

MF.

[BigChris]

Divide the item by 10000 in a query calculation, then in the properties of the calculation set the data format to Number and set the decimal separator to - and the thousands separator to -

Now that's sneaky...I like it!

[MFGF]

Now that's sneaky...I like it!

Sneaky is my middle name! :)

Well, actually, it isn't. I have two middle names, and you can probably guess their initials. ;)

[cognostechie]

Sneaky is my middle name! :)

Well, actually, it isn't. I have two middle names, and you can probably guess their initials. ;)

I guessed it. It's Fantastic Guru  ;D

[chinnucognos]

thanks for all ur help....
It worked with  "divide by 10000" logic...

Thanks you technies...

cheers,
chinnu

[chinnucognos]

Hi Techies,

now one more issue arised..the above logic working fine in pdf or html outputs.
But output is not exactly matching in excel 2007 format.
suppose telephone=4,156,156,156
after implimenting logic,
in pdf=415-615-6156
in html=415-615-6156
in excel=415,615.6156( which is not correct)

can you help on this,,,,

Thanks in advance

[MFGF]

Hi Techies,

now one more issue arised..the above logic working fine in pdf or html outputs.
But output is not exactly matching in excel 2007 format.
suppose telephone=4,156,156,156
after implimenting logic,
in pdf=415-615-6156
in html=415-615-6156
in excel=415,615.6156( which is not correct)

can you help on this,,,,

Thanks in advance

Hi,

From what I can see, this appears to be a limitation of Excel rather than anything odd Cognos is doing. Excel doesn't seem to support the option of using - as a thousands separator or as a decimal separator.

Two solutions spring to mind:

1. Delete Excel from everyone's computer and forbid it ever to be installed again.
2. Use a different approach to achieve the formatting in the report.

Assuming my favourite option proves to be unworkable, you will be stuck with the second:

substring(cast ([TELEPHONE_NUMBER], char(10)),1,3) + '-' + substring(cast ([TELEPHONE_NUMBER], char(10)),4,3) + '-' + substring(cast ([TELEPHONE_NUMBER], char(10)),7,4)

Cheers!

MF.