Setting object format for drill filter

Hi All,
I did a custom sort ing on the month name and i did that with the help of the following thread

--------------------------------------------------------------------------------------Mike.

I can’t find your original posting - but if what you’re trying to do is display month NAMES in CHRONOLOGICAL order as opposed to numeric - try this. I got it from BO support. It works fine.

The way to do this in the universe is to create a MONTH NUMBER object with the following
syntax:

TO_DATE(‘01’ || ‘/’ || SUBSTR(TO_CHAR(column date),4,2) || ‘/’ || ‘1900’ || ’ ’ || ‘00:00:00’)

After this, rightclick on the object - Object Format - Number tab - Properties box(Date/Time) - “Mmm” - OK
( define a new object format. Add the format “Mmm” and select it.) <<

Darren.


Now I am able to sort the monthnames but in the drill filter the formatted value is not coming.In the drill filter i am getting

1.1.1900 12.00.00.00AM
1.2.1900 12.00.00.00AM

instead of

‘Jan’
"Feb’

Please guide me to change this format

Thanks,
Pradeep


pradeep.bm :india: (BOB member since 2004-06-09)

The solution you copied is also inserting 01 as a date, 1900 as a year, and a time stamp. If you don’t want all that, try creating the object as follows: TO_CHAR(date,‘Mmm’) - this will return just Jan, Feb, etc.


amybz :us: (BOB member since 2004-07-21)

Hi,
Thanks for the reply Amy.But no doublt if you do like that you will get ‘jan’, ‘Feb’ but it will not be sorted in drill filter and you will loose the sorting in webi report also

regards,
Pradeeo


pradeep.bm :india: (BOB member since 2004-06-09)

If the filter is made off that object it will display as Jan, Feb, etc. But you will lose the monthly sort, it will sort alphabetically instead. You could change the Mmm format to MM and get 01, 02, 03, etc instead. Or concatenate the two to get something like 01Jan (or add dashes or slashes or underscore, or whatever between the two).

Examples:
to_char(date,‘MM’)||to_char(date,‘Mmm’) will result in 01Jan
to_char(date,‘MM’)||’_’||to_char(date,‘Mmm’) will result in 01_Jan


amybz :us: (BOB member since 2004-07-21)