This discussion is archived
9 Replies Latest reply: Oct 9, 2012 6:24 AM by GRK RSS

Sort Values of Day

Manu Newbie
Currently Being Moderated
Hi All,

i have Day column where i get day names as Mon Day to Sun Day, but when they are not in sort Order from Monday to Sunday... i am getting as in gigag saturday thursday monaday... in this order. how can i get sort these columns in Subject area so that even though i create n no.of reports i can get sot day names in all reports.

Can some one help me...

Thanks
Manu!!
  • 1. Re: Sort Values of Day
    David_T Guru
    Currently Being Moderated
    Manu wrote:
    Hi All,

    i have Day column where i get day names as Mon Day to Sun Day, but when they are not in sort Order from Monday to Sunday... i am getting as in gigag saturday thursday monaday... in this order. how can i get sort these columns in Subject area so that even though i create n no.of reports i can get sot day names in all reports.

    Can some one help me...

    Thanks
    Manu!!
    Manu, I understand that English may not be your first language for you (and for many others as well), but that just means it is more important to make sure you proof what you write. Unfortunately, because this is a free forum, if people have a hard time understanding your question, they may just skip it. You spell "Monday" two different ways (Mon Day and monaday), you spelled "sort" correctly in the Subject line and in two different places, but then spelled it "sot" at the end of the sentence. Finally, I'm not sure your understanding of when to capitalize a word, but at the very least, capitalize the first word of a new sentence. It helps to when you have completed your thought and are starting a new one. This is not a criticism, just a way to help you get a better response from others. :) (I think one of the words you meant to write was "zigzag" not "gigag," but I may be wrong.)

    As far as your question/issue:

    Sorting all depends on the type of column you have. Since you have a CHAR column, sorting will be alphabetical, not the logical way you would do it by when the day actually occurs in the week. To sort the way you want, you basically have to "convert" the column to way that putting the "sorting" on would do it the way you expect.

    If you have a column that has values "Monday, Tuesday, Wednesday...," then get another instance of this column, click on the fx button, and create a BINS out of this.

    When the value is Monday, call it 01, if it is Tuesday, call it 02, etc.

    Now sort this column. Put it at the beginning of your Table and hide it. If you have a pivot table, put it at the beginning of the Rows section, sort it and hide it.

    This is how you can do it.
  • 2. Re: Sort Values of Day
    MuRam Expert
    Currently Being Moderated
    Hi,

    Have a column and pull it in the rpd for sorting purpose with 1,2,3,...for corresponding days and check the sort order for the days column based on the sorting column.
    Or you can also have this column pulled in presentation layer and use the column always when days column is used and sort it.

    Refer-
    http://obiee101.blogspot.com/2008/11/obiee-sort-order-column-property.html
    http://www.biconsultinggroup.com/obiee-tips-and-tricks/setting-a-sort-order-column.html

    Hope this helped/ answered

    Regards
    MuRam
  • 3. Re: Sort Values of Day
    prassu Pro
    Currently Being Moderated
    Hi,
    You can sort the column based on another column @RPD

    BMM layer--->properties of the day of the week-->General--->Sort order column ---->day_number_of_week--->then data will display as required....

    But the day_number_of_week monday should be 1 and sunday should be 7........

    mark if helpful/correct....

    thanks,
    prassu
  • 4. Re: Sort Values of Day
    Manu Newbie
    Currently Being Moderated
    HI

    Thanks for your suggestion.

    I know how to get sort values at report level, but i need to do it in repository.

    Thanks
  • 5. Re: Sort Values of Day
    prassu Pro
    Currently Being Moderated
    you better to read all the replies which you got........The earlier posts may useful to you @RPD...........
  • 6. Re: Sort Values of Day
    Manu Newbie
    Currently Being Moderated
    Hi Prassu,

    This really helpful to me..
    But can you please give me more information since the column which i am work is not physical column, it is logical hierarchy column. Can you please give some more clarification where i have to use day_number_of_week.

    Thanks
    Manu!!
  • 8. Re: Sort Values of Day
    prassu Pro
    Currently Being Moderated
    Manu,
    If it is a logical hierarchy column means it also a logical column.So follow the steps as i mentioned in the earlier post on the logical column.Why i am advising you to take day_number_of_week ,because the column name will be sorted based on the day_number_of _week.

    day_number_of _week   values like 1,2,3.....7


    mark if helpful/correct...

    thanks,
    prassu
  • 9. Re: Sort Values of Day
    GRK Journeyer
    Currently Being Moderated
    Hi Manu,

    Create a lookup table in your database or else, physical layer - such that if you want to start week from monday and end to sunday.

    Day column, Day id

    Monday 1
    Tuesday 2
    .....

    Sunday 7

    Now, join this table to your table from which column Day is pulled.

    Now, either you can click on column in rpd and say sort by day id.

    Or else,

    In your report just pull Day Id in front of Day column. And, click on sort symbol.

    Thank you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points