bogge.info | Archive | Crystal syntax

Changeing timezone in crystal reports

Posted on July 7, 2011 by bogge

I have a data source that is in UTC (Coordinated Universal Time) and iam in the CET (Central European Time) timezone. So a need too change this time to the relevant timezone so that the data can be understood.

For example if some one is adding a post to the database in 1300 CET, the database says 1200 UTC and when the report is created it says that the post was created 1200 and the reader thinks that this is in CET.

So you can do it litke this:

local datetimevar x:=*Some Date*;
DateTime (YEAR(x), Month(x), day(x), hour(x)+1, minute(x), second(x))

But there is a problem with this approach and that is when is summer time we have Daylight savings so CET (Central European Time) becomes CEST (Central European Summer Time). So CET is +1h to UTC

So a better way to do this is to do it like this:

local datetimevar x:=*Some Date*;
ShiftDateTime (DateTime (YEAR(x), Month(x), day(x), hour(x), minute(x), second(x)), "CET,-60,CEST,0", "")

Explaining the syntax

ShiftDateTime shifts a DateTime value from one time zone to another time zone.

ShiftDateTime (inputDateTime, inputTimeZone, newTimeZone)

inputDateTime: a DateTime value to be shifted.
inputTimeZone: a “TimeZoneString” representing the time zone of the inputDateTime.
newTimeZone: a “TimeZoneString” representing the time zone to which the inputDateTime is shifted

The last empty string in my example indicates the local time zone is taken from the OS settings.

My TimeZoneString is constructed like this:
CET = String name of the standard time zone (eg Central European Time).
-60 = Offset, in minutes, of the standard time zone (CET is -1 hence -60 minutes).
CEST = String name of the DST time zone (eg Central European Summer Time).
0 = Optional DST offset from standard, defaults to one hour ahead of standard offset.

Calculate number of workdays in crystal reports

Posted on August 12, 2010 by bogge

In Sweden we typically work between Monday to Friday and are not working Saturdays and Sundays.

So for calculating how many days we work in a month we can use this formula in crystal reports:

Local DateTimeVar Start := #2010-01-01#;
Local DateTimeVar End := #2010-01-31#;
Local NumberVar Weeks;
Local NumberVar Days;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0)  +
(if DayOfWeek(End) = 7 then -1 else 0); 

Weeks + Days

This returns the number of workdays in January when used in a formula in crystal reports.

Share variables between main crystal report and subreport

Posted on August 10, 2010 by bogge

To share a variable in crystal reports between a subreport and the main report you can do the following.

In the sub report create a formula and add this:

Shared NumberVar TheAnswer := 41;

Then in the main report you just add this in a formula:

Shared NumberVar TheAnswer;
TheAnswer

This will print out 41 in the main report.

Bur remember that the sub report must be executed before the formula in the main report, else the answer is 0 or null.

Finding first and last day of a week in crystal reports

Posted on February 8, 2010 by bogge

If you have one day and want to select a whole week in crystal reports then you can do like this.

{@Today} is any date in a week and the only date we know.

This returns the date of the first day in the week if the first day is a Monday.

If DayOfWeek({@Today}) = 2 Then 
{@Today}
Else If DayOfWeek({@Today}) = 3 Then 
dateadd ("d",-1,{@Today})
Else If DayOfWeek({@Today}) = 4 Then 
dateadd ("d",-2,{@Today})
Else If DayOfWeek({@Today}) = 5 Then 
dateadd ("d",-3,{@Today})
Else If DayOfWeek({@Today}) = 6 Then 
dateadd ("d",-4,{@Today})
Else If DayOfWeek({@Today}) = 7 Then 
dateadd ("d",-5,{@Today})
Else If DayOfWeek({@Today}) = 1 Then 
dateadd ("d",-6,{@Today})

This returns the last date in the week (if its a Sunday) and the last time of the week.

Local DateTimeVar d := DateTime (DatePart ("yyyy", {@Today}), _
DatePart ("m", {@Today}), DatePart ("d", {@Today}), 23, 59, 59);
 
If DayOfWeek({@Today}) = 2 Then 
dateadd ("d",+6,d)
Else If DayOfWeek({@Today}) = 3 Then 
dateadd ("d",+5,d)
Else If DayOfWeek({@Today}) = 4 Then 
dateadd ("d",+4,d)
Else If DayOfWeek({@Today}) = 5 Then 
dateadd ("d",+3,d)
Else If DayOfWeek({@Today}) = 6 Then 
dateadd ("d",+2,d)
Else If DayOfWeek({@Today}) = 7 Then 
dateadd ("d",+1,d)
Else If DayOfWeek({@Today}) = 1 Then 
d;