Quick DYI Sharepoint Chart webpart

Problem

I had a client who wanted to use the Chart webpart to display aggregated data from a list. Unfortunately I couldn’t find a way to aggregate the data from the list using the Chart webpart thus we had to find another solution. They wanted us to avoid writing any code(C#) to achieve this functionality in SharePoint Online.

 

Solution

In order to aggregate the data I decided to use the SharePoint Client Side ECMAScript API and to display the charts jQuery Google Charts .
In SharePoint 2007 we could add the javascript directly in the Content Editor webpart. This proved to be a problem on some of the websites where the script code would be stripped when pressing save. SharePoint displayed a warning “the HTML source you entered might have been modified”. In order to avoid this “bug” I created a Document Library to contain the javascript file and referenced the script file directly in the Content Editor webpart. This also enabled us to have versioning on the javascript. Instead of this work around I found that one can use the HTML Form Web Part which won’t strip the javascript. (http://blog.pathtosharepoint.com/2010/10/27/about-scripts-web-parts-and-urban-myths)

Document library containing the JavaScript files.
clip_image002[4]
Modifying the Content Editor to use the uploaded client script.
clip_image004[4]
Just pasted in the URL to the JavaScript to make the code work on the page.
To ensure that the ECMAScript API is initialised you need to use the following code.
ExecuteOrDelayUntilScriptLoaded(initialize,"SP.js");

function initialize() {
//Do Init
}



If you don’t do this you can risk that the SP.Context = null if your script is activated before the SP.js has been loaded.


I used this code to aggregate the data from the list.




function ReadListItem() {

var listTitle = "Rekvisitioner";

//Get the current client context
context = SP.ClientContext.get_current();

var rekvisitionList = context.get_web().get_lists().getByTitle(listTitle);
var camlQuery = SP.CamlQuery.createAllItemsQuery();
this.listItems = rekvisitionList.getItems(camlQuery);

context.load(listItems);

context.executeQueryAsync(ReadListItemSucceeded, ReadListItemFailed);
}



function ReadListItemSucceeded(sender, args) {
var itemsString = '';
var enumerator = listItems.getEnumerator();
var varslede = 0;
var total = 0;

while (enumerator.moveNext()) {
total++;
var listItem = enumerator.get_current();
if (listItem.get_item('HHD_x0020_Varslet') == 'Ja')
{
varslede++;
}
}

makegraph(varslede, total - varslede);
}


function ReadListItemFailed(sender, args) {

alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());

}

Now that I had the list data aggregated I could use jQuery Google Charts to display the data.

function makegraph(varslede, ikkevarslede) {
var varsletProcent, ikkeVarsletProcent = 0;

varsletProcent = (varslede / (varslede + ikkevarslede)) * 100;
ikkeVarsletProcent = (ikkevarslede / (varslede + ikkevarslede)) * 100;


varsletProcent = Math.round(varsletProcent);
ikkeVarsletProcent = Math.round(ikkeVarsletProcent);

var api = new jGCharts.Api();

// Bar graph
jQuery('<img>').attr('src', api.make({
data: [varsletProcent, ikkeVarsletProcent],
axis_labels: ['Varslet', 'Ikke varslet'],
type: 'bvs',
bar_width: 50,
bar_spacing: 5
})).appendTo("#bar1");

//Pie Chart
jQuery('<img>').attr('src', api.make({
data: [ikkevarslede, varslede],
type: 'p',
axis_labels: ['Ikke varslet', 'Varslet'],
size: '400x250'
})).appendTo("#bar1");

var text = 'Varslet ' + varsletProcent + ' % Ikke varslet ' + ikkeVarsletProcent + '%';
$("#bar1").append('<div>' + text + '</div>');
}



You can find more information on how to use jQuery Google Charts here.

http://www.maxb.net/scripts/jgcharts/include/demo/

This is a screenshot of the end result.

clip_image006[4]

I initially wanted use Excel to aggregate data and generate the graph. The excel document was to be placed in a document list and the use the Excel Webpart to show the graph. Unfortunately it isn’t supported OOTB to have excel update a SharePoint list data source in excel services. For this to work we needed to install Power Pivot and this is not yet supported on SharePoint Online.

More informationen regarding Power Pivot can be found here.

http://technet.microsoft.com/en-us/library/gg576960.aspx

Can anyone explain why the content editor only stripped the javascript on some websites? It appears there is some kind of security feature that can be activated on the site but have been unable to find any information regarding this.

Etiketter: ,