7 Replies Latest reply on Feb 14, 2013 2:24 PM by SriniVEERAVALLI

    Day order by

      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.

        • 1. Re: Day order by
          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
          1 person found this helpful
          • 2. Re: Day order by
            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

            • 3. Re: Day order by
              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
                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.

                • 5. Re: Day order by
                  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
                    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
                      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 :)