Month subject OBIEE sort order

User_LL6E2

Hi all. Ive read a previous thread and just need a little more help on the sorting of the month column into chronological order instead of alphabetical. The correct syntax is then put in the 'edit column formula field'?

month.png

Uploaded 9:44 am109.3 KB

Arjun provided the below answer I assume I am to amend and replace, but to what?

My thinking if using January as an example. Is the below anywhere near correct?

WHEN ("TableHeading"."Month1"='JAN' ) THEN '1'

Below is the syntax -

CASE

WHEN ("TableHeading"."ColumnName1"='Value1' ) THEN '1'

WHEN ("TableHeading"."ColumnName2"='Value2' ) THEN '2'

WHEN "TableHeading"."Column Name"='Value3' THEN '3'

ELSE "TableHeading"."Column Name"

Dori S.-Oracle

Yes this essentially correct, if those MONTH values are literally 'JAN' , 'FEB', etc. A caveat, is '1' is a string, like '12', and you don't want a sort order of '1', '12', '2', '3', ... so you will want to use number values, not strings OR use '01', '02', .., '12' as chars.

There is much more on this topic I could say, but don't want to overload you without knowing your exact situation. However, if you have additional questions, let us know.

User_LL6E2

Hi Doris - Thanks. Very new to R&A as a hotel manager wanting to produce a report but the subject month column is creating a messy report. I attached the column formula for month but need what Im placing in here to make it present in chronological order.

month.png

Mallikarjuna Kuppauru-Oracle

Hi @ User_LL6E2 ,

In general we will recommend to develop the Sorting order Logic at RPD layer only instead of reporting level.

How is your data in month column? like its an Month name or numeric . What is the expected output , can you give some example to suggest further.

Regards,

Arjun

User_LL6E2

Hi Arjun - the data in the month column is list alphabetically. Ive attached a screen shot if that helps.

I would like it to display from APR - MAY - JUN through to MAR

Screenshot 2023-12-12 150830.png

I have read an article on RPD and it seems alot easier to do it this way but I think I will need to request access to that.

Mallikarjuna Kuppauru-Oracle

Hi @User_LL6E2

You need to write an column level case statement as below -

You can change the order of the month, for example here i have given order from Jan - DEC (1-12)

If you want to display order 1 as 'APR' you can change below formula order and apply sorting on column level.

Write the formula as below -

case when "Calendar"."Month" = 'JAN' then 1

when "Calendar"."Month" = 'FEB' then 2

when ""Calendar"."Month" = 'MAR' then 3

when "Calendar"."Month" = 'APR' then 4

when ""Calendar"."Month" ='MAY' then 5

when "Calendar"."Month" = 'JUN' then 6

when "Calendar"."Month" = 'JUL' then 7

when "Calendar"."Month" = 'AUG' then 8

when "Calendar"."Month" = 'SEP' then 9

when "Calendar"."Month" = 'OCT' then 10

when "Calendar"."Month" = 'NOV' then 11

else "Calendar"."Month" end

select the column and apply the sorting

By default order :ASC

Regards

Arjun

Dori S.-Oracle

One benefit to using the RPD is you can set the display column to have a different column to use for sorting. This feature is helpful if you have a date dimension that is a string/character, rather than an actual date data type. The custom sorting will be automatic when the field is used and you don't need to bring the sort column into the analysis.

However, the RPD is not required to get the functionality you need on this one analysis.

You can still create this sort key column with the formula above and add a sort within the analysis. You can also make this column hidden in your table and place it to the left of the date display column. This should work for the sample table in your screenshot without also exposing the sort number values.

Mallikarjuna Kuppauru-Oracle

Hi @User_LL6E2

If you feel we have answered you question, can you accept the answer so that other community user can refer the same.

Regards,

Arjun

User_LL6E2

Hi Arjun - Thankyou for the case statement. So I place this in the edit column formula field of the month column?

I see your using alternate calendars though which I dont appear to have. Sorry so new to this and its a very simple report I trying to create.

Screenshot 2023-12-13 083833.png

Mallikarjuna Kuppauru-Oracle

Hi @ User_LL6E2

Are you able to resolve your issue? After copying above cast statement in report column

Regards,

Arjun

User_LL6E2

Hi Arjun,

Ive attached a screenshot of the case statement and the error codes I get. I assume Im doing something incorrectly?

month.png