Adding Graphs, Bar and Pie Charts to SharePoint

Here is a quick trick I learned today that solves an issue I’ve been having for awhile now.  How to quickly make charts and graphs that pull dynamically from SharePoint Lists.

Screen Shot of the finished product:

graphsscreenshot

Claudio 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 Pie Chart.  I tried it out and it literally took me 1 minute to have my first pie chart.  I quickly saw some limitations that people may run into and in this article I will talk about some tweaks I did to Claudio’s code to do some very cool stuff.

First off, the code works by rooting for any table cell that has the class “ms-gb” and then checks to see if that cell has an “:” in it.  Here is the code

var arrayList=$(“td.ms-gb:contains(‘:’)”);

This works fine except if you want to have multiple graphs on the same page, then the code would tally all your lists together regardless if they were related or not.  If you would like to have more than on graph here is how to edit that line of code.

var arrayList=$(“td.ms-gb:contains(‘[name of column that's grouped]‘)”);

Since my first list was grouped om the Category so I put Category in there instead of the colon.
var arrayList=$(“td.ms-gb:contains(‘Category’)”);

My second graph, I made another list and grouped it by Status, I copied and pasted the code a second time in the second instance I changed the code to:
var arrayList=$(“td.ms-gb:contains(‘Status’)”);

Now at least you can have multiple graphs as long as the column names that are doing the grouping are different.  I also made an update to the second div tag id to prevent there being two identical id’s from being on the same page.  On the second div <div id=”jLoadMe” class=”content”> I changed to <div id=”jLoadMe2″ class=”content”> at the end where it dumps the information I updated $(“#jLoadMe”).append to $(“#jLoadMe2″).append

Since I did not one of the lists to actually show up I went to layouts and checked the hidden box, and the pie chart continued to work fine.

Other notes:

I went to google charts at http://code.google.com/apis/chart/ and spent some time learning how to customize the charts a little bit.

To change the color all you have to do is add &chco=#[Hexadecimal color number], the &chs=250×100 tells the width and heigth of the cart image and you can change the piechart to a non-3d chart with the cht=p here is what my code looked like when it was done.

var vinc= “<IMG src=’http://chart.apis.google.com/chart?cht=p&chs=250×100&chd=t:”+txt+”&chl=”+txt1+”&chco=0000FF&#8217; />”;
 $(“#jLoadMe”).append(“<p>”+vinc+”</p>”)

Here is what the chart looked like:

bluegraph

I hope the rest of you find this solution as useful as I did.  As I said, I had a dynamic charts of my SharePoint Lists up in minutes!

UPDATE!
Here is the code that I used to do the two pie charts side by side:



<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">
var chartkeyword = [];
var chartcolor = [];

////////////////////////// Customizations /////////////////////////////////

chartkeyword[1]="Release" 	//name of the first column that's grouped
chartkeyword[2]="Release"   //name of the second column that's grouped
chartcolor[1]="FF0000"		//Color of Pie Chart 1
chartcolor[2]="00FF00"		//Color of Pie Chart 1

var charts=2 // if you only want one pie chart chang this to 1

</script>
<table width=100%><tr>
<td><div id="jLoadMex1" class="content"><strong>

<!------------- Name of Chart 1 -------------------------->
Add a Name Here

</strong></div></td>
<td><div id="jLoadMex2" class="content"><strong>

<!------------- Name of Chart 2 -------------------------->
Add a Name Here

</strong></div></td>
</tr></table>

<script type="text/javascript">
$("document").ready(function(){

for(var i=1;i<=charts;i++)
{
makeChart(i)
}
});

function makeChart(i)
{
	var arrayList=$("td.ms-gb:contains('" + chartkeyword[i] + "')");
	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
		labels[i]=txt1
	});
	//----------Start of "Summerize Lists into a single Pie Chart" Code -----------
	var labelsCoords= new Array();
	for ( var count1=0;count1<labels.length;count1++) //Dupe Check
	{
		if (labels[count1]=="!delete!")
		{
			 labels.splice(count1,1); //delete dupe from array
			 coord.splice(count1,1); //delete numbers from array
			 count1-=count1; //reset the counter to match the index
		}
		else
		{
			for ( var count2 in labels )
			{				
				if (labels[count1].replace(/ /g,"-")==labels[count2].replace(/ /g,"-")&&count1!=count2&&labels[count1]!="!delete!")
				{
					coord[count1]=(coord[count1]*1)+(coord[count2]*1) //total dupe counts
					labels[count2]="!delete!" //label as a dupe to delete				
				}
			}
		}		
		labelsCoords[count1]=labels[count1]+"("+coord[count1]+")";
	} 

	var txt= coord.join(",");
	var txt1= labelsCoords.join("|"); // This replaces  var txt1=labels.join("|");
	
	//----------End of "Summerize Lists into a single Pie Chart" Code -----------



	// Adjust Chart Properties below - See Google Charts API for reference
	var vinc= "<IMG src='http://chart.apis.google.com/chart?cht=p&chs=320x120&chd=t:"+txt+"&chl="+txt1+"&chco="+chartcolor[i]+"' />";
	$("#jLoadMex"+i).append("<p>"+vinc+"</p>")
}

</script>

Change the keywords at the top of the code to the name of the categories you are grouping by… Good Luck!

About these ads

56 Responses to Adding Graphs, Bar and Pie Charts to SharePoint

  1. Mark Miller says:

    Bryon – Thanks for the update on Claudio’s solution. I wish you would post more often… very good ideas here. You’re always welcome to submit an article.

    Regards,
    Mark

  2. Nicole says:

    Hi Bryon,

    i tried to put 2 chart on one page but it’s not working for me. I am wondering if i should use 1 list or 2 list on this page. how do I group these list(s)?

    Thanks.

    Nicole

    • wyly says:

      Nicole,
      I have another article on how to add multiple pie charts to one page and this one about how to convert the count to percentages. I can email you the code that does both if you would like.

      Perhaps I will make another iteration that allows a someone to change easy to read variables for number of charts and another for percentage or count.

      • reyayanami says:

        I’d like to know how the count can be converted into percentages.
        I’m new to jquery so I really don’t know how to do it.

        thanks

      • wyly says:

        reyayanami,

        I have another post on this blog on how to do just that.

        https://wyly.wordpress.com/2009/05/12/make-a-pie-chart-that-shows-percents/

      • Hi,

        I really love what you have done here. Great alternative to expensive solutions. I was wondering if you could help me with the “multiple charts on one page” problem.
        Just to summarize, i have three list on one page and would like to add a chart to the left of each list to get a quick overview of the data. Is this even possible with this code?

      • wyly says:

        Chistian.

        The answer is yes, but it would take some modification by someone who understood jQuery. At the end, instead of writing the google chart out to the same div tag, you would change it to write to a div tag you made and had in a content editor next to each list.

        If I get time soon I might try it.

  3. Jeff says:

    How did you get the charts to line up side by side?
    Did you have two seperate CEWPs?

    I can get two charts, but I’m trying to place them side by side within one webpart, due to the limitations of the page.

    • wyly says:

      Jeff,

      I added the code I used to make those two side side images to the post. Just copy and paste the code into a Content Editor Web Part and change the Keywords and colors to match your page.

  4. gt says:

    Bryon,
    You reference information provided by Claudio, however I have not been able to access this. Would you have this information available to complement your work. It would help a lot and be greatly appreciated.
    Thanks for sharing your work.
    gt

    • wyly says:

      Thanks for the info. It looks like endusersharepoint is in the process of revision right now and the link is broken.

      Here is a link to another article I have that has complete working code for two side by side graphs on the same page. Let me know if you have questions to tweak this for only on graph.

  5. endusersharepoint says:

    Hi – Yes, EndUserSharePoint.com is down due to an overload of traffic. I’m in the process of moving to a new server at RackSpace. Planning on being up and running by Monday. — Mark

  6. Lloyd Cruz says:

    Is it possible to create more than two graphs, I was hoping to utilize four.

  7. Oliver says:

    Bryon,

    Thanks for this great post. I’m trying to use the 2 pie chart code but can only render the first pie (1st grouped column). I’ve tried with different 2nd grouped columns but the second pie chart does not render… Any idea what could be causing this?

    Thanks!

    • Oliver says:

      …Found out why: I was trying to use a single list with 2 groupings. I created a second list instead and created my second grouping there. It works fine now.

  8. Laurent Bel says:

    Looks really nice !

    If you want to try another Charts Feature for SharePoint, you can follow the link below:

    http://charts4sharepoint.codeplex.com

    It is free, open source… and looks great as well. Just the way we like it.

  9. u0010002 says:

    Bryon – I just submitted a question that you might like to take a crack at on EndUserSharePoint.com in the “Stump the Panel – JQuery for everyone” section related to this solution.. Much too long to pose here – but I’d love to get your take on it! (summarizing 2+ lists with a single chart)

  10. [...] by stump the panel on endusersharepoint.com if I could make the Pie Chart I demonstrated in a previous post, summarize content from multiple SharePoint lists. (see stump the panel thread -Summarizing List [...]

  11. Victor says:

    Howdy!

    Hey, these are GREAT solutions, but I have a problem though… I CANNOT use Google APIs. Since I work with confidencial material, I cannot allow information to leave our server so… Can you guys help me with that? (BTW… I do not have access to anything but a full control profile, sadly)

    Thank you guys!

  12. Venu says:

    Wow you have make this charting so simple and it worked imeediately and the instructions are straightforward…

    Thanks a lot

  13. Sam says:

    How can I have a pie chart of colors that don’t change is a list item is missing. eg Defect list sometimes have a defect with an “Active” status and sometimes there are none with “Active” status. How do I keep the colors the same if a slice of data contains records or not?

  14. Joe says:

    Thank you very much! This is excellent information! I also wanted to share a workaround that I am using and thought others might find useful. I wanted multiple charts, with each reporting off of a column titled “Status”. Basically, I wanted a different chart for each custom view in my list. I had 5 custom views, so I created 5 calculated fields titled “Status1″, “Status2″, “Status3″, “Status4″, and “Status5″. For the calculation I just made each of them = the original Status field. Then I grouped each of my custom views by one of the new calculated Status fields. That allowed me to show five unique charts, all showing information from the Status field. I hope that is helpful. This is my first post, and I am just an end user trying to figure things out as I go! Thanks again for the great information!

  15. sharepoint says:

    Hi,

    I am having trouble using this. I followed the instructions but i see only one chart. I grouped the same list first by Column A and then by Column B. Later i changed the keywords to Column A and Column B. But i see only one pie chart.

  16. sharepoint says:

    oh cool i got it .. i dint know you were using two lists… awesome stuff thanks :D

  17. Chris says:

    Hi,

    what level of data will be send to google by using the api? Is it just the number of entries for a category or is it the complete list data?

    regards
    Chris

  18. DF says:

    Bryon,

    Which data is being sent to the Google API?

    Is it only the count on the column labels, or are the column label names also sent?

    Thanks!

  19. K Elliott says:

    I have SharePoint MOSS 2007. I trying to display 2 graphs/charts within 1 (EWA) webpart. Even when I use the F11 key (in Excel) to place 2 charts into a chart-sheet and then save and publish the sheet using excel services to indicate that sheet (CHART 1) as published,it shows up on the list when configure the webpart, but then an error comes up when the webpart tries to load in the dashboard saying that the named reference may be hidden or not published. I cannot get this to work! GOAL: 2 or more charts inside one webpart ona dashboard page without custom coding if possible.

  20. DF says:

    Bryon,

    For those of us running SharePoint in a corporate environment (which is sensative to transferring data, even column headers which might contain customer names, etc… to Google) can you recommend a work around?

    For example, would it be possible to add a hidden column to our list that contained ID #’s unique to the sensative data (the ID #’s would be pre-defined and auto assigned); send just the the seemly arbitrary ID #’s and counts to Google, then have the ID #’s replaced by the actual values (customer names, etc…) after the chart is returned?

    Thanks!

    • wyly says:

      This can’t happen. The way that google analytics works is that you send it the info such as labels, numbers, type of chart, then it makes an image of that chart and sends it back to you. Since you can’t change the text on an image that idea would not work. The only other option for pie charts would be the codeplex chart part.

  21. Tahir says:

    Hi,

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

    URL: http://mysplist.blogspot.com/2010/04/intractive-sharepoint-charts-with-no.html

  22. EUSP says:

    > The only other option…

    You can also use the Yahoo Charting API, which is downloadable and can be called from within a document library.

    Mark

  23. Cloud says:

    Hi,
    I used your code and group two of my list:
    1. Status (Completed, In progress, not started)
    2. Issue Status (active, resolved)

    I do get two charts but first chart also contains the list items from second list.

    Results:
    1. chart contains (Completed, In progress, not started, active and resolved)

    2. chart shows correct data (active, resolved)

    What I am doing wrong, can you help?

    regards

    • wyly says:

      My guess is that you used Status as the keyword for the first chart. Since Status is in both lists (‘Status’ and ‘Issue Status’) the Jquery is hitting on both sets of lists for the key word ‘Status’. The quick way is to rename the column ‘Status’ to ‘Current Status’ or some other two word name.

      Let me know if you need more help!

  24. DF says:

    Hi All!

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

    Can anyone tell me if this is possible using the code posted above?

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

    Thanks!

  25. Jonathan says:

    Hello,

    Great article. I was able to implement it in just a couple of minutes; however, the solution I need is a little more complex. I have a list with items marked as either comeplete or not complete (yes/no labels). These items are organized in cycles delimited by dates (generally first of the month to 15th of the month and then 16-last days of the month)

    I need a solution that only displays the current period with the item count (complete yes/no) for the applicable period. Alternatively, I could use a solution that displays multiple periods. Any suggestions on how I can accomplish this?

    • Jonathan says:

      To follow up on my comment. Is it possible to ask for two set of keywords? For example: “Status”, and “DateRange” and pass the data to a single chart

      • wyly says:

        Jonathan,

        The best way to do this would be to create a view that only showed the applicable time period. For example make a new view and filter by date everything except from the 1st of the month to the 15th of the month. Then you can use the keyword status to display the count or percentage of Yes and No’s from that time period. You may also hide the list view on the page by opening the web part going to layout and selecting hidden.

        Unfortunately you could only have one pie chart on the page at a time. if you had another list on their with “status” the code would tally up ALL of the grouped Statuses instead of per list.

      • Jonathan says:

        wyly,

        Thank you for the help. I ended up implementing a modification of your proposed solution. Since the period is displayed as a concatenated field that joins a start date with an end date, I filtered the items using a “period-end-date < 9/15/10" condition. The obvious downside is that I will need to manually change this every two weeks, but the value we are getting from the graph is well worth the 5 minutes it takes to modify the filter

  26. Ian says:

    That’s great. Showed it to my users, and they thought it brilliant – but could they have a bar chart instead. No problem – but for the api, you need a maximum axis value as well. In case it’s of interest to people, this is how I did it. After line 87 of the original code add:


    // Get max value
    var sortedNums = new Array();
    sortedNums = coord.sort(function(a,b){return Number(a) - Number(b)});
    var maxNum = sortedNums.pop();

    Then to create the bar codes use something like:

    var vinc= "";

  27. Manu says:

    Great stuff here Bryon! I came across your articles here and in endusersharepoint and they are awesome! I was wondering though if there have been bar/column chart implementations using YUI? Thanks!

  28. Silvia says:

    Great tips! thanks for the code… it worked for me in 1 minute

  29. Thomas says:

    hi,
    I am trying to display 3 or more charts with this code, but the 3rd won’t be displayed:


    var chartkeyword = [];
    var chartcolor = [];

    ////////////////////////// Customizations /////////////////////////////////

    chartkeyword[1]="risk-owner" //name of the first column that's grouped
    chartkeyword[2]="area of impact" //name of the second column that's grouped
    chartkeyword[3]="RiskIndexValue" //name of the second column that's grouped
    chartcolor[1]="FF0000" //Color of Pie Chart 1
    chartcolor[2]="00FF00" //Color of Pie Chart 1
    chartcolor[3]="0000FF" //Color of Pie Chart 1

    var charts=3 // if you only want one pie chart chang this to 1


    grouped by risk owner



    grouped by area of impact



    grouped by blabla

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

    for(var i=1;i<=charts;i++)
    {
    makeChart(i)
    }
    });

    function makeChart(i)
    {
    var arrayList=$("td.ms-gb:contains('" + chartkeyword[i] + "')");
    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
    labels[i]=txt1
    });
    //----------Start of "Summerize Lists into a single Pie Chart" Code -----------
    var labelsCoords= new Array();
    for ( var count1=0;count1<labels.length;count1++) //Dupe Check
    {
    if (labels[count1]=="!delete!")
    {
    labels.splice(count1,1); //delete dupe from array
    coord.splice(count1,1); //delete numbers from array
    count1-=count1; //reset the counter to match the index
    }
    else
    {
    for ( var count2 in labels )
    {
    if (labels[count1].replace(/ /g,"-")==labels[count2].replace(/ /g,"-")&&count1!=count2&&labels[count1]!="!delete!")
    {
    coord[count1]=(coord[count1]*1)+(coord[count2]*1) //total dupe counts
    labels[count2]="!delete!" //label as a dupe to delete
    }
    }
    }
    labelsCoords[count1]=labels[count1]+"("+coord[count1]+")";
    }

    var txt= coord.join(",");
    var txt1= labelsCoords.join("|"); // This replaces var txt1=labels.join("|");

    //----------End of "Summerize Lists into a single Pie Chart" Code -----------

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

  30. Aditya says:

    Hi,
    I am trying to use the same methodology to prepare bar charts and line charts using YAHOO.widget.BarChart and YAHOO.widget.LineChart. Is it possible to use the same code to implement these?

    Thanks
    Aditya

  31. DF says:

    Wyly,

    This code has worked beautifully for me in my SP2007 environment for many months — Thanks!

    Recently however, we performed a test migration into a SP2010 envirtonment. Afterwards, I noticed this was one of the web parts that wwas broken.

    Have you tested this code in a SP2010 environment?

    My list is there, a refreshed copy of the code above is there, however, when I view the page, there are only place holders for the charts. The charts themselves are missing.

    Any idea what I should check?

    Thanks!

    • jd says:

      I don’t have access to SP10 – but would make sure that they haven’t restructured the coding of the page structure… the code is pointing to certain sections of the page with this snippet of code…(“td.ms-gb:contains if they changed the coding on the elements of the page – it wouldn’t find the data it’s looking for, and the graphs wouldn’t fire. Just a guess – but it’s a place to start!

    • DF says:

      Does anyone already have this working in SP2010?

  32. DF says:

    Wyly,

    Recently we performed a test migration from SP2007 to SP2010.

    After the migration, I checked the SP2010 site for problems.

    It was during this double-check that I noticed the code posted above works great in SP2007, but does not seem to work for SP2010.

    Can you recommend how the code posted above can be modified to work in SP2010?

    Thanks!

  33. Ethio says:

    TNX.
    its work only in sp 2007
    but no in 2010?

  34. VM says:

    This does not work on SharePoint 2010 CEWP. Does any one know what code can I use?

  35. Mack M says:

    I wonder if I an use my own pie image instead of google pie image for the security reason..please your feed back is very apprecated how to do it.

    Thanks,
    Mack M.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: