Category Archives: Charts

Charting for SharePoint using Google Visualization API: Update 16.08.2011

26.08.2011 v2.9.3.2 has been released with these changes:
Small fix to allow single quotes in chart options. This is necessary to handle “fontName” in IE – it has to be wrapped in double AND single quotes like this:
[javascript]
{color:"red",fontName:"’Arial’",fontSize:14}
[/javascript]


24.08.2011 v2.9.3.1 has been released with these changes:

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

Managed path

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

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

Debug
Add this to the CEWP code:
[javascript]
var chartDebug = true;
[/javascript]
When you edit the chart, a debug panel will be added to the top of the screen. This might come in handy if you need my help to debug the setup.

Filter example
The script is setup with a “listener” that lets you add a function like this to your CEWP code:
[javascript]
function chartSelectionHandler(data,chart,chartID){
var selection, item, x, y, z;
selection = chart.getSelection();
if(selection.length>0){
item = selection[0];
if(item.row!==undefined){
x = data.getFormattedValue(item.row, 0);
if(item.column!==undefined){
z = data.getFormattedValue(item.row, item.column)
y = data.getColumnLabel(item.column);
}
}
if(x===undefined && y===undefined && z===undefined){
return;
}
// Create your filter
alert("These are the variables you can use for your filter:nnx = "+x+"ny = "+y+"nz = "+z);
if(confirm("Open filtered list?")){
if(chartID===’MyChart1′){
window.open(‘/test/English/Charts/Lists/ByStatus/AllItems.aspx?FilterField1=Status&FilterValue1=’+x,”,’width=1000′);
}else if(chartID===’MyChart2’){
window.open(‘/test/English/Charts/Lists/Coffee2/AllItems.aspx?FilterField1=’+y+’&FilterValue1=’+z,”,’width=1000’);
}
}
}
}
[/javascript]
This is meant as an example for those who want to experiment with this feature.



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

These are the changes in this release


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

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

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

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

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


Other articles in this series:


Download

You find the code here

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

Regards
Alexander

Interactive Charts using Google Visualization API: Examples

This post describes the use of the tool posted here. The latest changes are found here.


This post will be updated with examples on how to utilize this tool to build various charts. I will add examples upon request. If you have a smashing chart or a special trick, please send it to me and i will post the images here.


Combo chart

The chart:
IMG

The list:
IMG

The configuration:
IMG

The reason for using “chartArea.left:50” is to align the chart left so that the legend is not truncated.

NOTE: The “Column index” used when setting up the series is one notch down from the index shown to the left of the columns. This has to do with the series only applying to the numeric columns.


How to put multiple charts in one page

Here is a few screenshots showing how to put multiple charts in one page.
IMG

IMG

IMG

You must ensure that the CEWP holding the code is placed below all containers.


Motion chart

The chart:
IMG

The list:
IMG

The configuration:
IMG


URL filter

This filter is applied in the “Custom CAML” section like this:
The configuration:
IMG
Use the FieldInternalName of your field – my field is named “Status”.

The URL:
IMG

The chart:
IMG

Manual filter setup filter

This is used to filter on “Single line of text” columns. This filter is applied in the “Custom CAML” section like this:
The configuration:
IMG
To get the CAML, select the checkbox “Create a filter field above the chart using”, select “manual filter setup” and then click “Build CAML from selected filters”. You need to know the FieldInternalName of the field and insert it in the placeholder in the CAML.

The list:
IMG

The chart:
IMG

Grouped by choice column value

I got a request from Michael:

Hello,
Im trying to chart a Calendar, i created a Column called “Category” (its a drop-down box with the categories)
Now i want to create a chart that:
– is a bar chart
– the h axis is “created by”
– the v axis is a the sum of a column that calculate the hours between end and starttime of events (allready solved this with a formular)
– the Bars are grouped by the Creator
– the chart is stacked (allready done)
– the stacked bars show the name of the category
– for each category a specified colour

Here are some screenshots of the list fields

The “Time” field calculates the difference between start and end:
IMG

The Category field is a plain choice field:
IMG

The series in the chart are made up by one calculated column per choice in the “Category” field:
IMG
Make one for each category.

The configuration:
IMG

The chart:
IMG


Please make requests, and i will post examples to the best of my knowledge.

Alexander

Charting for SharePoint using Google Visualization API: Update 05.06.2011


I have posted a new version of the “Charting for SharePoint using Google Visualization API” – v2.9.0. The changes are described here, but the background story and the code is found in original article. This post describes the new features in v2.9.0.

You should read the original post found here, the update for v2.8 posted here and the update for v2.8.5 posted here.


Changes from v2.8.5:

  1. Changed the behavior of “Average” to divide the grand total on the number of columns actually containing a value. This means any columns being blank or “null” will not be considered into the “total column count”. Thanks to Ayodele Ayodeji for pointing this out to me.
  2. Changed the “Chart option parser” to use eval on the Chart Options. YES i know it’s not the smartest thing to do, but as the Google Visualization API changes, my crude “option parser” failed to swallow some of the object literation formatted options.
  3. Added new chart types: Combo Chart and Candlestick Chart
  4. Added support for PatternFormat
  5. Pulled the variables: “showFilterInfo”, “showItemCount”, “itemCountPrefix”, “itemCountSuffix”, “labelIfEmpty”, introduced in v.2.8.5 in from the CEWP code and into the Chart configuration. This update should override the CEWP settings and these obsolete settings should not interfere.
  6. Added a few minor GUI tweaks like auto open the configuration for a newly added chart – no more “Table has no columns” error. Changed some of the error message. And a few other small fixes.
  7. Changed the solution to check for any missing columns in the configuration list and add them as needed. No more need to manually add columns to the configuration list when updating from a previous version!
  8. I have changed the code for the “save configuration function” as jQuery v1.6 introduced a new approach on DOM attributes and properties which affected the value returned by .attr(“checked”). This made saving “checked” checkboxes impossible when using jQuery v1.6.x.

NOTE: I have done one change in the way the formatters are targeted to a column. I originally went for a numbering of the columns starting with 1, but changed this to use 0 for the first column. This has to do with the columns being 0-indexed in the DataTable “behind the scenes” and this making things easier. To those of you using formatters now, you might end up with an error message, but it is just a matter of notching the “index” down by one.

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

Charting for SharePoint using Google Visualization API: Update 05.05.2011


I have posted a new version of the “Charting for SharePoint using Google Visualization API” – v2.8.5. The changes are described here, but the background story and the code is found in original article. This post describes the new features and changes from v2.8.

You should read the original post found here, and the update for v2.8 posted here.


Changes from v2.8:

  1. Removed option to “listen to” specific list filter columns using “{list:FieldInternalName}”
  2. Added option to “consume” a list view web part filter (read from the URL). You can now have the chart “connected” to a list view. Further details below. This new feature requires existing users to manually add one column to the configuration list: “ConsumeFilterFromListView”.
  3. Fixed a bug regarding the use of “&” in a custom filter field which broke the CAML
  4. Added a “counter” to display the total number of items below the chart
  5. Added a few “options” to be set in the CEWP code: “showFilterInfo”, “showItemCount”, “itemCountPrefix”, “itemCountSuffix”, “labelIfEmpty”. All these have a default value and are optional.
  6. Added option to pull the CAML from an existing list view to use as “Custom CAML”

Details on item number 2:

In v2.8 i introduced an option to “hook into” the list view veb part filter for specific columns. In this release this is removed and replaced with an option to “connect to” a list view web part and consume all filters applied to any column.

This has a few limitations:
It reads the filter values from the URL and therefore only one list view webpart can coexist with the chart. If more than one list view web part is placed alongside a chart, it would not know which list the filter came from.

To use this option, check this box:
IMG

This requires existing users to add one column to the configuration list: “ConsumeFilterFromListView”. For new users this field will be added automatically when creating the configuration list. The chart configuration list should look like this:
IMG

Details on item number 4:

I have added a “counter” to display the total number of items below the chart:
IMG
This counter can be switched off – see next item.

Details on item number 5:

These variables can be added to the CEWP code:

  • showFilterInfo: true [default] or false. Controls the visibility of the “filter label” below the chart when using a filter.
  • showItemCount: true [default] or false. Controls the visibility of the item count below the chart.
  • itemCountPrefix: A string to put in front of the item count. Default empty string.
  • itemCountSuffix: A string to add after the item count. Default ” items”.
  • labelIfEmpty: A string representing the default label for empty values. Default is “(empty)”.

Example CEWP code:
[javascript]
<div id="MyChart1"></div>
<div id="MyChart2"></div>
<script type="text/javascript">
/*****************************************************
Address all containers
*****************************************************/
// All charts must be represented by a container with a unique id. This container must be present in the page
arrOfChartContainers = ["MyChart1","MyChart2"];

var showFilterInfo = true;
var showItemCount = true;
var itemCountPrefix = "A total of ";
var itemCountSuffix = " items";
var labelIfEmpty = "(no value)";

</script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript" src="/test/English/Charts/Javascript/v2.8.5/ChartUsingGoogleVisualizationAPI.js"></script>
[/javascript]

Details on item number 6:

Use this link to pull the CAML from the list view:
IMG
Select a view in the “View selector” and click this link to pull it into the textarea.


Please post a comment if you have questions.

Alexander

Charting for SharePoint using Google Visualization API: Update 27.03.2011

05.05.2011 I have released version 2.8.5. Read about it here


I have posted a new version of the “Charting for SharePoint using Google Visualization API” – v2.8. The changes are described here, but the background story and the code is found in original article. This post describes the new features.


1. Filter the chart using a choice column from the list

IMG
The dropdown is created from the choice options supplied in the list settings for that column. You set the filter in the GUI like this:
IMG
You must use “custom CAML” and select “Create a filter field above the chart using”. In the dropdown you will find all the single-choice columns in your list. Click on the text “Build CAML from selected filters” to create the appropriate CAML query.


2. Manually build the dropdown filter

If you want to make a dropdown filter for your chart, filtering by a text in a single line choice column you can create it like this. Click “Create a filter field above the chart using”, and select “manual filter setup”. You can then build the filter manually. See the description below the “filter options” textarea for formatting options for the dropdown.

When using a manual filter setup and using “Build CAML from selected filters”, you must change the “FieldInternalNameToMatch” with the proper FieldInternalName of your target field in the CAML. In this example, switch the text “FieldInternalNameToMatch” with “Region”.
IMG
IMG


3. Filter the chart using a value from the URL query string

Use {url:ParameterName} as placeholder in the CAML.
IMG
Before the list is filtered, the chart looks like this:
IMG
Pass the filter in the URL like this:
IMG
IMG


4. Filter the chart by filtering the “attached” view

This only applies if the chart is in a page with a single list view. It reads the filter provided by the list filter action and filters the chart accordingly. There are no real connection between the list and the chart – it merely reads the URL and looks for a filter value matching the placeholder in the CAML.

Use {list:FieldInternalName} as placeholder in the CAML.
IMG
Before the list is filtered, the chart looks like this:
IMG
Filter the list by the appropriate column to render the chart.
IMG
IMG


Important

Users upgrading from v2.7 must add three columns to the configuration list:

  • UseCustomQueryFilter: Yes/No column (boolean)
  • CustomQueryFilterField: Single line text
  • CustomQueryFilterChoices: Multi line plain text

For new users, this list is automatically created, but for users upgrading from older versions these fields must be added manually. In that case, compare your configuration list with this image:
IMG


Bugfixes:
If a column name had “&” or “/” in it, the chart “broke”. Thanks to Eric Guy for finding the bug.

I might very well have forgotten something in this walktrough so please post any comment or question below.

Alexander

Poll for SharePoint

March 15, 2015: I have updated this solution. You find v2.0 here.

Please note: The Google Image Charts used in this solution is deprecated and will stop working April 20, 2015.

Change log
January 28. 2014
v1.54 fixes one incompatibility with SP 2013. I have not fully tested this so please let me know if you find any bugs.

July 01. 2013 v1.53: The sort order of answers in chart now reflects the order given in the setup. I have removed some script references so that you now must refer spjs-utility.js – get it here

You must update the CEWP code to include reference to spjs-utility.js

15.12.2011 v1.5.1: Bugfix in the file “PollForSharePoint.js” – line 238. The property “listBaseUrl” were missing – thanks to Jaap Dijkma for finding this bug.


04.12.2011 I have updated the solution to support putting the list in a central location. See “listBaseUrl” below.


22.10.2011 Updated the code to support free input rather than predefined answers – as requested by Claudia:

Hi, thank you for this wonderful piece of code, I think it’s great and we use it on our intranet :).
I wondered if it is possible to use a single line of text for free input instead of radiobuttons with preset answers?
We like to use the poll for ‘brandtagging’ so that people can type keywords.
Just wondering…..
thanks! Claudia

I thought this was a clever idea and have implemented this in v1.4. See updated CEWP code and description below.


17.08.2011 Updated the code with these changes:

  • Lets you view the results and come back to answer.
  • Tidied up the code.
  • The charts now supports https (Thanks to Google).
  • Removed the poll question from the chart title to preserve the formatting.

01.03.2011 Updated the code to fix a bug when “singleResponse” is true. Thanks to “Al”. I have also included two more settings in the “argObj”: qStyle and aStyle. These sets the CSS style of question and answers. See CEWP code below.


28.11.2010 Updated the code to support special characters in the poll question and answers. Thanks to Christophe for the tip.


This code lets you generate polls using JavaScript/jQuery only. No need for server side installed WebParts.

Poll
IMG

Result with column chart
IMG

Result with bar chart
IMG

Result with pie chart
IMG

Result with “table chart”
IMG

Already answered
IMG

Not active
IMG

Finished
IMG

The charts are generated using Google Chart Tools / Image Charts (aka Chart API).


Create a custom list with the following fields

  • Answer: Single line of text
  • Question: Single line of text

Name it anything you like, but keep the display name fairly simple (no special characters) as you will use the display name in the CEWP code.

CEWP code

The CEWP code below refers jQuery from Google. If you have a local copy of jQuery you can change the script src. You find the code for the file “PollForSharePoint.js” at the bottom of the page. In this example the file “PollForSharePoint.js” is placed in a document library named “Javascript”.

NOTE: You must change the script src for the file “PollForSharePoint.js” and “spjs-utility.js” to point your instance of the files – the CEWP code will not work unless you do this.

Place this code where you want the poll to appear:

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/Poll/spjs-utility.js"></script>
<script type="text/javascript" src="/test/English/Javascript/Poll/PollForSharePoint.js"></script>
<script type="text/javascript">

buildQueryWP({pollAnswerListName:'PollA',
				listBaseUrl:L_Menu_BaseUrl,				
				id:'myPoll_01-03-2011', // Allowed characters id a-z, 0-9 - and _
				start:'10/22/2011', // format: mm/dd/yyyy
				end:'11/10/2011', // format: mm/dd/yyyy
				singleResponse:false,
				q:"What is your<br />favorite junk food?",
				qStyle:'font-size:small;font-weight:bold;color:gray',
				aStyle:'font-size:xx-small',
				a:['Pizza','Hot dog','Hamburger','Neither of them'], // Leave empty for free input				
				color:['32CD32','FFCC11','FF3300','C0C0C0'],
				forceLowerCaseAnswer:false, // Group result by lowercase				
				chart:'col', // table, bar, col or pie
				height:100,
				width:450});
</script>

You can control these values in the top of the file “PollForSharePoint.js”:

var submitLinkText = 'Submit';
var backLinkText = 'Back';
var showResultBtnText = 'Show result';
var pollNotActiveText = 'The poll is not active prior to {0}';
var pollEndedText = 'The poll ended on {0}';
var alreadyRespondedText = 'You answered: ';
var totalResponsesLabel = "Total responses: ";

Object attributes explained

  • pollAnswerListName: DisplayName or GUID of the list that stores the answers
  • listBaseUrl: The baseUrl of the site. This is like “/sites/hr” when the list is located in the site “hr” under “/sites”. Use L_Menu_BaseUrl (or omit the property) for current site. New in v1.5
  • id: The unique id of the poll. All poll answers are stored in a list and this id is used to separate each poll
  • start: Start date in the format mm/dd/yyyy
  • end: End date in the format mm/dd/yyyy
  • singleResponse: true for one reply per user, false for unlimited number of replies
  • q: Poll question. To have a linefeed in the question, use <br>
  • qStyle: CSS syntax style
  • aStyle: CSS syntax style
  • a: Answers in an array format. To use free input and not predefined answers, leave the array empty.
  • color: Colors for the chart in an array format. This must have the same length as the previous parameter – one color for each answer
  • forceLowerCaseAnswer: Primarily for use with free input to avoid getting two “series” when the only difference are uppercase characters. New in v1.4
  • chart: “bar” for bar chart, “col” for column chart, “pie” for pie chart or “table” for a plain table.
  • height: Height in pixels
  • width: Width in pixels

Regarding free input
If you leave the attribute “a” as an empty array, the user can supply free text as “answer”. When using free input, the result are automatically presented as a table.

Download code

The code for the file “PollForSharePoint.js”:
Download code from this location

Ask if anything is unclear.

Alexander

Interactive Charts using Google Visualization API v2.0

You find v3 of this tool here


16.08.2011 I have released version 2.9.3. Read about it here


05.06.2011 I have released version 2.9.1. Read about it here


05.05.2011 I have released version 2.8.5. Read about it here


27.03.2011 I have released version 2.8 featuring different filtering methods. Read about it here


*** See bottom of article for change log on older versions ***


Since i posted the previous version of the solution that utilizes the Google Chart Tools / Interactive Charts (aka Visualization API), Google has updated their API and made the previous version more or less obsolete.

NOTE:
I have made a “bridge” between Google’s Visualization API and SharePoint. How the charts render or function it up to Google to decide – it is their “product”. Refer the various configuration options by following the link from the “Edit chart UI”.

Read Google’s terms of Use here.

Google’s Data Policy on this charts
All code and data are processed and rendered in the browser. No data is sent to any server.

This new release features these chart types:

Other modifications made:

  • All code in one file: “ChartUsingGoogleVisualizationAPI.js”
  • In the CEWP: Refer the above file, jQuery (fixed external link) and the Google jsapi (fixed external link)
  • Chart configuration list is automatically created if it is not already present

The solution is tested in IE8, Google Chrome 5.0.375.127 and Firefox 3.6.8.

Some screenshots (from v2.0 – changes introduced in subsequent versions are not reflected her):

Not all chart types are presented.
When first adding a chart CEWP to a site, the configuration list is created:

Press OK:

Gauge – a good KPI:

GeoMap:

Column chart – counting unique values per country:


This chart type merges all “lines” where the country is the same, counting “fruits” in the choice column named “Fruit or vegetable”.

LineChart:

Map:

Motion Chart:



The “date” column can be a SharePoint data-column, or a “US formatted” string (still formatted as “Date in the UI”) in the format “mm/dd/yyyy”. Refer the “Instructions for MotionChart” link in the UI.

Organizational Chart:



This is the data that makes up this chart. Note that it is built with various calculated columns – no need for it to render as HTML in the list – it is “raw data”.

PieChart:

More examples – including custom CAML-queries will be added on request.


The new GUI from v2.6.5 (introduced in v2.5)



The Code:

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

Note the <div id=”MyChart1″></div> This is the chart “placeholder”. To add more charts, add more placeholders and add them to the array “arrOfChartContainers”.

The placeholders can be inserted in other CEWP’s as long as they render before the chart is initiated (place them above the “Chart CEWP”).

It is essential that the scripts tags for “jsapi” and “ChartUsingGoogleVisualizationAPI.js” are placed below the script tag that defines the “arrOfChartContainers”.

The code for the file “ChartUsingGoogleVisualizationAPI.js”:

Download code from this location

This link can contain different “versions”:

  • ChartUsingGoogleVisualizationAPI_vX.Y.Z.js – Uncompressed code
  • ChartUsingGoogleVisualizationAPI_vX.Y.Z-min.js – Minified using YUI Compressor to reduce the file size
  • ChartUsingGoogleVisualizationAPI_vX.Y.Z-packer.js – Compressed/obfuscated using Dean Edward’s Packer to further reduce the file size

You must update the script name in the CEWP to reflect the version number.

Note:

When new versions are released, they will be placed in a folder with the version number as label. Be sure to download the latest version.

If you are using a browser other than IE, right click the file and select “Save link as” or “Save linked content as…”.

Tip:

To make it convenient for end users to use this charting tool, create a few CEWP’s with your favorite setup (1 placeholder, 4 placeholders in a table form and so on). Export the CEWP and upload it to your web part gallery (in the root of the site collection).

You can now add it as a regular webpart from the “web part selector” in edit page mode.

Enjoy!
Alexander


Change log:

27.03.2011 : New features are described here

19.10.2010 v2.7 is released. You find it in the “download section” above This version is the final fix (i hope…) for the web selector problems for non Site Collection Administrators (SCA).

For SCA’s there are no changes, but for those not SCA, I have abandoned my attempt to auto populate a web selector and gone for a input field. The user must write the URL of the web to load the list collection from. The reason for this is the fact that the method “GetAllSubWebCollection” is not accessible to non SCA’s, and that the manual iteration trough all webs caused prompts for credentials as it hit webs the current user did not have access to.

You will still get a credential prompt if you try to load the list collection for a web you have no rights to, but you will not get stuck in a “endless” loop of credential prompts.

New features:
In this version i have added the option to restrict edit access for a individual chart to a specific SharePoint user group. You find this setting in the Advanced option area in the “Edit Chart GUI”:

This is no “real” security setting as it only skips the rendering of the “edit chart button” and does not restrict the edit rights to the chart configuration list. If you want a true security setting you must change the permissions for each “chart” in the configuration list, or the permission for the configuration list itself.

Important!

v2.7 requires you to add one column of type “Single line of text” to the chart configuration list. The name of the field must be exactly “EditAccessGroupID” (without the quotes). Optionally you can delete the configuration list and have the script recreate it. The latter will result in you loosing all your existing chart configurations.


05.10.2010 v2.6.5 unintentionally made it impossible for other than site collection administrators to select web. This update (v2.6.6) fixes this issue. For uses that are not site collection administrators, the loading of the web selector may take a few seconds extra.

The check for edit page rights has been removed due to feedback from users that had problems editing charts. To restrict editing of the charts, manage user right for the configuration list – all users must have at least read access!

28.09.2010 Still some issues with the web selector as the v2.6.5 unintentionally made it impossible for other than site collection administrators to select web. I’m working on an updated version that will fix this. In the meantime, use v2.6.4 if you are not on a managed path…

23.09.2010 A new release (v2.6.5) has been added to the download section. This intends to fix the empty web selector for users with site collection on a managed path.

22.09.2010 There are some unresolved issues with the current release. You can follow the progress in the comment section below.

19.09.2010 A new version (2.6.1) is added to the “download section” – see bottom of article. It features enhanced functionality for selecting web. It now let you pick freely from all sites within the site collection (siblings, parent sites and subsites). This version may contain bugs, so please let me know if you find any.

14.09.2010 Small update to the code to fix the “Initial state” in motion charts, as well as some minor bug fixes. I have moved the files to my home server for you to download. This provides a better solution then manually highlighting and saving 2000+ lines of code. Please note that the file now come in three different “versions”. Read more in the “download section” below.

09.09.2010 Small update to the description regarding update from v2.0 to v2.5. I forgot to mention another field required to be added to the Chart configuration list: ListBaseUrl. This in addition to the already mentioned “ChartFormatters”. I have also removed an alert that was left in the code (line 40).

Christophe notified me of a problem with the 2010 compatibility in sub-site “homepage” – which is a wiki page. The CEWP HTML handling in SP2010 is a bit “different” than in SP2007. When editing the HTML source code, it actually pulls the contents generated by the script into the edit dialog and does not use the content that was originally saved to the CEWP… This is a bit of a headache!. A workaround for the wiki-pages is to use the “Content Link” property for the CEWP and link to a .txt file with the CEWP code placed in a document library (alongside the other scripts).

06.09.2010 Updated the script “ChartUsingGoogleVisualizationAPI.js” and the CEWP code. The version number is notched up to 2.5 and the changes are:

  • Added the ability to chart data from all ascending parent sites and all first level subwebs of current site
  • Added a few formatter options in the UI – refer the linked resources for instructions (thanks to Paulo for the tip).
  • Added new “action” for formatting as % (value*100).
  • The solution is now SharePoint 2010 compatible
  • Enhanced the UI for editing charts – all options and columns are now directly editable.
  • No more need to specify the “userListGuid” and the “userListBaseUrl” in the CEWP code.
  • The naming of the charts in the Chart Configuration list is enhanced – it now prefixes the chartId with “location.pathname” to prevent overwriting an existing chart when reusing the CEWP code in another page (thanks to Christophe for the tip).
  • Edit chart is now done in the standard “Browse mode” and not in “EditPage mode”. The edit button is found in the top left corner of the chart. The button is only visible for users with “Edit page rights” and does not show on printouts.
  • And more…

Note: This update introduces two new columns in the configuration list (GoogleVisualization_InteractiveChartsConfig). To reuse the existing Chart configuration list you must add one field of type “Multiple lines of text (Plain text) named “ChartFormatters” and one field of type “Single line of text” named “ListBaseUrl”. Optionally you can delete the Configuration list and have this solution automatically recreate it for you.

Please note that the CEWP code has changed alongside the code for the file “ChartUsingGoogleVisualizationAPI.js”.

Note about existing charts:
All your existing charts will need to be updated with the new “page id” in the chart configuration list (GoogleVisualization_InteractiveChartsConfig). Look at the new configuration item that is automatically added – copy it’s name and update your existing chart configuration – while deleting the newly added (empty) chart configuration.

27.08.2010 Small update to the file “ChartUsingGoogleVisualizationAPI.js” to fix a formatting issue when using object literal notation in the chart options.

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
* https://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