Month Nbr Formating

[former_member402770]

Hi,

Iam using Oracle 11g and BO SP5.

In Universe:

1) iam trying to display values YearMonth Dimension in format 2014008.

I have Fiscal year which is coming from database in the format as = 2010......2015

I have Fiscal Month which is coming from database in the format = 1.....12

My Need is to display as 2014008 format, i tried like below

@Select(cc\Fiscal Year) ||'00'|| @Select(cc\Fiscal Month) = > 2014001

                                                                                           ....

                                                                                           ....

                                                                                           20140010

                                                                                           20140011

                                                                                           20140012

above i have an issue while iam trying to format fiscal month 10 onwards it is prefixed with two zeros..which 20140010 instead 2014010.

2) i have period name which is in format Apr how do we change to 004 format.

Appreciate your help.

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

use case statement for this in universe.

example

Case

when

length @Select(Date\Calendar Month)=1

then

(@Select(Date\Calendar Year))||'00'||@Select(Date\Calendar Month)

else

(@Select(Date\Calendar Year))||'0'||@Select(Date\Calendar Month)

end

Amit

[former_member402770 · Accepted solution]

Hi Amit,

Thanks for your quick help,  Great works charm on the 1st point..

Any thoughts on 2nd point

2) i have PeriodName Universe Object which is display in format Apr how to make an this object to display in format 004.

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

use this.assuming month data type is already character.

to_char(to_date(@Select(Date\Calendar Month),'MON'),'MM')

data type of this will be character.

Amit