Tips and Tricks: Reporting on Outlook Calendar

Reporting on Outlook Calendar

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10373
Printed Date: 22 May 2024 at 12:35am

Topic: Reporting on Outlook Calendar

Posted By: dpm1028
Subject: Reporting on Outlook Calendar
Date Posted: 28 Jun 2010 at 11:28am

Hello,

I have built a report that pulls off data from an Exchange Calendar. What I learned is that when an appointment is scheduled to last several days, I only get the first date. Can anyone suggest a method or code to display the event for each day up to an including the end date?

Thank you in advance,
Dan

Replies:

Posted By: phillytom
Date Posted: 07 Jul 2010 at 11:57pm

Hi. I use Crystal Reports 2008 with Outlook 2007. In my version, at least, I am seeing both the start date and the end date of the event.

Do you have a separate field from the Outlook Calendar called end date?

That should have the info you are looking for.

Otherwise you would have to make the appointment separate events in Outlook on each day , instead of one big event that spans several dates.

Hope this helps.

If not, let me know, and I will try something else.

Tom

-------------
Tom Fragale
www.theaccessanswerman.com
(215)280-1073

Posted By: dpm1028
Date Posted: 08 Jul 2010 at 2:04pm

Hi Tom,

Thanks for responding. I do have access to the end date, and can report on it, but I cannot report on the dates in between Start and End. You are correct that I could have the users enter an entry for each day, but I am trying to avoid that.

Dan

Posted By: phillytom
Date Posted: 10 Jul 2010 at 4:26am

Hey Dan. The only other thing I can think of would be to include the start date and the end date of the event in your record selection expert so it would show all dates between those two dates.

To actually make the new records, you might make a simple report of the events in Crystal, export the report to Excel, do some VBA code to add the additional event records for those dates in between the start date and end date, save the Excel spreadhsheet, then make another Crystal Report out of the resulting Excel data. I don't think there's a way to do that right from Outlook.

Hope this helps.

Thanks,

Tom

-------------
Tom Fragale
www.theaccessanswerman.com
(215)280-1073

Posted By: dpm1028
Date Posted: 10 Jul 2010 at 4:36am

Thanks again Tom, that is exactly what I did. I created a Formula Field that essentially said if the appointment meets either of the following criteria:

1. Non-Recurring but the start and end date fall on either side of today - display

2. Recurring and recurring start and recurring end fall on either side of today - display. I added a field to say "every Monday until 8/22/2010"

Once I started trying to figure out how to address Weekly, Every Monday or Weekly Every Monday and Thursday..... I knew I was in trouble.

I am sure there are better ways to do this, but this works for now.

Thanks again Tom,

Dan

Posted By: dpm1028
Date Posted: 10 Jul 2010 at 4:47am

Actually I thought I would add the code.

Because I have users populating the calendar from multiple time zones, it was easier for me to write code based on the time the apt was set for. For apt that start at 11:00pm CST I know those are the next day apts for EST. The same with PST entries. This explains the time conversion code.

The basic objective is to return the start date, but if the apt is recurring or longer than a day, and today is within that range, return today as the start date. This way, for as long as the apt is active it will appear with todays date.

I would welcome any suggestions on a better way to do this.

DateTimeVar StartDate;
DateTimeVar EndDate;
// Start here, are we dealing with a recurring apt? If we are then....
If {Calendar.Recurring} = True Then
    (
    StartDate := {Calendar.Recurrence Range Start Time};
    EndDate := {Calendar.Recurrence Range End Time};
    //convert recurring start date for CST.
    //Because I have users from multiple zones this was the easiest method.
    if Time(StartDate) > time(22,59,00) or Time(StartDate) = time(02,00,00) Then
        StartDate := Date(StartDate) +1;
    //convert recurring end date for CST
    if Time(EndDate) > time(22,59,00) or Time(StartDate) = time(02,00,00) Then
        EndDate := Date(EndDate) +1;
    //if the recurring apt has an end date that is more than the start date
    //and today is somewhere in the middle - print the record
    If Date(EndDate)>= CurrentDate and Date(StartDate)<= CurrentDate Then
        StartDate := CurrentDate;
       Date(StartDate)
    )
Else                 //non recurring apt
    (
    StartDate := {Calendar.Start Time};
    EndDate := {Calendar.End Time};
    //convert recurring start date for CST
    if Time(StartDate) > time(22,59,00) or Time(StartDate) = time(02,00,00) Then
        StartDate := Date(StartDate) +1;
    //convert recurring end date for CST
    if Time(EndDate) > time(22,59,00) or Time(StartDate) = time(02,00,00) Then
        EndDate := Date(EndDate) +1;
    // non recurring but an apt that is long enough to span multiple days
    // and today is somewhere in the middle - print the record
    If Date({Calendar.End Time}) > Date(StartDate) and Date({Calendar.End Time}) >= CurrentDate and Date(StartDate)<= CurrentDate Then
        StartDate := CurrentDate;
       Date(StartDate)
    )