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:

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.

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:

1
Shared NumberVar TheAnswer := 41;

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

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.

vbscript to run a bat against several computers

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.

1
2
3
4
5
6
7
strMachines = "Computer1;Computer2;Computer3;Computer4;Computer5"
aMachines = split(strMachines, ";")
For Each machine in aMachines
 Set WshShell = WScript.CreateObject("WScript.Shell")
 WshShell.Run "\\Server\Source\avdeployment\push.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.

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]

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.

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].

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

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.

HTML img tag

The HTML img tag shows an image.

1
<img height="90" width="90" src="angry.gif" alt="Angry" title="Angry Smiley" />

Speed

The height and width tags are added for speed.

“When the height and width tags are included the browser will automatically know the size of the image. As a consequence it will be able to hold a place for the image and load the rest of the page contemporaneously. Apart from the improvement on the load time of the page this method is also more user friendly since the visitor can start reading the text or other information while the image is being downloaded.” From Daily Blog Tips

Search engine optimization (SEO)

The title tag is added for seo purpose, but alt tag is more important in seo.

“Make sure that your TITLE and ALT tags are descriptive and accurate.” From Google

Accessibility

The title tag is also added for accessibility, with a maximum of 100 chars.

Section 508
WCAG 1.0

Etomite Tagcloud script

This TagCloud script is useless “as-is” but it will create a tagcloud.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
/****************************************************
* Name: Tagcloud
* Version: 0.2
* Desc: Displays the containing words of documents in a "tag cloud"
* Based on the Tagcloud 1.1. snippet created by Marc Hinse, mh@madeyourweb.com
* Converted to EtomiteCMS by Bogge, bogge.com
*
* Change 0.1 => 0.2 Removed explode $parent and optimized sql query. Thanks to adamzyg.
*
* Usage: [[tagcloud?parent=`1,3,5,6,7,14`&min=`3`&landing=`12`
* Parameters: 
* 	parent: comma separated list of the folders that conatin the documents which are to be counted
*	min: Minimum occurrences of a word to be displayed
*	landing: the id of your search result page. If you don´t have one, create it like: [!FlexSearchForm?FSF_showResults=`1` &FSF_showForm=`0`!]
*   (this is required for linking the tags)
* ***************************************************/
 
//Start Config
	$parent = isset($parent)? $parent : "0"; 
	$min = isset($min)? $min : "2";
	$landing = isset($landing)? $landing : "[~[*id*]~]";
	$minChars = 4; //Minimum number of chars in word
	$exclude = array('',' ','  ','   ','and','a','-','—','&ndash;','the','&mdash;','to','.',':',',','p&aring;','&nbsp;'); //exclude list
	$indication = array(',','.',':');		//array of chars to be deleted
//End Config
 
If (!$tags) {
	$select = $etomite->getIntTableRows($fields="pagetitle,longtitle,description,content", $from="site_content", $where="parent IN ($parent) AND published = 1 AND searchable=1 AND deleted=0", $sort="", $dir="", $limit="", $push=false, $addPrefix=true); 
 
	while ($contents = $etomite->fetchRow($select, $mode='both')) {
		$content.=$contents['pagetitle'].' '.$contents['longtitle'].' '.$contents['description'].' '.$contents['content'];	
	}
	$content=strtolower(str_replace($indication,' ',strip_tags($content)));	//all to lower and without HTML
	$array_content=explode(' ',$content);	//split them in separate words
	$number=array_count_values($array_content);		//count the words
	$words=array();
 
	foreach($number as $key => $worth) {
		if($worth>$min && (!in_array($key,$exclude) && (strlen($key) >= $minChars))) { 	//look if the word counts the required minimum and is not in the exclude list 
			$words[$key] = $worth;	//put them in a new array
		}
	} 
}
else {
	$words=array();
	foreach($tags as $tag) {
		if($tag['count']>=$min && (!in_array($tag['tag'],$exclude))) { 
			$words[$tag['tag']] = $tag['count'];
		}
	}
}
 
//unset($words['etomite']);  //if you want to override the value of words (in this case 'etomite'), uncomment it and put in your word
$max_size=max($words); //word with most hits
//$words['etomite']=8; // put in again your deleted word and value from two lines above
ksort($words); //sort them alphabetically (just comment that out, then they will be unsortet
 
$multiplier=400/$max_size; //define the multiplier for the size (play with that to fit your site!)
$min_size=80; //minimum size
 
$output='<div class="tagcloud">';
foreach($words as $key => $worth) {
	$font_size=$multiplier*$worth;
	//$countvalues='('.$worth.')'; //uncomment this for displaying the hits next to the links 
	$output.='<span><a style="font-size:'.max($font_size,$min_size).'%;" href="[~'.$landing.'~]&amp;FSF_search='.$key.'">'.$key.'</a>'.$countvalues.'</span>  ';
	$output.="\r\n";
}
 
$output.='</div>';
return $output;