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?
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:
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.)
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 <listName> will be the same regardless of whether the value is the name or the GUID of the list. 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.
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.
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.
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.
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:
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. Finally, the validated XML is ready to be rendered in the HTML div that's defined in the CEWP.
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 buisness'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.