Interactive Charts using Google Visualization API

25.08.2010 You find a new version of this feature here

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:
IMG

An example on Custom CAML:
IMG

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

The same page in “Edit page mode”:
IMG

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”):
IMG

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.

The scripts “interaction.js” and “stringBuffer.js” is created by Erucy and published on CodePlex.

Create a list to hold the configuration with these fields:

  • Title (the standard title field already in the list)
  • ChartConfig (Multiple lines of plain text)
  • LabelCol (Single line of text)
  • NumCols (Multiple lines of plain text)
  • UseCustomCAML (Yes/No check box)
  • CustomQuery (Multiple lines of plain text)
  • ViewGuid (Single line of text)
  • ListGuid (Single line of text)
  • ChartHeight (Single line of text)
  • ChartWidth (Single line of text)
  • ChartType (Single line of text)
  • GetFilterFromProfile (Yes/No check box)
  • UserProfileProperty (Single line of text)
  • RegexProfileProperty (Single line of text)

Note: These are FieldInternalNames and must be exactly like the above fields.

The GUID for this configuration list is used in the CEWP code below. Read here how to get the GUID for a list.

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'];

/*****************************************************
Init charts
*****************************************************/
// Load the visualizations from Google
google.load("visualization","1",{packages:["columnchart","barchart","areachart","linechart","piechart","OrgChart"]});
google.setOnLoadCallback(onloadInitChart);
// Call the script when the visualization packages are loaded
function onloadInitChart(){
call_drawChart(arrOfChartContainers);
}
</script>
[/javascript]

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:
IMG

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.

When adding a new chartID, you get an empty chart:
IMG

Edit page to access the configuration:
IMG

The code for the file “ChartUsingGoogleVisualizationAPI.js”:
[javascript]
/* Charting for SharePoint using Google Visualization API
* ———————————————
* Created by Alexander Bautz
* alexander.bautz@gmail.com
* http://spjsblog.com
* Copyright (c) 2009-2010 Alexander Bautz (Licensed under the MIT X11 License)
* v1.0
* LastMod: 24.05.2010
* LastChange: 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’
* ———————————————
* Include reference to:
* jquery – http://jquery.com
* interaction.js – http://spjslib.codeplex.com/
* stringBuffer.js – http://spjslib.codeplex.com/
* http://www.google.com/jsapi
* ChartUsingGoogleVisualizationAPI.js (this file)
* ———————————————
*/

function call_drawChart(arrOfChartContainerIDs){
$.each(arrOfChartContainerIDs,function(i,chartContainerID){
init_drawChart(chartContainerID);
});
}

function init_drawChart(cId){
// Check if container is defined
if($("#"+cId).length==0){
alert("The container with id "+cId+", is not defined!");
return;
}
$("#"+cId).before("<div id=’"+cId+"_chartConfig’ style=’padding:10px;border:1px silver solid;background-color:#F5F5DC;display:none’></div>");
wsBaseUrl = L_Menu_BaseUrl + ‘/_vti_bin/’;
var query = "<Where><Eq><FieldRef Name=’Title’ /><Value Type=’Text’>"+cId+"</Value></Eq></Where>";
chartConfig = queryItems(chartConfigListGuid,query,
['ID',
'Title',
'ChartConfig',
'LabelCol',
'NumCols',
'UseCustomCAML',
'CustomQuery',
'GetFilterFromProfile',
'UserProfileProperty',
'RegexProfileProperty',
'ListGuid',
'ViewGuid',
'ChartHeight',
'ChartWidth',
'ChartType'],1);
if(chartConfig.count==0){
addChartId = addItem(chartConfigListGuid,{‘Title’:cId,’ChartHeight’:’250′,’ChartWidth’:’500′,’ChartConfig’:’title:Add chart title here’});
if(!addChartId.success){
alert("An error occured while creating the configuration container:n"+addChartId.errorText);
}else{
var myChartConfig = {
myChartId:cId,
chartConfigID:addChartId.id,
configRaw:’title:Add chart title here’,
useCustomCAML:false,
useUserProfileProperty:false,
userProfileProperty:”,
regexProfileProperty:”,
customCAML:”,
listGuid:”,
viewGuid:”,
labelCol:”,
numCols:”,
chartHeight:’200′,
chartWidth:’450′,
chartType:’ColumnChart’}
}
}else{
thisChartConfig = chartConfig.items[0];
var myChartConfig = {
myChartId:cId,
chartConfigID:thisChartConfig['ID'],
configRaw:(thisChartConfig['ChartConfig']!=null)?thisChartConfig['ChartConfig']:”,
useCustomCAML:(thisChartConfig['UseCustomCAML']==1)?true:false,
useUserProfileProperty:(thisChartConfig['GetFilterFromProfile']==1)?true:false,
userProfileProperty:(thisChartConfig['UserProfileProperty']!=null)?thisChartConfig['UserProfileProperty']:”,
regexProfileProperty:(thisChartConfig['RegexProfileProperty']!=null)?thisChartConfig['RegexProfileProperty']:”,
customCAML:(thisChartConfig['CustomQuery']!=null)?thisChartConfig['CustomQuery']:”,
listGuid:(thisChartConfig['ListGuid']!=null)?thisChartConfig['ListGuid']:”,
viewGuid:(thisChartConfig['ViewGuid']!=null)?thisChartConfig['ViewGuid']:”,
labelCol:(thisChartConfig['LabelCol']!=null)?thisChartConfig['LabelCol']:”,
numCols:(thisChartConfig['NumCols']!=null)?thisChartConfig['NumCols']:”,
chartHeight:(thisChartConfig['ChartHeight']!=null)?thisChartConfig['ChartHeight']:’200′,
chartWidth:(thisChartConfig['ChartWidth']!=null)?thisChartConfig['ChartWidth']:’450′,
chartType:(thisChartConfig['ChartType']!=null)?thisChartConfig['ChartType']:’ColumnChart’}
}
// Code inactive in "edit page mode"
if($(".ms-WPAddButton").length>0){
editChartConfig(myChartConfig);
}else{
// build chart option object
chartOptions = {};
if(arrConfig!=”){
var arrConfig = myChartConfig.configRaw.split(‘;’);
$.each(arrConfig,function(i,optRaw){
var split = optRaw.split(‘:’);
if(split.length==2){
var param = split[0];
var val = split[1];
}else{
var param = optRaw.substring(0,optRaw.indexOf(‘:’));
var val = optRaw.substring(optRaw.indexOf(‘:’)+1);
}

if(param==’colors’){
var colorArr = [];
if(val.indexOf(‘color’)>-1 && val.indexOf(‘darker’)>-1){
var colorArrRaw = val.match(/{color:.*?}/g);
$.each(colorArrRaw,function(i,colorRaw){
var colorSplit = colorRaw.replace(/{|}|"|’/g,”).split(‘,’);
var color = colorSplit[0].split(‘:’)[1];
var darker = colorSplit[1].split(‘:’)[1];
obj = {color:$.trim(color),darker:$.trim(darker)};
colorArr.push(obj);
});
}else{
var colorArrRaw = val.replace(/[|]|"|’/g,”).split(‘,’);
$.each(colorArrRaw,function(i,color){
colorArr.push(color);
});
}
val = colorArr;
}
chartOptions[param]=val;
});
}
// Width and height
chartOptions.height=myChartConfig.chartHeight;
chartOptions.width=myChartConfig.chartWidth;

// labelCol
if(myChartConfig.labelCol!=”){
myChartConfig.labelCol = myChartConfig.labelCol.split(‘:’)[0];
}
// NumCol
numColNameLabelAndType = [];
if(myChartConfig.numCols!=”){
var arrNumFields = myChartConfig.numCols.split(‘;’);
$.each(arrNumFields,function(i,fieldOpt){
if(fieldOpt.length==0)return;
var full = fieldOpt.split(‘,’);
numFieldObj = {};
$.each(full,function(i,paramRaw){
var split = paramRaw.split(‘:’);
var param = split[0];
var val = split[1];
numFieldObj[param]=val;

});
numColNameLabelAndType.push(numFieldObj);
});
}
// Call chartBuilsing function
drawChart(myChartConfig,numColNameLabelAndType,chartOptions);
}
}

/*****************************************************
Get views
*****************************************************/
function getViewsForThisList(obj,lGuid,vGuid){
var onLoad = true;
if(typeof(obj)==’object’){
onLoad = false;
var listGuid = obj.find(‘option:selected’).val();
var chartID = obj.attr(‘chartID’);
}else{
var listGuid = lGuid;
}
if(listGuid!=”){
var viewColl = customGetViewCollection(listGuid);
var options = "<option value=”>&lt;Select view&gt;</option>";
$.each(viewColl.views,function(){
var personalView = ”;
var personalViewTooltip = ”;
if($(this).attr(‘Personal’)==’TRUE’){
var personalView = " (Personal view)";
var personalViewTooltip = "If you create a chart from a personal view, the chart will only be available for you. Other users accessing the chart will receive a "No Data" message.";
}
if($(this).attr(‘Name’)==vGuid){
selected = "selected=’selected’";
}else{
selected = ”;
}
options+="<option title=’"+personalViewTooltip+"’ value=’"+$(this).attr(‘Name’)+"’ "+selected+">"+$(this).attr(‘DisplayName’)+personalView+"</option>";
});
// Load eller select
if(onLoad){
return options;
}else{
$("#"+chartID+"_viewGuid").html(options);
//customGetListFields(listGuid,false);
fieldsOnloadOrOnchange(”,listGuid,chartID);
}
}else{
// Load or select
if(onLoad){
alert("ListGuid not defined");
return ”;
}else{
$("#"+chartID+"_viewGuid").html(”);
$("#"+chartID+"_labelFieldsDiv").html(”);
$("#"+chartID+"_numFieldsDiv").parents(‘td:first’).find(‘select’).each(function(){
$(this).remove();
});
$("#"+chartID+"_numFieldsDiv").html(”);
$("#"+chartID+"_cloneNumSelectLink").hide();
}
}
}

function customCloneFieldSelector(chartID){
var td = $("#"+chartID+"_numColTd");
var clone = td.find(‘select:last’).parent().clone();
clone.find(‘option:first’).attr(‘selected’,true);
td.append("<div>"+clone.html()+"</div>");
}

/*****************************************************
Save config
*****************************************************/
function saveChartConfig(obj,chartId,chartConfigId){
chartConfigData = [];
var configDiv = obj.parents("div[id='"+chartId+"_chartConfig']");
// Current options
configDiv.find("input[fin='ChartConfig']").each(function(){
chartConfigData.push($(this).attr(‘optionName’)+":"+$(this).val());
});
// New options
configDiv.find("div.newChartConfigDiv").each(function(){
var newOptionName = $(this).find(‘input:first’).val();
var newOptionVal = $(this).find(‘input:last’).val();
if(newOptionName!=”&&newOptionVal!=”){
chartConfigData.push(newOptionName+":"+newOptionVal);
}
});

useCustomCAML = (configDiv.find("input[id='"+chartId+"_UseCustomCAML']").attr(‘checked’)==true)?’1′:’0′;
useUserProfileProperty = (configDiv.find("input[id='"+chartId+"_GetFilterFromUserProfile']").attr(‘checked’)==true)?’1′:’0′;
userProfileProperty = configDiv.find("select[id='"+chartId+"_selectUserprofileProperty'] option:selected").val();
regexProfileProperty= configDiv.find("input[id='"+chartId+"_RegexProfileProperty']").val();

customQueryData = $.trim(configDiv.find("textarea[id='"+chartId+"_customQuery']").val());
listGuidVal = $.trim(configDiv.find("select[id='"+chartId+"_listGuid'] option:selected").val());
viewGuidVal = $.trim(configDiv.find("select[id='"+chartId+"_viewGuid'] option:selected").val());

var labelSelect = $("#"+chartId+"_labelFieldsDiv").find(‘select option:selected’);
labelCol = labelSelect.val()+":"+labelSelect.text();

numCols = ”;
$("#"+chartId+"_numColTd").find(‘span’).each(function(){
if($(this).attr(‘fin’)!=undefined){
numCols += "label:"+$(this).attr(‘value’)+",fin:"+$(this).attr(‘fin’)+",action:"+$(this).attr(‘action’)+",prefix:"+$(this).attr(‘prefix’)+",fieldType:"+$(this).attr(‘fieldType’)+";";
}
});

$("#"+chartId+"_numColTd").find(‘select.numFieldSelect’).each(function(){
var thisOpt = $(this).find(‘option:selected’);
if(thisOpt.val()!=”){
var fieldAction = $(this).next().find(‘option:selected’).val();
var prefix = $(this).next().next().attr(‘checked’);
numCols += "label:"+thisOpt.text()+",fin:"+thisOpt.val()+",action:"+fieldAction+",prefix:"+prefix+",fieldType:"+thisOpt.attr(‘fieldType’)+";";
}
});
chartHeight = $.trim(configDiv.find("input[id='"+chartId+"_ChartHeight']").val());
chartWidth = $.trim(configDiv.find("input[id='"+chartId+"_ChartWidth']").val());
chartType = configDiv.find("select[id='"+chartId+"_selectChartType'] option:selected").val();
wsBaseUrl = L_Menu_BaseUrl + ‘/_vti_bin/’;
res = updateItem(chartConfigListGuid,chartConfigId,
{‘ChartConfig’:chartConfigData.join(‘;’),
‘UseCustomCAML’:useCustomCAML,
‘GetFilterFromProfile’:useUserProfileProperty,
‘UserProfileProperty’:userProfileProperty,
‘RegexProfileProperty’:regexProfileProperty,
‘CustomQuery’:customQueryData,
‘ListGuid’:listGuidVal,
‘ViewGuid’:viewGuidVal,
‘LabelCol’:labelCol,
‘NumCols’:numCols,
‘ChartHeight’:chartHeight,
‘ChartWidth’:chartWidth,
‘ChartType’:chartType});

if(!res.success){
alert("Update chart config error:n"+res.errorText);
}else{
if(confirm("Saved OKnnRefresh page?"))location.href=location.href;
}
}

/*****************************************************
Test regular expression
*****************************************************/
function testRegExp(id){
var testRegExp = $("#"+id+"_RegexProfileProperty").val();
var profileProperty = $("#"+id+"_selectUserprofileProperty").val();
var up = getUserInfo();
if(up[profileProperty]!=”){
try
{
var regexResult = up[profileProperty].match(testRegExp);
msgBuffer = ["The full text in the user property field ""+profileProperty+"" for the user ""+up.Title+"" is:n"];
msgBuffer.push(up[profileProperty]);
msgBuffer.push("nnThe full RegExp match is this:n"+regexResult);
msgBuffer.push("nnThe part that will be used as a filter is this:n"+regexResult[1]);
var msg = msgBuffer.join(”);
}catch(err){
var msg = err.description;
}
}else{
var msg = "The property field ""+profileProperty+"" is empty!";
}
alert(msg);
}

/*****************************************************
Chart config options link
*****************************************************/
function setLinkToConfigurationOptions(obj,chartType,chartId){
var link = [];
var onLoad = true;
if(typeof(obj)==’object’){
var chartId = obj.attr(‘chartID’);
var selectedChartOption = obj.find(‘option:selected’);
var chartType = selectedChartOption.val();
var chartTypeFriendly = selectedChartOption.text();
}else{
var selectedChartOption = $("#"+chartId+"_selectChartType").find(‘option:selected’);
var chartType = selectedChartOption.val();
var chartTypeFriendly = selectedChartOption.text();
}
link.push("<a title=’Opens in new window’ href=’http://code.google.com/intl/en-EN/apis/visualization/documentation/gallery/");
link.push(chartType.toLowerCase()+".html#Configuration_Options’ target=’_blank’>Instructions for "+chartTypeFriendly+"</a>");
link = link.join(”);
// Write to placeholder
$("#"+chartId+"_chartOptionsLink").html("<div style=’font-size:10px;font-weight:normal;display:inline’>"+link+"</div>");
}

/*****************************************************
Edit chart config
*****************************************************/
function editChartConfig(config){
var editOptLeftColArr = [];
var editOptRightColArr = [];
var editOptBottomArr = [];
var editOptTopColArr = [];
var editOptTopLeftColArr = [];
var editOptTopRightColArr = [];
var arrOfChartTypes = ['BarChart|Bar Chart','ColumnChart|Column Chart','AreaChart|Area Chart','LineChart|Line Chart',"PieChart|Pie Chart","OrgChart|Org Chart"];
var strChartTypeOptions = [];
// Chart types
$.each(arrOfChartTypes,function(i,typeRaw){
var split = typeRaw.split(‘|’);
var cType = split[0];
var cTypeFriendly = split[1];
if(config.chartType==cType){
strChartTypeOptions.push("<option value=’"+cType+"’ selected>"+cTypeFriendly+"</option>");
}else{
strChartTypeOptions.push("<option value=’"+cType+"’>"+cTypeFriendly+"</option>");
}
});

strChartTypeOptions= strChartTypeOptions.join(”);
// ChartType
var strArr = [];
strArr.push("<tr>");
strArr.push("<td colspan=’2′ style=’padding:3 5 0 5′><label style=’font-weight:bold’>Chart type</label><br />");
strArr.push("<select id=’"+config.myChartId+"_selectChartType’ chartID=’"+config.myChartId+"’ onchange=’javascript:setLinkToConfigurationOptions($(this))’>");
strArr.push(strChartTypeOptions);
strArr.push("</selec>");
strArr.push("</td></tr>");

// ListGuid
views = "<option value=” selected>Select list first</option>";

var listColl = customGetListCollection();
var listOptionsStrBuffer = ["<option value=''>&lt;Select list&gt;</option>"];
$.each(listColl.lists,function(){
if($(this).attr(‘Name’)==config.listGuid){
views = getViewsForThisList(”,config.listGuid,config.viewGuid);
selected = "selected=’selected’";
}else{
selected = ”;
}
listOptionsStrBuffer.push("<option value=’"+$(this).attr(‘Name’)+"’ "+selected+">"+$(this).attr(‘Title’)+"</option>");
});

listSelect = "<select id=’"+config.myChartId+"_listGuid’ chartID=’"+config.myChartId+"’ onchange=’javascript:getViewsForThisList($(this),""+config.listGuid+"",""+config.viewGuid+"")’>"+listOptionsStrBuffer.join(”)+"</select>";

strArr.push("<tr>");
strArr.push("<td colspan=’2′ style=’padding:3 5 0 5′><label style=’font-weight:bold’>Select list</label><br />");
strArr.push(listSelect);
strArr.push("</td></tr>");

// ViewGuid
strArr.push("<tr>");
strArr.push("<td colspan=’2′ style=’padding:3 5 0 5′><label style=’font-weight:bold’>Select view or use custom CAML</label><br />");
strArr.push("</td></tr><tr><td colspan=’2′ style=’padding:0 5 0 5′><select id=’"+config.myChartId+"_viewGuid’ chartID=’"+config.myChartId+"’>");
strArr.push(views);
strArr.push("</select>");
strArr.push("</td></tr>");
// Width and height
strArr.push("<tr>");
strArr.push("<td colspan=’2′ style=’padding:3 5 0 5′><label style=’font-weight:bold’>Chart height</label><br />");
strArr.push("<input style=’width:100px’ id=’"+config.myChartId+"_ChartHeight’ value=’"+config.chartHeight+"’ />");
strArr.push("</td></tr>");
strArr.push("<tr>");
strArr.push("<td colspan=’2′ style=’padding:3 5 0 5′><label style=’font-weight:bold’>Chart width</label><br />");
strArr.push("<input style=’width:100px’ id=’"+config.myChartId+"_ChartWidth’ value=’"+config.chartWidth+"’ />");
strArr.push("</td></tr>");
str = strArr.join(”);
editOptTopLeftColArr.push(str);

// Fields
var strArr = [];
strArr.push("<tr>");
strArr.push("<td colspan=’2′ style=’padding:3 5 0 5′><label style=’font-weight:bold’>Chart label column</label><br />");
strArr.push("<div id=’"+config.myChartId+"_labelFieldsDiv’></div>");
strArr.push("</td></tr>");
strArr.push("<tr><td colspan=’2′ style=’padding:3 5 0 5′><label style=’font-weight:bold’>Chart value columns</label></td></tr>");
strArr.push("<tr><td colspan=’2′ id=’"+config.myChartId+"_numColTd’ style=’padding:0 5 0 5′><div id=’"+config.myChartId+"_numFieldsDiv’></div></td></tr>");
strArr.push("<tr><td colspan=’2′ style=’padding:0 5 0 5′><a id=’"+config.myChartId+"_cloneNumSelectLink’ chartID=’"+config.myChartId+"’ style=’display:none’ href=’javascript:customCloneFieldSelector(""+config.myChartId+"")’>Add new</a></td></tr>");
strArr.push("<tr><td colspan=’2′ style=’padding:10 5 0 5′><div title=’Click to read about column types’ style=’cursor:pointer;font-weight:bold’ onclick=’$(this).next().toggle()’>About column types</div>");
strArr.push("<div style=’display:none;width:350px’>");
strArr.push("<div style=’padding:3px;border:1px silver solid’>Columns containing numbers can be summed. The column can contain text and number mixed. ");
strArr.push("A Regular expression extracts the number from the text.<br /><br />");
strArr.push("However notice that only the first occurance of a int/float is used.<br /><br />");
strArr.push("Boolean columns can be counted or summed (Yes=1, No=0).<br /><br />");
strArr.push("All columns can be counted (not empty=1, empty=0)</div></div></td></tr>")
strArr.push("</td></tr>");
str = strArr.join(”);
editOptTopRightColArr.push(str);

// Options
if(config.configRaw!=”){
var arrConfigData = config.configRaw.split(‘;’);
$.each(arrConfigData,function(i,rawOption){
split = rawOption.split(‘:’);
if(split.length==2){
var label = split[0];
var val = split[1].replace(/’/g,’"’);
}else{
var label = rawOption.substring(0,rawOption.indexOf(‘:’));
var val = rawOption.substring(rawOption.indexOf(‘:’)+1).replace(/’/g,’"’);
}
var strArr = [];
strArr.push("<tr>");
strArr.push("<td style=’padding:0 5 0 5′>"+label+"</td>");
strArr.push("<td style=’padding:0 5 0 5′><input style=’width:150px’ ");
strArr.push("fin=’ChartConfig’ optionName=’"+label+"’ id=’"+config.myChartId+"_"+label+"’ type=’Text’ value=’"+val+"’ />");
strArr.push("<a title=’Remove current option’ style=’padding-left:5px’ href=’javascript:’ onclick=’addOrRemove($(this),false)’>X</a>");
strArr.push("</td></tr>");
str = strArr.join(”);
editOptLeftColArr.push(str);
});
}

// Add new option
var strArr = [];
strArr.push("<tr>");
strArr.push("<td colspan=’2′ style=”>");
strArr.push("<a style=’padding-left:5px’ href=’javascript:’ onclick=’addOrRemove($(this),true)’>Add new option</a>");
strArr.push("</td></tr>");
str = strArr.join(”);
editOptLeftColArr.push(str);

// CAML
var customCAMLchecked = ”;
var customCAMLAreaDisplay = ‘none’
if(config.useCustomCAML){
customCAMLchecked = ‘checked’;
customCAMLAreaDisplay = ‘block’;
}
var filterFromUPchecked = ”;
var filterFromUPdisplay = ‘none’;
if(config.useUserProfileProperty){
filterFromUPchecked = ‘checked’;
var filterFromUPdisplay = ‘block’;
}

var strArr = [];
strArr.push("<tr>");
strArr.push("<td colspan=’2′ style=’padding:0 5 0 5′>");
strArr.push("<input type=’checkbox’ id=’"+config.myChartId+"_UseCustomCAML’ "+customCAMLchecked+" onclick=’javascript:$("#"+config.myChartId+"_tableUseCustomCAML").toggle()’><label for=’"+config.myChartId+"_UseCustomCAML’>Use custom CAML (overrides selected view)</label>");
strArr.push("</td></tr>");
strArr.push("<tr><td id=’"+config.myChartId+"_tableUseCustomCAML’ style=’display:"+customCAMLAreaDisplay+"’>");
strArr.push("<table><tr><td>");
strArr.push("<input type=’checkbox’ id=’"+config.myChartId+"_GetFilterFromUserProfile’ "+filterFromUPchecked+" onclick=’javascript:$("#"+config.myChartId+"_userProfilePropertyDescription").toggle()’><label for=’"+config.myChartId+"_GetFilterFromUserProfile’>Get filter value from this profile property:&nbsp;</label>");
strArr.push("<select id=’"+config.myChartId+"_selectUserprofileProperty’ chartID=’"+config.myChartId+"’>");
var arrOfUserProfileProperties = ['ID','Name','Title','EMail','Department','JobTitle','SipAddress'];
$.each(arrOfUserProfileProperties,function(i,prop){
var propSelected = ”;
if(prop==config.userProfileProperty){
propSelected = ‘selected’;
}
strArr.push("<option value=’"+prop+"’ "+propSelected+">"+prop+"</option>");
});
strArr.push("</select>");
strArr.push("</td></tr>");
strArr.push("<tr><td colspan=’2′ style=’padding:0 5 0 5′>");
strArr.push("<div id=’"+config.myChartId+"_userProfilePropertyDescription’ style=’width:390px;display:"+filterFromUPdisplay+"’>");
strArr.push("<div style=’color:red’>");
strArr.push("To use the user profile property as a filter, you must insert {} as a placeholder where the user profile property is to be inserted.<br /><br />");
strArr.push("If you use a regular expression to identify the value, it is the first backreference that is used.</div><br />");
strArr.push("<label>Optional RegEx to match property:&nbsp;</label><a href=’javascript:testRegExp(""+config.myChartId+"")’>Test regular expression</a><br />");
strArr.push("<input id=’"+config.myChartId+"_RegexProfileProperty’ type=’Text’ value=’"+config.regexProfileProperty+"’ style=’width:100%’></div>");
strArr.push("</td></tr>");
str = strArr.join(”);
editOptRightColArr.push(str);

escapedCAML = config.customCAML.replace(/</g,’&lt;’).replace(/>/g,’&gt;’);
var strArr = [];
strArr.push("<tr>");
strArr.push("<td style=’padding:0 5 0 5′><label>Custom CAML-query</label><br />");
strArr.push("<textarea style=’width:400px;height:150px’ id=’"+config.myChartId+"_customQuery’>"+escapedCAML+"</textarea>");
strArr.push("</td></tr></table>");
strArr.push("</td></tr>");
str = strArr.join(”);
editOptRightColArr.push(str);
// Save button
var strArr = [];
strArr.push("<tr>");
strArr.push("<td colspan=’2′ style=’text-align:right’>");
strArr.push("<input onclick=’javascript:saveChartConfig($(this),""+config.myChartId+"",""+config.chartConfigID+"");’ type=’button’ value=’Save’ />");
strArr.push("</td></tr>");
str = strArr.join(”);
editOptBottomArr.push(str);

// wrap up
var strArr = [];
strArr.push("<table style=’background-color:#ffffff;width:100%’ cellpadding=’0′ cellspacing=’2′>");
strArr.push(editOptTopColArr.join(”));
strArr.push("</table>");
wrapTop = strArr.join(”);

var strArr = [];
strArr.push("<table style=’background-color:#ffffff;width:100%;’ cellpadding=’2′ cellspacing=’0′>");
strArr.push(editOptTopLeftColArr.join(”));
strArr.push("</table>");
wrapTopLeft = strArr.join(”);

var strArr = [];
strArr.push("<table style=’background-color:#ffffff;width:100%’ cellpadding=’2′ cellspacing=’0′>");
strArr.push(editOptTopRightColArr.join(”));
strArr.push("</table>");
wrapTopRight = strArr.join(”);

var strArr = [];
strArr.push("<table style=’background-color:#ffffff;width:100%;’ cellpadding=’2′ cellspacing=’0′>");
strArr.push(editOptLeftColArr.join(”));
strArr.push("</table>");
wrapBottomLeft = strArr.join(”);

var strArr = [];
strArr.push("<table style=’background-color:#ffffff;width:100%’ cellpadding=’2′ cellspacing=’0′>");
strArr.push(editOptRightColArr.join(”));
strArr.push("</table>");
wrapBottomRight = strArr.join(”);

var strArr = [];
strArr.push("<table style=’background-color:#ffffff;width:100%’ cellpadding=’2′ cellspacing=’0′>");
strArr.push(editOptBottomArr.join(”));
strArr.push("</table>");
wrapBottom = strArr.join(”);

var wrap = [];
wrap.push("<table cellpadding=’0′ cellspacing=’0′>");
wrap.push("<tr><td valign=’top’ colspan=’2′ style=’padding:5px;font-size:14px;font-weight:bold;background-color:#F5F5DC’>Chart configuration for "+config.myChartId+"</td>");
wrap.push("<tr><td valign=’top’ colspan=’2′ style=’border-left:1px silver solid;border-top:1px silver solid;border-right:1px silver solid’>"+wrapTop+"</td></tr>");
wrap.push("<tr><td valign=’top’ style=’border-left:1px silver solid;border-bottom:1px silver solid;padding:3px’>"+wrapTopLeft+"</td>")
wrap.push("<td valign=’top’ style=’border-right:1px silver solid;border-bottom:1px silver solid;padding:3px’>"+wrapTopRight+"</td></tr>");
wrap.push("<tr><td valign=’top’ style=’padding:5px;font-size:14px;font-weight:bold;background-color:#F5F5DC’>Options&nbsp;<span id=’"+config.myChartId+"_chartOptionsLink’></span></td>");
wrap.push("<td valign=’top’ style=’padding:5px;font-size:14px;font-weight:bold;background-color:#F5F5DC’>Advanced options</td></tr>");
wrap.push("<tr><td valign=’top’ style=’border-left:1px silver solid;border-top:1px silver solid;padding:3px’>"+wrapBottomLeft+"</td>");
wrap.push("<td valign=’top’ style=’border-top:1px silver solid;border-right:1px silver solid;padding:3px’>"+wrapBottomRight+"</td></tr>");
wrap.push("<tr><td colspan=’2′ valign=’top’ style=’border-left:1px silver solid;border-bottom:1px silver solid;border-right:1px silver solid;text-align:right;padding:3′>"+wrapBottom+"</td></tr></table>");
wrap = wrap.join(”);

$("#"+config.myChartId+"_chartConfig").show().html(wrap);
// Fields
if(config.listGuid!=”){
fieldsOnloadOrOnchange(config);
}else{
$("#"+config.myChartId+"_labelFieldsDiv").html(‘Select list first’);
$("#"+config.myChartId+"_numFieldsDiv").html(‘Select list first’);
}
// Option link
var chartConfigLink = setLinkToConfigurationOptions(”,config.chartType,config.myChartId);
}

function fieldsOnloadOrOnchange(config,listGuid,chartID){
if(typeof(config)==’object’){
var onLoad = true;
listGuid = config.listGuid;
}else{
var onLoad = false;
}
var fieldsObj = customGetListFields(listGuid,true);
var currNumFieldsStrBuffer = [];
var labelFieldSelectBuffer = [];
var numFieldSelectBuffer = ["<option value=''>&lt;select&gt;</option>"];
if(onLoad){
// Numcols
currNumFieldsArr = config.numCols.split(‘;’);
$.each(currNumFieldsArr,function(i,rawVal){
var split = rawVal.split(‘,’);
if(split.length>1){
var label = split[0].split(‘:’)[1];
var fin = split[1].split(‘:’)[1];
var action = split[2].split(‘:’)[1];
var prefix = split[3].split(‘:’)[1];
var fieldType = split[4].split(‘:’)[1];
var actionLabel = " ("+action+")";
if(prefix!=’true’){
var actionLabel = " ("+action+" no prefix)";
}
currNumFieldsStrBuffer.push("<span title=’Click to remove’ style=’cursor:pointer;padding-left:10px;’ ");
currNumFieldsStrBuffer.push("fin=’"+fin+"’ value=’"+label+"’ action=’"+action+"’ prefix=’"+prefix+"’ fieldType=’"+fieldType+"’ onclick=’javascript:$(this).next().andSelf().remove()’>");
currNumFieldsStrBuffer.push("&bull;&nbsp;"+label+actionLabel+"</span><br />");
}
});

// Build the select for the "string" col
var labelColSplit = config.labelCol.split(‘:’);
var strFin = labelColSplit[0];
var strLabel = labelColSplit[1];

}

// Build field selector
$.each(fieldsObj.labelFields,function(i,rawOpt){
var split = rawOpt.split(‘|’);
var fin = split[0];
var disp = split[1];
var selected = ”;
if(onLoad){
if(fin==strFin){
selected = "selected";
}
}
labelFieldSelectBuffer.push("<option value=’"+fin+"’ "+selected+">"+disp+"</option>");
});

$.each(fieldsObj.numFields,function(i,rawOpt){
var split = rawOpt.split(‘|’);
var fin = split[0];
var disp = split[1];
var fieldType = split[2];
numFieldSelectBuffer.push("<option value=’"+fin+"’ fieldType=’"+fieldType+"’>"+disp+"</option>");
});
numFieldsBuffer = [];
labelFields = "<div><select chartID=’"+config.myChartId+"’>"+labelFieldSelectBuffer.join(”)+"</select></div>";
if(fieldsObj.onLoad){
numFieldsBuffer.push(currNumFieldsStrBuffer.join(”));
}
numFieldsBuffer.push("<div><select class=’numFieldSelect’>"+numFieldSelectBuffer.join(”)+"</select>");
numFieldsBuffer.push("<select class=’numActionSelect’>");
numFieldsBuffer.push("<option value=’Sum’ selected>Sum</option>");
numFieldsBuffer.push("<option value=’Count’>Count</option>");
numFieldsBuffer.push("<option value=’Average’>Average</option></select>");
numFieldsBuffer.push("<input title=’Uncheck to remove the "Sum of", "Count of" or "Average of" prefix’ type=’checkbox’ checked><label>Prefix</label></div>");
var numFields = numFieldsBuffer.join(”);
// Load or select
if(onLoad){
$("#"+config.myChartId+"_labelFieldsDiv").html(labelFields);
$("#"+config.myChartId+"_numFieldsDiv").html(numFields);
$("#"+config.myChartId+"_cloneNumSelectLink").show();
}else if(!onLoad){
$("#"+chartID+"_labelFieldsDiv").html(labelFields);
$("#"+chartID+"_numFieldsDiv").parents(‘td:first’).find(‘select’).each(function(){
$(this).parent().remove();
});
$("#"+chartID+"_numFieldsDiv").html(numFields);
$("#"+chartID+"_cloneNumSelectLink").show();
}
}

/*****************************************************
Get fields
*****************************************************/
function customGetListFields(listName,onLoad){
xmlStr = [];
xmlStr.push(‘<GetList xmlns="http://schemas.microsoft.com/sharepoint/soap/">’);
xmlStr.push(‘<listName>’ + listName + ‘</listName>’);
xmlStr.push(‘</GetList>’);
xmlStr = xmlStr.join(”);
var result = {success:false,labelFields:[],numFields:[]};
wrapSoapRequest(wsBaseUrl + ‘lists.asmx’, ‘http://schemas.microsoft.com/sharepoint/soap/GetList’, xmlStr, function(data){
if($(‘ErrorText’, data).length > 0){
result.success = false;
}else{
result.onLoad = (onLoad==true)?true:false;
result.success = true;
result.name = $(‘List’, data).attr(‘Name’);
var arrOfTypesToIncludeInStr = ['Number','Currency','Text','Calculated','Boolean','User','DateTime','Choice','Lookup'];
var arrOfTypesToIncludeInNum = ['Number','Currency','Text','Calculated','Boolean','User','DateTime','Choice','Lookup'];
$(‘Field’, data).each(function(){
if($(this).attr(‘DisplayName’)!=undefined){
if($.inArray($(this).attr(‘Type’),arrOfTypesToIncludeInStr)>-1){
// Include user created single item lookup
if($(this).attr(‘Type’)==’Lookup’ && $(this).attr(‘FromBaseType’)==’TRUE’)return;
result.labelFields.push($(this).attr(‘Name’)+"|"+$(this).attr(‘DisplayName’));
}
if($.inArray($(this).attr(‘Type’),arrOfTypesToIncludeInNum)>-1){
// Include user created single item lookup
if($(this).attr(‘Type’)==’Lookup’ && $(this).attr(‘FromBaseType’)==’TRUE’)return;
result.numFields.push($(this).attr(‘Name’)+"|"+$(this).attr(‘DisplayName’)+"|"+$(this).attr(‘Type’));
}
}
});
}
});
return result;
}

/*****************************************************
Get list collection
*****************************************************/
function customGetListCollection(){
xmlStr = ‘<GetListCollection xmlns="http://schemas.microsoft.com/sharepoint/soap/" />’;
var result = {success:false, errorCode:”, errorText:’internal error’, lists:[]};
wrapSoapRequest(L_Menu_BaseUrl + ‘/_vti_bin/lists.asmx’, ‘http://schemas.microsoft.com/sharepoint/soap/GetListCollection’, xmlStr, function(data){

if ($(‘ErrorText’, data).length > 0) {
result.success = false;
} else {
result.success = true;
var arrTypesToSkip = ['110','111','112','113','114','115','116','117','118'];
$(‘List’, data).each(function(i){
if($.inArray($(this).attr(‘ServerTemplate’),arrTypesToSkip)==-1){
result.lists.push($(this));
}
});
}
});
return result;
}

/*****************************************************
Get view collection
*****************************************************/
function customGetViewCollection(listGuid){
xmlStr = "<GetViewCollection xmlns=’http://schemas.microsoft.com/sharepoint/soap/’><listName>"+listGuid+"</listName></GetViewCollection>";
var result = {success:false, errorCode:”, errorText:’internal error’, views:[]};
wrapSoapRequest(L_Menu_BaseUrl + ‘/_vti_bin/views.asmx’, ‘http://schemas.microsoft.com/sharepoint/soap/GetViewCollection’, xmlStr, function(data){
if ($(‘ErrorText’, data).length > 0) {
result.success = false;
} else {
result.success = true;
$(‘View’, data).each(function(i){
if($(this).attr(‘Hidden’)!=’TRUE’){
result.views.push($(this));
}
});
}
});
return result;
}

/*****************************************************
Wrap webservice call
*****************************************************/
function wrapSoapRequest(webserviceUrl,requestHeader,soapBody,successFunc){
var xmlWrap = [];
xmlWrap.push("<?xml version=’1.0′ encoding=’utf-8′?>");
xmlWrap.push("<soap:Envelope xmlns:xsi=’http://www.w3.org/2001/XMLSchema-instance’ xmlns:xsd=’http://www.w3.org/2001/XMLSchema’ xmlns:soap=’http://schemas.xmlsoap.org/soap/envelope/’>");
xmlWrap.push("<soap:Body>");
xmlWrap.push(soapBody);
xmlWrap.push("</soap:Body>");
xmlWrap.push("</soap:Envelope>");
xmlWrap = xmlWrap.join(”);
$.ajax({
async:false,
type:"POST",
url:webserviceUrl,
contentType:"text/xml; charset=utf-8",
processData:false,
data:xmlWrap,
dataType:"xml",
beforeSend:function(xhr){
xhr.setRequestHeader(‘SOAPAction’,requestHeader);
},
success:successFunc,
error:function(xhr){
alert(xhr.statusText);
}
});
}

/*****************************************************
Add or remove chart config option
*****************************************************/
function addOrRemove(obj,add){
if(add){
var strArr = [];
strArr.push("<tr>");
strArr.push("<td style=’padding:0 5 0 5′>Option name</td>");
strArr.push("<td style=’padding:0 5 0 5′><div class=’newChartConfigDiv’><input title=’Option name’ style=’width:100px’ id=’NewOptionName’ type=’Text’ />");
strArr.push("<label style=’padding:0 5 0 5′>Value</label><input title=’Option value’ style=’width:100px’ id=’NewOptionVal’ type=’Text’ />");
strArr.push("<a title=’Remove current option’ style=’padding-left:5px’ href=’javascript:’ onclick=’addOrRemove($(this),false)’>X</a></div>");
strArr.push("</td></tr>");
str = strArr.join(”);
obj.parents(‘tr:first’).before(str);
}else{
obj.parents(‘tr:first’).remove();
}
}

/*****************************************************
Draw chart
*****************************************************/
chart = ”;
function drawChart(config,numFieldsArrFromConfig,options) {
var data = new google.visualization.DataTable();
// String
data.addColumn(‘string’,config.labelCol);
// Number
if(config.chartType==’OrgChart’){
var coltype = ‘string’;
}else{
var coltype = ‘number’;
}
$.each(numFieldsArrFromConfig,function(i,colObj){
var actionFriendlyName = ”;
if(colObj.prefix==’true’){
switch(colObj.action){
case ‘Sum’:
var actionFriendlyName = ‘Sum of ‘;
break;
case ‘Count’:
var actionFriendlyName = ‘Count of ‘;
break;
case ‘Average’:
var actionFriendlyName = ‘Average of ‘;
break;
default:
var actionFriendlyName = ”;
}
}
data.addColumn(coltype,actionFriendlyName+colObj.label);
});

wsBaseUrl = L_Menu_BaseUrl + ‘/_vti_bin/’;
viewFields = [config.labelCol];
$.each(numFieldsArrFromConfig,function(i,colObj){
viewFields.push(colObj.fin);
});
if(config.useCustomCAML){
var customCAML = config.customCAML;
if(config.useUserProfileProperty){
userInfoObj = getUserInfo();
var filterValue = userInfoObj[config.userProfileProperty];
if(config.regexProfileProperty!=” && filterValue!=”){
try
{
var regExpMatch = filterValue.match(config.regexProfileProperty)[1];
}
catch(err)
{
window.status="SharePoint Javascripts – Error in RegExp match in chart with ID ""+config.myChartId+"". The error was: "+err.description;
}
if(regExpMatch!=undefined){
filterValue=regExpMatch;
}
}
if(filterValue==”){
$("#"+config.myChartId).after("<div style=’width:"+options.width+";padding-left:5px;font-size:8px’>The selected user profile property ("+config.userProfileProperty+") is empty!</div>");
}else{
$("#"+config.myChartId).after("<div style=’width:"+options.width+";padding-left:5px;font-size:8px’>Filtered by user profile property "+config.userProfileProperty+"="+filterValue+"</div>");
}
customCAML = customCAML.replace(/{}/,filterValue);
}
res = queryItems(config.listGuid,customCAML,viewFields);
if(res.count==-1){
alert("An error occured in the "customCAML" for the chart: "+config.myChartId+".n"+
"Check list guid and CAML query:nn"+
"ListGuid:n"+config.listGuid+"nn"+
"CAML-query:n"+config.customCAML);
}
}else{
res = queryItemsByViewName(config.listGuid,config.viewGuid,viewFields);
}
dataObj = {};
var rowCount = 0;
$.each(res.items,function(i,item){
labelColVal = (item[config.labelCol]!=null)?item[config.labelCol]:”;
// Strip off any prefix
if(labelColVal!=null&&labelColVal.indexOf(‘;#’)>-1){
labelColVal = labelColVal.substring(labelColVal.indexOf(‘;#’)+2);
}
if(dataObj[labelColVal]==undefined){
dataObj[labelColVal]={};
rowCount ++;
}
if(config.chartType==’OrgChart’){
$.each(numFieldsArrFromConfig,function(idx,obj){
var thisVal = item[obj.fin];
// If the source is a calculated column
if(thisVal!=null&&thisVal.indexOf(‘;#’)>-1){
thisVal = thisVal.match(/([d.]+$)/)[0];
}
// Build object
if(dataObj[labelColVal][obj.fin]==undefined){
var val = (thisVal!=null)?thisVal:null;
dataObj[labelColVal][obj.fin]=val;
}
});
}else{
$.each(numFieldsArrFromConfig,function(idx,obj){
var thisVal = item[obj.fin];
// If the source is a calculated column – find first number
if(thisVal!=null&&thisVal.indexOf(‘;#’)>-1){
thisVal = thisVal.match(/[0-9.-]+/).toString();
}
// Build object
if(dataObj[labelColVal][obj.fin+obj.action]==undefined){
if(obj.action==’Sum’){
var val = (thisVal!=null)?parseFloat(thisVal.match(/[0-9.-]+/).toString()):null;
if(isNaN(val))val=null;
}else if(obj.action==’Count’){
if(obj.fieldType==’Boolean’){
var val = (thisVal==1)?1:0;
}else{
var val = (thisVal!=null)?1:0;
}
}else if(obj.action==’Average’){
var val = (thisVal!=null)?parseFloat(thisVal.match(/[0-9.-]+/).toString()):null;
if(isNaN(val))val=null;
}
dataObj[labelColVal][obj.fin+obj.action]={‘value’:val,’action’:obj.action,’count’:1};
}else{
if(obj.action==’Sum’){
var val = (thisVal!=null)?parseFloat(thisVal.match(/[0-9.-]+/).toString()):null;
if(isNaN(val))val=null;
}else if(obj.action==’Count’){
if(obj.fieldType==’Boolean’){
var val = (thisVal==1)?1:0;
}else{
var val = (thisVal!=null)?1:0;
}
}else if(obj.action==’Average’){
var val = (thisVal!=null)?parseFloat(thisVal.match(/[0-9.-]+/).toString()):null;
if(isNaN(val))val=null;
}
dataObj[labelColVal][obj.fin+obj.action]['value']+=val;
dataObj[labelColVal][obj.fin+obj.action]['count']+=1;
}
});
}
});

data.addRows(rowCount);
rowIndex=0;
if(config.chartType==’OrgChart’){
$.each(dataObj,function(propName,obj){
var descr = null;
// If the name is added with a comma and a description – like "Bruce Springsteen,Bruce Springsteen<br /><div style="text-align:center"><font color="red"><em>The Boss</em></font></div>"
var split = propName.split(‘,’);
if(split.length>1){
propName=split[0];
descr=split[1];
}
data.setCell(rowIndex,0,propName,descr);
colIndex=1;
$.each(obj,function(idx,objVal){
data.setCell(rowIndex,colIndex,objVal);
colIndex++
});
rowIndex++
});
}else{
$.each(dataObj,function(propName,obj){
data.setValue(rowIndex,0,propName);
colIndex=1;
$.each(obj,function(idx,objVal){
if(objVal.action==’Average’){
val = objVal.value/objVal.count;
}else{
val = objVal.value;
}
var roundedVal = Math.round(val*100)/100;
data.setValue(rowIndex,colIndex,roundedVal);
colIndex++
});
rowIndex++
});
}
// Draw chart
chart = new google.visualization[config.chartType](document.getElementById(config.myChartId));
chart.draw(data,options);
// Add mouse over
google.visualization.events.addListener(chart,’onmouseover’,chartMouseOver);
google.visualization.events.addListener(chart,’onmouseout’,chartMouseOut);
}

function chartMouseOver(e){
this.setSelection([e]);
}

function chartMouseOut(e){
this.setSelection([{'row': null, 'column': null}]);
}

/*****************************************************
Web service calls
*****************************************************/
function queryItemsByViewName(listName, viewName, viewFields, pagingInfo){
var content = buildQueryContentByViewName(listName, viewName, viewFields, pagingInfo);
var result = {count:-1, nextPagingInfo:”, items:new Array()};
innerPost(wsBaseUrl + ‘lists.asmx’, ‘http://schemas.microsoft.com/sharepoint/soap/GetListItems’, content, function(data){
result.count = $(‘rs\:data’, data).attr(‘ItemCount’);
result.nextPagingInfo = $(‘rs\:data’, data).attr(‘ListItemCollectionPositionNext’);
$(‘z\:row’, data).each(function(idx, itemData){
result.items.push(generateItem(itemData, viewFields));
});
});
return result;
}

function buildQueryContentByViewName(listName, viewName, viewFields, pagingInfo){
var result = new StringBuffer();
result.append(‘<GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">’);
result.append(‘<listName>’ + listName + ‘</listName>’);
result.append(‘<viewName>’ + viewName + ‘</viewName>’);
if(viewFields != null && viewFields.length > 0){
result.append(‘<viewFields><ViewFields xmlns="">’);
$.each(viewFields, function(idx, field){
result.append(‘<FieldRef Name="’ + field + ‘"/>’);
});
result.append(‘</ViewFields></viewFields>’);
}
result.append(‘<queryOptions><QueryOptions xmlns=""><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>’);
if(pagingInfo != undefined && pagingInfo != null && pagingInfo != ”)
result.append(‘<Paging ListItemCollectionPositionNext="’ + pagingInfo.replace(/&/g, ‘&amp;’) + ‘" />’);
result.append(‘</QueryOptions></queryOptions>’);
result.append(‘</GetListItems>’);
return result.toString();
}

/*****************************************************
Access user infor for filtering chart data
*****************************************************/
function getUserInfo(UserId){
wsBaseUrl = userListBaseUrl + ‘/_vti_bin/’;
var uiObj = {};

if(typeof(UserId)=="undefined" || UserId==”)UserId = _spUserId;

var arrOfFields = ['ID', 'Name', 'Title', 'EMail', 'Department', 'JobTitle', 'Notes', 'Picture',
'IsSiteAdmin', 'Created', 'Author', 'Modified', 'Editor', 'SipAddress', 'Deleted'];

var item = getItemById(userListGuid,UserId,arrOfFields);
if(item != null){
for(i=0;i<arrOfFields.length;i++){
if(item[arrOfFields[i]]!=null){
uiObj[arrOfFields[i]] = item[arrOfFields[i]];
}else{
uiObj[arrOfFields[i]] = ”;
}
}
return uiObj;
}else{
for(i=0;i<arrOfFields.length;i++){
uiObj[arrOfFields[i]] = "User with id " + UserId + " not found.";
}
return uiObj;
}
}
[/javascript]
Hover over the code, select “view source”, highlight and copy. Save as “ChartUsingGoogleVisualizationAPI.js”, mind the file extension, and upload to the scriptlibrary as shown above.

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:
IMG
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”.


Regards
Alexander

112 thoughts on “Interactive Charts using Google Visualization API”

  1. Wow! Absolutely stunning! I know I will love the result, can’t wait to see your script.

    Chris

  2. I created one chart and it worked ok. When I went to create another chart on another page, I got the following error:

    Update chart config error;
    The list that is referenced here no longer exists.

    Any suggestions as to how to troubleshoot it?

    Good work on your part, this can be very handy.

    1. Hi,
      The chart configuration list must reside in the same site or subsite as the chart is to be used. You will need to crate a copy of the chart config list in each site/subsite.

      Alexander

  3. Hi Alexander:
    This is another stellar piece of work!

    One note: I tried to use currency and it does not work. I changed it to numbers and it works. Any clue or fix?

    Thanks again!

    Charlie Epes

    1. Added support for Currency – i actually thought it would sort under “Number”, but no… Fixed.

      Alexander

  4. Another question Alexander:
    Each time I edit the CEWP/Chart, the “Chart label column” field returns to the top-most selection.

    Is there any way to keep the prior selection?

    Thanks-

    Charlie Epes

    1. Thanks!

      Again, this is amazing work Alexander. Celebrate your new celebrity and go out for dinner tonight!

      Charlie Epes

  5. 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.

    1. Hi,
      It should be enough to include the <div> in a CEWP in the page, and call the script once at the bottom of the page, with the reference to both/all “container id’s”.

      Alexander

    2. I have the script running in a webpart zone with a CEWP without <div Id reference.

      I have added 2 charts, each added to its own web part zone. On each one i reference their id by adding and on the other .

      The only one that appears is the “xxx”. I looked at the script:

      // Each chart must be represented by a container with a unique id. This container must be present in the page
      arrOfChartContainers = ['Test'];

      So test is the only one showing. As you can tell, I am not much of a programmer – which is a testament to your instructions – well done.

      Any help would be great.

    3. My last reply was not edited correctly.

      I have created 2 ids – Test and DemoChart1

      3 web part zones – One holding script and the other 2 holding CEWP’s referencing a ID.

      Webpart zone 1 – CEWP -” ”
      Webpart zone 2 – CEWP – “”

      Only configuration UI showing is Test. How do i get DemoChart1 config UI to show also…and others would follow suite.

    1. The article is updated with an example. Please let me know of any trouble you might encounter.

      This goes to everybody! – please test the solution and post back any issues, either it is “success stories”, bug reports or request for changes/modifications/enhancements.

      Alexander

    2. Thx Alexander. Only reason i could not test your recommendations is i was not clear on what to do. Appreciate the help.

  6. This works great! The only issue that I have (and is probably due to how SharePoint works) is in the “Chart label column”. It does not show a column that is based on the Lookup option.

    Or is there a way to select data from multiple lists?

    1. Hi,
      I will include single item lookup columns as “Chart label column”. The code will be updated within a few hours, but if you are impatient you can change the code in line 699-710 with this:

      			var arrOfTypesToIncludeInStr = ['Number','Text','Calculated','User','DateTime','Boolean','Choice','Lookup'];
      			var arrOfTypesToIncludeInNum = ['Number','Currency','Text','Calculated','Boolean','Choice'];
      			$('Field', data).each(function(){
      				if($(this).attr('DisplayName')!=undefined){	
      					if($.inArray($(this).attr('Type'),arrOfTypesToIncludeInStr)>-1){
      						// Include user created single item lookup
      						if($(this).attr('Type')=='Lookup' && $(this).attr('FromBaseType')=='TRUE')return;
      						result.labelFields.push($(this).attr('Name')+"|"+$(this).attr('DisplayName'));
      						}
      					if($.inArray($(this).attr('Type'),arrOfTypesToIncludeInNum)>-1){
      						result.numFields.push($(this).attr('Name')+"|"+$(this).attr('DisplayName')+"|"+$(this).attr('Type'));
      					}
      				}
      			});
      
      

      Alexander

  7. Hi Alexander,

    I tested the code, charts can be displayed well in firefox but not in IE 8, is there any configuration to do?
    Thanks

  8. Alexander,

    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.

    Once again, great work with this piece….

    Thanks

    1. Hi,
      That is not possible for now, and i so not now of any other method than to create calculated columns to “split” the values into two columns, and then to chart using the calculated columns.

      Alexander

  9. 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.

    Thanks

    1. Hi,
      Try this out (change the “MyDate” to match your FieldInternalName):

         <Where>
            <And>
               <Geq>
                  <FieldRef Name="MyDate" />
                  <Value Type="DateTime">
                    <Today OffsetDays="-15" />
                  </Value>
               </Geq>
               <Leq>
                  <FieldRef Name="MyDate" />
                  <Value Type="DateTime">
                  <Today OffsetDays="15" />
                  </Value>
               </Leq>
            </And>
         </Where>
      

      Alexander

  10. excellent! can this extend to display Site Usage Reports? If yes, what all do I need to do? anyone done it before?

    thanks!

    1. Hi,
      As far as i know this is not possible as the site usage report is not stored in a list.

      Alexander

  11. The code for the file “ChartUsingGoogleVisualizationAPI.js” is updated. The array of fields to use for chart label and value columns is modified.

    The two now uses the same array: ‘Number’, ‘Currency’, ‘Text’, ‘Calculated’, ‘Boolean’, ’User’, ’DateTime’, ’Choice’, ’Lookup’.

    The version number is now 1.0.

    Alexander

  12. Alexandar, thanks for the prompt reply. If I’m implementing the “ChartUsingGoogleVisualizationAPI.js” is any of my SharePoint List data (corporate data) being sent to Google, in any form or factor?

    Off topic, but if you happen to know, could you guide me in a direction you are aware of a free sharepoint charting software which would allow me to do some graphical site usage report?

    thanks.

    1. Hi,
      This solution does NOT send any data out of the house. As specified in the article:

      All code and data are processed and rendered in the browser. No data is sent to any server.

      I have no answer to your question, sorry.

      Alexander

  13. Hi Alex,

    I can’t get it to work, IE always reports a script error “Object expected” which refers to this part of the rendered page.

    <td id="MSOZoneCell_WebPartWPQ2" orientation="Vertical" name="MSOZoneCell" relatedWebPart="WebPartWPQ2" ondragenter="MSOLayout_MoveWebPartDragEnter(this)" ondragend="MSOLayout_MoveWebPart(this, MSOLayout_oDropLocation);" ondragover="MSOLayout_MoveWebPartDragOver(this, 'True');" vAlign="top"><table TOPLEVEL class="ms-WPSelected" border="0" cellpadding="0" cellspacing="0" width="100%">
    					<tr>
    						<td><table border="0" cellpadding="0" cellspacing="0" width="100%">
    							<tr class="ms-WPHeader">
    								<td onmousedown="MSOLayout_MoveWebPartStart(MSOZoneCell_WebPartWPQ2, 'Content Editor Web Part u202D[1]u202C', false)" relatedWebPart="WebPartWPQ2" title="Content Editor Web Part [1] - Use for formatted text, tables, and images." id="WebPartTitleWPQ2" style="cursor:move;width:100%;"><h3 class="ms-standardheader ms-WPTitle"><nobr><span>Content Editor Web Part ?[1]?</span><span id="WebPartCaptionWPQ2"></span></nobr></h3></td><td align="right" style="padding-right:2px"><span style="display:none;"><menu id="MSOMenu_ConnectionsWebPartWPQ2" class="ms-SrvMenuUI">
    									<ie:menuitem disabled="true" title="The Web Part you are connecting from does not allow authoring of connections." id="MSOMenu_Connections" type="option">
    										Connections
    									</ie:menuitem>
    								</menu></span><div style="cursor: pointer" class="ms-HoverCellInActive" onmouseout="this.className='ms-HoverCellInActive'" onmouseover="this.className='ms-HoverCellActiveDark'"><nobr><img src="/_layouts/images/blank.gif" alt="" style="padding-left:2px;"/><a onclick="MSOWebPartPage_OpenMenu(MSOMenu_WebPartMenu, this, WebPartWPQ2,'True');return false;" id="WebPartWPQ2_MenuLink" onkeydown="MSOMenu_KeyboardClick(WebPartWPQ2_MenuLink, 13, 40)" href="#"><span class="ms-WPEditText">edit</span><img src="/_layouts/images/db/menu1.gif" border="0" align="absmiddle" title="Content Editor Web Part [1] Web Part Menu" alt="Content Editor Web Part [1] Web Part Menu" style="padding-left:2px;"/></a></nobr></div></td><td valign="middle" style="width:10px;padding-right:2px;"><a title="Close Content Editor Web Part [1]" href="#" onclick="javascript:MSOLayout_RemoveWebPart(document.all['WebPartWPQ2'])" id="WebPartWPQ2_Close"><img class="ms-HoverCellInActive" onmouseout="this.className='ms-HoverCellInActive'" onmouseover="this.className='ms-HoverCellActiveDark'" align="absmiddle" border="0" src="/_layouts/images/ptclose.gif" alt="Close Content Editor Web Part [1]"/></a></td>
    							</tr>
    						</table></td>
    					</tr><tr>
    

    I’ve got only two webparts on the page, on that should display the chart and the other one is your script. The configuration list is addressed, also is the user list.

    1. Hi,
      “Object expected” indicates a syntax error in the script. Please try to save the script again:

      Hover over the code, select “view source”, highlight and copy. Save as “ChartUsingGoogleVisualizationAPI.js”, mind the file extension, and upload to the scriptlibrary as shown above.

      If that does not help, look at this article: How to troubleshoot when the scripts does not work

      Alexander

    2. Hi,

      I had a look at Endusersharepoint, one of the comments had the info that was needed. I had to give absolute references to the scripts, now everything’s working.

      It seems that ‘the other” scripts (interaction.js and stringBuffer.js) won’t be found by sharepoint if not addressed with absolute path.

      Thanks!

  14. 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.

    Can you think of a reason for this behaviour?

    1. 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:

      =TRUNC(((Created-DATE(YEAR(Created),1,0))+6)/7)&" "
      

      Note the &” ” at the end.

      Alexander

    2. Hi,

      thanks, that did the trick. Although I’m feeling a bit depressed because of your simple calendar week formular….mine is looking a bit more complicated

      =INT(((Created)-DATE(YEAR((Created)-WEEKDAY((Created)-1)+4);1;3)+WEEKDAY(DATE(YEAR((Created)-WEEKDAY((Created)-1)+4);1;3))+5)/7)&" "
      

      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.

      Chris

    3. Hi,
      The tooltip format is default for Google Visualization API, i cannot change it. There may be some options to format it in the “options” section over at Google’s site (linked in the chart config).

      Alexander

    1. 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…

      Alexander

  15. Hi Alex,

    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.

    1. 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.

      Alexander

    2. I’ve had this error too, only thing that helped me was to start from scratch with the chart, most of the times it has worked then.

  16. 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 %?

    Thanks again and great job.

    1. 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.

      To add labels to the pie-chart you must edit the code – look at this comment for instructions

      Alexander

  17. Each of those fixes worked great. I have a new issue/question to ask though. Will do that with new item here. Thanks for your help.

  18. 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.

  19. 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..

    1. Hi,
      This solution does work in IE6.

      If the “chartConfigListGuid” is wrong, you would get this behavior.

      Verify that it is correct.

      Alexander

  20. 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

  21. Another question – and I want to apologize for the many requests :-)

    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.

    Br
    Wyl

  22. Hi,

    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.

    I can’t seem to find what is wrong. Any ideas?

    1. Hi,
      Check the list GUID for the config list. If this is correct it has to be one of the fields in the cofig list that has the wrong FieldInternalName.

      Alexander

  23. Hi Alex,

    I checked them all multiple times but coudl find a problem. I deleted the config list library and made a new one and it seem to sort it. Thanks.

    Now while i have you. Is there anyway i can pull the data from a form library?

    1. I’m not familiar with the form library, if the date is stored in regular SharePoint columns, I reckon it would work, but if the data is “in the InfoPath-file”, it would not work

      Alexander

    2. Hi Alex,

      The data is in the columns but the problem is that any form library’s do not come up in the Select List option. So I can’t get at the data.

    3. Try to change the array in line 734. I’m not sure what type the forms library is, but it is probably one of the types i have excluded…

      Alexander

    4. BINGO!

      Yeah XML Form Library is 115. I taught I needed to change the getListCollection to the getFormCollection. But removed 115 and it worked.

      Thanks!

  24. 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?

  25. 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:

    Line: 920
    Char: 6
    Error: ‘match(…)’ is null or not an object
    Code: 0

    Any idea or suggestion?

  26. No, I was using COUNT. I have changed no the result of the calculation to a value (1) and made the sum.. this works -> chart is beeing displayed.

  27. Thank you for sharing your knowledge.

    Some of the options will not work. For example, hAxis.title. It will not display.

    Is it possible to display data labels? I didn’t see that on the google chart config options page.

    Thanks again

    1. Hi,
      The original code uses the “packages”:
      “columnchart”,”barchart”,”areachart”,”linechart”,”piechart”.

      This is a deprecated version from Google, and you would have to add “_old” to the URL to the “Visualization” -like this: http://code.google.com/intl/en-EN/apis/visualization/documentation/gallery/areachart_old.html

      In a comment i have pointed to the “corechart” package, but the current code does not support all the options in this new package. I’m working on a updated version, but cannot promise a release date…

      Alexander

  28. Hi Scott,

    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.

  29. 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?

    1. 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

    2. 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.

      Alexander

    3. 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

  30. First of, great site Alex.

    Question : is there a way to show the date only, but not the time in a chart ? The datatype for my chart column is “date and Time”, and I select Date and Time format as “Date Only”.

    But in the actual chart, each column still displays the time portion as 00:00:00, wasting a lot of unnecessary space.

    Thanks.

    1. Hi,
      The date is stored in that format in the DB. The “Date only” is just used when rendering in the browser. You will have to use a calculated column to get the date only.

      Alexander

  31. Another question Alex, how do I insert the actual data value marker on any of the charts? Let’s use a bar chart for example, how do I display the following ?

    10 15
    __
    __ | |
    | | | |
    | | | |
    | | | |
    | | | |

    1. Hi Mike,

      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.

  32. 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.

    Thanks

    1. I got it working…needed to use the full path (including http) in the script file locations.

      Awesome scripts Alex!!!

  33. i cant get it to work. I cant see the webpart in edit mode. it just comes up blank.

    I have the all the files in a directory, also added the GUIDS etc.

    1. Do you get any error message in the status line? It can be tricky the first time – only a missing ‘ or something can break the whole script.

  34. Alexander,

    I have one set of charts/graphs that work perfectly.

    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?

    Thanks!

    1. Did you check the View-ID? And all the different columns you used in the first list the same as in the second?

    2. Alex/Wylaryzel,

      I figured it out :)

      I only needed to specify the correct List Guid and use unique container names….

      Thanks!

  35. Anyone using this code in conjunction with the “OrgChart” chart type?

    I’m testing it… So far I can create a simple Org Chart, no problem.

    However there are 2 issues that I’m struggling to resolve.

    1) How to handle the case of a manager that manages 2 different departments?

    I tried inputting the same managers name twice, specifying a different department for each and grouping my SP List by department…

    The SP List looks good, but the Google API combined the contents of the two departments when it created the OrgChart… As a result, the two unrelated departments appear as one.

    So far, my only work around is to list the managers name twice, with each name having some additional text added to the end.

    i.e. “John Doe 1” and “John Doe 2”

    It’s ugly, but it works.

    If anyone can recommend a more elegant way of handling the case of a manager whose name appears more than once in the Org Chart, I’d love to hear your solution.

    2) I’d like for more than just the employee name to be listed… I’d like to list the name, department and job title, etc….

    If possible, I’d also like the ability to add some popup text causing some additional details to be revealed as my mouse cursor hovers over different names.

    So far, I’ve been unsuccessful at adding additional rows of text beneath the employees name.

    I’ve been successful at adding pop-up text to Org Charts in the past, but never before when using this code.

    If anyone has a suggestion for accomplishing either of these tasks, please let me know.

    Thanks!

    1. I read the Google Org Chart does not support listing a managers name twice. I also read it’s currently not possible to change the Org Charts orientation from horizonal to vertical.

      I set “allowHtml” to “true”, but I’m still trying to figure out whether it’s possible to get the “Notes” listed in my SP list to appear as an HTML pop-up…

      And I’m still trying to figure out how to make the SP columns labeled “Department” and “Job Title” to appear beneath each name shown on the Org Chart.

      If anyone has any suggestions, please let me know :)

  36. Hi Alexander,

    I’m using these charts and its awesome.

    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.

    Is there any way to see YES and NO count on different column.??

    Thanks.

  37. Very nice.

    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.

  38. 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?

  39. With regards to the OrgChart, any suggestions on how to display something other than just the name? In my case, I’d like to display the name, department name and job title.

  40. Hi all,
    I have an new version nearly finished… It will add many new chart types, including motion chart.

    I hope to get it published within the week – just have to stop adding new features…

    Alexander

  41. 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.

    Any ideas?
    Kind regards!

  42. Pingback: Website
  43. Hi

    I have a doubt. How can I get the “userListGuid”?

    I think this is last thing for test in my sharepoint site, because the Item on googleconfiguration list was not created and the CEWP was empty, blank.

    1. Hi,
      Are you using the latest version of the script? There should be no need for specifying the userListGuid.

      Alexander

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>