Crystalkeen logo; we offer Crystal reports viewers and schedulers
 
Crystalkeen header image 3
Crystalkeen header image 5

Crystal Reports Tools: Improve Performance While Saving Time and Money

  Resources  

Articles:
Administration
Advanced
Basic
Database
Financial

Tools:

Analyzers
CR Schedulers
CR UFLs
CR Viewers
DataBase Tools
Graphics
International
Mail UFLs



 
 

Add'l:
About us

Contact Us
 

CrystalReports
on Steroids

Crystal Reports: Display the Complete Month in a Calendar

One way calendars appear when there are five weeks needed is to display the last two days of the month on the first line of the calendar.

First you will need to change the conditional display string of the Sunday on the first row to

if {@Sunday} < {@Start of Month} and {@Sunday} + 35 <= {@End of Month}

then totext({@Sunday}+ 35,"dd-MMM-yyyy")

else totext({@Sunday},"dd-MMM-yyyy")

Then change the conditional font color to

if {@Sunday} < {@Start of Month} and {@Sunday}+35 > {@End of Month}

then silver else black

You need to change the first two running total fields. The conditional evaluate of the First Sunday is

if {@Sunday} < {@Start of Month} and {@Sunday}+35 <= {@End of Month}

then {GLTrans.TRDate}={@Sunday} + 35

else {GLTrans.TRDate}={@Sunday}

Do a similar change to the First Monday in your calendar. As the month can only wrap two days in the top row, you don’t need to make any changes further along the row.

 

Another issue in month calculations is calculating the month values. We developed a series of useful formulas for month calculations. Use this one to first calculate the last day of the month:

Local numbervar m:=month(CurrentDate) + 1;

Local numbervar y:=year(CurrentDate);

If m>12 then (m:=m-12; y:=y + 1);

Date(y,m,1)-1;

Once you have that formula, then it is quite easy to calculate the number of days in the month

Day({@LastDayofMonth)

From there, the Last Friday of the month is calculated as follows.

If DayofWeek({@lastDayofMonth}) >=6 then // Friday or Saturday

Then {@LastDayofMonth) – DayofWeek({@lastdayofmonth}) + 6

Else {@LastDayofMonth) – DayofWeek({@lastdayofmonth}) – 1

If you require a different last day of the month, create a variation of the formula below. This example calculates the Last Wednesday of this Month

If DayofWeek({@lastDayofMonth}) >=4 then // Friday or Saturday

Then {@LastDayofMonth) – DayofWeek({@lastdayofmonth}) + 4

Else {@LastDayofMonth) – DayofWeek({@lastdayofmonth}) – 3

Use these directly with your database fields or modify the formulas to make them a custom function (version 9 and above).

 

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.

Authorship

Except where an author's name is given at the start of the article, all of these articles were written by Mo Naughton or Bruce Ferguson and edited by Mark Lamendola. Mo is a Crystal Reports consultant, trainer, and developer for Chelsea Technologies, Inc. Bruce Ferguson is a Crystal Reports consultant, trainer, and developer for CrystalKiwi, Inc. Mark Lamendola is a writer and editor with over 15 years experience in professional and trade publications.

 

  • Questions? Please write to mark@crystalkeen.com. We do want your business.
  • Do you have your own tips for Crystal Reports administrators and designers? Write to mark@crystalkeen.com and we'll post your tips with a link to your Website (or with some other attribution if you choose).
  • Crystal Reports is a subsidiary of Business Objects, which is owned by SAP.