Aug 12, 2010

Calculate number of workdays in crystal reports

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:

?View Code VB
1
2
3
4
5
6
7
8
9
10
11
12

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.

Aug 10, 2010

Share variables between main crystal report and subreport

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:

?View Code VB
1

Shared NumberVar TheAnswer := 41;

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

?View Code VB
1
2

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.

Feb 8, 2010

Finding first and last day of a week in crystal reports

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.

?View Code DELPHI
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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.

?View Code DELPHI
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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;

Note that the code examples says delphi, but it´s crystal syntax.