Calendar-style report help 1

[SophiaD]

I am working on a calendar-style report that I based on the tips in this thread:

http://www.tek-tips.com/viewthread.cfm?qid=1475890&page=4

BTW, Linda Bass, you're a gem! Your amazing advice got me this far, but now I'm stumped. I need to show course schedules that span several months, like if the course runs 1/17 - 2/18/2011, I need to show Jan and Feb calendars. If I do the report exactly as described in the thread, I pass a ?basedate and I get only that month's schedule. So my questions are:
1. How can I create a multi-month report using ?startdate, ?enddate
2. I only need to show Mon-Fri days. I don't use the Sat and Sun subreports, but if the first day of the month is a Sat or Sun, I get a row of blank boxes at the top. How do I suppress this blank row?

 

[lbass]

1. You really need to use the original calendar report design for multiple months, i.e., the one that uses an Excel spreadsheet of dates.

2. I don't think the blank boxes are because of missing subreports--unless you mean you don't want weeks to show that don't have scheduled activities. Wouldn't you still want the first row to show (Day) 1 and 2 if the month started on a Saturday?

-LB

 

[SophiaD]

OK, how do I show only the months that have activity in the original report? They would be contiguous.

Since I am not showing Sat and Sun, I want my January 2011 calendar to start on Mon, Jan. 3. What I am getting is a blank first row. The second row starts with 3rd.

 

[lbass]

So the rule is that you don't want to show the first week of the month if day 1 is on a Sat? Did you set up your week group to run from Monday to Sunday or is it still Sunday to Saturday?

You could omit the first week by running the report, noting the weekno for the first week of the month in the group tree, and then using group selection or record selection ({@weekno} <> 1). This is a quick, manual solution. I'd have to think about alternatives, depending upon the rules.

You can also limit the months in the record selection formula:

{%month} in 1 to 3

Or add a beginning month and end month parameter.

-LB

 

[SophiaD]

Yes, since the week runs Sun to Sat I don't want the first week to show up if the 1st is a Sat. I'm not sure I understand your manual solution. Wouldn't it always exclude week 1 regardless of what day it starts on?

The part about showing only the months with activity I figured out on my own (so proud of myself!).

 

[lbass]

Yes, the suggestion was to run the report, observe the weekno of the first week if it has no data, and then use the group selection to remove it. The weekno is not tied to the month, so the weekno of the first week in Feb is not 1, but 5 or 6, for example.

I'll think about a better way to do this. Do you also want to eliminate the last week in the month if the last day is on a Sunday?

I guess you could try a suppression formula on the week group header like this:

(
dayofweek({xl.date})=7 and //to suppress first week
day({xl.date})=1
) or
(
dayofweek({xl.date})=1 and //to suppress last week
day({xl.date})=day(dateserial(year({xl.date}),month({xl.date})+1,1)-1
)

-LB

 

[SophiaD]

I'll give it a shot with the formula. Yes, I want to also eliminate the bottom blank row if the last day of the month falls on a Sunday.

Thanks for your help!