Saturday, April 11, 2009

Dynamic Charts & Graphs in SharePoint (Part II)




SharePoint allows you to define list views that calculate Sum, Average, Min, Max, Standard Dev, etc. on the items, whenever your list has at least one numeric field or column.

The script below, combined with such a list view, will plot a Google Chart showing those totals dynamically. The chart scale is automatically adjusted with the maximum value to be charted so it fits the designated size in the script.

Once implemented, you can hide the list view web part so only the chart will render on the page. You can also play around with chart parameters within the script, to change chart type, style, size, colors or even build more complex charts. Take a look at the Google Chart API page for reference.

Hope this could be useful.

Here a step-by-step screen capture series:






















<div id="jLoadMe" class="content"></div>



<script src="/jquery/jquery.js" type="text/javascript">

// To load jQuery (redefine the path if necessary)

</script>



<script type="text/javascript">

/*

* Google Chart for list column totals

* By Claudio Cabaleyro (2009)

*/



Array.prototype.max = function() { // function to get the maximum value of Y axis

var max = this[0];

var len = this.length;

for (var i = 1; i < len; i++) if (this[i] > max) max = this[i];

return max;

}



function BuildChartURL(Data, Names) // Adjust Chart Properties below - See Google Charts API for reference

{

MaxData= Math.round(1.1*Data.max()).toString();

var DataPoints= "&amp;chd=t:"+Data.join(",");

var DataTitles= "&amp;chl="+Names.join("|");

var ChType= "cht=bvs"; // Vertical bars type

var ChSize = "&amp;chs=300x200"; //HeighxWidth in px

var ChScale = "&amp;chds=0,"+MaxData; //Vertical scale: 0 through 10% over max series value

var ChLabels ="&amp;chm=N,000000,0,-1,11,-1";

return ("<p><IMG src='http://chart.apis.google.com/chart?"+ChType+ChSize+DataPoints+DataTitles+ChScale+ChLabels+"'/></p>");

}





var ArrayTitle= $('.ms-unselectedtitle:html'); //find all columns in titles row

var TitleTxt = new (Array);

$.each(ArrayTitle, function(i,e)

{

TitleTxt[i] = $(e).text(); // Store ALL column titles in TitleTxt

});



var ArrayTotal= $('.ms-vb2', $('#aggr')); //find all columns in totals row

var TotalTxt = new (Array);

$.each(ArrayTotal, function(i,e)

{

TotalTxt[i] = $(e).text().substr($(e).text().indexOf("= ")+2); // Store ALL column totals in TotalTxt

});



var Titles = new (Array);

var Totals = new (Array);

$.each(TitleTxt, function(i,e) // clear empty elements in totals row

{

if (TotalTxt[i] != "" && TotalTxt[i] != null)

{

Titles.push(TitleTxt[i]);

Totals.push(parseFloat(TotalTxt[i].replace(',','')));

}

});



$("#jLoadMe").append(BuildChartURL(Totals, Titles))

</script>

8 comments:

Anonymous said...

This aint working. Can you please rellok at the code and let me know if it needs anything else on the MOSS Server to be tweaked?

Clacby said...

Take into account that the code converter I user to publish at blogger makes that when you copy-paste single and double quotes can cause problems. This article was re-published at EndUserSharePoint, try the code there:

Create Dynamic Bar and Pie Charts in WSS with any RSS feed: No code required

Anonymous said...

Parece muy sencillo, pero no acabo de entender como se relaciona la lista con el grafico.

Clacby said...

Para Anonymous:
El ejemplo es un caso ficticio en el que la lista contiene datos de ventas mensuales para tres productos diferentes. El gràfico simplemente grafica el total de las ventas para los tres producto por cada mes.

Craig said...

Claudio,

Thanks for this .. I've got it working, however I think my column sum totals may be too large ... we are applying this a financial list and the column totals are in the 6-figure range and the display seems confused by that ... any tips on how to handle those larger numbers?

Thanks

Clacby said...

Hi Craig,
I think you could try changing the following line:

TotalTxt[i] = $(e).text().substr($(e).text().indexOf("= ")+2);

Into this:
TotalTxt[i] = ($(e).text().substr($(e).text().indexOf("= ")+2))/1000;

so numbers should be represented in thouthands. You can also try changing the spacing beteewn bars, you check Google Charts API documentation for this. By the way, remember that the totals of your list are used in the API call, so they go to the internet. If it is sensitive data, I recommend not to use this solution.

claudio

Clacby said...

I just realized that my previous suggestion will probably not work, you better try this:

TotalTxt[i] = Math.round(($(e).text().substr($(e).text().indexOf("= ")+2))/1000);
This will round the number so it will not contain decimals.

Hope this works.

Jess said...

Hi there! I was about to comment about having problems with the code when i saw the Anonymous comment on the left.

it's still great work!!!!!!!! Thanks so much! :)