Lightening


Trying to create an event calendar in VFP6 report

Have a table with 2 columns, cText, dDate

Created a group based on Week(dDate) -- group by every week.

Add 7 fields for dDate value across a row, with each like

iif( dow(dDate)=1, " "+SUBS(DTOC(DDATE),4,2) +" Sunday","")

iif( dow(dDate)=2, " "+SUBS(DTOC(DDATE),4,2) +" Monday","")

.......

Set Calculation to Nothing

Put the 7 fields in Group header band.

Add 7 fields for cText value in Detail band across a row, with each like,

iif(dow(dDate)=1, ctext, ' ' )

iif(dow(dDate)=2, ctext, ' ' ) ........

Problem:

1. When the dDate fields in the group header band, only the first one displays.

If I move them into Detail Band, you can see all of them, but not in the same row.

2. Detail band displays rows in the order of records, meaning, you have a bunch of empty rows for column 2, 3, 4, 5, 6, 7.

I may need to change the format of source table and use array to achieve this. But how

Thank you for watching and help.




Re: How to create a calendar in report?

Tamar E. Granor


As you surmised, you'll need to restructure the data to do what you want. I wouldn't use an array, though, because the Report Designer isn't configured for arrays.

Basically, you'll want to create a cursor with one record per week and one column per day, then move your data into that cursor. At first glance, this feels like a place for XBase code (SCAN/REPLACE) rather than SQL. Something along these lines:

* I'd create the cursor and give it the right number of
* records up front.

SELECT YourData
SCAN
SEEK WEEK(dDate)
REPLACE ("Day" + DOW(dDate)) ;
WITH cText ;
IN ResultCursor
ENDSCAN

This is totally untested, but I hope gives you the idea. Note that if you can have more than one item on the same date, you'll have to do a little more work (adding to the item rather than just replacing the field).

Tamar






Re: How to create a calendar in report?

Naomi Nosonovsky

That was my idea as well, BTW. I asked Tracy H to look into this thread because a while ago she implemented a calendar for report.




Re: How to create a calendar in report?

Lightening

Thank you both for your reply.

I feel scan ....replace.....scan will do.

But what about the first issue

Can I NOT put all the heading in the group head band





Re: How to create a calendar in report?

Naomi Nosonovsky

When you change your table to be cross-tab, the problem with group will go away as well.

E.g. your table should be like this

week Sunday Monday Tuesday ... (fields)

week1 date1 nextdate nextdate

week2 date2 ................................

Then it would be easy to display on the report. As I also said, I found an old topic by Tracy and I gave her a reference to this thread.





Re: How to create a calendar in report?

Lightening

Thank you, Naomi,

Just wonder if it is a VFP bug or something I did not do right. Where can i find Tracy's thread





Re: How to create a calendar in report?

Naomi Nosonovsky

It is not a VFP bug, but your table structure was wrong, that's why you only see one record in the Group.

At least that's how I understood the problem.

Regarding your second question, unfortunately, referenced thread is in a different forum which requires a registration.





Re: How to create a calendar in report?

Tamar E. Granor

The issue is that when you hit the group header with your current structure, you're looking at a single record that has a single date, so you get the day of the week for that date.

In this case, I see no reason not to hard code the names of the days in the group header, unless you need to handle multiple languages, in which case you can either write a little function or use some expression-- either one should call on CDOW().

Tamar




Re: How to create a calendar in report?

Lightening

  • Thank you both, I recreated the data table structure and it does work now. (using scan, replace, endscan....)

Thank you again.





Re: How to create a calendar in report?

Naomi Nosonovsky

You're welcome.