Sorting in order of Calendar Month in BEx

[former_member184551]

Hi All

I have searched this forum and all over the web, could not find a solution hence turning to SCN for help.

I have a BEx query based on a BPC infocube. There is a time dimension there called Monthnum, but this is a text field with type char and length 2 at the BW level and has values for calendar months ( from 1 to 12), the problem is, since this is a text, it is being sorted at Bex level as follows ; 10,11,12,1,2,3,4...and so on, I am using this BEx query in a dashboard we built in the Design Studio and business wants to see this sorted in order of Calendar Month. I want to keep the option of changing the BPC data model in the backend as my last option, is there a way to achieve the correct sort order in Bex ? i,e, to say sort this text as key (1,2,3,4....12)

All help will be greatly apprecieated.

Thanks

Sameer

[Loed · Accepted solution]

Hi,

My suggestion is to add an attribute (let's call it ZSORT with NUMC type length 2) to MONTHNUM field. Enter a value of 1 to 12 to your ZSORT for the 1 to 12 values of MONTHNUM.

MONTHNUM --- ZSORT

1 --- 1

2 --- 2

..

12 --- 12

Then in query, sort the MONTHNUM using the attribute ZSORT.

Regards,

Loed

[former_member231709]

You can try to use BADI RSROA_OLAP_BADI and method COMPUTE_TABLE for custom sorting output query table.

[giselle_lin]

Perhaps just in the text table in this iobj to use text 01, 02, 03, then in query result display and sort by text not key. To change the key have to change the model

[giselle_lin]

Perhaps you can try to use 01, 02, 03... as text instead of 1, 2, 3...

It may not look nice as 1, 2, 3... but otherwise the model has to be changed

[former_member184551]

Thanks for taking a shot at it, that is exactly what I thought, but the problem is, how do I add the leading zero without changing the data model ? Is there a way I can make this minor "enhancement" to the value of an InfoObject in the BEx query designer itself ?

Thanks

Sameer