Display Gannt Chart in a web page using @Formula
Display schedules in a gannt chart using @formula.
This formula works based on schedule start date, end date and duration values.
These values can be populated using @Dblookup from a view or using lotus script agent. This example uses @DbLookup.
Schedule information input:
- A View with name "Tasks" (you can change the view name in Input_data field formula)
- 1st column Hidden (Schedule_StartDate, Ascending order sorted)
- 2nd column : Task Name ( required)
- 3rd column : Schedule_StartDate (required)
- 4th column : Schedule_EndDate (required)
- 5th column : Duration ( required)
- 6th column : Assigned To (optional)
- 7th column : Document Unique ID (used to display URL href link)
1.Create a form named Gannt Chart or something
2.Create a computed field Input_data or anyname
This field got schedule information input and other reference fields
code:
ViewName:="Tasks";
REM {Input data, add error conditions if required};
FIELD tasknames:=@DbColumn("":"";"":"";ViewName;2);
FIELD taskstartdate:=@DbColumn("":"";"":"";ViewName;3);
FIELD taskenddate:=@DbColumn("":"";"":"";ViewName;4);
FIELD taskduration:=@DbColumn("":"";"":"";ViewName;5);
FIELD resources:=@DbColumn("":"";"":"";ViewName;6);
FIELD docids:=@DbColumn("":"";"":"";ViewName;7);
FIELD DbPath:=@ReplaceSubstring(@Subset(@DbName; -1); " " : "\\"; "+" : "/");
REM {calculate first day of the schedule start date month};
FIELD schedule_startdate:=@ToTime(@Min(taskstartdate));
FIELD schedule_startdate:=@ToTime(@Text(@Month(schedule_startdate))+"/01/"+@Text(@Year(schedule_startdate)));
REM {calculate last day of the schedule end date month};
FIELD schedule_enddate:=@Max(taskenddate);
schedule_enddate:=@ToTime(@Text(@Month(schedule_enddate))+"/01/"+@Text(@Year(schedule_enddate)));
schedule_enddate:=@Adjust(@Adjust(schedule_enddate;0;1;0;0;0;0);0;0;-1;0;0;0);
REM {Total days between firstday and last day of the schedule dates};
FIELD totaldays:=((schedule_enddate-schedule_startdate)/86400)+1;
tmpdate1:=schedule_startdate;
FIELD schedule_months:="";
@While(tmpdate1<= schedule_enddate; tmpdate1:=@Adjust(tmpdate1;0;1;0;0;0;0); @Set("schedule_months";schedule_months:@Text(@Date(@Adjust(tmpdate1;0;0;-1;0;0;0)))) ); schedule_months:=@Trim(schedule_months); FIELD schedule_months:=schedule_months; schedule_months:=@ToTime(schedule_months); schedule_months1:=@Text(@Month(schedule_months))+"/01/"+@Text(@Year(schedule_months)); FIELD fulldays:=@Explode(@ToTime("["+schedule_months1+" - "+@Text(schedule_months)+"]")); REM {Reference fields}; FIELD monthlist:="January":"February":"March":"April":"May":"June":"July":"August":"September":"October":"November":"December"; FIELD weekdays:="S":"M":"T":"W":"T":"F":"S"; ""
3.Create a computed text and enable pass thru HTML code:
schedule_bgcolor:="#FFD39B";
weekend_bgcolor:="#cccccc";
REM {Populate month names};
schedule_months:=@ToTime(schedule_months);
tmp1:="<th colspan="+@Text(@Day(schedule_months))+" align=center>"+@Transform(@Month(schedule_months);"x";monthlist[x])+", "+@Text(@Year(schedule_months))+"</th>";
month_html:="<tr><th rowspan=3 class=\"locked\">Task Name</th>"+@Implode(tmp1)+"</tr>";
REM {Populdate Weekday title};
tmp:=@Date(@ToTime(fulldays));
weekdays1:=@Transform(@Weekday(tmp);"x";weekdays[x]);
weekends:=@Replace(weekdays1;"S";"bgcolor=\""+weekend_bgcolor+"\"");
tmp1:="<th "+weekends+">"+weekdays1+"</th>";
weekday_html:="<tr>"+@Implode(tmp1)+"</tr>";
REM {Populdate days};
tmp:=@Day(@ToTime(fulldays));
tmp2:="<th "+weekends+">"+@Replace(@Text(tmp);"1":"2":"3":"4":"5":"6":"7":"8":"9";"01":"02":"03":"04":"05":"06":"07":"08":"09")+"</th>";
days_html:="<tr>"+@Implode(tmp2)+"</tr>";
REM {Populate Tasks};
html_td:="<td "+weekends+"> </td>";
task_html:=task_html;
@For(n :=1; n<=@Elements(tasknames); n:= n + 1; title:=tasknames[n]; firstday:=((@ToTime(taskstartdate[n])-@ToTime(schedule_startdate))/86400)+1; nodays:=@ToNumber(taskduration[n]); hlinktitle:=@If(@Length(title)>nodays*4;@Left(title;nodays*4)+"..";title);
hlink:="<a href=/"+DbPath+"/0/"+docids[n]+"?opendocument>"+hlinktitle+"</a>";
task_html:= task_html+"<tr><td nowrap class=\"locked\">"+" "+@If(@Length(title)>40;@Right(title;40);title)+"</td>"+@If(firstday>1;@Implode(@Subset(html_td;firstday-1));"")+"<td colspan="+@Text(nodays)+" bgcolor="+schedule_bgcolor+"><center>"+hlink+"</center></td>"+@Implode(@Subset(html_td;(firstday+nodays-1)-totaldays))+"</tr>"
);
"<table cellpadding=0 cellspacing=0>"+month_html+weekday_html+days_html+task_html+"</table>"
4. Adding style to gannt chart (optional)
- this style sheet freezes the column header (month names, days) and Task name.
4.1 Place the gannt chart computed text inside a div tag with id "ViewBody"
<div id="ViewBody">
<computed value>
</div>
4.2. Create a css style sheet page (ie:style.css)
div#ViewBody {
width: 90%;
height: 300px;
overflow: auto;
scrollbar-base-color:#999;
}
div#ViewBody table th {
width: 100px;
position:relative;
top: expression(document.getElementById("ViewBody").scrollTop-1);
z-index: 30;
}
ViewBody th.locked {This formula works based on schedule start date, end date and duration values.
These values can be populated using @Dblookup from a view or using lotus script agent. This example uses @DbLookup.
Schedule information input:
- A View with name "Tasks" (you can change the view name in Input_data field formula)
- 1st column Hidden (Schedule_StartDate, Ascending order sorted)
- 2nd column : Task Name ( required)
- 3rd column : Schedule_StartDate (required)
- 4th column : Schedule_EndDate (required)
- 5th column : Duration ( required)
- 6th column : Assigned To (optional)
- 7th column : Document Unique ID (used to display URL href link)
1.Create a form named Gannt Chart or something
2.Create a computed field Input_data or anyname
This field got schedule information input and other reference fields
code:
ViewName:="Tasks";
REM {Input data, add error conditions if required};
FIELD tasknames:=@DbColumn("":"";"":"";ViewName;2);
FIELD taskstartdate:=@DbColumn("":"";"":"";ViewName;3);
FIELD taskenddate:=@DbColumn("":"";"":"";ViewName;4);
FIELD taskduration:=@DbColumn("":"";"":"";ViewName;5);
FIELD resources:=@DbColumn("":"";"":"";ViewName;6);
FIELD docids:=@DbColumn("":"";"":"";ViewName;7);
FIELD DbPath:=@ReplaceSubstring(@Subset(@DbName; -1); " " : "\\"; "+" : "/");
REM {calculate first day of the schedule start date month};
FIELD schedule_startdate:=@ToTime(@Min(taskstartdate));
FIELD schedule_startdate:=@ToTime(@Text(@Month(schedule_startdate))+"/01/"+@Text(@Year(schedule_startdate)));
REM {calculate last day of the schedule end date month};
FIELD schedule_enddate:=@Max(taskenddate);
schedule_enddate:=@ToTime(@Text(@Month(schedule_enddate))+"/01/"+@Text(@Year(schedule_enddate)));
schedule_enddate:=@Adjust(@Adjust(schedule_enddate;0;1;0;0;0;0);0;0;-1;0;0;0);
REM {Total days between firstday and last day of the schedule dates};
FIELD totaldays:=((schedule_enddate-schedule_startdate)/86400)+1;
tmpdate1:=schedule_startdate;
FIELD schedule_months:="";
@While(tmpdate1<= schedule_enddate; tmpdate1:=@Adjust(tmpdate1;0;1;0;0;0;0); @Set("schedule_months";schedule_months:@Text(@Date(@Adjust(tmpdate1;0;0;-1;0;0;0)))) ); schedule_months:=@Trim(schedule_months); FIELD schedule_months:=schedule_months; schedule_months:=@ToTime(schedule_months); schedule_months1:=@Text(@Month(schedule_months))+"/01/"+@Text(@Year(schedule_months)); FIELD fulldays:=@Explode(@ToTime("["+schedule_months1+" - "+@Text(schedule_months)+"]")); REM {Reference fields}; FIELD monthlist:="January":"February":"March":"April":"May":"June":"July":"August":"September":"October":"November":"December"; FIELD weekdays:="S":"M":"T":"W":"T":"F":"S"; ""
3.Create a computed text and enable pass thru HTML code:
schedule_bgcolor:="#FFD39B";
weekend_bgcolor:="#cccccc";
REM {Populate month names};
schedule_months:=@ToTime(schedule_months);
tmp1:="<th colspan="+@Text(@Day(schedule_months))+" align=center>"+@Transform(@Month(schedule_months);"x";monthlist[x])+", "+@Text(@Year(schedule_months))+"</th>";
month_html:="<tr><th rowspan=3 class=\"locked\">Task Name</th>"+@Implode(tmp1)+"</tr>";
REM {Populdate Weekday title};
tmp:=@Date(@ToTime(fulldays));
weekdays1:=@Transform(@Weekday(tmp);"x";weekdays[x]);
weekends:=@Replace(weekdays1;"S";"bgcolor=\""+weekend_bgcolor+"\"");
tmp1:="<th "+weekends+">"+weekdays1+"</th>";
weekday_html:="<tr>"+@Implode(tmp1)+"</tr>";
REM {Populdate days};
tmp:=@Day(@ToTime(fulldays));
tmp2:="<th "+weekends+">"+@Replace(@Text(tmp);"1":"2":"3":"4":"5":"6":"7":"8":"9";"01":"02":"03":"04":"05":"06":"07":"08":"09")+"</th>";
days_html:="<tr>"+@Implode(tmp2)+"</tr>";
REM {Populate Tasks};
html_td:="<td "+weekends+"> </td>";
task_html:=task_html;
@For(n :=1; n<=@Elements(tasknames); n:= n + 1; title:=tasknames[n]; firstday:=((@ToTime(taskstartdate[n])-@ToTime(schedule_startdate))/86400)+1; nodays:=@ToNumber(taskduration[n]); hlinktitle:=@If(@Length(title)>nodays*4;@Left(title;nodays*4)+"..";title);
hlink:="<a href=/"+DbPath+"/0/"+docids[n]+"?opendocument>"+hlinktitle+"</a>";
task_html:= task_html+"<tr><td nowrap class=\"locked\">"+" "+@If(@Length(title)>40;@Right(title;40);title)+"</td>"+@If(firstday>1;@Implode(@Subset(html_td;firstday-1));"")+"<td colspan="+@Text(nodays)+" bgcolor="+schedule_bgcolor+"><center>"+hlink+"</center></td>"+@Implode(@Subset(html_td;(firstday+nodays-1)-totaldays))+"</tr>"
);
"<table cellpadding=0 cellspacing=0>"+month_html+weekday_html+days_html+task_html+"</table>"
4. Adding style to gannt chart (optional)
- this style sheet freezes the column header (month names, days) and Task name.
4.1 Place the gannt chart computed text inside a div tag with id "ViewBody"
<div id="ViewBody">
<computed value>
</div>
4.2. Create a css style sheet page (ie:style.css)
div#ViewBody {
width: 90%;
height: 300px;
overflow: auto;
scrollbar-base-color:#999;
}
div#ViewBody table th {
width: 100px;
position:relative;
top: expression(document.getElementById("ViewBody").scrollTop-1);
z-index: 30;
}
font-size: 10px;
color: black;
background-color: lightblue;
border-right: 2px solid silver;
position: relative;
cursor: default;
left: expression(parentNode.parentNode.parentNode.parentNode.scrollLeft-2);
z-index: 40;
}
div#ViewBody td.locked {
font-size: 12px;
color: black;
background-color: #efefef;
border-right: 2px solid silver;
border-bottom: 1px solid silver;
WHITE-SPACE: nowrap;
left: expression(parentNode.parentNode.parentNode.parentNode.scrollLeft-2);
position: relative;
z-index: 10;
}
div#ViewBody td {
font-size: 12px;
color: #c0c0c0;
border-top: #bbbbbb 0px solid; border-right: #bbbbbb 1px solid;
border-bottom: #bbbbbb 0px; border-left: #bbbbbb 1px;
padding: 1px;
}
div#ViewBody th {
font-size: 10px;
color: black;
padding:4px;
text-align: center;
background-color: #efefef;
border-top: #bbbbbb 0px solid; border-right: #bbbbbb 1px solid;
border-bottom: #bbbbbb 1px solid; border-left: #bbbbbb 1px;
}
div#ViewBody a {
font-size: 10px;
TEXT-DECORATION: none
}
4.3. Use this style sheet in Gannt Chart form
 | GANNT CHART COMPONENT DATABASE DOWNLOAD LINK |
