Data format

[niahanth]

Hi,

In one of my report i have column SSN which is stored in database as XXXXXXXXXX, but i need to display as XXX-XXX-XXXX.
Can anyone tell me how to achieve it.

Thanks

[Penny]

Hi

I had a similar problem with custom patterns in Cognos 10.2.1.  Our Cognos 7 environment used them all over the place, but I couldn't get the custom patterns to work in Report Studio 10.2.1.  I created a PMR with IBM and they confirmed that the custom pattern would not be able to format the data the way I wanted to.  I was working mainly with character data.   Here is an excerpt from the PMR response from IBM regarding the custom patterns:

...the custom pattern will not be able to format our data the way you require. It is more used for dates and scaling (as outlined in the description for custom patterns). I apologize for the inconvenience this may cause you..

I've taken the time to create a more efficient workaround for you regarding the problem. You mentioned that the solution you're currently employing is to use substrings, concatenation and if/else clauses. The following expression (depending on the format of the numbers in the datasource) may prove to be a little easier to use (as it isn't using if/else clauses):

'(' + substring([Data Item1],1,3) + ') ' + substring([Data Item1],4,3)+'-' + substring([Data Item1],7,4)

Additionally, for the postal code, you can employ the following:

substring([Data Item1],1,3) + ' ' + substring([Data Item1],4,3)


Not sure if this helps you or not.  I tried many different ways before creating the PMR.

[bdbits]

Not always true, but if support tells you a pattern won't work, it probably won't.

I only chime in here to note that if you need plan to use this all the time, it should really be done once in the Framework Manager model. Then you have a consistent format everywhere, and nobody has to remember/copy/paste the syntax as shown.

Have a great weekend.

[niahanth]

thanks Penny