CAML Query, Charts, Javascript, SharePoint, SharePoint 2007, SharePoint 2010, Utilities

Charting for SharePoint using Google Visualization API: Update 16.08.2011

81 Comments

26.08.2011 v2.9.3.2 has been released with these changes:
Small fix to allow single quotes in chart options. This is necessary to handle “fontName” in IE – it has to be wrapped in double AND single quotes like this:

{color:"red",fontName:"'Arial'",fontSize:14}

24.08.2011 v2.9.3.1 has been released with these changes:

  • For non-site collection administrators on a managed path*, there were a bug with how the baseUrl of the site was found. This led to an error when retrieving the fields for the selected list.
  • For non-site collection administrators, the “Get CAML from selected view” did not work.
  • Due to incompatibility i have removed the possibility to filter the chart on a DateTime column when using “Consume filter from list view web part”.
  • In orgchart there were a blank box added to the top of the “tree”. This is now gone.
  • Better debug – see details below.
  • Added support for a selection handler for use when you want to click the chart and redirect to a filtered list view. See example below.

Managed path

When you are on the root site in your site collection, does the URL look like this:
http://www.contoso.com/default.aspx
or like this:
http://www.contoso.com/sites/mysite/default.aspx

The last one would indicate that you are on a managed path.

Debug
Add this to the CEWP code:

var chartDebug = true;

When you edit the chart, a debug panel will be added to the top of the screen. This might come in handy if you need my help to debug the setup.

Filter example
The script is setup with a “listener” that lets you add a function like this to your CEWP code:

function chartSelectionHandler(data,chart,chartID){
	var selection, item, x, y, z;
	selection = chart.getSelection();
	if(selection.length>0){
		item = selection[0];
		if(item.row!==undefined){
			x = data.getFormattedValue(item.row, 0);
			if(item.column!==undefined){
				z =  data.getFormattedValue(item.row, item.column)
				y = data.getColumnLabel(item.column);
			}
		}
		if(x===undefined && y===undefined && z===undefined){
			return;
		}
		// Create your filter
		alert("These are the variables you can use for your filter:nnx = "+x+"ny = "+y+"nz = "+z);
		if(confirm("Open filtered list?")){
			if(chartID==='MyChart1'){	
				window.open('/test/English/Charts/Lists/ByStatus/AllItems.aspx?FilterField1=Status&FilterValue1='+x,'','width=1000');
			}else if(chartID==='MyChart2'){	
				window.open('/test/English/Charts/Lists/Coffee2/AllItems.aspx?FilterField1='+y+'&FilterValue1='+z,'','width=1000');
			}
		}
	}	
}

This is meant as an example for those who want to experiment with this feature.

16.08.2011 I have posted a new version of the “Charting for SharePoint using Google Visualization API” – v2.9.3. You find it in the download section below.

These are the changes in this release

Save as template:
I have added a new checkbox to the configuration: “Use display name for list and view”. This one will replace the GUID with the display name for the list and view in the chart configuration. By doing this, the chart will “survive” if the site is saved as a template end restored in another location.

Use this option only if you plan to use the site as a template.

If you have a setup with multiple languages and you have translated the list title, the chart will work only for the language used when the template was saved. You can however revert back to using the GUID as soon as the site has been created from the template by removing the check in “Use display name for list and view” and saving the configuration.

Chart options:
Fixed a bugs where some options starting with 0 – like region for charts – were parsed as a number thus removing the leading 0.

Minor bugfixes:
I have done a few small bugfixes that are to small to mention.

Other articles in this series:

Download

You find the code here

If you use the solution, please consider donating a few dollars.

Regards
Alexander

81 thoughts on “Charting for SharePoint using Google Visualization API: Update 16.08.2011”

  1. BTW, I thought of one modification. Right now charts are loaded automatically but on my site I needed a way to defer loading the sites. I needed this because I have a select drop down where the user can select which chart they want to see and I saw no point in loading a chart till the user actually asks for it. I figured out I couldn’t call “onloadInitChart” or “call_drawChart[‘div’])” because it overwrites the “myChartConfig” variable, and there is a chance google’s stuff hasn’t loaded yet. So I added a call to a user function in “onloadInitChart” that calls my function (once google’s stuff is loaded) which lets me do whatever I need to. Works pretty well.

    1. Actually I think I figured out a way to ‘queue’ chart rendering requests until Google’s stuff is loaded. Will have to TIAS tomorrow at work.

  2. I have been using the old script on SharePoint linking the graphs to lists. But when i upgraded the graphs don’t seem to recognize the columns in the list!
    please help

    1. OK,
      It might be a bug. I will look into it and let you know.

      It would help if you send me a screenshot of the configuration (from the configuration list) for one of your charts.

      I also need to know if the list you use as source for the chart is in the current web.

      Alexander

  4. Hi Alexander,

    I am hoping to create a stacked column chart that is displays not the counts, but the percentages of each series. Currently my chart is a stacked column, merging by one variable, and counting (excluding blanks) another for the values.

    I want a chart where each data value would actually be a percent of that merged group, and the column would expand the whole length of the chart, from 0 – 100%. I assumed this is what “Format as % (val*100)” means, but now I realize it probably is only used for situations when your data points are decimals below between 0 and 1.

    Is this type of chart possible with google’s api? It is a basic function excel charts has had for years, but I can’t find a similar version in google charts api.

    Alex

    1. Hi,
      The pie chart does this, but i do not think this is possible with the column chart without providing the data in already “modified” so that all stacked columns have a total of 100.

      Alexander

  5. I have released a new version (v2.9.3.1) that intends to fix the bugs from v2.9.3. It adds some new functionality as well. See top of article for details.

    Alexander

  6. Alex,

    Thank you so much for this solution. It is fantastic! I plan on donating! And thank you for getting back to me regarding my question about 100% stacked column charts. I decided to go with a pie chart as you suggested.

    One question though: I’m having troubel with the titleTextStyle. I have it configured thus: {color: “black”, fontName: “Arial”, fontSize: 14}

    The title responds when I change the color or the fontSize, but it does not respond when I change the fontName. The font remains the same (it is bubbly and bold and weird… not sure what font it is).

    Any help would be greatly appreciated.

    1. Hi,
      I’m glad you like the solution.

      There is an issue with IE and fontName. If you test it in Firefox or Chrome you will see the difference.

      I will try to create a fix, but until then, use double AND single quotes around Arial like this
      ” ‘ Arial ‘ ”

      The white-space between the quotes must be removed.

      NOTE
      This will give an error when you try to edit the chart again as the single quotes will be replaced by double.

      Stay tuned and i will see if i can find a more permanent fix to this problem.

      Alexander

    2. Hi,
      I have addressed the problem in v2.9.3.2. This update will allow single quotes to be used. It will still require you to use double and single quotes around the fontName like this:

      {color:"red",fontName:"'Arial'",fontSize:14}

      Alexander

  8. Hey A,

    Is there a way to change the image on the gauge charts, the gauge itself? I am looking to substitute that image with one of my own. I have been trying to find the option out on google, but haven’t found it. I want to add something more cutitng edge.

  9. This solved my boss’ request perfectly. Great solution! One question though: can the charts be shown outside the site collection using jQuery?

    1. Hi,
      Glad you liked it. The chart does not exist before you load the page and therefore you cannot retrieve it using jQuery.load(… from another page.

      Alexander

  10. Hi Alex, thanks so much for a fantastic charting solution – proving very popular here!. I’ve got a query around the guage charts which I was hoping you might be able to help with. Is there any way to produce a guage that has a title using a text string entered manually in the GUI and for the needle to then show the count of items in the view specified? Currently I can only get guage to work by building a separate list and manually updating it with the count of items/labels i wish to show.

    Many thanks & keep up the fantastic work!

    1. Hi,
      It is not possible to add a title to the gauge using the configuration options. You will have to put it in the CEWP HTML.

      To have a item count you could do the following:
      Create a calculated column in the list with the name “ItemCount” and this formula this

      ="Item count"

      You then setup the chart with this columns:
      [0] ItemCount : Format as string – merge duplicates
      [1] ID: Count

      If you would like to skip the text “Item count” in the gauge, just leave the formula empty like this

      =""

      Alexander

  11. Thanks for getting back to me so quickly Alex – when I try to use ID: Count I get the following back; Error: You cannot include columns of type Sum, Count, Average or Merge duplicates and columns of type String, Number, Boolean or Date in the same chart.

    Any thoughts?.

  12. Hi Alex,

    I am using your Charting for SharePoint using Google Visualization API v2.9.3.2 (August 26, 2011), on a page that utilizes multiple jquery API to render various types of views (treeview, orgchart view, etc). It works great in IE 6.0 but all my jquery stops working in IE 8. but if I disable the Google Visualization API in IE 8 all my other scripts start working again.

    any ideas…

    Thanks
    Waqas Iftikhar

  13. Hey Alexander,

    Is there a way to have one drop-down filter apply to all other charts on the page as well? It would be nice as opposed to changing the drop-down filter for each chart, but not sure how I would go about doing this. I’m assuming the Drop-Down is just CEWP code in the background?

  14. Hey Alex, got any ideas on how to fix the IE 7,8,9 Issue where the graph title font displays an ugly “impact” type font no matter what is set in the setting? Works perfect in other browsers. The font issue is with the Google API itself, not your tool, just didn’t know if you may have a work around?

    1. Hi,
      Option: fontName
      Value: “Arial”

      Should do the trick.

      PS. Do not copy from this post as the quotes will be wrong – use regular double quotes.

      Alexander

  15. First of all, I love the work you have done with this tool!
    I have a question concerning the possible source URLs that are available for charting. Is there any way for this tool to be able to chart data from a separate site collection? Thus far I have been working with the chart tool within one large site collection, however we are branching out into separate collections to help manage the overall storage size.
    Long story short, is there any way to use this from one site collection to another assuming you have full admin rights to both?
    Thank you so much!
    ~Erich Zann

  16. Firstly, congratulations for your blog. It is brilliant!

    I am feeling so frustated I cannot get this working at all!
    Here is what I’ve done:

    – I created a document library and uploaded the ChartUsingGoogleVisualizationAPI.js file on it
    – I changed the reference path in the code accordingly
    – I pasted the code in a CEWP in the page I want the chart to be exposed

    It does not show anything to me.
    Is there any step missing?

    Thanks,

    JP from Brazil.

    1. Hi,
      Ensure the file name / path to “ChartUsingGoogleVisualizationAPI.js” is correct – the file name can contain the version number like this “ChartUsingGoogleVisualizationAPI_v2.9.3.2.js”.

      Alexander

    2. Hi Alex,

      I’m with JP here…I can’t get any version after 2.5 to work. Starting from scratch, just like I did with the working one nothing happens after I’ve completed the inital setup. I’ve added an alert to the lastest version, just below myChartConfig = {}; which pops up but then nothing.

      My site lives on a http://sharepoint.*.com/sitexxxx/yyy/default.aspx url, this seems to be a managed path, though I’m not sure if that changes anything when I can’t even manage to have the config created.

      regards,
      Chris

    3. Hi Chris,
      Please double check the path (src) to the file “ChartUsingGoogleVisualizationAPI_vX.X.X.X.js” – ensure the src reflects the version number of the file you use.

      Alexander

    4. Hi Alex,
      did that. Usually I upload the js files to a script library and then “copy the url” to paste it into the CEWP. Strange enough, the first alert, right after the myChartConfig pops up so the script is called, but then nothing else happens. I’ve tried to find other ‘vital’ parts to place an alert but my jscript knowledge is too bad I can’t figure out any good places.

      Can you advise on a ‘break’ chain, like place an alert here and then there and we will see where the script stops?

      Chris

    5. Hi,
      Try appending this to the bottom of your CEWP code:

      <script type="text/javascript">
	alert(typeof(drawChart));
</script>

      The alert should read “function”.

      Alexander

    6. Hi,
      yes, the alert reads function. But that’s still all that happens 🙂 No config list is being generated. Any other ‘break points’ that I can set?

      Chris

    7. If you have used the solution previously (an older version) and have an old configuration list – try removing (or renaming) this list to allow the solution to recreate the list.

      Alexander

    8. Hi Alex,
      no, sorry, it’s a completely new site. Right now there’s only a list with two CEWPs, one being the CEWP code and one with the . I had this problem before, the last version of your script that I can get to work (regardless of new site or upgrade on existing one) is the 2.5 version. Can I send you anything to try and narrow the search for the missing link?

  18. Hey A,
    you know I am one of your biggest fans. I am not sure this is an issue but ai want to describe what I am experiencing. I have 4 charts on a page, 2 bar, 1 table and on gauge. the gauge is the only chart linking to a list in another site. everything I tried to edit the gauge it appeared to freeze. I finally had to navigate to the GoogleVisualization_InteractiveChartsConfig list and make the change manually there. It worked and the data updated in the gauges. Do you know what might cause this to be slow and sluggish?

    1. Hi,
      No i do know what could cause this. Try activation the console in IE developer toolbar (or similar in other browsers) and see if you get any errors there.

      Alexander

  20. Alexander,
    I am runnig sharepoint 2010, IE8, and version 2.9.3.2.js of your script. I can get the charts to load and render fine. This is a great utility. However, when I clcik on the Edit Chart button, all I get is a message that says “Loading Edit Chart GUI – Please wait…” It never lets me edit and existing chart. If I refresh the page, the current chart is rendered again. Any thoughts on what I am doing wrong?
    Thanks,
    –Jon

    1. Hi,
      In IE8, hit F12 and select “Console” or “Script” > Start debugging.

      Look for errors and send me a screenshot if you get any errors.

      Alexander

  21. Hey A,
    you still providing help with build charts? I am trying to get data in a chart and I cant figure it out. I have division, MonthYear, Budget, actual. I want to show data by division and monthyear budget vs actual. I cant get total by month year, or by division, but not a combo of both. any suggestions?

    1. Hi,
      Your best option is to use a calculated column to concatenate “division” and “monthyear” into one column. Then you use this column as first column and select “Format as string – merge duplicates”.

      The next two columns are “budget” and “actual” with “Sum”,

      There are currently no support for dual “hAxis” in the Google Visualization API.

      Alexander

    2. as always your the best.

      Is there a way to add links to the charts, either on the labels or by clicking somewhere on the chart? Considering the granular data is stored in a list, it would be nice to click on the visual data and get the details.

  22. When I attempt to use the “Edit Chart” button I receive the following JavaScript error:

    Webpage error details

    User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; InfoPath.2; .NET CLR 1.1.4322; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; MS-RTC LM 8; .NET4.0C; .NET4.0E)
    Timestamp: Fri, 21 Oct 2011 17:54:13 UTC

    Message: ‘userInfoObj’ is null or not an object
    Line: 2136
    Char: 2
    Code: 0
    URI: http://my.sharepoint.com/site/folder/ChartUsingGoogleVisualizationAPI_v2.9.3.5.js

    Any advice would be appreciated.

  23. Hi Alexander,
    Great work – thank you so much for this tool!

    I am using the OrgChart at the moment, and I have noticed an issue with commas in the employee names.

    Our format is Surname, Firstname Initials’ (double space between Firstname and Initials).

    The output in the OrgChart is transformed into “Firstname Initials”, i.e. the Surname and the comma are deleted.

    I am not able to change the format, since I storing information using the ‘People or Group’ type in the Custom List on SharePoint.

    Is there any way around this?

    Thank you in advance – and once again thank you for an excellent job!

    1. Hi,
      The reason this is happening is that the comma is used to separate the name and description for the “name” in this format:

      Bruce Springsteen,Bruce Springsteen<br /><div style="text-align:center"><font color="red"><em>The Boss</em></font></div>

      I will include a workaround in the next release, but until then you could search the code for “split = obj.value.split(‘,’);”
      and replace it with “split = obj.value.split(‘|’);”.

      This will switch the separator between the name and description from “comma” to “pipe”.

      Alexander

  24. I’m using your chart component with the jquery ui tabs, it works great in firefox and chrome but in IE7 the charts that are on the position 2,3, etc the axis legend doesn’t show, only appears if I have a filter field and select it.

    The charts that i’m using are line charts.

    Anyone with a similar problem?

  25. I’m using the Column chart on a web part page with three webparts accross. The chart looks good, except that the legends are truncated. Is there any way to hide the legends in the column or bar charts?

  26. Using the Charting for Sharepoint v2.9.3.5 against a list which has 73 items in it’s view. The view is set to display 100 items at a time. However, the chart only displays the first 30 items. Anyway to expand that amount to include more than 30 items?

    1. This sounds strange. If you are not using a custom CAML, The item limit from the view is used. Are you absolutely sure the view have a 100 items limit?

      Alexander

  27. Forgive my ignorance as this may be a simple question. When using a customer CAML filter is there a way to provide a “null” value to show all data? For example, if I am filtering by a person’s first name, I would like to provide the option to show “all” along with the individual name filter.

    1. Hi,
      In CAML you can use 

      <IsNull><FieldRef Name='TheFieldInternalName' /></IsNull>

      There are however no way of adding a null selection in the custom dropdown. I’ll make a note to add this in a future release.

      Alexander

  28. Hi Alexander – just a quick heads up in case this hasn’t been mentioned already. Google seem to have changed their API recently, which may give an error on exisiting column/bar charts Error message is : “Bars series with value domain axis is not supported”. The fix for this is to add a new column to your source list, of type single line of text, and make this the first column (type string) in the charts config.

    Cheers

    Jim

    1. just to clarify too, the new column does not need to contain any data, and it doesn’t matter what you call it. It just needs to be present as the first item in the config.

    2. Hi,
      This error occurs when you have a column that is NOT of type string as first column in a chart that expects a string column.

      As Google does not have versioning on the chart api (it’s “1” for production and “1.1” for release candidate), you are automatically updated to the latest version when they push a new release.

      If you have “business critical” charts you should sign up for the newsletter from the chart team over at Google by going here

      I will do my best to keep this solution updated at all times, but I’m glad to receive bug reports from you all as i cannot monitor and test all charts when a new version is pushed.

      I will implement a method of testing the release candidate (v1.1) in future releases to make it easier to test the charts before the new version is put into production.

      EDIT: Added the missing link.

      Alexander

  29. Hello

    I am really sorry but I am a bit lost

    1- the download section does not seems to be reachable
    https://bautz.homeserver.com/SharePointJavascript/Scripts/ChartUsingGoogleVisualizationAPI/
    I found that http://spjsfiles.com/ but none file matche the naming in the article
    and there are 20 folders with a lots of js file without any dependancies information

    2- Is there a step by step guide to make that work especially the “Report” List is there a List template or an up to date description somewhere ?
    there is a huge part of update in different post but not a valid up to date document

    Thanks in adavance

  30. Has anyone figured out a way to use this SPJS_ChartsForSharePoint to display data from a SQL Server?
    I’m a bit confused about how to put the pieces together to actually display my first chart. I have downloaded jquery 1.7.1 and SPJS_ChartsForSharepoint_v3.0 and uploaded them into a document library on SharePoint 2007. I am just not certain what needs to happen next. I have tried reading the various blog entries across time but so far the specific steps to follow haven’t became obvious to me.

    1. First you should get jQuery v1.6.4 as this 1.7.1 does not work with the current solution. Then add a <abbr title="Content Editor Web Part"CEWP to the page where the chart should appear.

      Add this code:

      <div id="MyChart1"></div>
<script type="text/javascript">
/*****************************************************
		Address all containers
*****************************************************/
// All charts must be represented by a container with a unique id. This container  must be present in the page
arrOfChartContainers = ["MyChart1"];
</script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script>
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript" src="/test/English/Javascript/ChartUsingGoogleVisualizationAPI.js"></script>

      Remember to change the script src to point to your local files.

      EDIT: Not the http://www.google.com/jsapi – this one is not to be added locally.

      Alexander

  31. Hi !

    great page !

    hope you can help me with this.

    I need to substract the total of one column agaist another one, the result will be the data on the third colum

    One of those colums to be substracted is an stacked column

    therefore the data in the third colum should be (sum of Column1 – sum of Column2) = Column 3

    any ideas?

    1. Hi,
      Sorry for the late reply,
      This can be done by adding a calculated column in your SharePoint list and using this new column in your chart.

      Alexander

  32. Hi Alexander, and thank you very much for building and sharing this amazing tool for us!

    I am having problems using single quotes in the Filter options entries, like: “Alexander’s awesome API”.

    How can I do?

    Thank you!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.