A common business pain point is the inability to display meaningful Business Intelligence (BI) information in a cost-effective, efficient manner. In Microsoft Office SharePoint Server (MOSS) 2007, you can generate powerful graphs through Performance Point, Excel Services, and SQL Server Reporting Services (SSRS) but these technologies can come at a significant cost. The costs make sense for many large companies that are setting up their own SharePoint farm and running extensive analytics. However, this may be overkill for smaller businesses who only want to generate a few basic charts. Still other businesses have their servers hosted by the Microsoft Business Productivity Online Suite (BPOS) where custom code and SharePoint solutions are not even allowed to be deployed. What should they do?
In this article, I demonstrate how to generate free flash graphs and charts from SharePoint list data through jQuery, AJAX, SOAP, and Extensible Style Language Transformation (XSLT), along with FusionCharts Free charting solution. This charting solution is entirely client-side, which makes it ideal for the hosted Microsoft BPOS environment. The solution can be deployed through the Content Editor web part (CEWP) and javascript—both of which are allowed in BPOS. These charts are both animated and dynamic and use CSS to control the styling while using a configuration list to control everything from the chart size to the text on the captions.
Hello World Flash Charting
Our first task will be to develop a simple but working graph, called Hello World, that will read static data from an XML file. Here are the prerequisites:
- A MOSS or Windows SharePoint Services (WSS) development site based on the Team Site template (ideally not part of your company's production portal)
- FusionCharts Free charting components, which you can download at www.fusioncharts.com/free/
The FusionChartsFree zip file contains three files required to get a working graph: FCF_Column3D.swf (contained in the Charts folder), Column3d.xml (in the Gallery, Data folder), and FusionCharts.js (JSClass folder). Save all three of the FusionChartsFree files to the Shared Documents libray on the SharePoint site. Open the development site's home page and add a CEWP to any of the web part zones. Then open the Source Editor and enter the following script:
<script src="Shared%20Documents/FusionCharts.js"></script>
<div align="center">Chart Text</div>
<script>
var myChart = new FusionCharts("Shared%20Documents/FCF_Column3D.swf", "myChartId", "300", "250");
myChart.setDataURL("Shared%20Documents/Column3d.xml");
myChart.render("chartdiv");
</script>
In this script, each of the three files are being loaded out of the Shared Documents library. After running the script, you should see a flash chart similar to the one in Figure 1.
The true power of a charting solution like this is the ability to dynamically react to changing data. There are two ways of accomplishing this when charting SharePoint list data: RSS feeds and SharePoint Web Services. Let's tackle RSS first.
RSS: Not Just For News Feeds
SharePoint allows list data to be consumed through a list feed that transmits the data via XML. Let's look at how to access this feed and transform it into the XML format that FusionChartsFree is looking for. The first thing to do is get the GUID for the list. This identifies the list for the RSS feed to use. An easy way to do this is to navigate to the target list and go to the list settings page for the list. The GUID of the list will be at the end of the URL (e.g., http://home.trucare.com/sites/sps/_layouts/listedit.aspx?List=%7B00AEECB3%2D3B6F%2D4EED%2D945F%2D7155026CFD3E%7D.)
Use a javascript variable to contain the XML from the RSS feed, as in the code snippet below. Replace the GUID in the code with the GUID retrieved from your list.
var rssXml = new ActiveXObject('Msxml2.DOMDocument.6.0')
rssXml.async = false
rssXml.load("_layouts/listfeed.aspx?List=%7B00AEECB3%2D3B6F%2D4EED%2D945F%2D7155026CFD3E%7D ")
This puts the data into one XML file, but it's not in the XML format that FusionCharts will understand; you need to do an XSLT to get it into the proper format. There are several great tools for constructing an XSLT file. One of the best tools is Altova's XML Spy, but you can also edit XSLT by using Visual Studio or Notepad (not for the faint of heart!). How XSL works and building the actual XSL files required for this Fusion Chart is outside the scope of this article. However, all of the XSL and javascript source files used in this article can be downloaded from http://www.windowsitpro.com//content/content/125046/macquarrie WebFig1.jpg've created an XSLT file, you'll need a javascript variable to load the XSLT file and another to contain the transformed XML. The code sample below loads the XSLT document into the xsl variable. The xsl variable then transforms the XML retrieved from the RSS feed into a new XML document stored in memory in the generateChartXml variable:
var xsl = new ActiveXObject('Msxml2.DOMDocument.6.0')
xsl.async = false
xsl.load("Shared%20Documents/TransformRSSToChart.xslt")
var generateChartXml = rssXml.transformNode(xsl)
myChart.setDataXML(generateChartXml)
Save the XSLT file to the Shared Documents library so that the javascript will be able to access it. Performing these steps should create a dynamic stacked bar chart similar to the one in Web Figure 1.
It's important to note that every browser requires different javascript objects, so you'll need to do checks based on the browser. IE uses Active X objects, and all the sample code has been designed and tested with IE in mind because it's the recommended browser for SharePoint.