Calendar question - How do I show a date span? 1

[Solojer]

Hi! I followed the excellent tutorial from LBass found here:

http://www.ml-consult.co.uk/cryst-06.htm,

and it worked like a charm! Thank you so much!

I'm hoping someone can help me extend the functionality a bit. I've got two dates in my data table (start and end). I would like to have the description appear for each day that falls between the start and end date (inclusive) for eat appt. Any suggestions would be very much appreciated! Thanks!

 

[lbass]

Please see thread149-1646517.

-LB

 

[Solojer]

Thanks!!

Interestingly, I'm having pretty much the exact same problem magiez was reporting the thread you pointed me to. Did you ever get this problem resolved?

 

[lbass]

I couldn't recreate the problem, and the OP in the reference thread did not follow up with my requests, so I don't know what happened. If you would like to pursue this, you should try to provide the information I requested in that thread so we can troubleshoot the issue.

-LB

 

[Solojer]

Sorry for the delay.

I've added the fields you asked for. The problem I'm experiencing is related to the cell below Nov. 25 which shows an end date to the ISE11 appointment, even though there is no actual date in that cell.

Perhaps of relevance: For December, the same appointment (ISE11) repeats as an end date for the second cell in the table -- also with no actual date in the cell.

The values for the additional cells you asked me to add are identical for the cell in November and the cell in December.

 

[Solojer]

Oh, forgot to mention: to save time, I only added the fields to Mon and Fri..

 

[lbass]

Can you also please show the exact same cells with how they actually display--so the problem is a little more obvious?

-LB

 

[Solojer]

Not sure I understand... the value of the end date in the database for ISE11 is 02/12/2011 2:30:00PM, which is not displayed in the December calendar. Let me know if you want me to send the PDF of the December calendar as well.

 

[lbass]

I am not following what is:

a) Appearing where it shouldn't, and

b) Not appearing where it should

...because you are (helpfully) showing the results of formulas for problem-solving purposes. I am asking for a corresponding version that shows the actual finished display.

-LB

 

[Solojer]

Here's the same PDF as before, but illustrating the problems with it.. hopefully this will assist. Thanks again!!

 

[lbass]

Okay, I now see the problem, and I think the following changes will fix the report.

1) Format each subreport to suppress with formulas like this:

maximum({@Sun},{@weeknumber})=0 //for the Sunday sub, etc.

This will eliminate data in empty month cells.

2) In the main report, change {@dayofyear} to:

{@Weeknumber}*7

Note that this not ACTUALLY the dayofyear, but it will allow simplication and should return the correct results.

3) Within each sub, create new formulas:

//{@DOY Start}:
datepart({"ww",{table.startdate})*7+dayofweek({table.startdate})-1

//{@DOY End}:
datepart({"ww",{table.enddate})*7+dayofweek({table.enddate})-1

4) Then change the record selection formulas in each sub to this:

//{@Monday}:
{@Weekno-Start} <= {?Pm-@weeknumber} and
{@Weekno-End} >= {?Pm-@weeknumber} and
{%Month-Start} <= {?Pm-%Month} and
{%Month-End} >= {?Pm-%Month} and
{%Year-Start} <= {?Pm-%Year} and
{%Year-End} >= {?Pm-%Year} and
{@DOY Start} <= {?Pm-@dayofyear}+1 and //1 for Monday, 2 for Tues, etc.
{@DOY End} >= {?Pm-@dayofyear}+1

This replaces the Day of Year SQL expressions with formulas that we know exactly match the formula used in the main report.

Please confirm that this works for you. Sorry I didn't see this problem earlier.

-LB

 

[Solojer]

Thanks so much for your time with this... I've implemented your suggestions the best I could - though I had to remove a "{" from the @DOY-Start and @DOY-End to get the formula to save. See attached for the results..

 

[lbass]

I don't think I can troubleshoot this without seeing your actual report and how you implemented all elements of it.

-LB

 

[Solojer]

Here is the report file with data saved..

 

[lbass]

You need to reference the new {@DOY-Start} and {@DOY-End} formulas within your {@Product} formula--you would no longer be using the SQL expressions for day of year for anything.

The suppression formulas you entered are incorrect. The suppression formula should be for the day of week the sub is for, and ALL should be checked for a zero value, so for the Thursday sub, the suppression formula for the subreport object should be:

maximum({@Thurs},{@Weeknumber}) = 0

To check the results easily, add the actual start date and end date fields to a detail_b section in the subs--then you can make sure they are falling in the correct cells.

-LB