Browsing Category Blog

Page 1 of 312 3 »

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.

vbscript to run a bat against several computers

Posted on February 20, 2010 by bogge

This is a real simple vbscript to run a bat file with computer name as a parameter.
Computer names is in a string that splits in a array, then loop the array to send a command for each computer.

strMachines = "Computer1;Computer2;Computer3;Computer4;Computer5"
aMachines = split(strMachines, ";")
For Each machine in aMachines
 Set WshShell = WScript.CreateObject("WScript.Shell")
 WshShell.Run "\ServerSourceavdeploymentpush.bat " & machine
Next
Wscript.Echo("Done")

In the bat file you just add the variable %1 to capture the computer name.

Msi error 2709 – The specified Component name (‘[2]‘) not found in Component table.

Posted on February 12, 2010 by bogge

Solution
Probably, it is a conflict between a Merge Module and a Component. Remove the Component then Merge Module is better to use.

If Component name is comctl32.ocx then you should use the merge module: COMCTL32, so delete the comctl32.ocx Component.

msi error 2728 – Table definition error: [2]

Posted on February 11, 2010 by bogge

Solution
The table is most likely missing.

For example, if there is “Internal Error 2728. Component” then table “Component” is missing, this can be fixed by putting install dir and recompile package.

Error 2613 – RemoveExistingProducts action sequenced incorrectly.

Posted on February 10, 2010 by bogge

Solution
RemoveExistingProducts is not in the right place. Move it between InstallValidate and InstallInitialize.

Error 1406 – Could not write value [2] to key [3]. System error [4].

Posted on February 9, 2010 by bogge

Solution
You do not have enough rights to create / change this value in the registry.

In some cases the value is in the wrong format. As an example, you may have a DWORD reg value with the value 0×300, but it should be written in decimal form, 768, in order to work.

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;

Page 1 of 312 3 »