Supporters of End User
Web

Finally: Dynamic charting in WSS, no code required!

Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.
Categories:Javascript and jQuery; Site Manager/Power User; MOSS; WSS; 2007; 2010

A note from Mark Miller: When i first saw this solution, my jaw dropped! Could this actually be a viable alternative for WSS users who don’t have Excel Services? You be the judge… please give Claudio some feedback on how you will use his solution.

 Update: The script has been updated so that the page loads before the script is triggered. You can read more about it in the comments.

Inspired by the GREAT jQuery series by Paul Grenier at EndUserSharePoint and the TERRIFIC ideas of Christophe at Sparklines and other charts in SharePoint lists, I have built a small jQuery script and used Google Charts service to get the following (click to enlarge):

Dynamic Charting
I based the example in a FAQ list, so the chart shows the distribution of questions among the different "Topics". Being a little more general, the chart shows the statistical distribution of items from an underlying SharePoint list, being the groups defined by the meaning of the column chosen to "Group by" in the list view. You can think of representing statuses of an issues list, completion percentages on a task list, and so on. You can even explore the use of calculated columns in innovative ways to define your groups... (I just thought about that!)

Solution

I will not present a detailed step-by-step approach here, you can drop me a note if you have doubts. Recipe:
  1. On the list you want to base your chart, create a "Group By" view.
  2. Create a new web part page and insert the just created list view.
  3. Insert a Content Editor Web Part and paste the code below (remember to use the Source Editor).
That's it! NOTICE: if you are new to jQuery, you need to install it prior to use the code. You just need to upload it on a Document Library as explained here, Do not forget to adjust the path at the beginning of the code, to point to your library. *** UPDATE: when you copy the code to your script editor, take a look at quotes (single and double) as they can cause a wrong script.
<div id="jLoadMe" class="content"><strong>Pie Chart Using Google Charting API</strong></div>
<script type="text/javascript">
if(typeof jQuery=="undefined"){
	var jQPath="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/"; 
	document.write("<script src='",jQPath,"jquery.js' type='text/javascript'><\/script>");
}
</script>

<script 
type="text/javascript">
$("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 
	});
	var txt= coord.join(",");
	var txt1= labels.join("|");
	// Adjust Chart Properties below - See Google Charts API for reference
	var vinc= "<IMG src='http://chart.apis.google.com/chart?cht=p3&chs=750x200&chd=t:"+txt+"&chl="+txt1+"'/>";
	$("#jLoadMe").append("<p>"+vinc+"</p>")
});

</script>

Tips and Tricks

TIP I: you can hide the list view in your page so only the chart is rendered (click to enlarge):
Dynamic Charting
To do that, edit the page and look for the "Hidden" check box in the web part panel(click to enlarge):
Dynamic Charting
TIP II: you can experiment changing the chart type, colors and size through the parameters in the URL by the end of the code. Check the Google Charts API documentation and have fun!!! 

Comments

2 Minute Screencast: Dynamic Charts in WSS - no code required | End User SharePoint

Finally: Dynamic charting in WSS, no code required!

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

Posted 20-Apr-2009 by 2 Minute Screencast: Dynamic Charts in WSS - no code required | End User SharePoint
Kevin

Finally: Dynamic charting in WSS, no code required!

How do I connect the Content Editor Web Part to the Group By view that I added? I have multiple group by views on the page I'm adding the chart and I want to point the chart to just one of these.

Posted 20-Apr-2009 by Kevin
Nick

Finally: Dynamic charting in WSS, no code required!

Fantastic Idea! I wasn't aware of the Google Graphing API until just now!

I'm having an issue though, it seems as if using the code above it's not parsing my list correctly.

When I get to the page, the portion which should be a graph is an empty image.

The url it's trying to use is:
http://chart.apis.google.com/chart?cht=p3&chs=750x200&chd=t:&chl=

Seems as if it's not filling out txt and txt1:
=t:"+txt+"&chl="+txt1+"

Is there some additional configuration needed to this code to tell it how to find our list?

Posted 20-Apr-2009 by Nick
Nick

Finally: Dynamic charting in WSS, no code required!

I figured out my issue. For some reason my Javascript jQuery was firing before the table loaded, as such there were 0 items in the array.

Started by doing an alert(arrayList.length) and getting 0 back.

As such, a much more clever fella here at work recommended me wrap the code in a statement to allow the document to load prior to firing off and that fixed the issue!

[code]

$("document").ready(function(){

var arrayList=$("td.ms-gb:contains(':')");
..... more code.....

});

Posted 20-Apr-2009 by Nick
EndUserSharePoint

Finally: Dynamic charting in WSS, no code required!

Nick - Did you create a document library, load all the files extracted into that library and then change the references in the .dwp parts>? -- Mark

Posted 20-Apr-2009 by EndUserSharePoint
EndUserSharePoint

Finally: Dynamic charting in WSS, no code required!

Kevin - You'll have to figure that one out on your own unless Claudio jumps in and has something to add. -- Mark

Posted 20-Apr-2009 by EndUserSharePoint
Nick

Finally: Dynamic charting in WSS, no code required!

I had someone much smarter than me look at it and they came to the conclusion through the use of some creative "alerts()" that my javascript was trying to execute before the list was even loaded..

So initially my array.length() was 0...

My co-worker recommended a small addition to the code to ensure it only fires once the document is fully rendered, something like the following:

$("document").ready(function(){


});

Encasing all of the jquery code into this "block" ensured it wouldn't fire until the document was loaded completely and then my graph rendered fine!

Posted 20-Apr-2009 by Nick
EndUserSharePoint

Finally: Dynamic charting in WSS, no code required!

Nick - Thanks for the follow up. I'll have some people take a look at confirm. -- Mark

Posted 20-Apr-2009 by EndUserSharePoint
AutoSponge

Finally: Dynamic charting in WSS, no code required!

Nick is right. The jQuery document ready event will help immensely.

Posted 20-Apr-2009 by AutoSponge
Sandeep

Finally: Dynamic charting in WSS, no code required!

Does this one sends data to google and get u the new image every time .. trying to see if this has dependency on Google site ?

Posted 20-Apr-2009 by Sandeep
EndUserSharePoint

Finally: Dynamic charting in WSS, no code required!

Paul - I know you have a standard way to do this. Would you recommend the way that Nick is showing or is there a "best practices" we should be considering here? -- Mark

Posted 21-Apr-2009 by EndUserSharePoint
EndUserSharePoint

Finally: Dynamic charting in WSS, no code required!

Sandeep - Yes, there is a call made to the Google API. I am running a secure connection on my SharePoint site, so everytime the page loads, I get an "Insecure Data" type warning and that's where it's coming from. -- Mark

Posted 21-Apr-2009 by EndUserSharePoint
Claudio

Finally: Dynamic charting in WSS, no code required!

Thanks Mark and all for the interest and sorry if the script is a little buggy (it is). Some comments to questions raised above:

Kevin, I am working on a multiple charts per page solution, that I hope will address your point. The script as it currently is just uses the first List View Web Part in the page.

Nick you fixed it right! I just have experienced your problem today, when I tried to put the CEWP to the left of the list.

Finally, I am trying to catch up on Paul's great best practices, but sometimes I cannot cope with his speed... next time I will do better.

Best regards!

Posted 21-Apr-2009 by Claudio
SharePoint Daily

Finally: Dynamic charting in WSS, no code required!

SharePoint Daily for April 21, 2009... Top News Stories Ride The SharePoint ECM Wave (Business Solutions) It was a little more than two years...

Posted 21-Apr-2009 by SharePoint Daily
stevec

Finally: Dynamic charting in WSS, no code required!

sparklines is one of new visualization type of charts. There are more at style chart. It's a free service, you can also create your own charts and embed them.

Posted 21-Apr-2009 by stevec
Colleen Parker

Finally: Dynamic charting in WSS, no code required!

This is fantastic! Thank you for sharing this type of "no code" content. It is proving to be extremely valuable in customizing our SharePoint sites.

Posted 22-Apr-2009 by Colleen Parker
chiqnlips

Finally: Dynamic charting in WSS, no code required!

Awesome solution! Anyone know how we might be able to display the actual percentage of each item?

Posted 24-Apr-2009 by chiqnlips
Adding Graphs, Bar and Pie Charts to SharePoint « Codeless Solutions for SharePoint

Finally: Dynamic charting in WSS, no code required!

[...] Cabaleyro posted this article on endusersharepoint.com on how to use jquery and google charts to pull info off of SharePoint Lists and display it as a [...]

Posted 24-Apr-2009 by Adding Graphs, Bar and Pie Charts to SharePoint « Codeless Solutions for SharePoint
Bryon Wyly

Finally: Dynamic charting in WSS, no code required!

I found this very informative and I was able to make a graph of a list in less than a minute.

I did find that I was limited to one graph per page though so I figured a work around with the code you provided. I posted it on my blog here.

http://wyly.wordpress.com/category/sharepoint-dashboard/

I'm thinking of digging deeper here writing about how to inclorporate the bar graphs, charts and google-o-meters with the same code.

Thanks for a great post!

Posted 24-Apr-2009 by Bryon Wyly
Colleen Parker

Finally: Dynamic charting in WSS, no code required!

FANTASTIC. I put this to immediate use, it's amazing!

Posted 24-Apr-2009 by Colleen Parker
Finally: Dynamic charting in WSS, no code required! - Part 2 | End User SharePoint

Finally: Dynamic charting in WSS, no code required!

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

Posted 28-Apr-2009 by Finally: Dynamic charting in WSS, no code required! - Part 2 | End User SharePoint
Create Dynamic Bar and Pie Charts in WSS with any RSS feed: No code required | End User SharePoint

Finally: Dynamic charting in WSS, no code required!

[...] A note from Mark Miller: Last week, Claudio provided us with a solution for creating charts against a list in SharePoint: Finally - Dynamic Charting in WSS, no code required!. [...]

Posted 28-Apr-2009 by Create Dynamic Bar and Pie Charts in WSS with any RSS feed: No code required | End User SharePoint
Finally: Dynamic charting in WSS, no code required! - Part 3 - Multiple Pie Charts | End User SharePoint

Finally: Dynamic charting in WSS, no code required!

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

Posted 30-Apr-2009 by Finally: Dynamic charting in WSS, no code required! - Part 3 - Multiple Pie Charts | End User SharePoint
Sham

Finally: Dynamic charting in WSS, no code required!

I have follows the instructions and have not managed to get this to work. I have just the jquery.js in the library

Posted 30-Apr-2009 by Sham
Dishaniti

Finally: Dynamic charting in WSS, no code required!

Hello

Thanks for superb article. I implemented your example with pie chart. Thne I tried to implement bar chart using following
var vinc= "";

It is displaying bar graph, but at the bottom count and field name are overlapping. In this regard I have following queries

1. Can we increase the space between the bar, so field value should display properly
2. How can we display count of field on top of each bar respectively
3. How can I display stacked bar?

Please advise

Posted 01-May-2009 by Dishaniti
Claudio

Finally: Dynamic charting in WSS, no code required!

Sham,
check quotes when you paste the code, specially in the following line:
$("td.ms-gb:contains(':')");

Dishaniti,

1- Sure you can modify the space between bars. You just need to add a parameter in the URL line like this: &chbh=,,. Replace each expression with the number of pixels.

Check at Google Charts API documentation for more details, but you can test building the URL with some test data in notepad and pasting into a browser window. Cycle through this until you get the desired result then modify the script.

2- To add labels to the bars, you can use another parameter in the following form: &chm=N*f0*,000000,0,-1,11

Take into account that a problem arises with bar charts, not present with pie charts: you need to properly scale the chart to the values to represent in each specific scenario. This is done with another parameter "&chds". We could make this parameter variable if we put the maximum value to represent. I used this approach in Part II of this series, maybe you can try to adapt this trick here.

3- I am working on a stacked bars solution I hope to release any time soon.

Regards,
Claudio

Posted 01-May-2009 by Claudio
Colleen Parker

Finally: Dynamic charting in WSS, no code required!

Although I previously mentioned how amazing this is, after reading that it sends our internal data without encryption to Google I had to pull the charts. Great solution all the same for public content.

Posted 06-May-2009 by Colleen Parker
Nicole

Finally: Dynamic charting in WSS, no code required!

Does anyone know how to conver the total count to percentage for the pie chart?

Posted 11-May-2009 by Nicole
Make a pie chart that shows percents « Codeless Solutions for SharePoint

Finally: Dynamic charting in WSS, no code required!

[...] a pie chart that shows percents This is a quick update to Claudio Cabaleyro’s ground breaking blog on endusersharepoint.com to use jquery and google charts api to create pie charts of SharePoint [...]

Posted 12-May-2009 by Make a pie chart that shows percents « Codeless Solutions for SharePoint
Bryon Wyly

Finally: Dynamic charting in WSS, no code required!

Nicole, I just figured it out, here is the amended code to have percentages instead of sum.

http://wyly.wordpress.com/category/sharepoint-dashboard/

Just copy and paste the amended code. Hope this helps!

If you cannot send unencrypted code to google, there is a custom part chart available for free on codeplex.

You will have to talk you administrator into adding the .net chart control framework to your Sharepoint servers, but it will allow you to make charts w/o sending data outside of sharepoint. The details can be found here.

http://chartpart.codeplex.com/

Posted 12-May-2009 by Bryon Wyly
Nicole

Finally: Dynamic charting in WSS, no code required!

Thanks. i found another problem on my chart. I am trying to use the sum of the column in the group view. Not the total count of the column (some columns might have the number of 2). That's why my chart is not display correct.

How do I do sum instead of total of the columns?

thank you.

Posted 18-May-2009 by Nicole
Case Study: Dynamic Charts in SharePoint, No Code Required! | End User SharePoint

Finally: Dynamic charting in WSS, no code required!

[...] had already found the post http://www.endusersharepoint.com/?p=1537 then created a new page and added the task list as a web part then added the content editor web [...]

Posted 05-Jun-2009 by Case Study: Dynamic Charts in SharePoint, No Code Required! | End User SharePoint
Gráfico dinâmico no SharePoint « Solução Microsoft – MOSS

Finally: Dynamic charting in WSS, no code required!

[...] had already found the post http://www.endusersharepoint.com/?p=1537 then created a new page and added the task list as a web part then added the content editor web [...]

Posted 05-Jun-2009 by Gráfico dinâmico no SharePoint « Solução Microsoft – MOSS
Richard Duffy

Finally: Dynamic charting in WSS, no code required!

Finally got this working!! Not an error on with the code but in my column which is used to make the labels I was using brackets e.g text text (text) - this was causing the image not to render!

Really useful, going to see if I can get the rest to work!

Posted 22-Jun-2009 by Richard Duffy
Pritesh gandhi

Finally: Dynamic charting in WSS, no code required!

Awesome. I have read your article. Mind blowing ...

Posted 27-Jun-2009 by Pritesh gandhi
Kerrie

Finally: Dynamic charting in WSS, no code required!

Is it possible to get this to work with a Data View? This would be sweet if I could get it to work with a Data View I created using a Database Connection in SPD.

??

Thanks
Kerrie

Posted 09-Jul-2009 by Kerrie
Claudio

Finally: Dynamic charting in WSS, no code required!

Kerrie,
I am not sure if this can help, but you can try to discover if can identify a DOM object where your Data View web part renders your numbers, then modify the following line of the code:

var arrayList=$("td.ms-gb:contains(':')");

this line defines a jQuery variable that stores all the totals to be plotted, and they are found using a jQuery selector (the whole thing between parenthesis). In the standard List View web part, the totals are within an html table row with cells of class "ms-gb" that contains the ":" symbol. This translates from english into jQuery the way you see above.

Take a look around your Data View numbers using Firebug or inspect your page source code and search html tags around them that could help.

Good luck!

Posted 11-Jul-2009 by Claudio
Create Charts and Graphs for SharePoint Dashboards | End User SharePoint

Finally: Dynamic charting in WSS, no code required!

[...] Cabaleyro wrote a series of articles for us on using the Google Charts API to expose charts and graphs in SharePoint without recourse [...]

Posted 16-Jul-2009 by Create Charts and Graphs for SharePoint Dashboards | End User SharePoint
Jay

Finally: Dynamic charting in WSS, no code required!

Elegant!

I have two problems. I am grouping list items by employee name. I draw the names from an enterprise source that includes parentheses on occasion as part of the name. Your parser has a problem with that.

Secondly I am adapting your code for horizontal bars. Google has chosen to make the first value in the series the top bar, while the first label in the series is assigned to the bottom bar. So there is a mismatch using your text strings containing the values and labels. Are you working on that one?

Posted 25-Jul-2009 by Jay
Claudio

Finally: Dynamic charting in WSS, no code required!

Hi Jay,
You are right with the limitation on parenthesis, the following line:
var txt= MyIf.substring(MyIf.indexOf('(')+1,MyIf.length-1);

in plain english that could state: extract the portion of the MyIf string starting from the position of the first "(" and to the position before the first ")". In most cases, this contains the number of items within the group. But if your groups names also contain parenthesis, it wont work. As you mentioned your names occasionally contain parenthesis, I do not see an evident way to look for the number. You could try to learn on "regular expressions" that I think could help to build a better selector for your item totals (can not promise, but I will try to do that in the future).

Regarding the horizontal bars your are working on, you could try to reverse one of the arrays, like this:

var txt1= labels.reverse().join("|");

good luck!

Posted 28-Jul-2009 by Claudio
Jay

Finally: Dynamic charting in WSS, no code required!

Claudio,

I got it to work. I used lastIndexOf('(') in the parsing statement instead of indexOf.

I also changed the txt1 parsing to var txt1= MyIf.substring(MyIf.indexOf(':')+2,MyIf.lastIndexOf("(")-2); which eliminated a space and allowed me to use that string as data in an xy chart (in the case where the labels are numbers).

Posted 28-Jul-2009 by Jay
Claudio

Finally: Dynamic charting in WSS, no code required!

Hi Jay, glad to here about your fix and thanks. I know now another JavaScript method (I am far from an expert).
Regards.

Posted 06-Aug-2009 by Claudio
Rob Fahndrich

Finally: Dynamic charting in WSS, no code required!

can you use this same code/approach to create graphs from a DATAVIEW. I have some SQL queries that were created in Sharepoint Designer and I have put the DATAVIEW on my site and the data looks how I would like, would like to now just add a graph from that info. Everything I see keeps pointing back to SHAREPOINT LISTS. please advise...

Posted 17-Sep-2009 by Rob Fahndrich
Bob

Finally: Dynamic charting in WSS, no code required!

I used your code and was able to generate a pie chart very quickly. The problem I am having is the pie chart is using the number of occurences of the various values in the group. Within my view I am totaling a different field and though my view is providing subtotals by the group which is what I need, I also need the chart to reference the subtotal values rather than the number of occurences. Where in the jquery code is the group occurence being referenced and how could I modify the code to reference the subtotals instead?

Posted 06-Oct-2009 by Bob
Online Workshop – Easy Tabs for Project Dashboard « Path to SharePoint

Finally: Dynamic charting in WSS, no code required!

[...] goes to Craig Lowrie for the color coded Gantt, and Claudio Cabaleyro for the pie chart. An HTML calculated column was used to create the bar chart. Possibly related [...]

Posted 07-Oct-2009 by Online Workshop – Easy Tabs for Project Dashboard « Path to SharePoint
Claudio

Finally: Dynamic charting in WSS, no code required!

Hi Bob,
My code get the item occurences looking for the html tag that Sharepoint uses, wich is through the "td.ms-gb:contains(':')" selector.
For sub-totals, the situation is a bit more complex, actually I have been working to plot subtotals, but did not have time to finish an article on that. For you to have an idea, the selector to get the totals an sub-totals look like the following: tbody[id^='aggr'] b

Please be patient I hope to publish that soon.

Posted 21-Oct-2009 by Claudio
David Christianson

Finally: Dynamic charting in WSS, no code required!

Very slick! Thanks so much, Claudio!

Posted 30-Oct-2009 by David Christianson
Bob

Finally: Dynamic charting in WSS, no code required!

Claudio,

I have included the code we used to create a piechart using a subtotal field within the view. The view consist of a list of resources allocated by days against various activity types. I grouped the data by the various activity types and totaled the days allocated field. I was able to use the days subtotal field by each group within a pie chart to reflect the total days allocated by activity type within the pie chart. I also used some code posted on this site to customize the label for each subtotal replacing the word "Sum" with "Total Days by Activity Type".

Here is the code I used:

INSERT CHART TITLE HERE

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



$("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; //add also coordinates for better read
});

//sum data
var arrayListSum=$("NOBR:contains('Sum = ')");
var arraySum= new Array();
$.each(arrayListSum, function(i,e)
{
if(i > 0){ var MySum= $(e).text();
var txt= MySum.substring(MySum.indexOf('Sum =')+6,MySum.length); // Extract sum number
arraySum[i-1]=txt;
labels[i-1]=labels[i-1]+"("+txt+")"; //add also coordinates for better read
}
});


var txt= arraySum.join(",");
var txt1= labels.join("|");
// Adjust Chart Properties below - See Google Charts API for reference
var vinc= "";
$("#jLoadMe").append(""+vinc+"")
});

Posted 02-Nov-2009 by Bob
Iain Munro

Finally: Dynamic charting in WSS, no code required!

Hi

Is there an update to this at all as I cannot get it to work regardless of the way I try to implement.

Iain

Posted 03-Nov-2009 by Iain Munro
Richard

Finally: Dynamic charting in WSS, no code required!

Hi Claudio,

An update to your instructions using Bob's method to tally and display dollar sum totals of the items groups would be really Cool!

Greate article btw. Please add how to show dollar subtotals! please! please! :)

Posted 04-Nov-2009 by Richard
Claudio

Finally: Dynamic charting in WSS, no code required!

Hi Iain,
a coupe of suggestions to debug:
Delete an undesired backslash in the script closing tag for the jQuery load in the line:

document.write...

Replace the line:
$(#jLoadme)...

with:
alert(vinc);

and let me know what you get.

regards,

Posted 04-Nov-2009 by Claudio
Claudio

Finally: Dynamic charting in WSS, no code required!

Hi Bob,
glad to hear of your customization!
Really clever your selector to find the subtotal values:
$(”NOBR:contains('Sum = ')”);

Thanks for sharing!

Posted 04-Nov-2009 by Claudio
Blog do Enéas » Arquivo do Blog » Dynamic Charts for Sharepoint (WSS 3.0 and 2007)

Finally: Dynamic charting in WSS, no code required!

[...] I decided that I would do this manually, it would be very hardness. So I created, based on  EndUserSharepoint post, two webparts to generate dynamic charts from Google Charts API in grouped lists of [...]

Posted 27-Nov-2009 by Blog do Enéas » Arquivo do Blog » Dynamic Charts for Sharepoint (WSS 3.0 and 2007)
SharePoint and jQuery for the Common Man: Resources | EndUserSharePoint.com

Finally: Dynamic charting in WSS, no code required!

[...] of the authors on EndUserSharePoint.com have tackled this in previous articles. Claudio Cabaleyro designed a charting solution using the Google API. Ming Fung Yong used SharePoint RSS feeds as his [...]

Posted 29-Dec-2009 by SharePoint and jQuery for the Common Man: Resources | EndUserSharePoint.com
johnathan

Finally: Dynamic charting in WSS, no code required!

I am not able to find the code mentioned in

Recipe:

On the list you want to base your chart, create a “Group By” view.
Create a new web part page and insert the just created list view.
Insert a Content Editor Web Part and paste the code below (remember to use the Source Editor).

Could you please point me in the right direction?

Posted 15-Feb-2010 by johnathan
EndUserSharePoint

Finally: Dynamic charting in WSS, no code required!

Johnathan - Code should be visible now. -- Mark

Posted 16-Feb-2010 by EndUserSharePoint
Denis

Finally: Dynamic charting in WSS, no code required!

This is awesome! I'm a little light on coding, but i'm pretty good at tweaking what is available. I wonder if anyone has anything similar to this situation i can leverage:
3 groups with 3 stats: I'd like a grouped Bar chart group A: Stat1, Stat2, Stat3 | Group B: Stat1, Stat2, Stat3 etc. Does anyone have suggestions on how to create this?

We are tracking metrics for our internet, intranet and public blog. I want to create a SP list using monthly stats so these values can be compared. Visits, Unique Vistors & Page Views. Where i'm at is; for each platform an item is created "Internet, Visits#, Visitors#, Views#" this is a monthly list so over a 12 month span each platform would have 12 entries. It seems very possible to do, i just don;t know enough code to get it done using the google chart....any suggestions?

I'm thinking that if there is an example of a grouped chart (not using sums or calculations) that would be good.

Posted 17-Feb-2010 by Denis
Jay

Finally: Dynamic charting in WSS, no code required!

Denis,

Is this sort what you are looking for? It is screenshot of a dynamic chart that plots Project Starts and Finishes for each of last 12 months.

http://www.freeimagehosting.net/image.php?d3fda968cd.jpg

Posted 18-Feb-2010 by Jay
Denis

Finally: Dynamic charting in WSS, no code required!

Hi Jay, yes exactly like that (although with 3 in each group), the part i need to get a handle on is how to set up the arrays in the javascript using my sharepoint list. Know of any examples i can steal code from or is there a tutorial somewhere how to construct it from scratch?

Posted 18-Feb-2010 by Denis
Jay

Finally: Dynamic charting in WSS, no code required!

Bar Chart with multiple groups (in reply to Denis' Q on Feb 18, 2010).
For example: for Jan, Feb, Mar show Visits, Visitors, Views as grouped bars
A good way to have your data in the list is to have columns for the dates of visits, visitors, and views. Then create calculated columns that correspond to each of the date columns as follows: MonthOfVisit =DatedIf("2009,12,1",[VisitDate],"m"). So if you group the MonthOfVisit calculated column, you will get an output that has the group names 1,2,3,4,5,6,7, etc, corresponding to the months and next to it will be the group totals for each of those months.
1. Create 3 views of the list. Each view is grouped by each of the calculated columns (grouped by MonthOfVisit, etc.)
2. Add the list webpart to your webpart page 3 times!
3. To the first instance of the list webpart, assign the first view (grouped by MonthOfVisit)
4. To the second instance of the list webpart, assign the second view (grouped by MonthOfVisitor)
5. To the third instance, assign the third view (grouped by MonthOfView)
6. Use the code that Claudio has provided. You will need to replicate the section of code that reads in the data 3 times. Edit each replication to look for each of the groups and save each of the group totals in a separate array (txt1, txt2, txt3)
7. Use the arrays above in the google API.

=====
This is actual code I have used for charting project starts, project completions, and total active project in a particular month. It is a bit more complicated than the example above, but may help in getting to your desired chart. [Note: I take no credit for this code. I am not a programmer and I have borrowed liberally from the genius of others.]

Starts and Finishes Per Month

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



//*******This webpart reads in start dates and finish dates where the list is grouped by a calculated field where start date is in months since a reference date = iMonS.
//********A separate view of the same list is grouped by the finish month (reference to an arbitrary date)
//*********************Initialize x labels
var MonLabel = new Array("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
var today = new Date();
var Mon = today.getMonth();
var Day = today.getDay();
var RefDate = new Date();
RefDate.setFullYear(2007,11,31);//Same reference date as in calculated field
var Yr = today.getYear();
var LstYr = Yr -1;
var RefYr = RefDate.getYear();
var RefMon = RefDate.getMonth();
var Dif = (Yr-RefYr)*12+(Mon-RefMon);//difference between today and the reference date
//********************Reorder Month Labels according to todays date
var temp = new Array();
var n=11-Mon;
for(i=0;i<n;i++)
{
temp[i]=MonLabel[i+Mon+1];
}
for(i=n;i<12;i++)
{
temp[i]=MonLabel[i-n];
}
for(i=0;i<12;i++)
{
MonLabel[i]=temp[i];
}
//******************Find project Starts in view grouped by month of Start
$("document").ready(function(){
var arrayList=$("td.ms-gb:contains('iMonS')");
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("(")-2); // Extract the labels
labels[i]=txt1;
});

//Reset labels/indexes according to reference date
for(i=0;i<labels.length;i++)
{
labels[i]=labels[i]-Dif+11;
}

var ynew = new Array();
for(i=0;i<12;i++)
{
ynew[i]=0;
}
for(i=0;i<coord.length;i++)
{
ynew[labels[i]]=coord[i];
}
//*******************Do same for project Ends
var arrayList=$("td.ms-gb:contains('iMonE')");
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("(")-2); // Extract the labels
labels[i]=txt1;
});

//Reset labels/indexes according to reference date
for(i=0;i<labels.length;i++)
{
labels[i]=labels[i]-Dif+11;
}

var y2new = new Array();
for(i=0;i<12;i++)
{
y2new[i]=0;
}
for(i=0;i<coord.length;i++)
{
y2new[labels[i]]=coord[i];
}
//***************Find Totals from Yr Ago from 2 views that group to only one group giving a total
//***************There are actually two totals: 1)Projects that one year ago were active, but now closed
//*************** 2)Projects that one year ago were closed
//***************Subtract the two to get active projects on that date 1 year ago
var arrayList=$("td.ms-gb:contains('Project Owner')");
var total= 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
total[i]=txt;
});

var TotalInt=parseInt(total[0]);

//************Now build Total Projects each month by adding Starts and subtracting Finishes
var TotalP = new Array();

TotalP[11]=TotalInt;
for(i=0;i<11;i++)
{
TotalP[10-i]=TotalP[11-i]-parseInt(ynew[11-i])+parseInt(y2new[11-i]);
}
//***********************Final build of data for chart

var txt= ynew.join(",");
var txt2= y2new.join(",");
var txt1= MonLabel.join("|");
var txt4=TotalP.join(",");

// Adjust Chart Properties below - See Google Charts API for reference
var vinc= "";
$("#jLoadMe01").append(""+vinc+"")
});

Posted 27-Feb-2010 by Jay
Tahir Naveed

Finally: Dynamic charting in WSS, no code required!

I have extended the above technique to display the Google Intractive Charts.
Here is the code:



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 {
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'});
}


Posted 15-Apr-2010 by Tahir Naveed
Salva

Finally: Dynamic charting in WSS, no code required!

When I insert the code and save it, I get the error:
Changes could not be saved.

What can this mean?

My path to jquery looks like this:
Pie Chart Using Google Charting API

if(typeof jQuery=="undefined"){
var jQPath="https://xxxx.dk/ShareIQ/101438/CTT/IT/ITdokumenter/SystemFiler/jQuery/";
document.write("");

Is this correct?

Thanks

-Salva

Posted 19-Apr-2010 by Salva
theerdman

Finally: Dynamic charting in WSS, no code required!

so i am trying to get this to work on a new page and have done everything above. The chart appears to be trying but i get no data in the chart. It does however act as if it is loading something but doesnt show any charts or errors.

I have used the exact same code in other parts of my site with no issue. Any suggestions? The data piece i am using is a choice field, could that be causing the issue?

Thanks,
RD

Posted 04-May-2010 by theerdman
Secure Pie Charts for SharePoint « Codeless Solutions for SharePoint

Finally: Dynamic charting in WSS, no code required!

[...] to use the google apis to create dynamic pie charts of lists in SharePoint based on code written by Claudio Cabaleyro and published at endusersharepoint.com. Perhaps the biggest request I have heard is, “How can [...]

Posted 18-May-2010 by Secure Pie Charts for SharePoint « Codeless Solutions for SharePoint
Sangeetha

Finally: Dynamic charting in WSS, no code required!

Hi,

This code works really great.
I m not good at coding. Is there any way to customize the color of each bar in the colomn Chart?

Thanks,
-Sangeetha

Posted 19-Jul-2010 by Sangeetha
Nate

Finally: Dynamic charting in WSS, no code required!

I am utilizing the Google Charting solution discussed here to display action item status on an https site. The chart is linked to a list and displays a summary bar chart of the action items' status (On Track, Complete, Major Issue, Minor Issue, Not Started). I understand that this presents a mixed content issue as the data is displayed over the https site. I enabled the "Display Mixed Content" setting in IE8, and the graph displays fine with no prompting box. My question is, what is Google logging from their side with regards to the chart? Are they only seeing the chart data (on track, complete, etc.) or do they have access to the list to which the chart is linked?

Posted 23-Jul-2010 by Nate
Christophe

Finally: Dynamic charting in WSS, no code required!

Google doesn't have access to the list, it "only" gets the data you are sending. In this example, you are sending all the information contained in the grouping headers: Excel(1), Hardware(4), etc.

Posted 24-Jul-2010 by Christophe
Daniel

Finally: Dynamic charting in WSS, no code required!

Is there a limit of list entries? seems like those charts cant display more than 10 or 20 entries...

and I would like to know if there is a way to display the entries in a different way. right now the chart is showing the numbers of items grouped by. I would like the chart t pick the sum of a column referring to that group.

so if a group has for example made a profit of 20 k I would like to compare that group to others with the help of this chart.

Hope you guys understood what I mean and have some suggestions.

Thanks

Daniel

Posted 27-Jul-2010 by Daniel
DF

Finally: Dynamic charting in WSS, no code required!

All,

I'd like to change the pie chart to either a heat map, or a geo map.

Is this possible using the code posted above?

If so, can what changes do I need to make?

Thanks!

Posted 28-Jul-2010 by DF
Ewan Radcliffe

Finally: Dynamic charting in WSS, no code required!

Great post guys,
I've been using the fantastic ideas put forward this site, and the jQuery series by Paul Grenier plus great ideas of Christophe, @ path to SharePoint. One question though, Has anyone looked into using the Google API discussed in this panel, on the DVWP, where items are "grouped by", and totals added to the footer.

Posted 02-Aug-2010 by Ewan Radcliffe
Joao Paulo

Finally: Dynamic charting in WSS, no code required!

Thanks, thats just what was previnting me to display the chart.

Posted 04-Aug-2010 by Joao Paulo
Nate

Finally: Dynamic charting in WSS, no code required!

Thank you all for the valuable insight provided on this site. My next question deals with multiple charts on the same page, pulling info from different lists. Right now I am able to create one chart associated with a specific list, however when I try to add another list to the page and create a second chart from the new list, all the data is displayed on the original chart. Is there a specific process I need to undertake in order to display multiple charts on the same page? thanks in advance....nate

Posted 04-Aug-2010 by Nate
Zia

Finally: Dynamic charting in WSS, no code required!

Hi,
I have created a list with some data, and grouped on one of the fields, i have inserted this list in 1 of the webpart and i'm able to c the grouped view of the list, on the second webpart zone i have added the content editor webpart and in the source added the script that has been provided in ur sample above, i changed the src path to point to the location where my jquery.js is uploaded.
but still i'm not able to c the chart, what is missing here.
plz help as i'm stuck since long in this.

Posted 12-Aug-2010 by Zia
Amanda

Finally: Dynamic charting in WSS, no code required!

Many thanks for the tips - just what I need. Can you tell me though, it is possible to chart based on 2 group levels? I have a list grouped first by programme name and then by workstream name - I can get a great graph showing programme detail but want to see both programme and workstream. Is this possible? (PS I am not a developer of any kind, just slowly working my way through this!)

Posted 03-Sep-2010 by Amanda
Amanda

Finally: Dynamic charting in WSS, no code required!

I have created two pie charts - one doesn't display any labels, the other doesn't display at all.

For the one with no lables I have used the following:
cht=p&chs=200x200&chd=t:"+txt+"&chco=0000FF

(The labels I would expect to see are 1, M E, P Cr and TFO. I don't want to code these in as they will change going foward.)

For the one with no display at all I have used the following:
cht=bvg&chs=850x200&chd=t:"+txt+"&chl="+txt1+"&chxt=x,y&chxr=1,0,50&chbh=5

(The graph shows the axes but nothing else. The x axes shows -1 but nothing else.)
Any suggestions much appreciated.

Posted 03-Sep-2010 by Amanda
Sonja

Missing instructions to add jquery library to create Dynamic links

Hi - I am interested in trying out the dynamic charting in WSS, however, I need to upload the jquery library.  Unfortunately, the link to the instructions on how to do this is broken on this page.  (The 'here' link just above the script).
 
Thank you!
Sonja
 

Posted 11-Oct-2011 by Sonja
Claudio Cabaleyro

Broken link

Hi Sonja,
Actually, there is a little inconsistency between the article and the code. As it is now, the jQuery library is being loaded from the Google servers, so you do not need to upload the jQuery library to Sharepoint. 
I hope this helps.
 
Best regards,
Claudio

Posted 12-Oct-2011 by Claudio Cabaleyro
Neptune

the google jquery library is not working

Hi Claudio,
when I tried the the code in SP2010, it simply won't work.
Am I missing something besides the google jquery library is not working.
 
Looking forward to hear from you.
thanks,
Neptune.

Posted 03-Feb-2012 by Neptune
David L Ernstrom

SP2010 change

I had to change line 18 to make it work in SP2010 as follows: var txt= MyIf.substring(MyIf.indexOf('(')+1,MyIf.indexOf(')')); // Extract the 'Y' coordinates HTH

Posted 30-Mar-2012 by David L Ernstrom
Matt P

Warning: The HTML source you entered might have been modified

I added a list to my page, changed the view to one that groups one of the columns, added a CEWP, and entered the following code:
Pie Chart Using Google Charting API
After I click OK, I get a warning saying the code may have been modified. I click OK to the web part settings, and save the page, but the CEWP only displays "Pie Chart Using Google Charting API". When I go back into the CEWP, the code is shown as follows:
Pie Chart Using Google Charting API
Why is the bulk of the code being removed? ​​

Posted 06-Oct-2012 by Matt P
Matt P

Warning: The HTML source you entered might have been modified

I added a list to my page, changed the view to one that groups one of the columns, added a CEWP, and entered the following code:
Pie Chart Using Google Charting API
After I click OK, I get a warning saying the code may have been modified. I click OK to the web part settings, and save the page, but the CEWP only displays "Pie Chart Using Google Charting API". When I go back into the CEWP, the code is shown as follows:
Pie Chart Using Google Charting API
Why is the bulk of the code being removed? ​​

Posted 06-Oct-2012 by Matt P
Jared

Amazing!

This is fantastic! What a champion article and so simple to do, thank you for putting this up!

Posted 06-Mar-2013 by Jared
John

Any updates to this code/technique?

The code above no longer works.  Is there an update to create a pie chart out of a sharepoint (2007) list?

Posted 31-Jul-2013 by John

Notify me of comments to this article

E-mail:
   

Add Comment

Title:

 
Comment:
Email:

   


Name:

 
Url: