Convert MUN to MMDDYYYY format

[jasp]

Hi All,
I have a x tab report where i have a prompt page with a month selection/ parameter on it . This Parameter is then displayed in the column of a xtab.

What i want is for the date to be displayed in MMDDYYYY format.

The Mun that is being generated from the cube is as follows :

[Finance Cube].[Time].[All Years].[Month]->:[PC].[@MEMBER].[20080101-20080131] , when i try the formatting option nothing happens all that is displayed is Jan/2008.

Does anyone know how i can convert the above to display on a report to show Jan 31, 2008.

Thanks

[CognosPaul]

The easiest way to do this would be to add an item to the attributes of the month level. Set the Short Name of the level to the string you want displayed, and use that as the display item in your crosstab.

If you can't do that, for whatever reason, you can use the report expressions to convert the incorrect string to a date, then use the regular Data Format property.

The following is based on the Sales and Marketing cube in Cognos 10, but it will work perfectly in Cognos 8.

• In the query drag in Month - Category Code.
• In the crosstab, click on the Month node, in the properties row of the properties check Month - Category Code
• In the crosstab, click on the Month node. Change the Source Type to Report Expression.
• The row immediately below Source Type should now be Report Expression. Double click on it.
• Paste in the following expression string2date (substring ([Month - Category Code],16,10))
• Set the Data Format on the node to be Date, Date Style Medium

If all goes well you should get the results your expecting.

[jasp]

Paul,

i tried the below, but got a little lost from step 2....i could not find the properties row u refer to.

I followed the steps and it gives an error. It says contact administrator.

Any help

[jasp]

Hi Paul,

i finally got it to work, using your approach.

Thanks for your help much appreciated.