10.12.12 I introduced a new bug in v3.3.7 that broke the filter if you did NOT use “filterAdditionalCharts”. This should be fixed in v3.3.8.

13.11.2012 Updated to v3.3.7 to fix a bug regarding “filterAdditionalCharts” in the Custom CAML filter setup. This was broken in v3.3.6 – thanks to Paul Brown for notifying me.

Please note that you now have to supply the parameter for each filter in the filter setup. Click the “Instructions” link above the filter setup textarea for details.

20.09.2012 Updated to v3.3.5
06.09.2012 Updated to v3.3.1. This update is an attempt to fix an issue where you apply multiple formatters on the same column, and only the last one sticks.
18.07.2012: Updated to v3.3 and added a few new features. See this article for details.
07.06.2012: Updated to v3.2.2 to add support for special characters like single quote and “&” in the filter options. See change log.
29.05.2012: Updated to v3.2.1 to fix a bug. See change log.
31.03.2012: Updated to v3.2.
Added filter wildcard to allow “Show all” when using dropdown filter. See “Filter setup Instructions” in the “Edit chart GUI”.
See change log for details on some small bugfixes.
21.02.2012: Updated to v3.1.5. See change log.
14.02.2012: Updated to v3.1.4. See change log.
13.02.2012: Updated to v3.1.3. See change log.
06.02.2012: Updated to v3.1.2. See change log.

06.02.2012: Updated to v3.1.1 to avoid using eval. Read more in the article. Thanks to Christophe for pointing me to the “jQuery.parseJSON()” method – I was not aware that jQuery had this built in.

Keywords: New chart types, multiple dropdown filters and variables in the custom CAML query.

I have “silently” released version 3 of the “Charting for SharePoint using Google Visualization API” solution in November 2011. You find it here. The 3.0 release was not so mush about new functionality, but focused more on tidying up the GUI to make it easier for the user to understand the configuration options.

In the work with v3.0 i realized that i did not have any distinct name for the solution, and I decided to name the solution SPJS Charts for SharePoint.

A full walktrough of the configuration options in v3.0 can be found in this book from O’Reilly. The book is a collection of articles from Nothing But SharePoint. The article you find in this book (Publication Date: February 24, 2012) has not been published elsewhere.

To start using this solution, you only need the rights to create a list (will be done automatically by this solution), upload files and add a CEWP or a HTML form web part to a page.

After the initial setup, all you need is contribute rights to edit charts.

The setup process contains four steps:

1. Download the file “SPJS_ChartsForSharepoint_v3.x.js” and “CEWP.txt” from here from here. You can choose between the uncompressed (124KB) “SPJS_ChartsForSharePoint_v3.1.js” and the packed, smaller version version (64KB) “SPJS_ChartsForSharePoint_v3.1_min.js”. The latter is packed using packer. You find the CEWP code and the script file in separate folders. The folder name will reflect the script version. Ensure you download the latest version, and that you read the change log.
2. Upload the file “SPJS_ChartsForSharepoint_v3.x.js” to a document library, or put it in a folder created with SharePoint Designer for safer storage. Ensure all users have read access to the file.
3. Copy the link to the file “SPJS_ChartsForSharepoint_v3.x.js” and replace the example path in the CEWP code so that it reflects your local copy.
4. Either upload the CEWP code to the same folder as the file “SPJS_ChartsForSharepoint_v3.x.js” and use the content link option in a CEWP to link to the code, or use a HTML form web part and insert the code directly in the “Source editor”.

Reload the page, and if it is the first time it is run in the site, the solution will prompt you to create the configuration list. If you are upgrading from an earlier solution, you are prompted to update the configuration list. Both actions require no more than a click on the OK button to complete.

Switched GeoMap for GeoChart and added BubbleChart and SteppedAreaChart.

In the previous versions, you could create ONE dropdown filter above the chart. In v3.1 you can have multiple. The setup of the filter have changed, and those updating from a previous version will have to redo the filter setup (you will get an error message telling you that the filter setup is wrong).

The chart example above has this filter setup to create the “Year dropdown”:

You can have multiple filters created in the same way, Click “Instructions” above the Filter setup textarea to expand this description:

When you have written or prefilled the filter (by selecting it from the “Prefill textarea from choice field-dropdown”), you must add the appropriate CAML in the “Custom CAML-query” textarea. You can start by clicking “Build CAML from selected filters” below the Custom CAML-query textarea to have the raw CAML autogenerated.

Note that you must change the text “FieldInternalNameToMatch” in the Custom CAML-query textarea to the correct FieldInternalName.

The new filter method can use eval¹, and thus you can have parts of, or even the complete filter as a variable in the CEWP. To do this, you create a variable in the CEWP code and inserts the variable name in the “Filter setup” textarea. Please note that the CAML is “static” and you must insert the corresponding {filter:urlKey} in the Custom CAML-query textarea to pick up the filters.

var allowEval = true;

Another new feature is the ability to use variables directly in the CAML. Construct the variables in the CEWP code like an object named “spjsCAMLvariables”, then insert {variable:variable_name} in the Custom CAML-query textarea where you want the variable to be inserted. “variable_name” represents one property from the object “spjsCAMLvariables”.

In the following example i have used it to filter a calendar to show this weeks items.
In the CEWP code you insert code like this to get the two variables “thisWeekMonday” and “thisWeekSunday”:

// Get todays date
var today = new Date();
// Find monday in current week
while(today.getDay()!==1){
	today.setDate(today.getDate()-1); 
}
// Create two date objects - one for Monday and one for Sunday
var monday = new Date(today);
var sunday = new Date(today.setDate(today.getDate()+6));
// Convert the dates to ISO8601 format: yyyy-mm-dd for use in CAML
var thisWeekMonday = monday.getFullYear().toString()+"-"+(monday.getMonth()+1).toString()+"-"+monday.getDate().toString();
var thisWeekSunday = sunday.getFullYear().toString()+"-"+(sunday.getMonth()+1).toString()+"-"+sunday.getDate().toString();
// the object used in the Custom CAML-query
var spjsCAMLvariables = {"thisWeekMonday":thisWeekMonday,"thisWeekSunday":thisWeekSunday};

Insert the variables in the Custom CAML-query textarea like this:

<Where>
<And>
<Geq>
<FieldRef Name='StartDate' /><Value Type='DateTime'>{variable:thisWeekMonday}</Value>
</Geq>
<Leq>
<FieldRef Name='StartDate' /><Value Type='DateTime'>{variable:thisWeekSunday}</Value>
</Leq>
</And>
</Where>

Hit “Save” and your chart will always show the current weeks items.

var loadRC = true;

If you use this solution, please consider buying me a beer!

Alexander

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

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

24.08.2011 v2.9.3.1 has been released with these changes:

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

Managed path

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

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

Debug
Add this to the CEWP code:

var chartDebug = true;

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

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

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

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

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

These are the changes in this release

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

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

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

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

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

Other articles in this series:

• Interactive Charts using Google Visualization API v2.0.
• Update 27.03.2011
• Update 05.05.2011
• Update 05.06.2011
• Chart examples

Download

You find the code here

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

Regards
Alexander