Gantt view: a first test

Posted on by

This post shows a first attempt to use my “HTML calculated column” method to display Gantt views.

Why would I do this, knowing that SharePoint already offers Gantt views? Well, this addresses a few issues of the built-in solution:
– adjust the timescale
– add color coding
– use grouping options on the chart itself
– add various behaviors like context information

On the other hand, my solution doesn’t display a scale. Also, like the built-in SharePoint Gantt, it won’t manage task dependencies (Finish to Start, etc.).

The method

Well, as usual:
– a calculated column creates the HTML that will position the task on the Gantt
– the script renders the column as HTML instead of text

Here is the formula I used for the above example (did you expect a short one?):

="<DIV style='white-space:nowrap;'><SPAN style='display:inline-block; width:"&(MAX(DATE(2008,12,31),&#91;Start Date&#93;)-DATE(2008,12,31))*0.5&"px;'>&nbsp;</SPAN><SPAN style='display:inline-block;background-color:"&Color&";width:"&(MAX(DATE(2008,12,31),&#91;Due Date&#93;)-MAX(DATE(2008,12,31),&#91;Start Date&#93;))*0.5&"px;'>&nbsp;</SPAN><SPAN style='display:inline-block;'>"&"&nbsp;"&Title&": "&MONTH([Start Date])&"/"&DAY([Start Date])&" to "&MONTH([Due Date])&"/"&DAY([Due Date])&"</SPAN></DIV>"

How the formula works:
– the origin is represented by DATE(2008,12,31) – appears 4 times in the formula.
– 0.5 is the number of pixels for one day – appears twice in the formula.
– for tasks that start before the origin: the part before the origin won’t be displayed.
– the color is stored in a separate [Color] column (green, blue, etc.)

I’d love to hear from you on this first attempt. Does this offer a plus, compared to the built-in Gantt? Is it worth investigating it further? If so, what is still missing?

53 thoughts on “Gantt view: a first test”

  1. Hi Christophe,

    Interesting stuff. The possibility of being able to adjust the timescale will attract a lot of interest I think. On my own blog I’ve noticed several searches have arrived there looking for “SharePoint Gantt change timescale” or some variant.

    Derek

  2. Is it possible to see it in action somewhere (instead of a picture)?

    I don’t know how advanced/interactive this is, but are you able, for example, to use Dragging in order to adjust the timescale (which will invoke an ajax method).

  3. PM Hut: the picture says it all, and there is no interactivity. The method I describe is for SharePoint, it enhances the look of standard lists but doesn’t add any functionality. The benchmark here is the SharePoint Gantt view.

    Thanks for the idea. I was thinking about adding a script to draw the scale, so why not make it interactive…

  4. I can’t seem to get the color to show. Can you describe how you set up the Color column? I tried using a choice but it still doesn’t show.

    Thanks.

  5. In this example, the color is a choice or simple text. I used the following values: green, blue, gold, red.
    If you’re using Firefox, the non-breaking spaces ( ) are mandatory to make it work.
    Kate, you can e-mail me more details about your issue if it still doesn’t work:
    Christophe@PathToSharePoint.com

  6. Very interesting use of calculated columns there! I had not considered generating HTML in a calculated column before.

    If you are not already aware, there are some 3rd party webparts out there that will do things like color coding, adjusted timescale etc such as (blatant plug) my own SharePoint Planner http://www.pentalogic.net/sharepoint-planner-v2.aspx or Bamboo’s Calendar Plus web part.

  7. Pingback: How to scale down the SharePoint Gantt view « Path to SharePoint

  10. Great tips here… I’m trying to figure out how to implement a variable origin, i.e. that responds to a [start date] and goes on for a specific number of months. Some rows at the top showing a scale somewhat like Sharepoint’s generic Gantt chart but instead of days, months, years, and quarter markers would be useful too since these are not as easily visualised in the current representation.

  11. Tom, the origin in my formula is DATE(2008,12,31) (repeated 4 times). Just change it to your own start date, or link it to another column.
    Adding a scale is not easy, but I’ll try to propose a couple ideas in a future post.

  12. I would like to see the normal gannt chart, but be able to filter the tasks displayed, to match the tasks you filter in the list. Is that possible?

  13. Thinking I’m close, but when dispalying the list in “All Tasks” view (list created as a Task List), the value for my Gantt item is ” test task: 1/13 to 1/16″, rather than the expected color coded Gantt entity. It does not seem to be “calculating” the based on data…

  14. Interesting – I had typed a long piece of HTML script in quotes (because that’s what is showing as my SP column value), and it translated to ” test task: 1/13 to 1/16″ in my posy above!! Seems to be getting transalated here, but not on my Sharepoint site…

  15. Is there any way to increase the size of gantt chart view vertically so all the tasks can be seen without scrolling up and down?

  17. Based on this very good idea, I did some enhancement for those who wnat to use:
    1. now, the calculate Gant column manages Milestones (you need to upload a picture for this on your Site Collection)

    <!–
    =IF([Start Date]=[Due Date]," “&”–>”&Title&”: “&MONTH([Start Date])&”/”&DAY([Start Date])&” to “&MONTH([Due Date])&”/”&DAY([Due Date])&””,” “&”–>”&Title&”: “&MONTH([Start Date])&”/”&DAY([Start Date])&” to “&MONTH([Due Date])&”/”&DAY([Due Date])&””)
    –>

    2. based on the status, you have 3 colors calculated in a “Color” column

    =IF([Task Status]=”In Progress”,”#FFE100″,IF([Task Status]=”Completed”,”#129C00″,”#BEBEBE”))

    • Hi,

      I’m somehow not quite understanding how to insert the code snippit to get the milestones working.
      Could you please elaborate a bit on as to where the code is to be inserted?
      Thanks in advance.

  18. First of all thanks a lot for your great work!
    But I wondered if there exists a possiblity to insert a timescale. I thought of a row above all elements with a timescale i.e. 1/1 4/1 7/1 10/1 (or when using monthly view: 1 2 3 4 5 … 12) but at the right place for sure. That would improve to get a fast overview of the projects and tasks.

    Thanks

  20. Thank you for all the help, I was wondering…
    Do you think it is possible to have
    Columns that might read
    1st Quarter 2nd Quarter 3nd Quarter
    then have it calculate colume fileter each quarters below that column?

  21. When I try this I get this error in SharePoint
    “One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas.”

    What am I doing wrong?

  22. I’m having trouble assigning color and applying the time scale. Could you explain your Color calculated column formula? I started off with four IF statements, but couldn’t figure out how to position them to correspond with dates.

    You’re a great resource to have! Thanks

    • I figured it out!

      I now understand that the Color column was just assigning a color and didn’t have anything to do with dates.

      I have about three different CEWPs with slightly different code in them. I was using one that I use for a calendar, and not one for just a list.

      Good stuff, Christophe!

  24. hi Christophe

    i got this when i try to apply the code

    “The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column. ”

    do you have any idea?

  25. Pingback: Gantt demos – Update « Path to SharePoint

  26. Thanks for this nice write up.

    I have two questions
    1. Is it possible to change it from Month/Day display to the Day/Time display? The reason for this is I’m trying to use it for tasks with the same date but want to show the time elapsed between the start and due date.

    2. Is there a way to make it to actually show in the block 2 diff colors? Here is what I’m trying to do. For a task, I have the “Planned Start Date & Time” and the “Actual Start Date and Time” So I am trying to get the color block to show the “Planned Start Date/Time” vs the “Actual Start Date/Time”

    Thanks!!

    • 1. Sure, you can do it if your dates include the time (cf column settings).

      2. Also possible, just create two html strings next to each other, one with the planned date and the other with the actual date (one constraint though: the formula should be shorter than 255 characters)

  27. Chris

    Can you please tell me the step by step procedure to group the list items (say based on application name)
    How can I do it? the above blog does not give me the stpe by step procedure, like create CEWP and then click on Source editor and then paste the code etc etc

    I am new to sharepoint so appreciate if you can provide me the steps

  28. Hi Cristophe, excellent work. Maybe a tip to everyone out there. I’d have quirks with using constant values in calulated columns, and I did again trying to make this example work – specifically DATE(x,y,z). My “hack” around was to write them as float numbers for example DATE(2010.0,12.0,31.0) for SharePoint to be happy with the syntax. Have you come across this as well?

    • Search my blog for “Gantt”. Note that some code is public (like the one you’re mentioning) but other customizations are only available to workshop participants.

    • Two options:
      – change your browser settings to print backgrounds
      – modify the formula to apply color to the border, not just the background

      Search my blog for “print” to see some examples.

  32. Any idea why SP2007 would keep telling me that one of the fields doesn’t exist?? The only fields in there are [Start Date] and [Due Date], right? I changed [Due Date] to [End Date] to match my table, but that should work, right??

    Great work, by the way! 🙂 Thanks for sharing!!

  33. Hi Christophe,

    just to repeat: great stuff. It took me a while to find an error in the synthax: instead of i.e. DATE(2010,1,1) I needed to change it to DATE(2010;1;1) same with teh MAX function. As well, the 0.5 I had to change to 0,5. However, I used integer instead, that was more stable.

    Thanks a lot!
    Ralf

  34. Hi Christophe,

    Just spent some time working on taking this idea and expanding to show project duration from 01/01/2010 to any time in the future… as with the trend reporting we did a year or so back a little Photoshop work and CSS for the year scale (and trial and error!) makes for a pretty good “pseudo” gantt chart.

    Thanks for the original post and the formula….

    Cheers
    Ben

    • Nice! Thanks for sharing Ben.

      I am currently taking another look at this solution, as the SharePoint Gantt view still has some limitations, even in the new version.

  35. Pingback: My solutions spotted in the blogosphere « Path to SharePoint

  36. Hi Christophe,

    Haven’t had the time to write up a tutorial but have just created two .stp files (one for 2007 and one for 2010) these contain the solution and supporting graphic file.

    I am newbie to 2010 so I just found out you cannot create a list from a 2007 (v3) .stp template on 2010 (v4) 😦

    Anyway they files are here for people that are interested in seeing this in action:

    http://www.mode2.com/PseudoGantt2007.stp
    http://www.mode2.com/PseudoGantt2010.stp

    And the original screengrab is here (in case you missed it!):

    Will cross post this comment onto your “My solutions spotted in the blogosphere” post for completeness.

    If anyone wants the Photoshop file and the pixel font used for the year timeline then post here and I’ll make them available.

    Cheers
    Ben

  38. I would love to know how the bars can be formatted in order to highlight items that are still ongoing (i.e. status not “completed”) but have already passed the assigned due date. Do you have any code available?

    Thanks!!!

    Vivian

      • I really like the results I’m seeing from this technique. I would like to combine the gant chart results with the count up/count down technique as well. What I’m hoping to produce is the colored area with the diamond at today’s date. I would like to also make a modifiction to the count down such that if the task is complete, the results stop counting and indicate complete. This would include a color for the marker be set.

        Any direction would be gladly accepted.

  39. Hi Christophe, I have been using your excellent post on calculated column for a long time. When our server migrated to SP2010 from Sp2007, it wasn’t displaying the colors anymore. The dates and even the placement of texts are still there, it’s just that the SPAN tag that includes the color and color width info is not displayed anymore. Have you experienced the same in SP2010? Thanks!

  40. Hi Christophe,

    Is it possible to Group the task into “category” instead of Year?
    Like “Project ABC” Q1 Q2 Q3 Q4, “ProjectXYZ” Q1 Q2 Q3 Q4.
    I am not a technical person and have no knowledge on coding.I found your website very useful.
    Would you be able to provide example of what code I should put in the CEWP?
    Many thanks.

Comments are closed.