Sorting Date with Month Name in a Prompt?

[bonniehsueh]

I need to display the dates in the format below (data from a cube pulling from a SAP BW infoquery). The values are sorting alphabetically, how do I sort these values chronologically in the prompt?

Jan 2007
Feb 2007
...
Jan 2008

I tried to sort by using a numeric field in the 'Use Value' and setting the Display Value with the column showing Jan 2007 however its not sorting.

[josepherwin]

Can you create another column where the data item is month number || year?

therefore you will get
012007
022007
032007

and use this value to sort the month on display.

[bonniehsueh]

I kept the display value using the Month Name and tried using a column with these values V32007001 to sort it with Advanced sort on the prompt control and it did not change anything.

Also set the sorting of the new column at the Query. So I've abandoned using the month name as a display amd instead directly display the numeric value, however, when I use a substring to take out the V3 substring(new_column,3,10) it gives me this error.

CCLAssertError:0:Fatal: CCL_ASSERT(pMUN && !(*pMUN).empty()); RSV-SRV-0042 Trace back: RSReportService.cpp(791): CCLAssertError: CCL_CAUGHT: RSReportService::process() RSReportServiceMethod.cpp(217): CCLAssertError: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_Request RSASyncExecutionThread.cpp(690): CCLAssertError: RSASyncExecutionThread::checkException RSASyncExecutionThread.cpp(204): CCLAssertError: CCL_CAUGHT:

This is coming from a cube. The column is a attribute of the Month Level in the Time hierarchy. Any ideas on why this is happening and what I can do?

Thanks in advance

[phanicrn]

can you do advance sorting

extract year & month from date column, write aase statement for month , it worked for me,,