Interactive Charts using Google Visualization API

24.05.2010 The code for the file “ChartUsingGoogleVisualizationAPI.js” is updated. Modified the array of fields to use for chart label and value columns. The two now uses the same array: ‘Number’, ‘Currency’, ‘Text’, ‘Calculated’, ‘Boolean’ ,’User’ ,’DateTime’ ,’Choice’ ,’Lookup’.

The version number is now set to 1.0.

21.05.2010 Added support for Lookup column as “Chart value column”. The code for the file “ChartUsingGoogleVisualizationAPI.js” is updated.

12.05.2010 Added support for Lookup column as “Chart label column”. The code for the file “ChartUsingGoogleVisualizationAPI.js” is updated.

11.05.2010 Added some examples at the bottom of the article.08.05.2010 Updated to fix some bugs:

Added support for “Currency”.

Fixed this issue noticed by Charlie.: Each time I edit the CEWP/Chart, the “Chart label column” field returns to the top-most selection.

An example on Custom CAML is added (see image below).

The code for the file “ChartUsingGoogleVisualizationAPI.js” is updated.

This is a solution for creating charts directly from SharePoint lists using Google Visualization API. The solution is a CEWP-solution and requiring no other than a few script references. All code and data are processed and rendered in the browser. No data is sent to any server.

The solution features “Sum”, “Count” and “Average”, and can pull data from any column type, including calculated columns. The chart config is stored in a separate “chart config list”, but all the configuration is done in the GUI seen in the bottom picture.

You can pull data from any existing list view (even from personal views – for personal use), or using a custom CAML-query. If you use a custom query, you have the ability to filter the data source using a user profile property pulled from the user profile of the logged in user.

You may use a regular expression to separate out the part of the profile property to use:

An example on Custom CAML:

A few pictures to look at while you wait for the code:
Multiple charts setup in the same CEWP

The same page in “Edit page mode”:

The code

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a sub site named “test” with a sub site named “English” with a document library named “Javascript”):

The jQuery-library is found here. The pictures and the sourcecode refers to jquery-1.4.2.min. If you use another version, please update the reference in the code.

Add this code to a CEWP and place it in the page where you want the chart to appear:
[javascript]
<!– Chart goes in this container –>
<div id="DemoChart1"></div>

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript" src="/test/English/Javascript/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/interaction.js"></script>
<script type="text/javascript" src="/test/English/Javascript/stringBuffer.js"></script>
<script type="text/javascript" src="/test/English/Javascript/ChartUsingGoogleVisualizationAPI.js"></script>
<script type="text/javascript">
/*****************************************************
Set list Guids and parameters
*****************************************************/
// List name or Guid of the config list. This list must reside in the same site as the charts are to be displayed
chartConfigListGuid = ‘E7A71324-043F-49A0-95EF-1E3E51DD8A85′;
// The "People and Groups" list – must be set to support filtering by user profile data.
userListGuid = "{570D772F-0EAB-45A8-8C54-9CCD4EC6A0AF}";
// Modify if the site collection is on a managed path
userListBaseUrl = ”;
// Each chart must be represented by a container with a unique id. This container must be present in the page
arrOfChartContainers = [‘DemoChart1′];

The list item in the configuration list is automatically created when you call the script using a “chartID” not already found in the configuration list:

A “chartID” can be “reused” if you want the same chart to appear in multiple pages. The chart configuration list is not suppoosed to be “hand edited”, all configuration are done trough the UI in “Edit page mode”. There i however nothing that stops you from hand editing the configuration if you like.

The article is not finished and will be updated with examples and more info, please be patient…

Examples

This example shows a web part page with 3 CEWP’s. The top two holds a <div> with unique id’s. The bottom one holds the code that calls the “chart builder” with an array of the id’s of the “containers” set in the top two CEWP’s:
The “container div” can be inserted in a CEWP holding text or other content.

You must ensure is that the CEWP holding the script are placed in the bottom (right) web part zone to ensure that the containers are rendered before the code “needs them”.

How do you add multiple CEWP to display charts. i add one, then go to add another and give it different config name that writes to config list and works but other one disappears. Probably missing something simple.

This is great work. It is such a great way to take advantage of existing tools and pull them into Sharepoint. I followed your tutorial, and everything works great. Have not tested across multiple browsers, but IE6/7 and FF seem to work just fine.

I had one question, and it pertains to functionality. With the existing build, can the count function for any field be extended to count the number of different values in the field rather than just the number of total entries? For example, if I have a field that has all Yes/No values, can the code be extended to count the total number of yes and no values for display within a chart?

IF this is not possible yet, it could be a good enhancement for the ability to have different types of functions for slicing and dicing the list values.

hi,
I am trying to make a custom CAML-query, and am using U2U CAML to make the code. In the programe I can do Today-15 and it will query it in the program, however when I am transfering it to the custom field I get no data. Do you know why that id and how to fix it?
Here is the code I am using: [Today-15Day(s)] . I need to show data for the curent month (+ and – 15 days). If there is a beter way I would gladely use it.

Hi, everything works fine now, except one thing that I haven’t been able to figure out.

I’ve build a chart reflecting incidents per week which is a line chart with a counted value of one column. The counts work ok, but for the weeks it returns numbers with 13 decimal zero’s which is not exactly what I want to see The week number is being pulled from a calculated field that returns a numeric value with no decimals. A similar problem exists with a second chart, this time a column type chart which shows the average duration of an incident. Again, the column numbers contain 13 decimal zero’s, the field data comes from a calculated numeric field with no decimals and what’s strange, the numbers are not sorted, e.g. the columns are named 3,4,10,5,6,26,13,…. each with up to 13 decimals.

Hi,
I have tested it and can conform your findings. This is the same behavior as it is when filtering a list view on a calculated column containing numbers only. I will fix it in the code, but a quick workaround would be to set the calculated column to return a text like “Week: 12″, in stead of only “12”.

Update: Or just set the calculated column to include a “space” at the end:

Is there a way to to be able format the tool tips? Right now it’s showing three lines of text, first one with the calendar week, next with the description (“count of calendar week”) and a last one with the number of entries.

Hi,
The solution uses Google’s “Visualization API”. There has been released a new version on may 18′th, but i haven’t had the time to update the code. This new update introduces data values on pie chart.

To test it you can update the CEWP-code like this:

// Remove this line
google.load("visualization","1",{packages:["columnchart","barchart","areachart","linechart","piechart","OrgChart"]});
// And add this
google.load("visualization","1",{packages:["corechart","OrgChart"]});

Some features has been removed, and some has been added. I will update the code, but cannot promise a specific date…

I am getting the error when I select the report options and refresh the page:

Line: 936
Char: 7
Error: ‘match()….’ is null or not an object.

May be I am doing it the wrong way. I want to show the chart for a list with a choice field of domain….where the bar graph will show no of request in different domains. I add Domain as title field and domain(sum) as the Y axis….please help.

Hi,
Looks like an error in one of the “regular expression handlers”. Could you please send me a screenshot of the chart config (in edit page mode) and one example on the values from the list it tries to render.

Great job!. Couple questions though. I added this to a site and is working fine. However when I try to add a 2nd CEWP to a webpartpage, the 1st one doesnt display anymore. I just copied the script from the 1st CEWP into the 2nd CEWP and edited the “DemoChart1″references to “DemoChart2″. Do I need to make any other changes? I read your Q/A above but didnt see anything that would answer this. Everything is on one site, so there are no cross site issues.

Also, can any type of data labels be added to the chart (ie within the slices of the pie chart display the # and/or %?

Hi,
If the two CEWP are in the same web part page, you only need to call the script once. All additional CEWP’s should contain only the “container div”

In the parameter “arrOfChartContainers” you add all the container id’s. Just assure that the script is called at the lowest position of the page – to make sure all the CEWP’s with the containers are rendered before the script is called.

I got everything setup last week and with your answer to a couple questions everything was working fine. I have a webpart page with 6 cewps on it where I am displaying different types of charts. However all day today I am noticing that the charts dont always render. Sometimes they do, sometimes they dont. Mostly I just see the cepw titles only on the page. Any idea why this might be? Im within a corporatve environment. Might there by network things blocking the calls to Google? Seems odd that it works sometimes and not others. Any thoughts are appreciated, and again great job. I dont get any error messages on the page.

I tried to implement the charts as it would offer others a big advantage not requiring any programming. After everything settled, I got the follow js-error in IE6:
Error: ‘thisCartConfig.ID’ is null or not an object.

I digged a bit in the code of the ChartUsingGoogleVisualizationAPI.js as the error refers to line 77, but to be honest, I js is not my favorite language. Any help? All scripts are loaded correctly..

I have to apologize. After taking a second look, I identified I used the wrong method to search for the chartConfigListGuid. Now it works like a charm

Btw, I updated the CEWP script to use the new package:corechart as reference as it provides the additional feature to display the values in the chart.

One additional question – would it be possible to add the idensity map chart? Would be an additional sugar to the already blowing-away script Charts were never more easy with your solution, many thanks

We are using several choice type columns. Would it be possible to modify the tool for choices that for bar or column charts to break this up into the individual choices? To explain this a bit more:

I want to display projects by names (this is the label for the x-axis). The projects consists of individual tasks to be performed. Currently I can only display the total number of tasks. It would be perfect if I could split them as stacked bars into the individual status of the task. To achieve this, I currently add 3 additional columns for the different status. If this could be achieved directly, it would generate less columns and gain a huge benefit
Thx for consideration.

I set this up on my site but when i try to save my chart configuration i keep getting an error:
Update chart config error:
One or more field types are not installed properly. go to the list settings page to delete these fields.

Thanks Alex for putting this together.
I am currently attempting to get your set up to work with a Visualization Motion Chart. Would you say that with a few changes here and there within your code this would be a plausible goal? Did you try to set up the code to enable additions like this, or should I just consider myself back at square 1?

As stated above, I added 3 additional columns which are calculated based on another Choice column. I checked the view and everything is finely displayed. If I use those new columns for the chart, I receive the following error:

the package currently uses the ‘old’ format of the google charts – therefore to add a title you have to use titleX instead of the new hAxis.title function.

For Column & Bar charts it seems that the google charts don’t provide an option to have datalabels directly displayed (a pitty you want to copy that to presentation) but all info can be gathered while hovering over the bars.

Alex:
I modified your code to implement a motion chart rather successfully with 1 caviot: is there any way to display the data without it being summed, averaged, or counted? Also, when I set the options in your nice UI, they didn’t transition to the actual chart, any suggestions?

Alex:
I am chugging along at my project making edits to your script here and there. I found out a problem with “options” and have fixed that.
My main problem now is the fact that the script only allows for String and Number types and as you may know, Google Visualization MotionCharts need Date type objects as their 2nd column. I see where you define the two data types in your code, but I am unsure of how to add the Date type.
Do you think you could lend a hand in what I am trying to do?
Thanks

Hi,
This script uses the “old” API, therefore the options configures as objects does not work. I’m working on an updated version with some more functionality, but motion chart is regrettably not something i have digged into…

If you look at the API over at Google, you find all there is to know, but i cannot help you at the moment as i have no experience with this chart type.

Okay thanks Alex.
One more question. Is there an edit or addition I could make so that the data is not compiled to averages/sums/counts BUT just raw data from the list? I can’t really tell where in the code this process is going on and what I would have to change to just read the raw data.
Thanks
Ryan

as far as I have identified most of the google charts do not have any options for data labels being displayed directly in the chart. The only chart showing datalabels is the PieChart and here only if you use the new corecharts.

Alex, I set everything up as your instructions suggest but after adding the CEWP with script and CEWP with statements, I don’t see the configuration interface load in the page. What could be causing this? I’ve loaded all the script files and have double checked the list ID multiple times.

Now I want to add a 2nd set of graphs using data from a different SP list…

Other than changing the List Guid, is there anything else I need to change???

On my 2nd attempt I’m seeing alot of errors… For example “An error occured while creating the configuration container. The list that is referenced here no longer exists”.

If I change the List Guid and container names to match the one used on my 1st attempt, the charts/graphs display properly… When I change the List guid to the one for my 2nd SP list, I receive an error…

I confirmed my List Guid… Are there any other gotcha’s I need to look out for?

But, i have little problem:
I want to see Column chart like you have in “MyTestChart5″. But, I have only two column to use.
– 1st column is location: Single line of text (For Chart Label Column)
– 2nd is Pass/Fail column: with YES and NO choice (For Chart Value Column), I use count.

When I see chart It shows location correctly, but in value it is showing me count of YES+NO, Instead of two different column with count of YES and No.

I have also changed the CEWP code to load the newer corechart package. This seems to work for the most part, but for a scatter chart I get an error saying that the X axis cannot be a string field, so the chart doesn’t render. The X axis is not a string field in my list, and the chart renders without problem using the older scatter chart package. I don’t know what is going on there.

I did get a motion chart working by including the motion chart package in the CEWP code.

I would really like to get a radar chart going, but I have not been able to do that yet. If anyone on this list has worked that out, I would appreciate knowing.

I didn’t change anything, but now my chart titles and pieSliceText, etc… have all suddenly changed. Everything appears as small cursive text. There are new configuration options shown on the Visualization pages, but even when I try them, I can’t seem to revert back to my previously used style of text… Any ideas?

Hi Alex,
awesome work! Thanks a lot !
But I have a little problem, I want to add an ‘Option’ “titleTextStyle” via the CEWP UI. I put “titleTextStyle” as ‘Option name’ and “fontSize:20″ for ‘Value’, but nothing changed when the PieChart is shown.
I tried it also for legendTextStyle, but the text doesn’t change.