User requirements for this Gantt chart made it even more challenging; have the bars be color coded by project name, have grouping, sorting and filtering by all fields, have as many fields as needed to the left of the Gantt.
Now before you say “oh there are third party products that create Gantts” …yes there are, I evaluated them and none of them met all my user requirements.
To save time, I will assume you already know how to take a List View and convert it to a Data View and that you know how to use SharePoint Designer. If you don’t know these things, just comment to this post and I will email you.
Now once you do this you will realize there are other ways to accomplish this but using the same techniques.
- Create your list. I created mine from the Project Tasks built in list so that I have that functionality.
- Add your columns
- Create your Gantt columns.
- Color – Calculated Field – Single Line of Text
- ="<SPAN style='width:100%;background-color:"&IF([Status]="","Black",IF([Status]="Not Started","Blue",IF([Status]="In Progress","Chartreuse",IF([Status]="Completed","Green",IF([Status]="Deferred","Darkorange",IF([Status]="Waiting on someone else","Gold",IF([Status]="","Aqua","")))))))&";'> </SPAN>"
- This field could also reference a Project Name field or any other choice field.
- Jan – Calculated Field – Single Line of Text
- ="<DIV style='background-color:"&Color&"; width:100%;'> </DIV>"
- Feb – Calculated Field – Single Line of Text
- ="<DIV style='background-color:"&Color&"; width:100%;'> </DIV>"
- Mar – Calculated Field – Single Line of Text
- ="<DIV style='background-color:"&Color&"; width:100%;'> </DIV>"
- Apr – Calculated Field – Single Line of Text
- ="<DIV style='background-color:"&Color&"; width:100%;'> </DIV>"
- May – Calculated Field – Single Line of Text
- ="<DIV style='background-color:"&Color&"; width:100%;'> </DIV>"
- Jun – Calculated Field – Single Line of Text
- ="<DIV style='background-color:"&Color&"; width:100%;'> </DIV>"
- Jul – Calculated Field – Single Line of Text
- ="<DIV style='background-color:"&Color&"; width:100%;'> </DIV>"
- Aug – Calculated Field – Single Line of Text
- ="<DIV style='background-color:"&Color&"; width:100%;'> </DIV>"
- Sep – Calculated Field – Single Line of Text
- ="<DIV style='background-color:"&Color&"; width:100%;'> </DIV>"
- Oct – Calculated Field – Single Line of Text
- ="<DIV style='background-color:"&Color&"; width:100%;'> </DIV>"
- Nov – Calculated Field – Single Line of Text
- ="<DIV style='background-color:"&Color&"; width:100%;'> </DIV>"
- Dec – Calculated Field – Single Line of Text
- ="<DIV style='background-color:"&Color&"; width:100%;'> </DIV>"
- Optional fields – you can use these fields to automatically change the Year or Month Headers – however, I suggest for your first Gantt you just manually type in the headers.
- StartMonth - Calculated Field – Number
- =IF([Start Date]="",0,MONTH([Start Date]))
- CompleteMonth - Calculated Field – Number
- =IF([Complete Date]="",0,MONTH([Complete Date]))
- StartYear - Calculated Field – Number
- =IF([Start Date]="",0,YEAR([Start Date]))
- EndYear - Calculated Field – Number
- =IF([Complete Date]="",0,YEAR([Complete Date]))
- StartMonth - Calculated Field – Number
- Color – Calculated Field – Single Line of Text
- Organize your columns in your view how you want them to appear, make sure you have the Jan-Dec month fields there.
- Then add a Content Editor Web Part to the end of the page. This web part must remain under the list view.
- Edit the CEWP in the Source Code button to add JavaScript that will render the HTML as HTML rather than text. I use to create my own but recently I started using the one supplied by “PathToSharePoint” site because Christopher’s is better.
<script type="text/javascript">
function TextToHTML(NodeSet, HTMLregexp) {
var CellContent = "";
var i=0;
while (i < NodeSet.length){
try {
CellContent = NodeSet[i].innerText || NodeSet[i].textContent;
if (HTMLregexp.test(CellContent)) {NodeSet[i].innerHTML = CellContent;}
}
catch(err){}
i=i+1;
}
}// Calendar views
var regexpA = new RegExp("\\s*<([a-zA-Z]*)(.|\\s)*/\\1?>\\s*");
TextToHTML(document.getElementsByTagName("a"),regexpA);// List views
var regexpTD = new RegExp("^\\s*<([a-zA-Z]*)(.|\\s)*/\\1?>\\s*$");
TextToHTML(document.getElementsByTagName("TD"),regexpTD);// Grouped list views
ExpGroupRenderData = (function (old) {
return function (htmlToRender, groupName, isLoaded) {
var result = old(htmlToRender, groupName, isLoaded);
var regexpTD = new RegExp("^\\s*<([a-zA-Z]*)(.|\\s)*/\\1?>\\s*$");
TextToHTML(document.getElementsByTagName("TD"),regexpTD);
};
})(ExpGroupRenderData);// Preview pane views
if (typeof(showpreview1)=="function") {
showpreview1 = (function (old) {
return function (o) {
var result = old(o);
var regexpTD = new RegExp("^\\s*<([a-zA-Z]*)(.|\\s)*/\\1?>\\s*$");
TextToHTML(document.getElementsByTagName("TD"),regexpTD);
};
})(showpreview1);
}</script>
- In SharePoint Designer convert the List View to a Data View.
- Now you will notice that your month fields are displaying the HTML. Don’t worry about that as the JavaScript won’t render it as HTML in this data view mode, but it will in the resulting page.
- Here I prefer to change each cell to a fixed width, especially the month fields.
- Now click on the Jan column, and click on its chevron button, and change the “Format as:” to Rich Text. You should notice the text change to <DIV style=….</DIV> . Sometimes it doesn’t change for some reason. In those case I have to change the Format as: to Label, then save it, then change it back to Rich Text.
- Then repeat step 9 for each month field.
- Then save it.
- Then you will create a Conditional Formatting statement for each month field.
- Then repeat step 12 for each month field.
- Save your changes to your page.
- View page in browser.
- To remove the spaces in between the colored bars you need to click on the Jan-Dec columns in Split mode so that you can see their code, then in their code change all “padding=” or “spacing=” to equal 0. You will also have to set the entire table and each column to are set widths, otherwise as the monitor resolutions change they can space out again.
Now your Gantt should look like the one above. This is just the beginning. Now you can do the same thing for week views, quarter views, or yearly views, using the same basic concepts of creating calculated fields and showing them with conditional formatting in SPD.
I know I usually would explain things step by step, but I promised to get this posted for someone quickly tonight that needed it. Let me know if you need more instructions.
Also note that since we are using background colors you could not use the right click Print function. If you need to print this Gantt you should change your formula from using a background color to displaying a repeating graphic instead. That is what I did in my second version of it, which then also gave it more of the traditional 3 dimensional graph look.
In my next article I will explain how to add scroll bars to this. The scroll bars I did strictly through code. So I might work on a generic STP to upload here.
I am getting a "The formula contains a syntax error or is not supported" when I use your formula for Color in a Project Task list.
ReplyDeleteAnonymous: Thank you for catching my typo. The second STATUS field referenced above I forgot my first bracket. If you already caught that and are still getting an error, remember the name of your columns must match the names referenced in your formula, AND your Status Choice options must all match exactly too, otherwise you will get an error.
ReplyDeleteI have now corrected my formula above to have the starting bracket.
Getting a little lost on the instructions. Do you have a more full instruction set?
ReplyDeleteSean: No as a complete set would be several pages long. This above assumes you already know how to create data views in SPD, and Conditional Formatting in SPD.
ReplyDeleteWhich part are you stuck on, the original list with calculated columns and its view, or the SharePoint Designer Data View portion?
You will want to make sure you have your list's built in view with calculated columns and JavaScript applied using CEWP before you attempt the SharePoint Designer portion.
I could attempt to document it more step by step but couldn't get to that for another week at least.
Hi Linda,
ReplyDeleteYour post has very good information. I would like to view all other posts. But the background of the page is not letting me to read. I am facing hard time to read the post.
I would suggest make the backgroud clear for reading.
Thanks,
Veera
Thanks, I changed the post background to grey, hope that is better.
ReplyDeleteHi Linda,
ReplyDeleteI've used [Task Status] where you've used [Status] and [Due Date] where you've used Complete Date, and I've assumed you mean 2/1/2011 in 13.6.
I have pasted your formulas and edited them to use [Task Status] in to my calculated columns and dropped Christophe's html renderer script in a hidden CEWP underneath. I've followed your SPD steps carefully but the calculated Jan to Dec columns are being rendered as per the following example:
Test Task; width:100%;'>
Where have I gone wrong?
Mark
Mark: What is the Color field showing as the result? Sounds like the Color field is not rendering correctly.
ReplyDeleteTo verify it is that field I would change the Color field to a drop down choice field of like; Blue, Red, Black, etc (the actual text), just to see if it is the Color formula that is causing the problems.
Then if it is, try removing the Title out of the Color formula...remove this "&[Title]&"
I am going to try to upload a List Template for this, this weekend so that you have that to edit or copy from. Are you using 2007 or 2010?
Hi Linda,
ReplyDeletethanks for the prompt response. I added the Color column to my view and it renders as expected (coloured background, with the task title in black (might think about the text colour on those darker backgrounds...)
So I assume the Color formula is OK and I must have mucked up somewhere else.
I look forward to the template - I think I'll just spend the weekend on the lawn mower!
PS - I only spell the word 'color' wrong in formulas.... ;)
Mark
Linda
ReplyDeletesorry - missed the question re 2007 / 2010
I'm using 2007.
Thanks again,
Mark
Mark: I had pasted the wrong JavaScript code above. I have corrected it. Try copying the corrected JavaScript into your CEWP.
ReplyDeleteHi Linda,
ReplyDeletethanks again! Before I do that let me tell you a weird thing. I made a new list entry and it rendered perfectly, so I edited the current items without making any changes (edit and click OK) and they suddenly rendered OK too.
My main challenge now is figuring out how you have formatted your example to get the bars to look continuous etc. I'm by no means an xsl or css wizard!
Thanks again - your blog is terrific!
Mark
Mark: Good it is working. Always get it completely working in the default list view before proceeding to SharePoint Designer view.
ReplyDeleteSounds like you are now at step 7 and need to open it up in SharePoint Designer, right click on the list web part and Convert it to a Data View. Then once it is in data view follow the next steps to change the Jan-Dec fields to Rich Text rather then just text.
Then once it is displaying properly you will need to modify the individual Columns in the table to force each Jan-Dec column to be a specific width and make sure you remove ALL PADDING AND SPACING associated to the Table.
If you don't know how to do that, then change SPD to Split mode so you can see your code above you, click on Jan column, see where it is in the code, then basically in that entire section you will look for any "padding" or "spacing" words and ensure they =0
Hi Linda,
ReplyDeletethanks again - I got it all working.
How would I use a background image for each colour instead? I have been toying with a coloured calendar using Christophe's methods, but when I tried to use a calculated column to point to an image (1 px image for each required colour, stored in a SP library with read access for all users) I get a SharePoint error "The formula contains a syntax error or is not supported." and I don't want to break my Gantt chart until I can figure out why a background image is giving me grief in my calendar.
Mark
Well when changing it to display an image the calculated field formula changes significantly. First you need to make sure your image is the exact size of the row/cell, and you need to use the 'repeat' command.
ReplyDeleteA repeat command looks something like:
style="background-image: url("http://WebApp/Sites/Site/Images/icon.jpg"); background-repeat: repeat; background-color: [Color];"
If you want to paste your formula I could try to add it in.
When troubleshooting that generic formula error, I try to break the formula down to each individual command one at a time to ensure the syntax for each work and then join them after that.
So if your formula was working before you tried adding the icon, then create a different text calculated field that only displays the icon but doesn't perform any conditional statements to ensure your HTML for displaying the icon is correct.
Linda / Mark.. Hope one of you can help me here...
ReplyDeleteI religiously followed every step the way Linda explained but am getting the same error Mark was getting at some point...tried the color trick as well, but it works fine...so its gotto be something else...Helppp pleasee!!!
K
Hi Linda;
ReplyDeleteAwesome solution... lazy soul that I am, I'm wondering whether you made any headway in making an STP from this or whether the post on scrollbars is forthcoming. Thanks for all the hard work!
-Eric
Yes I do have a 2007 STP and 2010 STP. Email me at LindaChapman AT Live DOT com and I will send them to you. I don't really have a spot online yet that I have uploaded files to.
ReplyDeleteHey Linda,
ReplyDeleteHope you are well.
Thanks for the post, could you please email me the full instructions including sharepoint designer steps to the creation of the list and columns.
hassan.rami@gmail.com
That would be great,
Thanks
Rami
Hi Linda,
ReplyDeleteCould you tell me how to take a List View and convert it to a Data View?
Many thanks!!
Marta
Hi Linda,
ReplyDeleteCould you tell me how to take a List View and convert it to a Data View, please?
Thanks a lot!!!
Marta
I have been traveling some since Nov so am behind here sorry. Open your SITE in SharePoint Designer, then open your PAGE like above in SharePoint Designer, then click on the content area of the page, and RIGHT CLICK, and you will see an option to convert this page. I don't have it in front of me but I think it says convert to XSLT.
ReplyDeletegee I'd like an email as I do NOT know how to take a List View and convert it to a Data View or SharePoint Designer
ReplyDeleteYou open your page in SharePoint Designer and right click on the List View and there will be an option to Convert to Data View. Select that.
ReplyDeleteLinda can you email on r_jamgotre@hotmail.co.uk I am really struggling with colouring of GANTT view and jumpoing to current date on the timeline. The other online solutions using JQuery with CEWP etc are not working for me.
ReplyDeleteThe formulas are not working when creating the colums using Calucualted and Single line of text; comes up with a Syntax error.
ReplyDeleteHi Linda,
ReplyDeleteThanks for the post, could you please post me detailed step by step instruction for the above.
vprd11@gmail.com
Many Thanks
Vivek
Hi,
ReplyDeleteThanks for the post, could you please email me the full instructions including sharepoint designer steps to the creation of the list and columns. I am new to sharepoint.
pavi.spm@gmail.com
Thanks,
Pavi.
I have used Gantt charts and Christine's code before. However, I do not have access to SPD. can I still change the color of the Gantt bars?
ReplyDeleteI am using SP 2010.
Thanks,
Hank
Hi Linda -
ReplyDeleteThanks for posting this, I found it helpful in some related work I was doing. Can you tell me if you were able to include filters in the column headings on a DVWP? I have sorting enabled and three view areas (first with 3 set columns, second with 20 or columns you can scroll horizontally between, and third being the Gantt chart). However, I can't seem to get the filters on the columns.
Thanks,
Dan
Hie Linda,
ReplyDeleteI am a newbie to SharePoint,Can you email me the procedure for creating list view and convert it to a data View
Your help will be highly appreciated.
Thanks and Regards
sabina
sabina.ansari@hotmail.com
I want to create project resource management gantt chart. There some milestones like design, coding, testing, etc. If a resource is added or removed then gantt chart should dynamically changed. Is it possible to create such gantt chart using share point designer. Can you please guide me.
ReplyDeleteThanks in advance.
Hi Linda
ReplyDeleteI've been working through this and its almost working but I seem to be getting the following inside the formatting statements for each of the months "=DIV style='background-color:"SPAN style='width:100%;background-color:Chartreuse;'>/SPAN;width:100%;/DIV"
If only I could work out why, I've checked the fields and they only have what you have in above ="DIV style='background-color:"&Color&"; width:100%;'> /DIV"
What is being rendered is only ;width:100%;> which I would expect just can't work out where the SPAN tags are coming from.
The color field is working fine.
If there is any help you could provide I would be very grateful.
Jim
Hi Linda,
ReplyDeleteThe option to convert LVWP to DVWP in Designer 2013 is no more available. In designer 2010, when we used to select the code and right click on it, it used to show me the option (Convert to XSLT Dataview). Is there any other way to convert the same?
Hi Linda,
ReplyDeleteShould this solution be able to work in SharePoint 2013? I have worked through the example and cannot seem to get it work in 2013 like I can in 2007!
I used the same steps but the JavaScript doesn't seem to be working as the to is shown as text instead of qualifying as code and showing code. I used a script editor web part instead of a content editor webpart but cannot see why this should be a problem, I have ensured the script is being run by adding an alert.
Any idea why the JS might not be doing the job?
John
Hello, I am having a bit of trouble implementing this. Did you ever do more extensive instructions for this?
ReplyDelete