Hi All,
I need to create a report in Oracle Discoverer. I would like to know if it is possible to create a report with the following logic. If yes, then how do I go about creating a report.
I have a table TBLREPORT with the following structure from which I have to pick up records.
Table: TBLREPORT
dttime DATE
interval VARCHAR2(10) (The field will have values BASE, HOURLY)
rdvalue number (aggregate to be applied in business area is AVG)
Data in table:
Dttime interval rdvalue
20-AUG-2001 9:00 am HOURLY 10
20-AUG-2001 10:00 am HOURLY 20
21-AUG-2001 9:00 am HOURLY 20
21-AUG-2001 10:00 am HOURLY 30
22-AUG-2001 9:00 am HOURLY 10
22-AUG-2001 10:00 am HOURLY 20
23-AUG-2001 9:00 am BASE 10
23-AUG-2001 9:15 am BASE 20
23-AUG-2001 9:30 am BASE 30
23-AUG-2001 9:45 am BASE 40
23-AUG-2001 9:00 am HOURLY 25 (the base data for 23-AUG-2001 from 9:00 am to 9:45 am is rolled up to arrive at HOURLY data e.g. 10+20+30+40 = 100/4 = 25)
23-AUG-2001 10:00 am BASE 50
23-AUG-2001 10:15 am BASE 60
23-AUG-2001 10:30 am BASE 70
23-AUG-2001 10:45 am BASE 80
23-AUG-2001 10:00 am HOURLY 65 (the base data for 23-AUG-2001 from 10:00 am to 10:45 am is rolled up to arrive at HOURLY data e.g. 50+60+70+80 = 260/4 = 65)
24-AUG-2001 9:00 am BASE 10
24-AUG-2001 9:15 am BASE 20
24-AUG-2001 9:30 am BASE 30
24-AUG-2001 9:45 am BASE 40
24-AUG-2001 9:00 am HOURLY 25 (the base data for 24-AUG-2001 from 9:00 am to 9:45 am is rolled up to arrive at HOURLY data e.g. 10+20+30+40 = 100/4 = 25)
24-AUG-2001 10:00 am BASE 50
24-AUG-2001 10:15 am BASE 60
24-AUG-2001 10:30 am BASE 70
24-AUG-2001 10:45 am BASE 80
24-AUG-2001 10:00 am HOURLY 65 (the base data for 24-AUG-2001 from 10:00 am to 10:45 am is rolled up to arrive at HOURLY data e.g. 50+60+70+80 = 260/4 = 65)
From this table I have to pick-up data for week = 20-AUG-2001 to 24-AUG-2001.
(the week start date is entered by the user in the report)
I have to first check if BASE data exists for the week.
If no base data exists then check for HOURLY data.
The following data exists:
No BASE data will be available for 20-AUG-201, 21-AUG-2001 or 22-AUG-2001, since it would have been deleted. HOURLY data is available for these days.
BASE data would be available for 23-AUG-2001 and 24-AUG-2001. HOURLY is available for these days also.
20-AUG-2001 21-AUG-2001 22-AUG-2001 23-AUG-2001 24-AUG-2001
----------- ----------- ----------- ----------- -----------
No BASE No BASE No BASE BASE BASE
HOURLY HOURLY HOURLY HOURLY HOURLY
The input to the report is:
Week start date = 20-aug-2001
Hour range = 9:00 am to 10:00 am
Now in order to produce a weekly report,
1) The HOURLY data for 20-AUG-201, 21-AUG-2001 and 22-AUG-2001 should be viewed for the hour ranges the user enters.
2) The BASE data for 23-AUG-2001 and 24-AUG-2001 should be rolled-up to arrive at HOURLY data only for the hour ranges entered by the user, using the logic specified for rollup as given in the data. The HOURLY data present for 23-AUG-2001 and 24-AUG-2001 should not be considered but the BASE data should be considered which should be rolled-up to arrive at HOURLY data.
3) Then the HOURLY data from 20-AUG-201, 21-AUG-2001, 22-AUG-2001, Base data of 23-AUG-2001 and 24-AUG-2001 rolled-up to HOURLY should be used to generate the weekly report, taking into consideration the hour ranges entered by the user.
Any help will be greatly appreciated.
Thanks in advance
Viral.
