Friday, August 09, 2013

Make a Gantt Chart More Than A Gantt

by Robert Schley
(SharePoint 2010 Training Instructor)

You know what I find frustrating in SharePoint? Tasks. I find Tasks frustrating. More specifically, the lack of advanced features in SharePoint 2010 that could make tracking Tasks easy is what frustrates me.

The good news is that we can change this fairly easily by creating some custom Content Types.  I just happen to have a project that I'm working on where I need to update multiple tasks with the same set of checkboxes for each task.  Let's take a look . . .

This will take place in a 2010 environment, but the steps are almost the same for 2013 and Office 365.  I go to my Top Level Site because I’m not 100% certain that I won’t need this in other sites, so I want to make sure that the Content Type is able to follow the hierarchy down to all levels.

At my Top Level Site, I click on Site Actions -> Site Settings.  In Site Settings, I click on the Site Content Types under Galleries.  Once in the Content Gallery, I click on Create at the top of the page to create a new Content Type.  I name it Online Content Tasker and select a parent Content Type of Task from the List Content Types group.

I click Create to complete the process.

Now that my new Content Type is created, I need to get rid of the pieces I don’t want in here.  Rather than actually remove the extra columns, I choose to hide them in case I ever want to use them again.  Now I add four columns of Yes/No type checkbox: one for Audio Recording, one for Video Recorded, one for Articulate Package Built, and one for Uploaded. 

Here is a screenshot from the settings page of our Content Type that shows the columns that exist in our Content Type at this point.  Notice the four that we just created on the left side of the image.

Now I need to add the real guts of this project: a calculated column based off of the above columns.  This column will check each checkbox column for a true condition and then add 25% completed for each one.  In theory this will give us an updated status based only on the checkboxes.  This is the formula used: 

=(IF([Audio Created?]=TRUE,0.25,0))+(IF([Video Created?]=TRUE,0.25,0))+(IF([Articulate package built?]=TRUE,0.25,0))+(IF([Uploaded and Complete?]=TRUE,0.25,0))

Notice that each checkbox column we want to evaluate for a check is in place with square brackets around it.  The “TRUE” is the condition we are verifying.  The 0.25 is adding a quarter completion (remember it’s a percentage), and the 0 is what to return if the condition is false.  The pluses in between each condition check add the results together.  Let’s take a look at how it works.

I have to add the Content Type to a Task List I created.  I left the normal Task Content Type for demonstration purposes, but you will probably want to remove this in your own environment.  I add the item below to the List in order to try it out.

The results are that my calculated percentage is good at this point and entirely based off my checkboxes.

Now to add a graphical interface, we’re going to have a little bit of a problem.  Gantt charts won’t allow you to use a calculated column such as our percentage to display progress.  So I’m going to create a Workflow that simply copies the calculated result of our percentage complete into a simple number column so it can be used for the displayed result.  This Workflow needs to be created at the Top Level Site as well.  You’ll have to create it with SharePoint Designer and then attach it to the Content Type so it’s always used every time the Content Type is used.  The Workflow only has one line, but I’ll add another line to log that it is complete.

Now I have to set this as a Globally Reusable Workflow in order for it to be used anywhere in the Site Collection, but that’s not a problem.  I then go to my Site and apply the Workflow to my List. (I don’t have permissions to apply the Workflow directly to the Content Type.) I then edit an item to make sure it is working.  The first time it runs, the Workflow is a little slow while it creates the history and task list, but after that it works like a champ.

Lastly, I create a column for a permanent start date for all items.  This column will be set to the same date for all items. (It doesn’t matter which date.)  All items will have the same start date in the Gantt view, and I use the “last modified” column for the end date on the Gantt view.  This way all items start the same date, but you will see the last time the record was modified. I create my Gantt view with just the title, checkboxes and appropriate columns selected for the Gantt. This gives us a setup that looks something like this:

The good news is that this works well for displaying the information.  The bad news is that the Gantt chart only redraws when you refresh the page, even if you change the information.  I chose to use this as the default view, and now I work with a separate List view for actually checking the boxes of completion.