2 Replies Latest reply on Sep 28, 2016 6:04 PM by Thomas Dodds

    Arranging date ranges


      Hi all,


      I have a report requirement in the following way.


      and i have build the report in the following way. Please see the crieteria.

      I have calculated the date range like this


      for 12AM- 8 AM  the formula is CASE WHEN HOUR("Date 5") >= 0 AND HOUR("Date 5") < 8 THEN '12AM-8AM' END

      similarly for others i have calculated in the same manner.


      I am unable to do the following things.


      1. How to put hours range (12AM-8AM,8AM-4PM,4PM-12AM) in the above the report requirement order i.e one below the other?

      2. How to calculate <1hr, 1-2 hrs,2-3 hrs,>3 hrs columns as shown in the above report requirement?


      I have a limitation to access rpd and database, so i have to do all at the report level only. I am using OBIEE 11g.


      Request you please help me.




        • 1. Re: Arranging date ranges
          Gianni Ceresa

          Hi, for 1) if you really want to be sure about your order create a second column with the same CASE WHEN but returning a value (number) you use for sorting. You then make this column as hidden and add it just before the real column with the 12am-8am etc. values. In that way sorting on that numeric hidden column your sorting will always be fine.


          For 2) depends what does these things means for you ... It's your business which is supposed to tell you how to calculate things.

          We can of course give you random answers on the business rules you can apply to your column but I guess your business users will probably disagree...

          • 2. Re: Arranging date ranges
            Thomas Dodds

            #2 - use of information drives physical design ... why not have the values already calculated and stored at the intersection of timeofday and duration -- these should be dimensions which drive the grain of your fact table