Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Tips and Tricks
Message Icon Topic: Reporting on Outlook Calendar Post Reply Post New Topic
Author Message
dpm1028
Newbie
Newbie


Joined: 04 Feb 2010
Online Status: Offline
Posts: 12
Quote dpm1028 Replybullet Topic: Reporting on Outlook Calendar
    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
IP IP Logged
phillytom
Newbie
Newbie


Joined: 07 Jul 2010
Location: United States
Online Status: Offline
Posts: 2
Quote phillytom Replybullet 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
IP IP Logged
dpm1028
Newbie
Newbie


Joined: 04 Feb 2010
Online Status: Offline
Posts: 12
Quote dpm1028 Replybullet 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

IP IP Logged
phillytom
Newbie
Newbie


Joined: 07 Jul 2010
Location: United States
Online Status: Offline
Posts: 2
Quote phillytom Replybullet 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
IP IP Logged
dpm1028
Newbie
Newbie


Joined: 04 Feb 2010
Online Status: Offline
Posts: 12
Quote dpm1028 Replybullet 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
IP IP Logged
dpm1028
Newbie
Newbie


Joined: 04 Feb 2010
Online Status: Offline
Posts: 12
Quote dpm1028 Replybullet 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)
    )
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.