Finally: Dynamic charting in WSS, no code required! – Part 2

Please Join the Discussion

1. Cloudno9 says:

   April 28, 2009 at 1:36 pm

   I changed the code to point to the jQuery at the address shown below and I follow all the steps you mentioned in your post but cant seems to get the chart.

   am I missing something or doing something wrong?

   here is the code that I am using in CEWP:

   —Bar Chart Using Google Charting API

   if(typeof jQuery=="undefined"){
   	var jQPath="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/";
   	document.write("");
   }
       /*
       * Google Chart for list column totals
       * By Claudio Cabaleyro (2009)
       */
       Array.prototype.max = function() { //  function to get the maximum value of Y axis
       var max = this[0];
       var len = this.length;
       for (var i = 1; i  max) max = this[i];
       return max;
       }
       function BuildChartURL(Data, Names)  // Adjust Chart Properties below - See Google Charts API for reference
       {
       MaxData=  Math.round(1.1*Data.max()).toString();
       var  DataPoints= "&chd=t:"+Data.join(",");
       var  DataTitles= "&chl="+Names.join("|");
       var  ChType= "cht=bvs";  // Vertical bars type
       var  ChSize = "&chs=300x200"; //HeighxWidth in px
       var  ChScale = "&chds=0,"+MaxData; //Vertical scale: 0 through 10% over max  series value
       var  ChLabels ="&chm=N,000000,0,-1,11,-1";
       return ("");
       }
       var ArrayTitle= $('.ms-unselectedtitle:html');  //find all columns in titles row
       var TitleTxt = new (Array);
       $.each(ArrayTitle, function(i,e)
       {
       TitleTxt[i] = $(e).text(); // Store ALL column titles in TitleTxt
       });
       var ArrayTotal= $('.ms-vb2', $('#aggr'));  //find all columns in totals row
       var TotalTxt = new (Array);
       $.each(ArrayTotal, function(i,e)
       {
       TotalTxt[i] =  $(e).text().substr($(e).text().indexOf("= ")+2); // Store ALL column  totals in TotalTxt
       });
       var Titles = new (Array);
       var Totals = new (Array);
       $.each(TitleTxt, function(i,e) // clear  empty elements in totals row
       {
       if (TotalTxt[i] != ""  && TotalTxt[i] != null)
       {
       Titles.push(TitleTxt[i]);
       Totals.push(TotalTxt[i]);
       }
       });
       $("#jLoadMe").append(BuildChartURL(Totals, Titles))

   –

   Reply
2. Cloudno9 says:

   April 28, 2009 at 1:38 pm

   Just wanted to let you know that I do have the div line at the top but when i submitt it here it took it out…

   Reply
3. John G says:

   April 30, 2009 at 4:36 pm

   I see it will take the total sum number in a list, but will it create a bar on the graph for each individual group sum from a list of values? Maybe to difficult w/o excel services?

   Reply
4. scott says:

   May 1, 2009 at 4:32 pm

   I was able to get the single pie chart and the multiple pie charts to work, but not this one.

   Reply
5. ryan says:

   May 5, 2009 at 11:21 am

   i was unable to get the chart to render as well. i am using averages not totals but should pull into the chart all the same. Any help?

   Reply
6. Richard Duffy says:

   May 10, 2009 at 7:36 am

   Hi,

   I also have been unable to get the chart to render. I have tried various methods to call in the jquery.js code but still no joy!

   Any help appreciated

   Reply
7. Craig says:

   June 5, 2009 at 9:26 am

   I’ll add myself to the group above … i can get the first one and the multiples to work fine, but this one refuses to render.

   Any ideas?

   Thanks,

   Reply
8. Pritesh Gandhi says:

   June 26, 2009 at 3:03 am

   Can u please send Code Through Mail Because i dont Chart is not display.

   Thanks in Advance…

   Reply
9. Mike Keenan says:

   July 9, 2009 at 2:50 pm

   Add my voice to the chorus. I can not get this to work either

   Reply
10. EndUserSharePoint says:

    July 9, 2009 at 4:55 pm

    I’ll have Claudio take another look at this one to see where the problem is. For those of you who say it doesn’t work, can you be more specific? How far does it get before it bails? Does it break anything? What does “it doesn’t work” mean?

    Thanks,
    Mark

    Reply
11. EndUserSharePoint says:

    July 9, 2009 at 5:13 pm

    Just to make sure… did everyone change the path to jQuery to the instance within their own environement? The code same points to the way I have it set. Yours is probably in a different location.

    Check your source attribute and make sure it's pointing to your instance of jQuery.

    Reply
12. Mike Keenan says:

    July 10, 2009 at 7:27 am

    simple list: 3 columns title, Jan, Feb
    2 items: milk, 50, 100
    butter,40, 400
    summing Jan and Feb columns

        // To load jQuery (redefine the path  if necessary)
    
        /*
        * Google Chart for list column totals
        * By Claudio Cabaleyro (2009)
        */
        Array.prototype.max = function() { //  function to get the maximum value of Y axis
        var max = this[0];
        var len = this.length;
        for (var i = 1; i  max) max = this[i];
        return max;
        }
        function BuildChartURL(Data, Names)  // Adjust Chart Properties below - See Google Charts API for reference
        {
        MaxData=  Math.round(1.1*Data.max()).toString();
        var  DataPoints= "&chd=t:"+Data.join(",");
        var  DataTitles= "&chl="+Names.join("|");
        var  ChType= "cht=bvs";  // Vertical bars type
        var  ChSize = "&chs=300x200"; //HeighxWidth in px
        var  ChScale = "&chds=0,"+MaxData; //Vertical scale: 0 through 10% over max  series value
        var  ChLabels ="&chm=N,000000,0,-1,11,-1";
        return ("");
        }
        var ArrayTitle= $('.ms-unselectedtitle:html');  //find all columns in titles row
        var TitleTxt = new (Array);
        $.each(ArrayTitle, function(i,e)
        {
        TitleTxt[i] = $(e).text(); // Store ALL column titles in TitleTxt
        });
        var ArrayTotal= $('.ms-vb2', $('#aggr'));  //find all columns in totals row
        var TotalTxt = new (Array);
        $.each(ArrayTotal, function(i,e)
        {
        TotalTxt[i] =  $(e).text().substr($(e).text().indexOf("= ")+2); // Store ALL column  totals in TotalTxt
        });
        var Titles = new (Array);
        var Totals = new (Array);
        $.each(TitleTxt, function(i,e) // clear  empty elements in totals row
        {
        if (TotalTxt[i] != ""  && TotalTxt[i] != null)
        {
        Titles.push(TitleTxt[i]);
        Totals.push(TotalTxt[i]);
        }
        });
        $("#jLoadMe").append(BuildChartURL(Totals, Titles))
    

    Reply
13. Mike Keenan says:

    July 10, 2009 at 7:28 am

    sorry looks like src truncated, the jquery source defined as

    http://ajax.googleapis.com/ajax/libs/jquery/1.3/jquery.min.js

    Reply
14. EndUserSharePoint says:

    July 10, 2009 at 11:19 am

    Mike – Does that code work for you? — Mark

    Reply
15. Mike Keenan says:

    July 10, 2009 at 2:34 pm

    Well I got this working. Turned out it was mostly formatting from copying the code. Changes I made included:

    & globally changed to &

    Math.round(1.1*Data.max()) globally changed to Math.round(1.1*Data.max()+1) //I found that if I didn’t add +1 to the end then value of max never displayed

    && globally changed to &&

    I suggest the code include an alert statement just before the return statement that prints out what is being returned.

    This is very cool now that it works!

    Reply
16. Claudio says:

    July 10, 2009 at 8:30 pm

    I am really sorry for the problems that I did not noticed on code transcription. I have just sent the right script to Mark so he can update it in the post.
    I have also corrected another potential problem you could find with the above version if numbers to be plotted are greater than 1,000 (include commas).

    Hope this time goes well.
    Claudio

    Reply
17. EndUserSharePoint says:

    July 11, 2009 at 11:02 pm

    I am working with Claudio on the update. He has isolated the main problem and we can now get a graph/chart to show, but there is a slight problem with the label offset… stay tuned. — Mark

    Reply
18. EndUserSharePoint says:

    July 11, 2009 at 11:35 pm

    Here’s the latest, updated script. There is still a problem with label names being off by one. Let us know if you find a solution before we do:

    <div id="jLoadMe" class="content"></div>
    
    <script src="/jquery/jquery.js" type="text/javascript">
       // To load jQuery (redefine the path if necessary)
    </script>
    
    <script type="text/javascript">
    /*
    * Google Chart for list column totals
    * By Claudio Cabaleyro (2009)
    */ 
    
    $("document").ready(function ($) { 
    
    Array.prototype.max = function() {							// function to get the maximum value of Y axis
    var max = this[0];
    var len = this.length;
    for (var i = 1; i < len; i++) if (this[i] > max) max = this[i];
    return max;
    }
    
    function BuildChartURL(Data, Names)							// Adjust Chart Properties below - See Google Charts API for reference
    	{
    		MaxData= Math.round(1.1*Data.max()).toString();
    	var 	DataPoints= "&amp;chd=t:"+Data.join(",");
    	var 	DataTitles= "&amp;chl="+Names.join("|");
    	var 	ChType= "cht=bvs";						// Vertical bars type
    	var 	ChSize = "&amp;chs=300x200";					//HeighxWidth in px
    	var 	ChScale = "&amp;chds=0,"+MaxData;				//Vertical scale: 0 through 10% over max series value
    	var	ChLabels ="&amp;chm=N,000000,0,-1,11,-1";
    	return	("<p><IMG src='http://chart.apis.google.com/chart?"+ChType+ChSize+DataPoints+DataTitles+ChScale+ChLabels+"'/></p>");
    	}
    
    var ArrayTitle= $('.ms-unselectedtitle .ms-vb');		//find all columns in titles row
    
    var TitleTxt = new (Array);
    $.each(ArrayTitle, function(i,e)
    	{
    	TitleTxt[i] = $(e).text();	// Store ALL column titles in TitleTxt
    	});
    
    var ArrayTotal=$(' #aggr .ms-vb2'); 	//find all columns in totals row
    var TotalTxt = new (Array);
    $.each(ArrayTotal, function(i,e)
    	{
    	TotalTxt[i] = $(e).text().substr($(e).text().indexOf("= ")+2);	// Store ALL column totals in TotalTxt
    	});
    
    var Titles = new (Array);
    var Totals = new (Array);
    $.each(TitleTxt, function(i,e)		// clear empty elements in totals row
    	{
    	if (TotalTxt[i] != "" && TotalTxt[i] != null)
    		{
    		Titles.push(TitleTxt[i]);
    		Totals.push(parseFloat(TotalTxt[i].replace(',','')));
    		}
    	});
    
    $("#jLoadMe").append(BuildChartURL(Totals, Titles))
    alert(BuildChartURL(Totals, Titles)); // debug line
    
    })(jQuery);
     </script>
    

    Reply
19. EndUserSharePoint says:

    July 13, 2009 at 8:54 pm

    Been working on this for a couple days, troubleshooting and think I’ve got it worked out. Try this. If I get confirmation it is working, I’ll get it cleaned up a bit and publish a new article.

    <div id="jLoadMe" class="content"></div>
    
    <script type="text/javascript">
    if(typeof jQuery=="undefined"){
    	var jQPath="/javascripts/";
    	document.write("<script src='",jQPath,"jquery.min.js' type='text/javascript'><\/script>");
    }
    </script>
    
    <script type="text/javascript">
    /*
    * Google Chart for list column totals
    * By Claudio Cabaleyro (2009)
    */ 
    
    $("document").ready(function ($) { 
    
    Array.prototype.max = function() {							// function to get the maximum value of Y axis
    var max = this[0];
    var len = this.length;
    for (var i = 1; i < len; i++) if (this[i] > max) max = this[i];
    return max;
    }
    
    function BuildChartURL(Data, Names)							// Adjust Chart Properties below - See Google Charts API for reference
    	{
    		MaxData= Math.round(1.1*Data.max()).toString();
    	var 	DataPoints= "&amp;chd=t:"+Data.join(",");
    	var 	DataTitles= "&amp;chl="+Names.join("|");
    	var 	ChType= "cht=bvs";						// Vertical bars type
    	var 	ChSize = "&amp;chs=300x200";					//HeighxWidth in px
    	var 	ChScale = "&amp;chds=0,"+MaxData;				//Vertical scale: 0 through 10% over max series value
    	var	ChLabels ="&amp;chm=N,000000,0,-1,11,-1";
    	return	("<p><IMG src='http://chart.apis.google.com/chart?"+ChType+ChSize+DataPoints+DataTitles+ChScale+ChLabels+"'/></p>");
    	}
    
    var ArrayTitle= $(".ms-unselectedtitle .ms-vb",".ms-viewheadertr ");		//find all columns in titles row
    
    var TitleTxt = new (Array);
    $.each(ArrayTitle, function(i,e)
    	{
    	TitleTxt[i] = $(e).text();	// Store ALL column titles in TitleTxt
    	});
    
    var ArrayTotal=$('#aggr tr > td:not(.ms-vb)') 	//find all columns in totals row
    var TotalTxt = new (Array);
    $.each(ArrayTotal, function(i,e)
    	{
    	TotalTxt[i] = $(e).text().substr($(e).text().indexOf("= ")+2);	// Store ALL column totals in TotalTxt
    	});
    
    var Titles = new (Array);
    var Totals = new (Array);	
    
    $.each(TitleTxt, function(i,e)		// clear empty elements in totals row
    	{
    	if (TotalTxt[i] != "" && TotalTxt[i] != null)
    		{
    		Titles.push(TitleTxt[i]);
    		Totals.push(parseFloat(TotalTxt[i].replace(',','')));
    		}
    	});
    $("#jLoadMe").append(BuildChartURL(Totals, Titles))
    // alert(BuildChartURL(Totals, Titles)); // debug line
    
    })(jQuery);
     </script>
    

    Reply
20. Pritesh gandhi says:

    July 15, 2009 at 8:27 am

    Thanks Claudio.

    Now it is working smoothly.

    Reply
21. Mike Keenan says:

    July 23, 2009 at 9:35 am

    This works very nicely Claudio. Now since I know Mark, and hopefully you, love a challenge….I had a request to make this functionality work in WSS 2 but the table structure is not the same, so a search for ms-unselecteditem in wss2 does not provide the totals row. I made a simple 3 column list in wss 2. (Department, Jan, Feb). Put one row in (Sales, 5, 4). The code for the view is (hopefully this makes it thru with this post, I changed the word java script to js):

    ctx = new ContextInfo();
    ctx.listBaseType = 0;
    ctx.listTemplate = 100;
    ctx.listName = “{D26B7171-29EC-404A-8558-6CA64AE0811D}”;
    ctx.listUrlDir = “Lists/Wins”;
    ctx.HttpPath = “/_vti_bin/owssvr.dll?CS=109″;
    ctx.HttpRoot = “”;
    ctx.imagesPath = “/_layouts/images/”;
    ctx.PortalUrl = “/mysite/”;
    if (ctx.PortalUrl == “”) ctx.PortalUrl = null;
    ctx.displayFormUrl = “/Lists/Wins/DispForm.aspx”;
    ctx.editFormUrl = “/Lists/Wins/EditForm.aspx”;
    ctx.isWebEditorPreview = 0;
    ctx.ctxId = 2;

    ctx2 = ctx;
    DepartmentJanFebSum = 5Sum = 4Sales54

    Any thoughts on how we can leverage this very cool solution in wss2?

    Thanks for everything.
    Mike

    Reply
22. Claudio says:

    July 27, 2009 at 8:26 pm

    Hi Mike,
    I unfortunately do not know WSS 2.0 builds the pages, but I think you can still try to find a pattern that allows you to get titles and totals with jQuery. I strongly recommend you to take a look at this debugging session from Paul Grenier:
    http://www.endusersharepoint.com/?p=1417
    Even if you cannot use Firefox and Firebug, you can review your page HTML source to find such patterns and try to build new selectors.
    Let me know how it was.

    Reply
23. Oscar says:

    August 4, 2009 at 1:35 pm

    Claudio, I have tried this and got to graph out data, but labels on the x axis are all overwritting eachohter. What happens if you x-axis is not 3 letter abbreviations for months? Is there a way to get it to space the bars so that the labels can fit?

    Reply
24. Claudio says:

    August 5, 2009 at 9:12 pm

    Oscar,
    One option is playing with spacing between bars, changing the following line (I am replacing the & symbol by the word AMPERSAND as it might be wrongly displayed in this comment):

    ChScale = “AMPERSANDamp;chds=0,”+MaxData;

    to:

    ChScale = “AMPERSANDamp;chds=0,”+MaxData + “AMPERSANDamp;chbh=a,8″;

    This will double the space between bars.
    Another option is to change to an horizontal bars chart. For this, you need to replace two lines in the code (I show the modified versions here):

    1) var ChType= “cht=bhs”;
    2) var DataTitles= “AMPERSANDamp;chxt=x,yAMPERSANDamp;chxl=0:||1:|”+Names.reverse().join(”|”)+”|”;

    If you want to experiment with other options, take a look at this page:
    http://code.google.com/apis/chart/styles.html#bar_width

    regards,
    Claudio

    Reply
25. Oscar says:

    August 6, 2009 at 9:26 am

    Thanks for the quick reply.

    I tried the horizontal bar graph but I am having some trouble as the x-axis is not scaling coorectly or the bars are not placing right on the x-axis, although they are in the right proportions. Also the Y-axis is just pulling numbers from 0-X for as many items as I have, instead of the labels of those items. See the link here for an example:

    http://workspace.office.live.com/#oscarb2/Documents/chart1png.png

    Reply
26. Oscar says:

    August 6, 2009 at 11:13 am

    Claudio sorry about the link in the above posting, here is the correct link to get to it example of what I am getting and my list view is here:
    https://cid-500e0ee3b4860486.skydrive.live.com/self.aspx/gsoc/chart1.jpg

    Reply
27. Claudio says:

    August 13, 2009 at 7:49 pm

    Hi Oscar,
    Just guessing: take another look at the syntax on the line 2) in my previous answer, as the quotes do not render quite right in the post. Use straight double quotes in your code editor and do not forget also to change back the word AMPERSAND by the & symbol.

    Reply
28. Jamen says:

    August 17, 2009 at 3:00 pm

    This is a great solution! I have had some luck with it to generate both pie charts and bar charts.

    However, I have been trying to get this code to work with Google Charts “Google-O-Meter”. I just have one averaged column that I wanted to display using the meter as a visual representation. The column shows overall satisfaction based on a 1-10 rating. Is there an easy way to get this to work for this type of chart???

    Reply
29. Jamen says:

    August 20, 2009 at 2:31 pm

    Nevermind, finally got this working nicely after manipulating the variables enough. Thanks again for the script.

    Reply
30. Dhamodaran says:

    October 20, 2009 at 12:04 am

    hi.. i am new to sharepoint… i have the requirement as, to display a task list in a bar chart and to get it updated whenever there is a change in task list… i tried excel web services.. but it dint work.. found this post interesting… i am done with all the steps mentioned here… but still the chart is not displaying.. i doubt whether i shud do anything with jquery.. pls help me out

    Reply
31. Barbara Stroud says:

    January 15, 2010 at 11:23 am

    I have two questions as this topic seems to have cooled for a while. There is mention of code to add to a CEWP as part of the instructions, but I sure can’t seem to find it. I see code examples from other people, but not the original post.

    Second, has anyone come up with a stacked bar chart solution? Even Google API didn’t appear to have anything. We want to provide execs with a chart that shows the number of Green, Amber and Red projects for each region.

    Any help appreciated!

    Reply
    • EndUserSharePoint says:

      January 15, 2010 at 12:33 pm

      Barbara – The code should be visible now. Regarding a stacked bar chart, here’s the Google documentation for creating stacks:

      http://code.google.com/apis/chart/types.html

      Reply
32. Barbara Stroud says:

    January 15, 2010 at 3:23 pm

    Thanks, Mark. I got it all. I’ll post if I have any additional questions.

    Reply
33. Joe Tobey says:

    January 20, 2010 at 1:48 am

    Where is the latest code you refer to on July 11th? The Code I pulled down doesn’t have alerts and is not running in my SharePoint session.

    Reply
    • EndUserSharePoint says:

      January 20, 2010 at 11:28 am

      Joe – Take another look… should be available now. — Mark

      Reply
34. Joe Tobey says:

    January 20, 2010 at 11:32 am

    Thanks Mark…I will work with that one.

    Reply
35. Tahir says:

    April 15, 2010 at 2:05 pm

    I have extended this technique to display the Intractive Charts

    if(typeof jQuery==”undefined”){
    var jQPath=”http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/”;
    document.write(”");
    }

    var ColValue = new Array();
    var ColName = new Array();

    // Getting the Data
    $(”document”).ready(function(){
    var arrayList=$(”td.ms-gb:contains(’:')”);
    var coord= new Array();
    var labels= new Array();
    $.each(arrayList, function(i,e)
    {
    var MyIf= $(e).text();
    var txt= MyIf.substring(MyIf.indexOf(’(')+1,MyIf.length-1); // Extract the ‘Y’ coordinates
    coord[i]=txt;
    var txt1= MyIf.substring(MyIf.indexOf(’:')+2,MyIf.indexOf(”(”)-1); // Extract the labels
    labels[i]=txt1+”(”+txt+”)”; //add also coordinates for better read
    });

    ColValue = coord;
    ColName = labels;
    });

    //Graph Rendering
    google.load(”visualization”, “1″, {packages:["columnchart"]});
    google.setOnLoadCallback(drawChart);
    function drawChart() {
    var data = new google.visualization.DataTable();

    data.addColumn(’string’, ‘Department’);
    data.addColumn(’number’, ‘Department’);

    data.addRows(ColValue.length);

    for (i=0; i<ColValue.length; i++)
    {
    data.setValue(i, 0, ColName[i]);
    data.setValue(i, 1, parseInt(ColValue[i]));
    }

    var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
    chart.draw(data, {width: 600, height: 240, is3D: true, title: 'Graph Title'});
    }

    Reply
36. Rajiv Vishwa says:

    May 7, 2010 at 8:34 pm

    Hi, I’m still having that offset label prob, can you please help. Check the screenshot http://img.a4apphack.com/other/sharepointdash-barchartsprob.jpg

    The code which I’m using is here, http://gist.github.com/394176, this code displays the graph properly except
    1. One less than the total columns
    2. Bar labels does not correspond to the value

    Claudio, I’m in need of your help.

    Reply
37. Luc says:

    July 12, 2010 at 11:21 am

    Hello, is there any chance to split more charts in the same page?
    What i would like to do is having a stacked bar chart on the top of the page, and then a set of 3 different pie charts at the bottom. Whenever i try to add a new CEWP with the code to the page, the only result i achieve is to double the number of the charts visible…

    Thanks!

    /Luc

    Reply
    • Luc says:

      July 12, 2010 at 12:39 pm

      Just for confirmation, i was referring to the multiple pie charts article (part 3).

      Thanks!

      Reply
38. Robby Thesman says:

    August 19, 2010 at 12:11 am

    Hi,
    Does it work at all with WSS 2?

    Reply

Trackbacks

1. Finally: Dynamic charting in WSS, no code required! | End User SharePoint says:

   April 20, 2009 at 4:18 pm

   [...] Finally: Dynamic charting in WSS, no code required! – Part 2 [...]

   Reply
2. Live demo: FusionMaps on SharePoint « Path to SharePoint says:

   May 5, 2009 at 3:56 am

   [...] I found great interest in the charting solution recently proposed by Claudio Cabaleyro (and relayed on endusersharepoint.com). [...]

   Reply
3. Finally: Dynamic charting in WSS, no code required! - Part 3 - Multiple Pie Charts | EndUserSharePoint.com says:

   February 1, 2010 at 8:34 am

   [...] Finally: Dynamic charting in WSS, no code required! – Part 2 [...]

   Reply