A few months ago I received this email from Aymeric:
Hi Alexander,
I’ve created a JS API to use with Sharepoint. You may want to have a look at it: http://aymkdn.github.com/SharepointPlus/
I think this library could be useful for some other people. It’s on GitHub so feel free to share any comments.Thank you,
Aymeric
It took me “forever” to look at this API, but I finally had a go at it, and decided to use one of the methods: createFile in the SPJS Charts for SharePoint solution to be able to export the chart data to Excel as a CSV file.
You find the “Enable export to Excel as comma-separated values (CSV)” under the “Advanced options” section in the SPJS Charts for SharePoint GUI. When you enable this feature (done individually for each chart), you can hover over the chart to show a label in the bottom right corner of the chart. Hit this to create the file. When the file is successfully created, you are prompted to open it directly.
To achieve this I included the file “sharepointplus-3.0.3.js” in the CEWP code, and I created a function that exports the chart data as comma-separated values, which I then “inject” in a dedicated document library using Aymerics API method “createFile”. This document library is automatically created the first time you save a chart with the “Export feature” enabled.
You do NOT have to load the “sharepointplus-3.0.3.js” file if you do not intend to use the export to CSV feature. If you try to enable it, and “sharepointplus-3.0.3.js” is not loaded, you will get instruction on how to get hold of the file.
The files are named from the path to the page where the chart is located, the chart id and the user ID of the user exporting the chart. This means that the library “SPJS_ExcelExports” will not get filled up as the file is overwritten for each time the user exports the same chart. If you want to keep the file, save it in another location, or at least save it with another name to prevent it from being overwritten.
Example of the CEWP code with “sharepointplus-3.0.3.js” loaded:
<style type="text/css">
div.chartLoadingOverlay{
font-style:italic;
color:gray;
border:1px silver solid;
background-color:#F5F5F5;
line-height:250px;
height:250px;
width:500px;
text-align:center;
margin:2px;
}
</style>
<!-- The chart container is set up with an overlay to let the user know the chart is rendering -->
<div id="MyChart1">
<div class='chartLoadingOverlay'>Loading chart please be patient...</div>
</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
var arrOfChartContainers = ["MyChart1"];
var loadRC = false;
// Set this to true to allow for the use of variables in the "Filter setup textarea"
var allowEval = false;
// Set this to true to delay the loading of the chart.
var loadManually = true;
// You can override chart options set in the GUI
var chartOptionOverride = {"TheChartID":{"title":"This is the chart title!"}};
</script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript" src="/test/English/Charts/Javascript/SPJS_ChartsForSharePoint_v3.4.js"></script>
<!-- Used to enable export of chart data as CSV -->
<script type="text/javascript" src="/test/English/Charts/Javascript/sharepointplus-3.0.3.js"></script>
<!-- Used to enable export of chart data as CSV -->
<script type="text/javascript">
// This function will load the charts
function manualLoad(){
loadManually = false;
spjs_GenerateChart();
}
// Delay the call to the function by 10ms to give the overlay time to render
setTimeout(function(){
manualLoad();
},10);
</script>
In v3.4 I have made a “quick pass” trough the code to fix some compatibility issues for SharePoint 2013.
Because this solution is funded with an occasional beer donation, I cannot fully test it as if it was a commercial product. Therefore I must rely on you guys to find the rest of the bugs. Post them here, and I’ll fix them as soon as I can manage.
Alexander
Hi Alexander,
Thank you for your hard work on this awesome plugin you are sharing with us.
I am experiencing an issue whereby the exported CSV is actually a colon separated value file. Hence excel doesn’t parse it correctly.
Is it to do with my end?
Thank you.
Vinz
The colon is actually hardcoded in
csvBuffer.push(csvRow.join(“;”),”\n”);
Hi,
It appears Excel expects a comma delimiter for American/English version, and semicolon for European versions. I tested this in my setup and did not think Excel would handle it differently in other language-version.
I’ll include a delimiter-selector in the next release.
Alexander
Hi,
Yes, that makes sense.
You should be able to get the browser regional settings with [‘a’,’b’].toLocaleString().substring(1, 2) // I get ‘,’
Found it in http://www.codeproject.com/Articles/80083/Detecting-User-Regional-Settings-In-The-Web-Browse
Works great!
thank you very much for finding the time to look into it!
Could you test the 3.41_beta and let me know if the export to csv issue is fixed? I have used comma delimiter as default, but you can opt to use semicolon in the GUI.
Alexander
Could I humbly suggest two features in your solutions that I reckon could be quite useful?
-One would be to have the dropdown filtered by available items. Namely if “Company A” is included in a filter setup but there is no record for it in the list, “Company A” should be removed from the filter dropdown.
Whether to apply this to a filter could be defined in the filter object as a property, like
{“label”:”Company”,”urlKey”:”Company”,”hideMissing”:true,…}
-The other would be to extend the export functionality to also export PNG.
I am using this code which so far works well, also exports multi-svg charts (e.g. Gauges).
It needs two extra references to canvg.googlecode.com/svn/trunk/rgbcolor.js and canvg.googlecode.com/svn/trunk/canvg.js
function downloadChartImage(chartID,docLib){ var siteURL=window.location.protocol+'//'+window.location.host; $SP().createFile({ content:chart2DataURI(chartID + '_chart'), destination:siteURL+L_Menu_BaseUrl+'/'+docLib+'/'+chartID+'.png', url:siteURL, encoded:true, after:function() { location.href=L_Menu_BaseUrl+'/_layouts/download.aspx?SourceURL='+docLib+'/'+chartID+'.png'; } }); function chart2DataURI(chartID){ var $box=$(''); var $svg=$('svg','#'+chartID); $svg.each(function(){ $box.find('>svg').append($(this).clone().attr({ 'y':$(this).offset().top-$('#'+chartID).offset().top, 'x':$(this).offset().left-$('#'+chartID).offset().left})) }); $box.find('>svg').attr({ height:Math.max.apply(null, $svg.map(function (){return $(this).offset().top-$('#'+chartID).offset().top+$(this).height();}).get()), width:Math.max.apply(null, $svg.map(function (){return $(this).offset().left-$('#'+chartID).offset().left+$(this).width();}).get()) }); var $canv = $('').attr({height:$box.find('>svg').attr('height'),width:$box.find('>svg').attr('width')}); canvg($canv[0],$box.html()); return $canv[0].toDataURL('image/png').match(/,(.*)/)[1] }}It could be written much better but that’s as good as it going to get with me.
It obviously only exports charts if the browser supports SVG, but whatever button is bound to the function could be hidden if the chart is not in SVG.
Hope this makes sense.
Thanks for taking the time to read through
Hi,
Thank you for the feedback.
The filter issue is not so easy to fix as, when applied, the items pulled from SharePoint is already filtered by the CAML-query, and therefore we don’t know whether or not the other filter values will result in a “hit” when applied.
There could be a workaround using a series om “probing queries” initially to check whether or not the list contains matching data. This probing would result in more traffic on the server, and will slow down the loading of the chart, but I’ll see what I can do in the next release.
The export to image ability have already been considered, but as it does not support IE below version 9 – and the fact that SP2010 forces IE 8 compatibility mode, it does not work at all in SP 2007 / 2010 when using IE, but will in SP2013 as this does not force IE 8 compatibility mode.
It’s on the list of things to do.
Alexander
Hi,
Thanks for listening to my suggestions
For cleaning the filter the CAML needs to be parsed and the {filter:urlKey} condition(s) replaced with a wildcard expression. You then need an extra call to GetListItem to make an array of the returned filter field values to compare against the filter options.
It is slower, but it doesn’t bother if this is triggered after the chart is rendered. Plus the owner could choose whether to apply this or not.
As for the compatibility mode forced by SP that’s a good point. I didn’t think of it at all. Annoying..
Thank you.
p.s. They way I posted code is obviously wrong as it got stripped of HTML tags. Is there any recommended way of displaying code as code in a post?
Look here on how to post code in comments
Alexander
Alexander, it might be useful to clarify the license terms for this solution, considering that it includes code that is licensed under GPL v2.
Alexandre, how can I add the function “openEditor()”?(https://code.google.com/apis/ajax/playground/#chart_editor)
May I add it either in your SPJS_ChartsForSharePoint_v3.4 script or CEWP?
Hi,
It is not possible (without modifying the code) to apply this to the current version.
Alexander
hi alexander: this seems like a nice tool. i am testing it for use. i am having a problem. i want to make a stacked column chart. this seems entirely possible. but one of the columns holding data that i need to stack is not showing in the dropdown list of columns from the list. i think this is because the column name includes a slash, as there are three columns with a slash in the name and none of them appear in the dropdown, though everything else does. is there any hope for me? cheers
Hi,
I do not think a slash in the field name should interfere. What fieldtype is the fields that do not show?
Alexander
That sounds like there may be hope 🙂
The field type is: Choice
If it is multi choice it will not show – is it?
Alexander
I see. Yes, indeed. My own conundrum. Let me think…
Thanks much for your insight.
Hi Alex,
I was wondering can you add a hyperlink within the chart to the list it is pulling the data. I have set up a dashboard using the table view option however I would like the option for the end user to click on the chart somewhere to take them to the list view so they can see the data. I can code links below the chart however this is a messy solution if you have multiple charts within one web part.
Thanks
Hi,
I have previously given an example of such code here: https://spjsblog.com/2011/08/16/charting-for-sharepoint-using-google-visualization-api-update-16-08-2011/
This is the code you can use as a starting point:
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'); } } } }Alexander
You add it to the CEWP
Alexander
Alexander,
I can’t seem to get the above solution working in SPJS v4. I have this working in an earlier version.. I am setting the chart ID to equal the generated chart id eg: “ca96d69a-9f65-45e7-b9ac-0b66b101f6a4”
Any thoughts?
Cheers
Hi,
In v4 I have changed the function a bit. You can use this code as starting point:
function spjs_chartSelectionHandler(chartID,selection,data){ var message, item, value; message = ""; value = ""; item = selection[0]; if(item.row !== null && item.column !== null){ message = "{row:" + item.row + ",column:" + item.column + "}"; value = data.getValue(item.row, item.column); if(message === "") { message = "nothing"; } alert("You selected " + message +", the value is: "+value); } }Add the code to the master cewp.
Alexander
Excellent tool, saved me a ton of time. Definitely sending a beer (or two) your way! Do you plan on supporting color binding to specific categories? Like the following example: http://jsfiddle.net/Qquse/140/
Hi,
I’m glad you liked it! “sliceColorByValue” has already been implemented in version 3.3.83 (February 28, 2013).
Look at the very bottom of the “Options” section and click the “Special options by chart type” link to learn how to use it.
Alexander
Awesome! Concerning barcharts is there a similar option? I could not locate it in the Google API doc.
Sorry, but this has been added for pie charts only in this version. I might add support for other chart types in the future.
You wont find this in the Google API as it has been custom made by me for this solution.
Alexander
Hi again. I’m marching toward success, but I need your help again. I am trying to format dates and numbers in columns of a table. the data is coming from the sharepoint list. but the date is always displayed at 2013-08-15 00:00:00. I cannot for the life of me discover the correct syntax for formatting this date column. I am getting the column number correct when i try using a formatter because i can see my value showing up as text replacing all the dates. I would really like to convert the date to this 08-AUG-13. Please teach me 🙂
Hi Alex
I want use an option “series” in Combo charts with this config:
option series Value {0:{type:”line”}}, but always appears this error: Parameter: series Invalid JSON: {0:{type:”line”}}. I don’t want put chartOptionOverride in CEWP because I have 3 ChartsID and I want specifics rules in each chart. Could you help me the correct syntax?
{0:{type:”line”}} must be changed to {0:{“type”:”line”}}
Alexander
Hi Alexander,
I always read you Javascript tips and tricks.
In related to create the chart, there is a note written at http://www.aasoftech.com/SitePages/How%20to%20create%20a%20Chart%20from%20SharePoint%20List.aspx
This code is reading data from SharePoint list and create the Google Chart from it.
This tool allows you to create Google chart using client object model. The code is ready to use. The demo is located at http://www.aasoftech.com/demo . You can download the code from http://www.aasoftech.com/download
Hello Alexander,
I’ve implemented v3.4 but I’m having trouble with pie charts…..no doubt I am missing something very simple and hoping you can help.
I have a list with a field KPI – Red/Amber/Green and I’ve created the chart with column 0 = KPI, string merge duplicates, then column 1 = KPI, count.
All I get is a blank chart with the number of items at the bottom?
If I change column 1 to count inc/exc blanks, it displays the chart but only for one instance of the KPI, I’ve played around but seem to be stuck now…..any help?
Thanks,
Jason
Hi,
Sorry for the late reply. Did you figure it out? Try selecting “Table” as chart type to look at the datasource and verify you have the correct values before switching back to pie chart.
Alexander
Hi, Thanks for your script and I can see a beautiful chart on my SP. However I still see some errors as below. The line 802 is spjs_GenerateChart();
Any ideas? Thanks
Webpage error details
User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; InfoPath.2; MS-RTC LM 8; .NET4.0C; .NET4.0E)
Timestamp: Tue, 6 Aug 2013 02:30:32 UTC
Message: Object expected
Line: 807
Char: 2
Code: 0
URI: http://—/sites2/spqkmwhp/risk/SitePages/TestPage.aspx
/*****************************************************
Address all containers
*****************************************************/
// All charts must be represented by a container with a unique id.
// This container must be present in the page
var arrOfChartContainers = [“MyChart1”];
var loadRC = false;
var allowEval = false;
// Set this to true to delay the loading of the chart.
var loadManually = true;
// You can override chart options set in the GUI
var chartOptionOverride = {“TheChartID”:{“title”:”This is the chart title!”}};
// This function will load the charts
function manualLoad(){
loadManually = false;
spjs_GenerateChart();
}
Hard to tell what causes this error without seeing the full CEWP code (with the script tags). Could you send it to me by mail so I can look at it?
If possible, include a few screenshots of the setup as well.
Alexander
Hello Alexander. I need to create a graph Combo type, where the first column shows the month, second column counting the number of items per month, and the third counts quantity of items equal to a certain status. In the fourth column is my difficulty, It should shows a value which is the ratio between the third column and the second column to display as a percentage. Is it possible to do it? I want to show a combo chart with bars and lines showing as an indicator.
Hi,
To accomplice this I believe you will have to create a calculated column in the SharePoint list which calculates this number and then use this calculated column as the fourth series column in the chart.
Alexander
Hi Alex, Just let you know the error disappeared when I change the js from 3.3 to 3.36. Thanks
Hi Alex, I tried to use the Combo Chart with Options. I got syntax error during the chart loading when I enter the series:
Options: series
Value: {5:type:”line”}}
For the seriesType and chartArea.left are fine.
Thanks
I have found that when “Create filter above the chart” is used and configured for multiselect and multiple options are pre-selected, only the last selected option is actually used to filter the chart when it it initially rendered although the selector control above the chart shows all options selected. If I then select options with the mouse, the chart is subsequently filtered correctly.
Hi,
Thank you for the feedback. I will look into this and fix it in the next release.
Alexander
Hi Alex, I tried to use SPJS_ChartsForSharePoint_v3.3.6.js and configure a Combo Chart with Options. I got syntax error during the chart loading when I enter the series:
Options: series
Value: {5:type:”line”}}
For the seriesType and chartArea.left are fine.
Can you give me some lights?
Thanks
Hi,
Use {“5”: {“type”:”line”}}
Alexander
Hi,
jQuery v1.10 is mistaken to be v1.1. Should jQuery v1.10 be supported?
here:
if(parseFloat(jQuery.fn.jquery)<1.6)
Thanks
Yes,
I will fix it in the next release, in the meantime you can replace the line with this:
if(parseInt($.fn.jquery.split(".")[1]) < 6){Edited: changed “>=” to “<". Alexander
Alexander, should I be able to pass an array in the options? Some configuration options like “slices” for the pie chart are arrays and I haven’t been able to get them to work with getting a syntax error from your script. Thanks!
Wait, I am so dumb, looks like the answer is right above my post. Sorry.
Ok, the syntax above worked for the slices option, but I don’t know how to pass objects. For example, how would I mark up the values for the vAxis.ticks option? Google API syntax is:
{ticks: [5,10,15,20]}