I have a database table which is structured something like -
COLUMN NAME TYPE
for period start DATE
date entered DATE
value1 NUMBER
value2 NUMBER
value3 NUMBER
Each row holds a set of hourly values (columns value1-value3) for a period of time (for period start), the values apply to every hour from the ‘date entered’ until a) another row is added to the table with a later ‘date entered’ or b) end of period. The requirement is to report these values for each hour of the day and also as a cumulative value for the day so far (the report will run every hour).
Example -
for period start = 01-MAR-1998 06:00
date entered = 01-MAR-1998 03:00
value1 = 20
value2 = 10
value3 = 10
for period start = 01-MAR-1998 06:00
date entered = 01-MAR-1998 09:00
value1 = 30
value2 = 20
value3 = 10
In this example, the report should output values 20, 10, 10 for each hour from 06:00-08:59 and 30,20,10 for each hour from 09:00. The cumulative values at 08:00 would be 40, 20, 20 and at 12:00 would be 150, 90, 60.
Environment - Oracle 7.3, BO 4.1.2
Does anyone have any ideas how this can be achieved using BO?
I have a database table in which each row holds a set of hourly values for a period of time, the values apply to every hour from ‘date
entered’
until a) another row is added to the table a a later ‘date entered’ or
b)
end of period.
…
The report should output values for each hour…
Two problems here:
1st The all to well known end of validity not in the row itself. A nasty SQL
problem which involves a self-join to get the begin and end of validity in
one row. Better to use a view for this one and use this view in the universe.
(Why database designers keep normalizing this one out beets me, they should know better by now!).
2nd The data is aggregated in the database to a higher level then you want.
There are two ways of getting results on a lower level and both involve having a table with all datapoints for the lower level, in your case every
hour in the day.
1st method: Use oracle to give back a row for each hour in the validity
period by joining to the every hour in the day table for each record. Watch out that you do not use SUM(value) now! 2nd method: Use BO to create the cartesian join (two dataproviders with
all dimensions in common except for the ‘all hours’ object.) Putting the two
in one table block will force BO to show the values for every hour. You can now use your normal bag of tricks to format the result into what you want.