help with a report and shorttime

[acceptthis]

I'm stumped on creating a report and wondered if anyone could help.

How can I get a report to always print the group header-- even when there's no detail under that group header? Is it possible?

I have a table that contains the hours of the day broken into every half hour. The field is: display_time. I have another table that contains appointments with a field called: sch_time. These tables are linked in a set that looks like this:

Parent-Actual_Time (display_time)
|
L __Child- Appointments (sch_time)

I'd like to create a report to print daily appointments like the example below. It would make it easier to see when the appointments are really scheduled:

        <06:00 am

          06:00 am

          06:30 am
                   00236       Appointment         Installation         
                   00424       Project             Installation  
          07:00 am
                   00024       Appointment         Repair   
                   00029       Project             Installation
. . .

I can get a report like this:

. . .
        05:00 am

        05:30 am

        06:00 am

        06:30 am
                   00236       Appointment         Installation         
                   00424              Project         Installation  
                   00426       Appointment         Installation       
        07:00 am
                   00024       Appointment         Repair   
                   00029              Project         Installation
                   00046              Project         Installation
. . .

but this report has no filter on it. It's showing all the records of the appointment database, and groups them under the display_time field.
What I want to show is just the appointments of user entered date.

So, I have alpha5 ask for a date(Var->askD_Print_Date), and then I run a filter on it. This filter-- Sch_Date=Var->askD_Print_Date
That selects only the records in the child database that match the user selected date.
When I do this, it filters it on the detail section, and I lose my group headers that don't have matching detail data.
My report now looks like this:

        11:00 am 
                                 00602    Appointment         Repair Other
        12:00 pm
                                  00646   Appointment         Repair Stop-n-waste
                                  00646   Appointment         Repair Stop-n-waste
        02:00 pm
                                  00626   Appointment         Repair Other
        03:00 pm
                                  00613   Appointment         Repair Head

I've lost all my headers!
Any ideas would be appreciated.

Thanks,
Brad

P.S. Maybe the whole approach I'm taking is wrong, and this report can be created a better way. I thought of using variables instead of this table, and running a report using an array of variables. I even created a script that creates an array of variables with the thought of using them to get my header data. My problem is I don't even know how to get tables to print in a report the way I want, let alone an array of variables. Here's the code of that script if it sparks any ideas in anyone's head:

dim h_time[24] as c
dim hq_time[24] as c
dim hh_time[24] as c
dim ht_time[24] as c

for i = 1 to 24
h_time[i] = 0 ;hq_time[i] = 0 ;hh_time[i] = 0 ;ht_time[i] = 0
h_time[i] = i - 1 ;hq_time[i] = i - .75 ;hh_time[i] = i - .5 ;ht_time[i] = i - .25
h_time[i] = totime(val(h_time[i])*3600, 11, 0)
hq_time[i] = totime(val(hq_time[i])*3600, 11, 0)
hh_time[i] = totime(val(hh_time[i])*3600, 11, 0)
ht_time[i] = totime(val(ht_time[i])*3600, 11, 0)
next

h_time[11] = on the hour (ex. 12:00 PM)
hq_time[11] = on the quarter hour (ex. 12:15 PM)
hh_time[11] = on the half hour (ex. 12:30 PM)
ht_time[11] = on the three/quarter hour (ex. 12:45 PM)

[Peter.Wayne]

Since you have a report that does what you want on the unfiltered detail records, one approach is to export the selected detail records to a another table, then use that new table as the detail table for the report.

e.g.
c_sched_date=ui_get_date("Enter Date:","Date:",dtoc(date()))
query.filter="sch_date={"+c_sched_date+"}"
query.order="toseconds(your time field)"
query.options="T"
detail.query_create()
detail2=table.open("detail2",file_rw_exclusive)
detail2.zap(.t.)
detail.fetch_first()
while .not. detail.fetch_eof()
detail2.enter_begin()
detail2.sch_date=detail.sch_date
detail2.apptTime = detail.apptTime
detail2.description = detail.description
detail2.enter_end()
detail.fetch_next()
end while
detail2.close()
report.preview("report using detail2 as detail without filtering")

Sometimes when you can't get the report engine to do what you want, it's easier to just export the selected records into new table(s) and run the report from those. In many cases, even if you can get the report generator to do what you want, exporting records may be a heck of a lot faster.

[acceptthis]

export to another report

Dr. Wayne,

I woke up and turned on my computer, and to my suprise, someone had responded. I was beginning to wonder if my message was too long, so no one wanted to read it.
Thanks for your reply.

I haven't tried doing what you've suggested, but, if you don't mind, I had a couple of questions first, before I give it a whirl:

1) I already have "Actual_Time" as a table. Why would exporting this data to a new one give me any better results? Is it giving me problems now because I'm using it through a set structure?
The reason I wonder is because I tried something else. I took the fields in Actual_Time, put them on the detail section of my report, and then made a subreport using the child table (filtered on the user entered date). All I got with that was this:

01:00 am

The report was one page with only the first field of the "actual_time" table.
Then I took the filter off the subreport, and it did this:

01:00 am
  


            05:00am
            06:00am. . .

It just printed the first field and then all the detail of the subreport. What was interesting to me was the space between 01:00 am and 05:00am was about as far down the page as it would be if it was printing the other fields of "Actual_time".

2) This is a syntax question to help me understand xbasic and writing filters. Why do you write the query.filter like this:

query.filter="sch_date={"+c_sched_date+"}"

with the -- {"+ -- around the c_sched_date field? In my little brain, since they are both date fields, I'd think "sch_date=c_sched_date" should work. I'd do it, and then I'd be spending two hours figuring out why I'm getting an error.

3) Is there a way to write a report with xbasic one line at a time? If there is, where can I read about it? The approach I've started making is writing lines to a .txt file using the file_pointer.write_line() command. I can make it work, but it will be a little goofy and not asthetically pleasing. But, at this point, I'm now just trying to get a result so I can figure my time hasn't been wasted on the attempt. Also, xbasic report writing would be much more flexible than just manipulating the data the way alpha lets you.

Thanks again,
Brad

[JohnZaleski]

In conjunction with Peter's idea, you could write a single dummy record for each potential timeto force a grouping for each time. A little tweaking should allow you to getit to look like what you want.
Since the file will be zapped prior to printing, These extra records will not be a bother.

[Peter.Wayne]

Brad,
Let's try to go through your questions one at a time.
1) I already have "Actual_Time" as a table. Why would exporting this data to a new one give me any better results? Is it giving me problems now because I'm using it through a set structure?

The way I understook it, is that you had no trouble getting the report to print properly if you did not have a filter on the detail records, BUT if you placed a filter on the detail records, then you did not get what you want.
By exporting the selected records to a new table, you don't need a filter when you create the report. John Zaleski has also chipped in with another suggestion, also useful at time, which is to create records in the exported table for the missing times.


2) This is a syntax question to help me understand xbasic and writing filters. Why do you write the query.filter like this:
Quote:
query.filter="sch_date={"+c_sched_date+"}"
with the -- {"+ -- around the c_sched_date field? In my little brain, since they are both date fields, I'd think "sch_date=c_sched_date" should work. I'd do it, and then I'd be spending two hours figuring out why I'm getting an error.

Ah, that's one of the great bug-a-boos of Xbasic. All query filters are character-based. So the filter is really something like
query.filter="sch_date={1/22/06}"
Tom Cone wrote an article on mysteries of query syntax on www.learn alpha.com; it's still worth reading. You can also use the replace_parameters() function, but frankly I find it less confusing to make the substitution directly.

3) Is there a way to write a report with xbasic one line at a time? If there is, where can I read about it? The approach I've started making is writing lines to a .txt file using the file_pointer.write_line() command. I can make it work, but it will be a little goofy and not asthetically pleasing. But, at this point, I'm now just trying to get a result so I can figure my time hasn't been wasted on the attempt. Also, xbasic report writing would be much more flexible than just manipulating the data the way alpha lets you.


You can't do it by writing lines to a text file but you can write lines directly to a string variable and put the variable on the report, OR you could write lines to a memo field in a scratch table and put the memo field on the report, OR you can just do what John Zaleski and I have suggested, which is to write the records of interest to another table and base the report on that table. Any of the methods will work and you can still take advantage of Alpha Five's report writer. If you still want to create your own report entirely from scratch, you can do it using ui_printer_draw(), but that's not for the faint of heart.

[acceptthis]

Thanks to both of you. That gives me some good direction, and following your direction will be something I can use much more in the future. Besides, I'm usually faint of heart.
Also, I'm looking forward to finding and reading Tom's article so I can get a much better feel of query syntax.
Thanks again