Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: chinnucognos on 11 Jul 2014 05:55:09 AM

Title: Format Change for Integer type column
Post by: chinnucognos on 11 Jul 2014 05:55:09 AM
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
Title: Re: Format Change for Interger type column
Post by: BigChris on 11 Jul 2014 06:45:20 AM
Hi - could you cast it as a string then format it using substrings etc.?
Title: Re: Format Change for Interger type column
Post by: MFGF on 11 Jul 2014 07:21:06 AM
Quote from: chinnucognos on 11 Jul 2014 05:55:09 AM
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.
Title: Re: Format Change for Interger type column
Post by: BigChris on 11 Jul 2014 08:04:17 AM
QuoteDivide 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!
Title: Re: Format Change for Interger type column
Post by: MFGF on 11 Jul 2014 08:57:30 AM
Quote from: BigChris on 11 Jul 2014 08:04:17 AM
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. ;)
Title: Re: Format Change for Interger type column
Post by: cognostechie on 11 Jul 2014 07:05:58 PM
Quote from: MFGF on 11 Jul 2014 08:57:30 AM
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
Title: Re: Format Change for Interger type column
Post by: chinnucognos on 14 Jul 2014 06:24:23 AM
thanks for all ur help....
It worked with  "divide by 10000" logic...

Thanks you technies...

cheers,
chinnu
Title: Re: Format Change for Integer type column
Post by: chinnucognos on 14 Jul 2014 07:19:37 AM
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
Title: Re: Format Change for Integer type column
Post by: MFGF on 16 Jul 2014 07:02:11 AM
Quote from: chinnucognos on 14 Jul 2014 07:19:37 AM
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.
Text only | Text with Images