This discussion is archived
7 Replies Latest reply: Feb 14, 2013 6:24 AM by Srini VEERAVALLI RSS

Day order by

user607649 Newbie
Currently Being Moderated
Hi
i am using 11g.I have 2 prompt one date between and scond one is day.
Now day prompt shows Fri,Mon,Sun, like in order.Now i want to set orader like Sun,mon,Tue,Wed like.
How can i set.

Thanks
Gram
  • 1. Re: Day order by
    Srini VEERAVALLI Guru
    Currently Being Moderated
    If you are using OOB W_DAY_D then there would be a column like DAY_OF_MONTH which has 1 to 31.

    For that date column in BMM layer; COlumn properties-> 'Sort order colum' select DAY_OF_MONTH column.

    If helps mark

    Edited by: Srini VEERAVALLI on Feb 13, 2013 11:48 PM
  • 2. Re: Day order by
    user607649 Newbie
    Currently Being Moderated
    Hi
    I have date column.So i set "DayName" column properties sort column order i selected "date" column.Now if i create pompt it repeated multiple times like Sun,Mon,Tue,Wed,Thu,Fri,Sat Again Sun,Mon,Tue,Wed,Thu,Fri,Sat............................
    I trying to put distinct but it return error.How to avoid multiple entry.Is there any other way solution


    Thanks
    Gram.
  • 3. Re: Day order by
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Look for the column in w_day_d table which have values as DAYOFWEEK
    This would show only 7.
    if you dont have it then create logical column using DAYOFWEEK(date) and use it for sort.

    This should work for you.
  • 4. Re: Day order by
    user607649 Newbie
    Currently Being Moderated
    I created Logical column dayName(date) and DayOfweek(Date).In DAYNAME column properties i set order by DAYOFWEEK.
    After i selected new sample report selected DAYNAME then values displaying correct Sun,Mon,Tue....Sat. But same column i selected prompt.In prompt shows multiple values again.
    Thanks for your reply veera.

    Thanks
  • 5. Re: Day order by
    Srini VEERAVALLI Guru
    Currently Being Moderated
    I'm not sure why its not working in prompt. I hope you are using same in the prompt too.
    Any way try this:
    In the prompt use SQL Results and use
    Order by dayofweek(date)

    That should help you.

    If help mark :)
  • 6. Re: Day order by
    user607649 Newbie
    Currently Being Moderated
    Thanks a lot veera.Fixed.

    I used like below
    SELECT Time.DayName FROM "Sample Sales Reduced" Order by Time.DayOfWeek
    Now working fine.

    Yes i selected same dayName only in prompt.But when i select logic sort,It not displaying distinct values(Mutiple Values).

    Anyway thanks.If you any idea then share us thanks.
  • 7. Re: Day order by
    Srini VEERAVALLI Guru
    Currently Being Moderated
    You may go for distinct using SQL.
    Sort by column is suppose to relevant to the column.

    For day Names you used dayofweek since there are 7 day names and 7 from dayofweek.
    Just in case you got 8 you may end up 2 sundays.

    Since you have option to use SQL so got so many options.. just explore it.

    for issues let me(us) know where are there to help :)

Legend

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