Gantt Chart with EditGrid online spreadsheets

Today I tried EditGrid to implement a simple Gantt Chart. EditGrid is a free and very powerful on-line application, which you can use to create and manage your spreadsheets.

I found immediatly a clear interface, simple and more similar then Google Spreadsheets to Excel and, moreover, I found a more fast and better response then Google Spreadsheets during the use and a full and more complete control for cells' formatting (border, background, number format...).

Take a look at the spreadsheet

My Gantt chart is a simple list of activities with a start date and a finish date.


Activity's Start Date is calculated like the minimum value of all Start Dates of tasks that compose it.

=min(C6:C9)

Activity's Finish Date is calculated like the maximum value of all Finish Dates of tasks that compose it.

=max(D6:D9)

Task's Duration is calculated using datediff() function:

=datedif(C7,D7,"d")

Unfortunately what it lacks is conditional fromatting to apply to cells (present in Google Spreadsheets), so you have to rapresent the Gantt Chart manually.

In general, I think EditGrid has many points to its favor respect its more famous adversary in terms of usability and funcionalities and it can be a real alternative to Excel more than Google Spreadseets.

Related Posts

Project Management: organize a project plan
Implement a Project Plan and manage activities with Google Spreadsheets
Project Management: a project plan with Excel (template)

Simple newsletter system using Coldfusion

If you have a website it can be useful to mantain contacts with your users and send periodically some news from your site. Image you have a database with a table USER and some fileds like name and email. This lesson explains how to implement a simple newsletter system using Coldfusion and <cfmail> tag.

Download Tutorial


Step 1: Add form fields
Create a coldfusion blank page and add the following code to the new page:

<form name="insert" id="insert" method="post" action="newsletter.cfm">
<input name="mailObject" type="text" id="title" size="60">
<textarea name="mailText" cols="60" rows="10" wrap="soft" ></textarea>
<input name="submit" type="submit" value="Submit">
</form>

This code adds a field to insert the object and a textarea to insert the text for your message.

Step 2: Coldfusion code to send the message to all users
Add the following code before the <form> tag seen in the previous step:

<cfif isDefined('FORM.submit')>
<!---Set error = 1 if object field and text field are empty--->
<cfif FORM.mailObject EQ ' ' OR FORM.mailText EQ ' '>
<cfset error = 1>
<cfelse>
<cfquery name="getEmail" datasource="myDatasource">
SELECT *
FROM USER
</cfquery>
<!---If error is not = 1, send the message to all users --->
<cfloop query="getEmail">
<cfmail
to="#getEmail.email#"
from="myemail@mysite.com"
subject="#FORM.mailObject#"
replyto="myemail@mysite.com">

Hello #getEmail.name#,

#FORM.mailText#

*********************

Add other infos here like site address, email to contact you...

</cfmail>
</cfloop>
<!---Set error = 0 if object field and text field are not empty--->
<cfset error = 0>
</cfif>
</cfif>

When you submit the form in the step 1, the code execute a query which gets all email address and names of the users stored in your database and execute a loop to send, to each of them, your message. Remember, you have to change datasource name with your datasource name and <cfmail> parameters from nad replyto with your email address.

Implement a Project Plan and manage activities with Google Spreadsheets

In the past days I added some post about the topic project management: organize a project plan and implement a project plan with Excel. I added also an Excel template that you can download here. In this post I'm going to explain how to implement a project plan using Google Spreadsheets. You can see the example at this link.

Copy and use this spreadsheet

Create a copy of this spreadsheet for your Google Apps account


File structure
My Google Spreadsheets file has two woorksheet WBS (work breakdown structure of the project) and User View (a list with resources to assign to each task.)


Work breakdown structure
In WBS sheet you have the following columns: WBS (task ID), Task (task description), Pred (predecessor), % (percentage of completion), Owner (resource assiged to the task), Role (owner role);


Owner role is calculated looking for the name insert into the column Owner into the sheet User View using VLOOKUP function (vertical lookup), for example:

=vlookup(E5,'user view'!A:B,2,)

...it search the value contained into the cell E5 on the sheet user view in a colum's interval from A to B, and return - if the value in E5 exist in the column A - the result from the column with index 2 (column B).

Sheet WBS has also other columns like star date, finish date, re-planned star date, re-planned finish date, project delay, and a section to assign a variable cost + fixed cost + a manual adjust to each task.

Take a look at the spreadsheet for more info about all columns.


Resources
All resources (task's owner) are located into a second worksheet User view. I have added some basic columns and the hour cost for the resource.


Total Task is calculated automatically from the sheet. I have simply added a function, COUNTIF, that count how time an user si repeted into the sheet WBS

=countif(wbs!E:E,A3)

... it updates the counter if finds in the column E of the sheet WBS a value equal to the value contained into the cell A3.


Excel or Google Spreadsheet?
Sincerly, I found Google Spreedsheets a good application to create spreadsheets with a medium level of complexity, but the "response", for an Excel-addicted, is not the same. In any case it's a useful service and, above all, it's free!

Coldfusion VS PHP part 1

I love Coldfusion. I think, it is the more simple and powerful language to develop web application, but in some cases, expecially when you have to develop a "dynamic" site - let me pass this term... - for a small client, it can't be the better solutions in terms of cost if compared with PHP. This is a first post that evidences some basic differences between two programming languages such as variables and queries for all programmers that, for necessity, have to migrate from Coldfusion to PHP.

Variables
Coldfusion variables are set with <cfset> tag. In PHP, a variable is set inside a <?php ...?> block of code with a "$" char before the var name. For example:

Coldfusion: <cfset myVar = 0>
PHP: <?php $myVar = 0?>

Differences between URL variables:

Coldfusion: #URL.myVar#
PHP: $_GET['myVar'];

Differences between POST variables:

Coldfusion: #POST.myVar#
PHP: $_POST['myVar'];

Execute a query
Coldfusion queries are "beautiful" :) and very simple to define with <cfquery> tag. You have only to specify a datasource name (database you use in your application) and a query name:

<cfquery datasource="myDatasource" name="getUser">
SELECT * FROM USER
</cfquery>

If you use PHP, when you add a query, you have to include in your page all the parameters to connect to database. I suggest to read this post to have more info about this topic. A tipical query in PHP is structured in this way:

<?php
// Connection's Parameters
$db_host="localhost";
$db_name="database_name";
$username="database_username";
$password="database_password";
$db_con=mysql_connect($db_host,$username,$password);
$connection_string=mysql_select_db($db_name);
// Connection
mysql_connect($db_host,$username,$password);
mysql_select_db($db_name);

// Query
$sql = 'SELECT * FROM USER';
$getUser = mysql_query($sql);
?>

How you can see, I defined a variable $sql (with the query SQL code). mysql_query($sql) execute the query.

Query results
After the execution of a query you would show the query results. With coldfusion you ca use <cfouptut> tag and add the "query" parameter to specify what query's result you want to display.

<cfoutput query="getUser">
#name#, #email#, #city#
</cfoutput>

You can also use this dotted code if inside a <cfoutput> code you want to display results from different queries:

<cfoutput>
#getUser.name#, #getUser.email#, #getCity.city#
</cfoutput>

With PHP you have use mysql_fetch_array() method inside a while statement:

<?php
while ($row = mysql_fetch_array($getUser)){
echo $row['name'] . ',' ;
echo $row['email'] . ',' ;
echo $row['city'] . ',' ;
}
?>

I hope this post about this topic can be useful to all PHP/Coldfusion beginner developers. I will add other infos in the next posts.

Project Management: a project plan with Excel (template)

In the previous lesson I explained some general rules about how to organize a project plan structure and implement it with an excel worksheet to manage your projects. I implemented the Excel worksheet that contains two sheets: WBS and HR. WBS is the work breakdown structure of our project and HR contains data about the resources assigned to each task.

Download Project Plan Template (excel)


WBS Sheet: Activity-Task status
Columns A:F contains general information about Activities and Tasks:

Before assigning a resource to a task, you have to add all information about the task's owner into the sheet HR.

HR: Define Project Resources
In this sheet you can add resources and assign them to your project's tasks:


Task Assigned counts the number of task assigned to each resources. It is calculated using COUNTIF() Excel function. This is an example of use of this function:

=COUNTIF(WBS.D:D;B5)

... in other words: "update the counter only if, in the interval of reference, WBS.D:D (column D of the WBS worksheet), find a value qual to the cell's value B5 in the sheet HR".


WBS Sheet: Project Dates
Columns G:I contain informations about activity start-date/finish-date/total days. For each task, you have only add a value for Start Date and Finish Date. Days will be calculated automatically such as difference between columns H - columns G.


WBS Sheet: Re-planned Date
Columns K:Q, contains info about re-planned dates and the project delay.
If you want to re-plan start-date or finish-date of an activity, you have check the column K with an "X" into the cell relative to the task you want to re-plan.


So, you set SD Delay (start day delay - in terms of days) and/or FD Delay (finish day delay - in terms of days) - Column L:M. Re-Start Date, Re Finish Date, Re-Days (replanned days) and Project Delay will be calculated from the sheet.

WBS Sheet: Costs Control
Columns S:X contains informations about cost management. You can specify hours and hour cost for each resource assiged to a task.

You can also add Fixed Cost and an Adjust (flat) to simulate cost's trend.

Download Project Plan Template (excel)

More updates about this topic coming soon :)

Project Management: organize a project plan

This is a first lesson about how to organize a project plan structure to manage a project using Microsoft Excel.

Define the Work Breakdown Structure
What is it a Work Breakdown Structure? We can say it is a hierarchical tree structure useful to organize all necessary task to complete a project. In this example I chose a simplified structure wih only two level (Activity - Task):


An activity is composed from many tasks and every task has some informations about its status, for example: percentage of completion, owner, start date, finish date...
I suggest you not to exaggerate with the levels' structure. A three level plan such as the following is the in many situations:

Level 1. Macro Activity
Level 2. Activity
Level 3. Task

Calculate Start Date, Finish Date and POC of an activity
Let's look an activiy is an group of tasks therefore, all informations about its status depend from the single tasks that compose it.

Start Date of an activity is calculated such as the minimum value of all start date of tasks that compose it.

Start Date Activity = SDAi = min(SDTi)

...where SDTi are the start date of tasks that compose the activity i.

In the same way, Finish Date of an activity is calculated such as the maximum value of all start date of tasks that compose it.

Finish Date Activity = FDAi = max(FDTi)

...where SDTi are the finish date of tasks that compose the activity i.


POC, percentage of completion (%), is calculated such as the average value of all percentage:

POC = POCAi = AVERAGE(POCTi)

...where POCTi are the percentages of completion of tasks that compose the activity i.


Adding a control for replanned task
It can be useful to mantain a trace of original dates, in case you want to re-plan start date or finish date for one or more tasks . In this case, you can add the following columns on your file: Replanned Start Date, Replanned Finish date, Replanned Days (total), Delay (days):


Delay is calculated like the difference of:

Delay = (Replanned Start Date - Start Date) + (Replanned Finish Date - Finish Date)

In the next lesson I will explain how to add a cost control on this plan and how to implement it with Excel.

Simple 5 stars rating script using AJAX and Coldfusion

This lesson explains how to implement a simple 5 stars rating script using Ajax and Coldfusion. The following picture shows the tutorial structure that we will use to realize this script:

Step 1: Define graphic elements (CSS)

save this image and save it into the pic folder of your website.

Now, create a new css file (default.css) into css folder of your website ad copy and paste this code to create two layer div.star_y (star on), div.star_n(star off):

div.star_y{width:10px; height:10px; background:url(../pic/vote_star.gif); float:left;}
div.star_n{width:10px; height:10px; background:url(../pic/vote_star.gif) 0 10px; float:left;}

Step 2: Define graphic elements: (HTML)
Image you want to use this 5 stars rate script to rate posts in your blog. You have a query that return all posts' titles with the current rate (post rate) and show one or more stars "on" in this way:

5 stars rating script using Ajax
post rate 3/5

First, add a link to the css file adding this line of code into <head> page's tag:

<link href="css/default.css" rel="stylesheet" type="text/css" />

... so, copy and paste this code into index.cfm:

//include ajax_framework.js
<script language="javascript" src="ajax_framework.js">
<cfquery datasource="mydatasource"&gt name="getPosts">
SELECT id_post_pk, post_rate, title
FROM MYPOST
</cfquery>
<cfoutput&gt query="getPosts">
#title# <br/>
<cfloop from="1" to="5" index="i"&gt;
<div class="star_<cfif post_rate GE i>y<cfelse>n</cfif>" id="#id_post_pk#_#i#" onmouseover = "javascript:overstar(#id_post_pk#,#i#)" onmouseout = "javascript:outstar(#id_post_pk#,#post_rate#)">
// use a spacer image into the layer
<a href="javascript:ratestar(#id_post_pk#,#i#)">
<img src="pic/spacer.gif"/>
</a><br/>
</cfloop>
post rate: <strong>
<span id="rate_#id_post_pk#">#post_rate#</span>/5
<strong>
</cfoutput>

Step 3: Javascript Functions
Create a new javascript file ajax_framework.js and add the following code.
This is the code of javascript functions overstar() and outstar():

/* star "on" on mouse over */
function overstar(idElement,rate){
for(i=1; i<=5; i++){
if(i <= rate){
document.getElementById(idElement+'_'+i).style.backgroundPositionY = "20px";
} else {
document.getElementById(idElement+'_'+i).style.backgroundPositionY = "10px";
}
}
}
/* star "off" on mouse out */
function outstar(idElement,rate){
if(outstar_check == 0){
rate = rate;
} else {
rate = rateN;
}
for(i=1; i<=5; i++){
if(i<=rate){
document.getElementById(idElement+'_'+i).style.backgroundPositionY = "30px";
} else {
document.getElementById(idElement+'_'+i).style.backgroundPositionY = "10px";
}
}
}

... and this is the code to use AJAX functionality to save vote and calculate the new rate:

var idElementN=0;
var rateN=0;
var nocache=0;
var outstar_check=0;

function rateStar(idElement,rate){ // idElement is the id_post_pk
idElementN=idElement;
rateN=rate;
outstar_check=1;
nocache=Math.random();
http.open('get', 'ratePost.cfm?idElement='+idElement+'&rate='+rateN+ '&nocache='+nocache);
http.onreadystatechange = ratestarReply;
http.send(null);
}
function rateStarReply() {
if(http.readyState == 4){
var response = http.responseText;
document.getElementById('rate'+idElementN).innerHTML = response;
} }

Step 4: Coldfusion code: ratePost.cfm
rateStar() function send data to ratePost.cfm page to calculate the new rate after a vote. So create this page and copy and paste this code:

<cfset element = #URL.idElement#>
<cfset rate = #URL.rate#>
// get total votes and current rate
<cfquery datasource="mydatasource"&gt name="getTotalVotes">
SELECT total_votes, total_value, post_rate
FROM MYPOST
WHERE id_post_pk = #element#
</cfquery>
// calculate new rate
<cfset newRate = (#getTotalVotes.total_value# + #rate#)/(#getTotalVotes.total_votes# + 1)>
// update rate
<cfquery datasource="mydatasource"&gt name="updateRate">
UPDATE MYPOST
SET post_rate = #newRate#,
total_value= (#getTotalVotes.total_value#+#rate#),
total_votes= (#getTotalVotes.total_votes# + 1)
WHERE id_post_pk = #element#
</cfquery>
// return new rate

<cfoutput>#newRate#</cfoutput>

Save all and try it!