RS Data Format Text Pattern not working

[Penny]

Hi everyone

I have been unable to apply a pattern to a text query item in Report Studio.   I am creating relational reports against an Oracle database using Cognos 10.2.1 FP3.
My report output is correct except that I cannot seem to apply the pattern to the text field.

I have tried setting the Data Format property of the column in question.
Using format type -> text, with custom pattern @@@-@@@-@@@@, but the output doesn't change.
My text field is actually a 10 digit phone number stored as text. 

I have used the data format property in Report Studio to format date fields, but can't seem to get the text fields working.

Am I missing something simple?

Thank-you in advance for any advice.

[adam_mc]

Hi...

I'm not sure about the formatting of text fields either, but to avoid this, I have done the following:

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

Hope this helps, or at least keeps you moving until someone else gives a better solution.

Thanks,
Adam.

[MFGF]

Hi everyone

I have been unable to apply a pattern to a text query item in Report Studio.   I am creating relational reports against an Oracle database using Cognos 10.2.1 FP3.
My report output is correct except that I cannot seem to apply the pattern to the text field.

I have tried setting the Data Format property of the column in question.
Using format type -> text, with custom pattern @@@-@@@-@@@@, but the output doesn't change.
My text field is actually a 10 digit phone number stored as text. 

I have used the data format property in Report Studio to format date fields, but can't seem to get the text fields working.

Am I missing something simple?

Thank-you in advance for any advice.

Hi,

I don't think patterns work this way with text. You can see this id you enter the pattern above in the Pattern property of the data format dialog and look at the Sample area at the bottom - it has no effect. You could either convert the item to a number with a cast() function then apply a numeric format (###-###-####) or else use use the approach suggested by Adam.

Cheers!

MF.

[Penny]

First of all thank-you both MFGF and adam_mc.  I appreciate the answer even though I find it disappointing.

The character string I am working on display for is actually returned by a database function call.  I had trouble casting it and so tried to create another calculated query item using the results of the first, but I am still having trouble.

First calculated query item called 'calc PA Phone' contains a function call - functionname(field, field, field) that returns a string.
Second calculated query item uses cast function on the first item - cast([calc PA Phone], integer).  This validates ok.
When I run it, it crashes with an ugly Traceback that is impossible to decipher.  RSV-SRV-0042.  Is it failing because there are null values?  I tried it first using cast on the function call itself but had the same problem.
I checked to make sure that datasources in the model have limited local processing.

I wanted to avoid calling the functionname more than once which is why I preferred the cast, but also tried using adam_mc's solution.  Although I will use the oracle function substr, and have to use concat instead of +.  Expression looks like this:

concat(concat(concat(concat(substr([calc PA Phone], 1, 3), '-'), substr([calc PA Phone], 4, 3)), '-'), substr([calc PA Phone], 7, 4))

This works but now I realize that if there is no phone number the output is -- so I will have to make the expression even uglier by using nvl function.  Working on this now.

Seems very difficult for something that should be easy.  Also makes Report Studio writing painful as I am not sure how this can be placed in the model when I am using a function call.  Any suggestions for the model so that the report author doesn't have to deal with this. 

[BigChris]

Can you use the || function? You ought to be able to end up with something along the lines of:

if([calc PA Phone] is missing) then (NULL) else (
substr([calc PA Phone], 1, 3) || '-' || substr([calc PA Phone], 4, 3) || '-' ||  substr([calc PA Phone], 7, 4))

And I've put that sort of thing into FM models before now...

[Penny]

Yes that is better, thanks very much.   I will try to add this to the model.