I have incidents counts happend for each hour in a day. The day is defind in shift hours- which are 8 hour shifts and 12 hour shifts. The data in shift table is flatned. like this....
8 hour shift … 7 am - 2:59 pm …7 am- 7:59 am … same for 12 hour
8 hour shift… 3 pm- 10:59pm …9 pm - 9:59 pm…
8 hour shift … 11 pm - 6:59 am…6 am - 6:59 am…
The user wants to see it in object. When we pull this object in Webi - drill mode, then it should show the shift hierarchy starting from “All Shift Hour” under that it should show “8 Hour Shift” and " 12 Hour Shift" under each shift it should show the “shift Hour intervals” under each shift hour interval it should show single hour intervals…
any ideas how can I set the logic in the object/s…thinking of CASE but not sure
by the way we are using Teradata,BO XIR3 and WebI
Not sure what you’re asking for exactly, but you should be able to “chunk” up your days into 8,12 hour blocks by dividing the hour by either 8 or 12. That will give you the “hour group”, then inside the “hour group”, you can use the MOD function to determine which single hour increment it is without the grouping.
I have SHIFT table with all the values, and I had joined this table with 1:M to the fact table based on incident ID...
In the Shift table I have all the values, 8 hour shifts( 3 shifts in a day-7 am to 2:59pm, 3pm to 10:59pm and 11pm to 6:59 am) and 12 hour shifts(2 shifts in a day)…and I have each shift is broken into single hour intervals like 7am to 7:59am,8am to 8:59am,9 am to 9:59am and so on… hours in a table…
Users need one or two objects showing all these values in each 8 hour shift and 12 hour shift.
Right… but if this is to summarize based on when an incident occurred, that should be a timestamp, which goes back to a lookup that determines which hour interval it belongs to.
select rownum as HOUR,
trunc((rownum)/8)+1 as HOUR8_BLOCK,
mod(rownum-1,8) as HOUR_IN_BLOCK8,
trunc(sysdate) + (rownum/24) as HOUR_START,
trunc((rownum)/12)+1 as HOUR12_BLOCK,
mod(rownum-1,12) as HOUR_IN_BLOCK12
from ALL_OBJECTS
where rownum <= ( 24 )
(BOB member since 2002-08-15)