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'll 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:
Chart Text
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 the link at the beginning of this article. I'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.
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.
SharePoint Web Services
SharePoint has a number of web services that you can use remotely to work with SharePoint data. One of these web services is the Lists.asmx service, which allows the consumption of list data through the GetListItems web method.
Web service data is transmitted through a protocol called Simple Object Access Protocol (SOAP), which relies on XML. In building out a SOAP request, you need to construct a SOAP envelope with header information pertaining to the web service and method being used as well as the list that you're querying.
In the code shown in Figure 2, you need to replace var listGuid by either the GUID or name of the actual list you're querying. The XML parameter
var listGuid = '{00AEECB3-3B6F-4EED-945F-7155026CFD3E}';
var request = ""
+ ""
+ ""
+ ""
+ "" + listGuid + " "
+ " "
+ " "
+ " ";
This List GUID is being used to build the SOAP envelope stored by the request variable. An Active X object http request then uses the envelope to query the service and get a response.
In the code in Figure 3, a function called readresponse will receive the response. If the operation is a success, the response will be XML generated with the list data. If it is a failure, you will see an error message.
//ajax jquery call to List web service
$.ajax({
url: webURL + "/_vti_bin/Lists.asmx",
type: "POST",
dataType: "xml",
data: configListSoapEnvelopeID,
success: configListQueryResult,
contentType: "text/xml; charset=\"utf-8\""
});
After receiving the response XML, javascript and XSLT can be used to construct a dynamic chart in the same manner as the RSS feed above. Note that ActiveXObjects are exclusive to IE, so you'll need to generate alternate SOAP requests for other browsers.
jQuery, AJAX, CSS and a Configuration List
jQuery is an exciting new javascript library that can simplify coding for client-side functionality such as AJAX. With the CEWP and jQuery, you can do a number of really cool things without serverside code.
Jan Tielens has demonstrated much of this in his blog with such functionality as custom menus, sliding menus, task notifications, and more (See Jan Tielens Bloggings at weblogs.asp.net/Jan).
You can use jQuery to simplify the code needed to make web service calls. This is especially true with the jQuery AJAX web service call for making SOAP requests. The Flash Charting solution being used in this example application comes with documentation containing a multitude of configurable elements that can be separated into two groups: presentation (e.g., color, height) and configuration (e.g., captions, precision, display value booleans).The goal was to place the presentation elements in a CSS file that a web designer could readily understand and put the configuration elements in a SharePoint list that could be managed easily.
To accomplish this, you can use jQuery to consume the CSS file and the SharePoint Configuration list.
You can then use an XSLT to combine that data with the SOAP response retrieved by the sample code in Figure 3 to form an XML section (see the chartXMl.xml in the downloadable code) used to generate the animated Flash chart. This lets you change presentation and configuration without changing the underlying jQuery or XSLT.
To prepare for this example, you need to add a document library called Charting to the SharePoint site. You need to add the standard jquery-1.3.2.min library (http://code.google.com/p/jqueryjs/downloads/detail?name=jquery-1.3.2.min.js) and a JQuery XSLT transform library called jquery.transform.js (http://plugins.jquery.com/node/7100 ) to the Charting library.
The Fusion Free chart requires the FusionCharts.js javascript file and the FCF_StackedColumn3D.swf file, which you also need to add to the Charting Library.
The code for this example contains additional files called ChartingConfig.css, TaskListToStackedBarGraph.xslt, stackedbar.xsd, and jquery.generatechart.js, which will all need to be placed in the same Charting Library.
The .xsd file is an XML schema to ensure that the generated XML matches the Fusion Free format. Finally, you need to create a configuration list called Charting Configuration List in SharePoint. The Configuration List needs to have the custom properties displayed in Figure 2.
Now that all the files and SharePoint data structures and lists are ready, the magic happens in a CEWP placed on the page. First, you need to add javascript file references to the Charting Library as well as a HTML Div tag for rendering the chart, as the following code shows:
Tasks By Status Per Consultant
In the actual scripting, you need a reference to the list, which can take the form of either the list name or the GUID of the list. The ID for the div above must be a variable.
//ListName or List GUID var list = "Tasks"; //ID of div for chart var chartDivID = "stackedTasks2dDiv";
Naming conflicts can occur if multiple CEWPs are placed on a page. Because of this, variable names have been set up to be configurable within the CEWP. The paths to each of the non-javascript files in the Charting Library will need to be configured as variables, as the following code shows:
//Unique Identifier for soap envelope var soapEnvelopeID = "soapEnvelope1"; //File Path location for CSS file for styling chart var cssFile = "Charting/ChartingConfig.css";
At this point, you have a Charting Library that contains all the files used for transforming and rendering the Flash chart. The CEWP contains references to these files as well as variables to be passed to the chart. The jquery.generatechart.js file is a jQuery file created specifically for this project. Only a few of the variables have been displayed above, but you can see all of the actual variables in the method call below.
$.generateChart(list, chartDivID, soapEnvelopeID, cssFile, chartID, chartWidth, chartHeight, xsltFile, swfFile, xsdFile, schema, fusionChartID, chartDom, configListSoapEnvelopeID, webPartTitle, chartingList, webURL);
The jquery.generatechart.js file is quite complicated, but let's review a few of the key elements. The SOAP request can be changed to use AJAX, which will make an asynchronous request to the web service.The call shown in Figure 4 uses two variables.
jQuery.get(cssFile, null, function(data)
{
//remove spaces and lines from repsonse xml
var nospace = data.replace(/^\s*|\s*$/g, '');
var nolines = nospace.replace(/[\r\n\t]/g, '');
//Transform CSS document that has the spaces and lines removed into an xml dom that can be parsed
var xmlDoc = $.buildxmldomfromcss(nolines);
…[$.buildChartXml function call has been omitted]
The webURL variable is passed in from the CEWP and determines which site's Lists.asmx web service to call. The configListSoapEnvelopeID is a SOAP envelope that has been constructed as demonstrated earlier in this article.ConfigListQueryResult is the name of the javascript method to call upon a successful request. Then you use a jQuery method to retrieve the CSS file from the the filepath location and to remove all spaces and lines breaks, as the code in Figure 5 shows.
jQuery.get(cssFile, null, function(data)
{
//remove spaces and lines from repsonse xml
var nospace = data.replace(/^\s*|\s*$/g, '');
var nolines = nospace.replace(/[\r\n\t]/g, '');
//Transform CSS document that has the spaces and lines removed into an xml dom that can be parsed
var xmlDoc = $.buildxmldomfromcss(nolines);
…[$.buildChartXml function call has been omitted]
});
A jQuery method $.buildxmldomfromcss has been created to take the CSS file and turn it into formatted XML. It takes the nolines CSS value shown in Figure 5 as a parameter. By turning it into an XML DOM, it allows commands that will take XPath parameters for retrieving various style elements. The line of code below retrieves a hexadecimal color from the XML DOM using XPath:
// Get color using xPath
var color = cssXmlDom.selectSingleNode(xPath);
The configuration list data will be retrieved by a jQuery AJAX web service query. After retrieveing the list data, the CSS data, and the configuration list data, an XSLT file generates the XML for the flash chart. An XML schema validates the format of the XML, as the code in Figure 6 shows.
//load schema
schemaID = new ActiveXObject("MSXML2.XMLSchemaCache.6.0");
schemaID.add("xs:", schemaFile);
chartXmlDomID = new ActiveXObject('Msxml2.DOMDocument.6.0');
// Assign the schema cache to the DOMDocument's
// schemas collection.
chartXmlDomID.schemas = schemaID;
chartXmlDomID.async = false;
chartXmlDomID.validateOnParse = true;
chartXmlDomID.resolveExternals = true;
chartXmlDomID.loadXML(flashXML);
Finally, the validated XML is ready to be rendered in the HTML div that's defined in the CEWP.
chartID.setDataXML(flashXML);
chartID.render(divID);
When all the code is put together, you should see a final dynamic graph, such as the one in Figure 7.
Tying It all Together
The first thing that any user will notice about this solution is the visual appeal of the dynamic and animated charts. The flash animation and the 3D bar charts create a definite WOW factor that is missing from list-based data presentations.
The true beauty is how easily the chart can change without modifying any code. There is no software cost for these powerful charts.
A business can generate vital analytics without having to deploy an expensive BI solution. The business's internal development team has an incredible amount of flexibility and control in the presentation of the chart. The lack of Server side coding allows this to be deployed in tightly secured hosted environments such as BPOS. The consumption of web services and RSS feeds allows data to be easily charted from an infinite number of data sources hosted in the cloud. The possibilities are endless.
