INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Creating Calendar Style Reports

Creating Calendar Style Reports

(OP)
I recently ran a cross a wonderful article on creating calendar Style Reports in Crystal.

Create a calendar-style report
Need a report that looks like a calendar or month planner? Crystal can do it.
By Linda Bass
Here is a link to the tutorial.
http://www.ml-consult.co.uk/cryst-06.htm


My question - Does anyone know how to contact Linda Bass?
I would like to recreate this calendar report using dynamic system dates rather than using a brute force approach of creating a table containing all possible dates.

Currently, I am (a newbie) using the
SQL Server Business Intelligence Development Studio


RE: Creating Calendar Style Reports

9
That would be me. I need to update that article one of these days. What is your question?

-LB

RE: Creating Calendar Style Reports

(OP)
First, I love the article and it opened up some great possibilities for reports which had been lacking dynamics.

Ok, the question is rather complex and I am not sure if it is even possible. With the calendar report can this be done using dynamic system dates instead of the calendar date database or the excel calendar method? I can get through the first grouping using Monthdate(Month(Currentdate)) but when I try to create the @weeknumber formula using the datepart("ww", currentdatetime) I get an incorrect date format error. Any suggestions?

RE: Creating Calendar Style Reports

I'm working on this now, using a date parameter to determine the month for the calendar, but I don't have access to the report--I could get back to you later today.

-LB

RE: Creating Calendar Style Reports

First I created a date parameter {?basedate}, and then I created 31 formulas like this:

//{@day1}:
{?basedate}-day({?basedate})+1

//{@day2}:
{?basedate}-day({?basedate})+2

Note two things--the parameter should be used in a formula added to the page header:

totext({?BaseDate}, "MMMM yyyy")

...and you must have added a table to the report and added a field to the report header and suppressed it--even though you will not actually be referencing anything in the table. Note also that you could substitute currentdate for {?basedate}, but I thought using a parameter allowed more flexibility.

Then I created {@week}:

whilereadingrecords;
numbervar i := i + 1;
datevar array dayx := [{@Day1},{@day2},{@day3},{@day4},{@day5},{@day6},{@day7},{@day8},{@day9},{@day10},{@day11},{@day12},{@day13},{@day14},{@day15},{@day16},{@day17},{@day18},{@day19},{@day20},{@day21},{@day22},{@day23},{@day24},{@day25},{@day26},{@day27},{@day28},{@day29},{@day30},{@day31}] ;
numbervar j := day(dateserial(year({?BaseDate}),month({?BaseDate})+1,1)-1);
numbervar array wk; //week array
numbervar array dow; //day of week array
numbervar y;
numbervar z;
if i <= j then (
redim preserve wk[j];
redim preserve dow[i];
wk[i] := datepart("ww",dayx[i]);
dow[i] := dayofweek(dayx[i]);
y := wk[i];
z := dow[i];
);
y

Group on this formula to create the week groups. Note that I added in variables for day of week only because I needed that to dynamically create the daynumbers (using arrays) in the upper left hand corner of each calendar cell. If you always have data for every day, you could much more easily use the subreport date to populate the daynumbers. Then in the main report, also create formulas for month and year:

//{@month}:
month({?basedate})

//{@year}:
year({?basedate})

Then add seven subreports, one per day of the week, and link each subreport on {@month}, {@year}, and  {@week} (you would create SQL expressions for each of these in each subreport, but using the date field this time. The SQL expressions will make the report much faster than using regular formulas. In each subreport, you would also create a SQL expression {%dow}:

{fn DAYOFWEEK(`table`.`date`)}

Then your record selection formula would like this for the Sunday subreport:

{%year} = {?Pm-@Year} and
{%month} = {?Pm-@Month} and
{%week} = {?Pm-@week} and
{%dayofweek} = 1

Add your time field and description to the detail section of each sub.

I haven't finished working out the day of week arrays for the daynumbers, but maybe you don't need that. The issue is that the daynumbers won't appear in the subreport if there is no data for that day.

-LB

RE: Creating Calendar Style Reports

Here is how to set up the dynamic daynumbers, also, in the main report (for the upper left hand corner of each day cell). You need seven formulas, one for each day of the week. I am showing two examples:

//{@dow1} (Sun):
whileprintingrecords;
numbervar s1 := s1 + 1;
numbervar t1;
numbervar f1;
numbervar array dow;
datevar array dayx;
datevar array sun;
numbervar p1 := p1 + 1;
numbervar m1;
numbervar q1 := ubound(dow);
for p1 := 1 to q1 do(
if dow[p1] = 1 then (
m1 := m1 + 1;
redim preserve sun[m1+1];
sun[m1] := dayx[p1]
));
if dayofweek({?basedate}-day({?basedate}) + 1) = 1 then
t1 :=
(
select s1
case 1 : day(sun[1])
case 2 : day(sun[2])
case 3 : day(sun[3])
case 4 : day(sun[4])
case 5 : day(sun[5])
case 6 : day(sun[6])
) else

t1 :=
(
select s1
case 1 : day(sun[1])-7
case 2 : day(sun[2])-7
case 3 : day(sun[3])-7
case 4 : day(sun[4])-7
case 5 : day(sun[5])-7
case 6 : day(sun[6])-7
);
if t1 <= 0 and
groupnumber <> 1 then(
f1 := f1 + 1;
t1 :=
(
select t1
case -5 : day(sun[4+f1])
case -4 : day(sun[4+f1])
case -3 : day(sun[3+f1])
case -2 : day(sun[3+f1])
case -1 : day(sun[3+f1])
case  0 : day(sun[3+f1])
));
t1;

//{@dow2}:
whileprintingrecords;
numbervar s2 := s2 + 1;
numbervar t2;
numbervar f2;
numbervar array dow;
datevar array dayx;
datevar array mon;
numbervar p2 := p2 + 1;
numbervar m2;
numbervar q2 := ubound(dow);
for p2 := 1 to q2 do(
if dow[p2] = 2 then (
m2 := m2 + 1;
redim preserve mon[m2+1];
mon[m2] := dayx[p2]
));
if dayofweek({?basedate}-day({?basedate}) + 1) <= 2 then
t2 :=
(
select s2
case 1 : day(mon[1])
case 2 : day(mon[2])
case 3 : day(mon[3])
case 4 : day(mon[4])
case 5 : day(mon[5])
case 6 : day(mon[6])
) else

t2 :=
(
select s2
case 1 : day(mon[1])-7
case 2 : day(mon[2])-7
case 3 : day(mon[3])-7
case 4 : day(mon[4])-7
case 5 : day(mon[5])-7
case 6 : day(mon[6])-7
);
if t2 <= 0 and
groupnumber <> 1 then(
f2 := f2 + 1;
t2 :=
(
select t2
case -5 : day(mon[4+f2])
case -4 : day(mon[4+f2])
case -3 : day(mon[3+f2])
case -2 : day(mon[3+f2])
case -1 : day(mon[3+f2])
case  0 : day(mon[3+f2])
));
t2;

Somebody who is at better at arrays than I might be able to tighten these formulas up, but at least they worked when tested pretty thoroughly.

-LB

RE: Creating Calendar Style Reports

PS. You need to select all seven daynumber formulas after placing them on the report->right click->format objects->common tab->suppress->x+2 and enter:

currentfieldvalue <= 0 or
(
groupnumber >= 4 and
currentfieldvalue < 8
)

This will eliminate weird numbers before day 1 or after the last day of the month on the calendar grid.

-LB

RE: Creating Calendar Style Reports

(OP)
Linda, this is excellent. I was just hoping for a nudge in the right direction and you gave me everything. I hope that others take note of this solution as it is one of the best Crystal solutions I have seen.

RE: Creating Calendar Style Reports

I suggest simplifying my earlier dow formulas so that they appear as follows:

//{@dow1} (Sunday):
whileprintingrecords;
numbervar s1 := s1 + 1;
numbervar t1;
numbervar array dow;
datevar array dayx;
datevar array sun;
numbervar p1 := p1 + 1;
numbervar m1;
numbervar q1 := ubound(dow);

for p1 := 1 to q1 do(
if dow[p1] = 1 then (
m1 := m1 + 1;
redim preserve sun[m1+1];
sun[m1] := dayx[p1]
));

if dayofweek({?basedate}-day({?basedate}) + 1) = 1 then
t1 := day(sun[s1]) else
t1 := day(sun[s1])-7;

if t1 <= 0 and
groupnumber <> 1 then
t1 := day(sun[ubound(sun)-1]);
t1;

//{@dow2} (Monday):
whileprintingrecords;
numbervar s2 := s2 + 1;
numbervar t2;
numbervar f2;
numbervar array dow;
datevar array dayx;
datevar array mon;
numbervar p2 := p2 + 1;
numbervar m2;
numbervar q2 := ubound(dow);

for p2 := 1 to q2 do(
if dow[p2] = 2 then (
m2 := m2 + 1;
redim preserve mon[m2+1];
mon[m2] := dayx[p2]
));

if dayofweek({?basedate}-day({?basedate}) + 1) <= 2 then
t2 := day(mon[s2]) else
t2 := day(mon[s2])-7;

if t2 <= 0 and
groupnumber <> 1 then
t2 := day(mon[ubound(mon)-1]);
t2;

Also, when I recreated the Calendar Report using Oracle, I discovered a problem with using a SQL expression for week--and that is that the week as defined in the SQL expression using Oracle (but not Access) does not always match the week as defined by the datepart function in the main report, with the difference appearing to be related to the "First Day of Year" function. While there is a way to account for this, I thought the calendar report would be more flexibly used with different datasources if it were set up to ensure a match--using datepart in both subs and main reports.

In each subreport, create a formula {@week}:

datepart("ww",{table.date})

Then link {@week} from the main report to {@week} in each subreport.

-LB
 

RE: Creating Calendar Style Reports

Any way I could get a copy of this (updated) calendar?

Have you done anything like this for a DAILY format?   

RE: Creating Calendar Style Reports

It will be, but is not yet, available on another website. However, all the steps necessary for creating it or shown above.

I will respond to your other post.

-LB

RE: Creating Calendar Style Reports

I think you can definitely answer my question after reading the above post smile. My question might be very simple for you. I am using CR XI rel 2 and when I create a date parameter, itis defaulting to yyyy-mon-dd format with timestamp as well. Our Users want it to be displayed in mm/dd/yyyy in parameter [not on the report,but in the parameter box itself] when they click on some date. So I checked under OPTIONS and changed format type for Date/Date-Time but it did not take the changes. Looks like this change will effect how it's going to display in the report. Is there any way I an change the parameter format itself? Hope my question is clear.

I appreciate any help you can provide.
Thanks

RE: Creating Calendar Style Reports

Sorry, but your question does not relate to this thread. As far as I know there is no way to change the parameter display of the date within the parameter selection box in CR.

-LB

RE: Creating Calendar Style Reports

Just curious if the updated report was ever posted? Yeah, I'm being lazy...

Thanks.

RE: Creating Calendar Style Reports

Sorry, no, not yet. But all the steps are above, I think.

-LB

RE: Creating Calendar Style Reports

Ok, i must have missed something obvious. i am close but must have a blind spot for something.

If i should start a new thread, just say the word.

I have a staff schedule calendar that is printing 7 detail sections for each week group.

thanks
ed


For example, if i am doing a schedule for Bob i get something like this:
-->Report Header - suppressed = Does not print
-->Page Header - {?BaseDate} = Prints date entered
-->Group Header1 - grouped on {@week}in asc order= Prints week #
-->Details - Sunday Nov 1 10p-6a / Monday Nov 2 2p-10a / Tuesday Nov 3 2p-10a / etc = Subreports print correct staff, date, and shift times
-->Details - Sunday Nov 1 10p-6a / Monday Nov 2 2p-10a / Tuesday Nov 3 2p-10a / etc = Subreports print repeated staff, date, and shift times
-->Details - Sunday Nov 1 10p-6a / Monday Nov 2 2p-10a / Tuesday Nov 3 2p-10a / etc = Subreports print repeated staff, date, and shift times
-->Details - Sunday Nov 1 10p-6a / Monday Nov 2 2p-10a / Tuesday Nov 3 2p-10a / etc = Subreports print repeated staff, date, and shift times
-->Details - Sunday Nov 1 10p-6a / Monday Nov 2 2p-10a / Tuesday Nov 3 2p-10a / etc = Subreports print repeated staff, date, and shift times
-->Details - Sunday Nov 1 10p-6a / Monday Nov 2 2p-10a / Tuesday Nov 3 2p-10a / etc = Subreports print repeated staff, date, and shift times
-->Details - Sunday Nov 1 10p-6a / Monday Nov 2 2p-10a / Tuesday Nov 3 2p-10a / etc = Subreports print repeated staff, date, and shift times



The subreports link on the following with {%dow} incrementing for each day subreport:
//Sunday subreport
{%dow}=1 and
{%year} = {?Pm-@year} and
{@week} = {?Pm-@week} and
{%month} = {?Pm-@month} and
{VP_EMPLOYEEV42.EMPLOYMENTSTATUS} = "Active" and
Date({RDAILYSCHEDDTL.CALENDARDATE}) = {@ShiftStartDate} and
{VP_EMPLOYEEV42.GRPSCHEDNAME} = "CILA"


//{?BaseDate}
is a date parameter, allowing user to enter the start date for the schedule period ... eventually i will add {?enddate} too, allowing users to print a schedule spread over multiple months (ie: Nov 29 thru Jan 2)

//{%dow}
{fn DAYOFWEEK("RDAILYSCHEDDTL"."SHIFTDTLSTARTDTM")}

//{%year}
year("RDAILYSCHEDDTL"."SHIFTDTLSTARTDTM")

//{%month}
month("RDAILYSCHEDDTL"."SHIFTDTLSTARTDTM")

//{@week} - in main report
whilereadingrecords;
numbervar i := i + 1;
datevar array dayx := [{@Day1},{@day2},{@day3},{@day4},{@day5},{@day6},{@day7},{@day8},{@day9},{@day10},{@day11},{@day12},{@day13},{@day14},{@day15},{@day16},{@day17},{@day18},{@day19},{@day20},{@day21},{@day22},{@day23},{@day24},{@day25},{@day26},{@day27},{@day28},{@day29},{@day30},{@day31}] ;
numbervar j := day(dateserial(year({?BaseDate}),month({?BaseDate})+1,1)-1);
numbervar array wk; //week array
numbervar array dow; //day of week array
numbervar y;
numbervar z;
if i <= j then (
redim preserve wk[j];
redim preserve dow[i];
wk[i] := datepart("ww",dayx[i]);
dow[i] := dayofweek(dayx[i]);
y := wk[i];
z := dow[i];
);
y

//{@weeksub} - in subreport
datepart('ww',{@ShiftStartDate})

//{@ShiftStartDate}
Date({RDAILYSCHEDDTL.SHIFTDTLSTARTDTM})

If i forgot any info needed, please forgive me, request it and i will provide to the best of my ability.

thanks again!
 

RE: Creating Calendar Style Reports

If I'm understanding the issue correctly, you have placed the subs in the detail section. Instead, the subs should be in the week group, with the details section suppressed.

-LB

RE: Creating Calendar Style Reports

ahh.
thanks much!!!
that was it.
i must have read past that sentence.
ed

RE: Creating Calendar Style Reports

LBass -

Just wanted to give you some more kudos. I had the need to use this template of yours and not only does it work great, but it gave me a chance to learn some things about Crystal Methodology that will help a lot in other projects I'm working on! Many thanks and I hope everyone appreciates how much of your knowledge and time you are willing to share.

CSG

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

RE: Creating Calendar Style Reports

Thanks for your kind comments.

-LB

RE: Creating Calendar Style Reports

I have loved this example and it is very valuable to my company.  One question

we use the calendar for the weekdays showing the day's sales (revenue).

How can I have a total for the week (Monday-Friday) to the side of each week, or in the Saturday block?

thanks again LBass for this awesome teaching tool.
 

RE: Creating Calendar Style Reports

Set up a shared variable in the report footer within each subreport, as in:

//{@shMon}:
whileprintingrecords;
shared numbervar Monamt := sum(sales);

Remove the Saturday sub. Insert a group header_b section, and format group header_a in the section expert to "underlay following sections". GH_a will contain the subs. Then place a formula in the GH_b Saturday block like this:

whileprintingrecords;
shared numbervar Monamt;
shared numbervar Tueamt;//etc.
Monamt + Tueamt + //etc.

You should also put a reset formula in the group footer:
whileprintingrecords;
shared numbervar Monamt := 0;
shared numbervar Tueamt:= 0;//etc.

-LB

RE: Creating Calendar Style Reports

This is an amazing piece of work LB.  It's great to be able to use data I already have sitting in a database rather than chasing multiple paper calendars to keep track of vacation schedules.  I do have one question by way of an enhancement. I'm trying to figure out how to format the Max Of fields so that they change color to indicate a holiday.  Do you have any suggestions on the best approach?

RE: Creating Calendar Style Reports

I'm confused about what you referring to by "Max of fields". Are you referring to the dynamic calendar above or to the earlier version of the calendar that uses a dates table?

-LB

RE: Creating Calendar Style Reports

I was referring to the version from your original article.  That is the one I built.

RE: Creating Calendar Style Reports

Note that this solution refers to the earlier non-dynamic grid calendar version outlined in an earlier thread: thread149-703505: Calendar Style Report or as found in the link in the first post of this thread.

Right click on the summary (which returns the day number)->format objects->border->background->colr->x+2 and enter:

whileprintingrecords;
numbervar yr := {@year};//or a SQL expression for year
numbervar mo := {@month};//or a SQL expression for month
datevar array hols := [date(yr,1,1),date(yr,7,4),date(yr,12,25)]; //hard code in date elements except year
numbervar col;
if date(yr, mo, currentfieldvalue) in hols then
col := cryellow else
col := crnocolor;
col

Then if you want to color the subreport, too, format the subreport with:

whileprintingrecords;
numbervar col;

Repeat steps for each day of the week.

Then add a reset formula in the group #2 footer:  

whileprintingrecords;
numbervar col := crNoColor;

-LB

RE: Creating Calendar Style Reports

You are a Crystal master!  Thanks again for the assistance.  It works perfectly.

RE: Creating Calendar Style Reports

Hi Linda, this is what we were looking for for quite some time. Thanks for all the effort you put in.
We have followed your instructions listed above but cannot get the dynamic day numbers working correctly.
For the month of December 2007, here are the dynamic day numbers produced by the report:

SUN  MON  TUE  WED  THU  FRI  SAT
                              01
09   10    11  12   13   14   15
30   31    11  12   13   14   01
16   17    11  12   13   14   22
...

Report setup:
Report Header: reference to one column of the allocated table - surpressed
Page Header A: Prints Date Entered
Group Header # 1: Prints @Sun @ Mon @ Tue ...
                  Prints the 7 Subreports
Details: List @Sun @Mon @Tue ... but surpressed.
Group Footer #1: surpressed
Report Footer: surpressed
Page Footer: surpressed

We would greatly appreciate your help.
Thanks.
   

RE: Creating Calendar Style Reports

I just ran my report for December 2007 and the grid was as it should be, so I'm guessing you may have some incorrect formulas. I can't tell without seeing the formulas you used, {@weeknumber} and the sun, mon, tues formulas. I guess I would probably need to see a sample of your your days formulas, too. Each formula is slightly different, modified to reflect the particular day of the week or day of the month, and I think you must have done that incorrectly. Maybe post a few here.

-LB

RE: Creating Calendar Style Reports

Hi Linda,

wow - what a response time!!

Here are some of my formula's:
Day1
{?StartDate}-day({?StartDate})+1
...
Day5
{?StartDate}-day({?StartDate})+5

Week
whilereadingrecords;
numbervar i := i + 1;
datevar array dayx := [{@Day1},{@Day2},{@Day3},{@Day4},{@Day5},{@Day6},{@Day7},{@Day8},{@Day9},{@Day10},{@Day11},{@Day12},{@Day13},{@Day14},{@Day15},{@Day16},{@Day17},{@Day18},{@Day19},{@Day20},{@Day21},{@Day22},{@Day23},{@Day24},{@Day25},{@Day26},{@Day27},{@Day28},{@Day29},{@Day30},{@Day31}] ;
numbervar j := day(dateserial(year({?StartDate}),month({?StartDate})+1,1)-1);
numbervar array wk; //week array
numbervar array dow; //day of week array
numbervar y;
numbervar z;
if i <= j then (
redim preserve wk[j];
redim preserve dow[i];
wk[i] := datepart("ww",dayx[i]);
dow[i] := dayofweek(dayx[i]);
y := wk[i];
z := dow[i];
);
y

Monday
//{@dow2} (Monday):
whileprintingrecords;
numbervar s2 := s2 + 1;
numbervar t2;
numbervar f2;
numbervar array dow;
datevar array dayx;
datevar array mon;
numbervar p2 := p2 + 1;
numbervar m2;
numbervar q2 := ubound(dow);

for p2 := 1 to q2 do(
if dow[p2] = 2 then (
m2 := m2 + 1;
redim preserve mon[m2+1];
mon[m2] := dayx[p2]
));

if dayofweek({?StartDate}-day({?StartDate}) + 1) <= 2 then
t2 := day(mon[s2]) else
t2 := day(mon[s2])-7;

if t2 <= 0 and
groupnumber <> 1 then
t2 := day(mon[ubound(mon)-1]);
t2;
...
Thursday
//{@dow5} (Thursday):
whileprintingrecords;
numbervar s5 := s5 + 1;
numbervar t5;
numbervar f5;
numbervar array dow;
datevar array dayx;
datevar array thu;
numbervar p5 := p5 + 1;
numbervar m5;
numbervar q5 := ubound(dow);

for p5 := 1 to q5 do(
if dow[p5] = 5 then (
m5 := m5 + 1;
redim preserve thu[m5+1];
thu[m5] := dayx[p5]
));

if dayofweek({?StartDate}-day({?StartDate}) + 1) <= 5 then
t5 := day(thu[s5]) else
t5 := day(thu[s5])-7;

if t5 <= 0 and
groupnumber <> 1 then
t5 := day(thu[ubound(thu)-1]);
t5;

I'm more than happy to sent you my report file.

Cheers and thanks again.

 

RE: Creating Calendar Style Reports

The only mistake I noticed has been corrected in red--"i" should be "j"--but I'm not sure that is the real problem:

//{@Week}:
whilereadingrecords;
numbervar i := i + 1;
datevar array dayx := [{@Day1},{@Day2},{@Day3},{@Day4},{@Day5},{@Day6},{@Day7},{@Day8},{@Day9},{@Day10},{@Day11},{@Day12},{@Day13},{@Day14},{@Day15},{@Day16},{@Day17},{@Day18},{@Day19},{@Day20},{@Day21},{@Day22},{@Day23},{@Day24},{@Day25},{@Day26},{@Day27},{@Day28},{@Day29},{@Day30},{@Day31}] ;
numbervar j := day(dateserial(year({?StartDate}),month({?StartDate})+1,1)-1);
numbervar array wk; //week arraynumbervar array dow; //day of week array
numbervar y;
numbervar z;
if i <= j then (
redim preserve wk[j];
redim preserve dow[j];
wk[i] := datepart("ww",dayx[i]);
dow[i] := dayofweek(dayx[i]);
y := wk[i];
z := dow[i];
);
y

I think you will have to check each formula. You didn't add some outer group in the main report did you?

-LB

RE: Creating Calendar Style Reports

Thanks again Linda.
The mistake you pointed out is already in your original posting. Please confirm that it should be "j" and not "i".
I have double checked all formulas last night, but could not find a problem. I'll do it again today.
And "No", there is no outer group in the main report.
Cheers
SB

RE: Creating Calendar Style Reports

Sorry, I guess that didn't really matter.

When you added a table to the report header to generate the records, did you make sure there were at least 31 records in it? It looks like you aren't getting enough weeks in the month from your sample display--for one thing.

-LB

RE: Creating Calendar Style Reports

Yes, it's a dummy table with 47 rows in it.
And a little bit more information: all appointments are printed in the correct buckets, just the dynamic day allocation does not work.
SB

RE: Creating Calendar Style Reports

Linda,

just for my understanding: is my current assumption correct that the amount of rows in the dummy table allocated to the main report determines the amount of days listed in the report?
As mentioned in my last email, my dummy table contains 47 rows and 42 day columns are displayed in the report.
Cheers
SB

RE: Creating Calendar Style Reports

The table just needs to have at least 31 rows. I think you will need to check all of your formulas. Can you clarify what you see in the group tree to the left when you use Dec 2007? You should see:  48 through 53.

-LB

RE: Creating Calendar Style Reports

Yes, I'm seeing 48 to 53.

RE: Creating Calendar Style Reports

Ok, I found the problem:

As mentioned in my initial thread, the detailed section contained the formulas for @Sun to @Sat but the section was surpressed (this goes back to your initial report proposal which was using an Excel sheet for the date allocation). I have now removed the formula's from the detail section and everything looks fine.

Linda, thanks again for your efforts!
SB

RE: Creating Calendar Style Reports

Glad you figured that out. I didn't notice that in your original post. It makes sense that something like this would occur in retrospect--the variables were accumulating multiple times.

-LB

RE: Creating Calendar Style Reports

hello everyone and tons of congratulations to Linda for this valuable report!

I want to create this report with some extra features but it's getting a bit difficult..

In every page of every month i would like to add some extra data that's why i created a subreport and i placed it on the page footer.

The thing is that when a month has 6 weeks, thing are getting a bit uncomfortable and data doesn't fit.

So, I would like every month to has 7x5 boxes (7 days x 5 weeks) and if a month has 6 weeks, I'd like to act like this


31                   01 02
03 04 05 06 07 08 09
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

data
data
data

or similar (sometimes the sixth week of the month has 2 days)

I was thinking of changing the select expert or placing a formula in the subreports of Monday and Tuesday(Monday is the first day and i have placed "crmonday" in the formulas) and suppress the sixth week or something.

Any help to "save a wreck like me"? :))

RE: Creating Calendar Style Reports

I found out how to suppress the sixth week.
i created a running total (counter of weekdays) that evaluates on change of group 2(weeknumber) and resets on change of group 1 (calendar)
Then in section expert, i selected suppress if {runningtotal}=6.

RE: Creating Calendar Style Reports

I think the leading 31 is odd and will be misinterpreted as belonging to the previous month. Which method did you use to create the calendar grid--the spreadsheet or dynamic formula? Have you considered resizing the grid to minimize the height of each cell? Are you using subreports in each day of the week? You might be able to underlay the week group if it is the last week in the month and conditionally position the day number to mimic calendars that show like, e.g.:  24/31. Of course this wouldn't look right if you are populating those days with data.

-LB

RE: Creating Calendar Style Reports

First of all i would like to thank you for all of your posts in the forum. You have saved me from ton of work hours!Really helpful!

Here it goes,


//I think the leading 31 is odd and will be misinterpreted as belonging to the previous month

Our customer says it's ok (as long as i've placed a page header with the month_name)

//Which method did you use to create the calendar grid--the spreadsheet or dynamic formula?

I don't use the excel file, i use a table from our database

//Have you considered resizing the grid to minimize the height of each cell?

Yeah, i'll end up doing this if everything else doesn't work!

//Are you using subreports in each day of the week?

Yep!

I am now trying to create a formula/running total which will show the maximum @weeknumber in every month. (i've already created the minimum(@weeknumber))
I've tried that with a running total but the maximum(@weeknumber) (or the maximum number of weeks if you prefer) is shown only in the last week of month - the last group header 2, thus it can't be useful.
Anyway if i find how to do this, i would create two shared variables (minimum and maximum) and i would place them in the select expert of subreports Monday and Tuesday with some kind of code that looks like

if @maximum-@minimum=5    //if a month has 6 weeks
and {?Pm-@Weeknumber}=@minimum    //this is the first week of the month
then
(

{@dayofweek} = 1.00 and    //in subreport monday
{@month} = {?Pm-@month} and
{@weeknumber} = @maximum and    //@weeknumber has the maximum value of the month - is the sixth week
{@year} = {?Pm-@year}
)
else

{@dayofweek} = 1.00 and     //already exists
{@month} = {?Pm-@month} and     //already exists
{@weeknumber} = {?Pm-@Weeknumber} and     //already exists
{@year} = {?Pm-@year}     //already exists

Is this possible or i am wasting my time on this?

Thank you!
 

RE: Creating Calendar Style Reports

I'm not sure how you've set this up. If you have a group on month, and then a group on weeknumber, you should be able to suppress the last week in the month (and therefore automatically all subreports) by using a simple formula like the following on the Group header #2 in the main report.

distinctcount({@weeknumber},{table.date},"monthly") = 6 then
{@weeknumber} = maximum({@weeknumber},{table.date},"monthly")

I haven't thought about how you would get the remaining day numbers in the first week or whether it is possible. Still think it will be confusing.

-LB

RE: Creating Calendar Style Reports

I've done it! These are the two formulas:

//@maximum_
numbervar maximum_:=DatePart ("ww",cdLastDayOfMonth ({V_CALENDAR_DAY.VPCCL_DAY}),crmonday);

//@minimum_
numbervar minimum_:=DatePart ("ww",cdFirstDayOfMonth ({V_CALENDAR_DAY.VPCCL_DAY}),crmonday);

Then, I linked with the subreports (creating the parameter fields "Pm-@maximum_" and "Pm-@minimum_") and in the select expert i've placed :

if {?Pm-@maximum_} - {?Pm-@minimum_} =5    //if a month has 6 weeks
and {?Pm-@Weeknumber}={?Pm-@minimum_}    //this is the first week of the month
then
(

{@dayofweek} = 1.00 and    //in subreport monday
{@month} = {?Pm-@month} and
{@weeknumber} = {?Pm-@maximum_} and    //@weeknumber has the maximum value of the month - is the sixth week
{@year} = {?Pm-@year}
)
else
(
{@dayofweek} = 1.00 and
{@month} = {?Pm-@month} and
{@weeknumber} = {?Pm-@Weeknumber} and
{@year} = {?Pm-@year}
)

Now i just want to place the number of day (30 or 31) in the GH2, where other number of dates are, suppressed of course if total week numbers in one month is 6 and this is the first week.

If you can help me one this one i'll be forever loyal to you! :)

RE: Creating Calendar Style Reports

done it!

RE: Creating Calendar Style Reports

Please explain how you accomplished this.

-LB

RE: Creating Calendar Style Reports

Hi LB

   I'm trying to use your calendar report but I must be missing something. I'm using Crystal XI with a SQL Database. I've followed your instructions but when I get to the section on creating the {@week} formula and then Group on that formula; my {@week} formula has an error on the line below and the {@week} formula is not available for Grouping.

CODE

datevar array dayx := [{@Day1},{@day2},{@day3},{@day4},{@day5},{@day6},{@day7},{@day8},{@day9},{@day10},{@day11},{@day12},{@day13},{@day14},{@day15},{@day16},{@day17},{@day18},{@day19},{@day20},{@day21},{@day22},{@day23},{@day24},{@day25},{@day26},{@day27},{@day28},{@day29},{@day30},{@day31}] ;
Crystal says a date range is required here......any ideas why I'm getting this error?

Below is the entire {@week} formula

CODE

whilereadingrecords;
numbervar i := i + 1;
datevar array dayx := [{@Day1},{@day2},{@day3},{@day4},{@day5},{@day6},{@day7},{@day8},{@day9},{@day10},{@day11},{@day12},{@day13},{@day14},{@day15},{@day16},{@day17},{@day18},{@day19},{@day20},{@day21},{@day22},{@day23},{@day24},{@day25},{@day26},{@day27},{@day28},{@day29},{@day30},{@day31}] ;
numbervar j := day(dateserial(year({?BaseDate}),month({?BaseDate})+1,1)-1);
numbervar array wk; //week array
numbervar array dow; //day of week array
numbervar y;
numbervar z;
if i <= j then (
redim preserve wk[j];
redim preserve dow[i];
wk[i] := datepart("ww",dayx[i]);
dow[i] := dayofweek(dayx[i]);
y := wk[i];
z := dow[i];
);
y

Thanks for your help!!

 

RE: Creating Calendar Style Reports

LB

   I just opened the Calendar report I was creating with Crystal XI in Crystal 2008 and I don't receive the "date range required here" error.

  That's really strange!  I'm going to continue on with Crystal 2008.

Thanks!
 

RE: Creating Calendar Style Reports

I designed this report in XI, so I'm not sure why you would get this error--especially if the same report didn't cause this error when opened in CR2008.

-LB

RE: Creating Calendar Style Reports

Hi I have a similar problem,
I have an clock in solution, where i have some dates with data such as, various entries for a date eg, 01/11/2010 1hr, 01/11/2010 3 hrs etc, 03/11/2010 2hrs , 05/11/2010 4.5hrs, 05/11/2010 4 hrs so i need total for each day and highlight only those days, where total is less than 4.5, including days which don't have records eg 02/11/2010 & 04/11/2010, I summarise in totals using a cross tab, to get summarised output for each day as,
            Totals
01/11/2010    4
03/11/2010    2
05/11/2010    8.5
in order to get the dates which didn't have records, i added a dataset from Excel spreadsheet (Thanks to lbass suggestions,) where i just have a sequential dates for the year , and use record selection to select only those dates in range which i need to display, so the result i get
01/11/2010    4
02/11/2010    0
03/11/2010    2
04/11/2010    0
05/11/2010    8.5
so far so good, all using cross tab (thanks to lbass again), now i want to suppress rows which have total > 4.5 so the result should be
01/11/2010    4
02/11/2010    0
03/11/2010    2
04/11/2010    0
How can i do that?
 

RE: Creating Calendar Style Reports

gino2020, Your post does not relate to the current thread. I responded to your other thread on the same topic.

-LB

RE: Creating Calendar Style Reports

Hello Linda

In your original article I am having a problem. Can you assist me? When I add the following SQL relevant to my excel fields

{fn DAYOFWEEK(`Tasks`.`date`)}

I get a Crystal Error

Error in compiling SQL Expression:
Database Connector Error: DAO Error Code 0xc03
Source: DAO.Database
Description: malformed GUID. in query expression {fn DAYOFWEEK(`Tasks`.`date`)}

I have setup data source (Tasks) as Excel 8.0, and using Crystal 11 and Office 2007. Some searching has lead me to think the error is related to Excel.

Secondly, what is the key difference between your older post and this newer version.

Thanks

RE: Creating Calendar Style Reports

Whether you need to use the {fn dayofweek()} or simply dayofweek() depends upon your database. Try using dayofweek() instead--that is what works with my version of Excel.

My orginal calendar report requires Excel to create the grid that is then populated with data from another source. The version described above eliminates the need for the separate spreadsheet for dates--it is dynamic and creates the grid based on a formula.

-LB

RE: Creating Calendar Style Reports

Thank you Linda. I had tried that. What I resorted to was to setup a Access DB, and create a ODBC connection. It worked. Thanks for the explanation between the two, I can live with the manual dates in a Excel file.  

RE: Creating Calendar Style Reports

Hi Linda,

I am trying to use your sample report and have this situation. I don't want to print the page where month doesn't have any appointments scheduled.Right now it is printing the month calendar with empty schedules.Please tell me how to accompolish this ?

Thanks a million

janakiram

RE: Creating Calendar Style Reports

I assume you are using the original calendar report (non-dynamic grid with multiple months using an Excel spreadsheet) then?

-LB

RE: Creating Calendar Style Reports

Yes Linda.I am using original calendar report.I don't want to print the month if no appointments is scheduled for that month.

RE: Creating Calendar Style Reports

Is there a way to supress the calendar when no appointments scheduled for a month ?
Please guide me on this.

Thanks,
Janakiram

RE: Creating Calendar Style Reports

You need to be a little more patient. We are volunteers here and have other commitments which often prevent quick responses.

You would need to add a subreport in the report header that checks for appt dates in each month, by using the spreadsheet linked to the table that you used in the subreports by a left join. Group on the spreadsheet date by month, and then set a shared booleanvar array instance to true if there are no records for the month. Then reference this in the main report to suppress the displayed sections.

-LB

RE: Creating Calendar Style Reports

Thanks for the response and I am very sorry for my repost.
I thought I was not clear in my earlier posting and that's why I reposted it again.
I know you guys are busy and appreciate your patience and help.

Janakiram

RE: Creating Calendar Style Reports

Specifically you would need to set up a subreport in the report header. To make it not show, suppress all sections within the subreport, format it to "suppress blank subreport" on the format->subreport tab, remove the borders, and format the report header section to "suppress blank section."

In the subreport, add both the table you are using in the day subreports for the appointments and the Excel spreadsheet you are using for the grid. Link the spreadsheet using a left join FROM the spreadsheet date field (Sheet1_.Date} in the example below) TO the appt table date. Insert a group on the spreadsheet date on change of month. Then add a formula like this to the group header:

whileprintingrecords;
shared booleanvar array flag;
shared numbervar i := i + 1;
shared numbervar j := 12;
if i <= j then (
redim preserve flag[j];
if isnull(maximum({appt.date},{Sheet1_.Date},"monthly")) then
flag[i] := true else
if month(maximum({appt.date},{Sheet1_.Date},"monthly")) = i then
flag[i] := false
);

Then in the main report go into the section expert and select the page header sections and the Group Header 2 section->suppress->x+2 and enter:

whileprintingrecords;
shared booleanvar array flag;
booleanvar flagmo;
local numbervar i;
local numbervar j := 12;
for i := 1 to j do(
redim preserve flag[j];
if i = month({sheet1_.Date}) then
flagmo := flag[i]
);
flagmo

This should leave only those months with appointments displayed.

-LB

RE: Creating Calendar Style Reports

Thanks Linda for such a wonderful article on calendar. I just came across to this thread and thought of asking question.
We have 5 offices and around 10 employees per office. I have asked to create a report that will display all employees monthly calendar for a perticular office. I have used your calendar report as a template and created a report. The report works perfect if I send following parameters: OfficeID (100), Year (2011), Month (4) and employeeID (10). It displays April 2011 calendar for employeeID 10 for office 100.
I want all employees caledar for say office 100. So the report  should display 10 april 2011 calendars for EmployeeID 10,11,12,13...20, one employee per page. I am only sending following 3 parameters: OfficeID (100), Year (2011) and Month (4). I have created employeeID as a group on main report but somehow not able to link it with subreports...
Please guide me, I have very limited knowledge of crystal.

Thanks in advance....

RE: Creating Calendar Style Reports

So which method for creating a calendar did you use?  The one with the Excel spreadsheet or the formula/array approach to creating the grid?

-LB

RE: Creating Calendar Style Reports

One with excel spreadsheet...

RE: Creating Calendar Style Reports

Employee ID should be your first group. Then in the subreport linking expert, link the ID from the main report to the ID field in the subreport. You might need to suppress the employee ID group header/footer and place the employee name in the page header. Be sure to format the employee group footer wiht new page after->not onlastrecord (in the conditional formula area).

-LB

RE: Creating Calendar Style Reports

I got as far as inserting the @week formula using:
whilereadingrecords;
numbervar i := i + 1;
datevar array dayx := [{@Day1},{@day2},{@day3},{@day4},{@day5},{@day6},{@day7},{@day8},{@day9},{@day10},{@day11},{@day12},{@day13},{@day14},{@day15},{@day16},{@day17},{@day18},{@day19},{@day20},{@day21},{@day22},{@day23},{@day24},{@day25},{@day26},{@day27},{@day28},{@day29},{@day30},{@day31}] ;
numbervar j := day(dateserial(year({?BaseDate}),month({?BaseDate})+1,1)-1);
numbervar array wk; //week array
numbervar array dow; //day of week array
numbervar y;
numbervar z;if i <= j then (redim preserve wk[j];redim preserve dow[i];wk[i] := datepart("ww",dayx[i]);dow[i] := dayofweek(dayx[i]);y := wk[i];z := dow[i];);y

I can't figure out why I'm getting an error, "A date array is required here."highlighting my day # formulas. All of my day formulas are correct and my basedate parameter is set. What am I doing wrong?

Thanks!

RE: Creating Calendar Style Reports

Did you copy your actual formula into the thread? Did you set up {?BaseDate} as a date parameter? Did you add a table to the report and place a field in the report header and suppress it? This is a little hard to troubleshoot.

-LB

RE: Creating Calendar Style Reports

LB, I don't have a question for you, but I wanted to add my own praise for your knowledge and thanks for your helpful posts in the past.  I have posted here many times in the past, and you have been most helpful in coming up with creative and efficient solutions to my issues.  Not only have you helped with my CR issues, but you have helped me to think outside the box when developing my reports.  You have really made me a better CR developer, and I thank you so very much for that.

RE: Creating Calendar Style Reports

I have a question about the SQL Expression for DayOfWeek. Currently I have following:
{fn dayofweek("Table1"."BegDatTim1")}

I want to check something like:
If ISNULL({"Table1"."BegDatTim1"}) THEN
{fn dayofweek("Table1"."BegDatTim2")}
ELSE {fn dayofweek("Table1"."BegDatTim1")}

How is this possible? Thanks in advance...

RE: Creating Calendar Style Reports

I would just create two SQL expressions, and then create a formula {@dow} like this:

if isnull ({%DOW1}) then
{%DOW2} else
{%DOW1}

Then use {@DOW} in place of {%DOW}.

-LB

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close