Home » Developer & Programmer » Reports & Discoverer » how to display a time range
how to display a time range [message #275775] Mon, 22 October 2007 11:30 Go to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

Help , I dont know how to do this one....

I have a min and max time range in a repeating frame, I am trying to display all the hours in between the min and max time, but I dont know which way to go with this I tried on the data model and creating a function but it doesnt work how I want b/c I am looping through to create the time in between the range, I also tried creating a text field setting it to dsname and placing the below in the format trigger, what am i missing to display the time range. here is my code
thanks in advance


function F_time_rangeFormatTrigger return boolean is
cursor time_cur is SELECT
MAX(TO_char(paint_end_time,'HH24:MI:SS')) maxt --15:00:00
,MIN(TO_char(paint_start_time,'HH24:MI:SS')) mint--07:00:00
FROM time_table_t
WHERE date_of_use = :date_of_use AND site = :p_site
time_rec time_cur%rowtype;

endtime varchar2(25);
vtime date;
time_range varchar2(25);
vstart varchar2(25);
begin
open time_cur;
fetch time_cur into time_rec;
vstart:= time_rec.mint;--7:00:00
Loop
vtime := to_date(vstart,'HH24:MI:SS') + 1/24; --add one hr @ end
endtime:= to_char(vtime, 'HH24:MI:SS');

time_range := vstart||' - '||endtime;
srw.set_field_char(0, time_range);
vstart:= to_char(vtime, 'HH24:MI:SS');-change start time here
exit when vstart = time_rec.maxt;--15:00 = 15:00
end loop;
close time_cur;
return (TRUE);
end;

I want it to look like this if time is between 7:00 to 15:00
then
7:00- 8:00
8:00 - 9:00
9:00 - 10:00
etc
14:00 - 15:00
Re: how to display a time range [message #275788 is a reply to message #275775] Mon, 22 October 2007 12:49 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, read the OraFAQ Forum Guide to learn how to properly format your message (using the [code] tags) so that it would be readable.

As of your question: I'd rather use a simple query with help of the CONNECT BY clause. Something like this example (adjusted for the Reports Builder, while the output comes from the SQL*Plus):
SELECT 
  (:start_hour + LEVEL - 1) start_time, 
  (:start_hour + LEVEL) end_time
FROM dual
CONNECT BY LEVEL <= :end_hour - :start_hour; 

START_TIME   END_TIME
---------- ----------
         7          8
         8          9
         9         10
        10         11
        11         12
        12         13
        13         14
        14         15
Re: how to display a time range [message #276916 is a reply to message #275788] Fri, 26 October 2007 16:14 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

thanks for responding, but I didnt get this result, my result was only one row, it showed only the end result and it didnt work in report builder. All I want to do is create a loop to display hours between a given range and have them displayed on the report
Re: how to display a time range [message #276917 is a reply to message #276916] Fri, 26 October 2007 16:26 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I've written a report using Report Builder 10g Data Block wizard; SQL query was copy-pasted from my previous post. A 30-seconds report. This is the result:

./fa/3303/0/

Which database and developer version do you use? On 10g it works like a charm.

If this simple approach can not be applied in your environment, you might:
- create a (global?) temporary table
- create two report parameters: start and end hour
- compute intervals in the AFTER PARAMETER FORM trigger and write them into the temporary table
- use those values in report's query
  • Attachment: interval.PNG
    (Size: 4.74KB, Downloaded 370 times)
Re: how to display a time range [message #277304 is a reply to message #276917] Mon, 29 October 2007 08:38 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

I am using developer 9i, database 10g
the two values I used was 7 and 15 I get error ora-01436 , if I use 7 and 3 I dont get a error but it only displays
start time --- end time
7 --------------------- 8


I will try the other way you suggested.....

[Updated on: Mon, 29 October 2007 08:40]

Report message to a moderator

Re: how to display a time range [message #277331 is a reply to message #277304] Mon, 29 October 2007 11:07 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

Whats a global temporary table?
I created a table and insert the range to the table, thats fine. but I just realized this can not work because in the report the time range changes so I can just insert value in the parameter form, the start and end times changes on every page of the report, which is causing the below not to display on the report .
I get the following error
REP-1814: Report cannot be formatted. Object '<object name>' can never fit within '<location>'.

I placed the following code in a formula because I needed to pull the date for every page.
Please can anyone tell me what I am doing wrong.

delete from timeranges_rpt_tbl;
commit;

while vstart <= vstop
loop
insert into timeranges_rpt_tbl values(vstart,vstart+1.00);
commit;
vstart:= vstart + 1.00;
end loop;
Re: how to display a time range [message #277374 is a reply to message #277331] Mon, 29 October 2007 16:27 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If start and end time change, I believe they change depending on something (for example, employee_id, product_id, ...). Could you expand the table you are using and enter this additional information ("product_id") along with time intervals, and later select those values in the main report query; something like
SELECT i.start_time, i.end_time, t.column_1
FROM interval i, table t
WHERE i.product_id = t.product_id
  AND ...
ORDER BY i.start_time


And, really, what might this global temporary table be? ./fa/1600/0/

Also, is upgrading Developer Suite to a more recent version feasible solution to you?
Re: how to display a time range [message #277578 is a reply to message #277374] Tue, 30 October 2007 11:45 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

ok, Im getting closer Smile , I did what you said I combined the two tables but Im now getting duplicates for some reason, and I dont see why. I attached an excel spreadsheet so you can see whats going on...
P.S. theres no chance of upgrading developer.
Re: how to display a time range [message #277591 is a reply to message #277578] Tue, 30 October 2007 13:13 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
... and there's no chance for downloading an XLS file. Sorry. Post a screenshot (JPG or PNG) or a pure TXT file instead. Or, even better, a test case (CREATE TABLE and INSERT INTO sample data, along with a RDF file) so that we could see what's going on.

Forgot to say ... would a simple SELECT DISTINCT solve that duplicate records problem?

[Updated on: Tue, 30 October 2007 13:14]

Report message to a moderator

Re: how to display a time range [message #277609 is a reply to message #277591] Tue, 30 October 2007 14:44 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

no, I tried DISTINCT, all my data is located below.
Re: how to display a time range [message #277610 is a reply to message #277609] Tue, 30 October 2007 14:46 Go to previous message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

here are snap shots, shot of bad report
  • Attachment: bad_rpt.jpg
    (Size: 56.34KB, Downloaded 161 times)
Previous Topic: REP-0159 Synatx error on command line
Next Topic: FRM-41213: Unable to connect to report server Forms/Reports 6i
Goto Forum:
  


Current Time: Sat Dec 10 22:52:38 CST 2016

Total time taken to generate the page: 0.04349 seconds