Building Dynamic BI Using SharePoint List Data

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:

<script src="Charting/FusionCharts.js"></script> 

<script src="Charting/jquery-1.3.2.min.js"></script>

<script src="Charting/jquery.transform.js"></script>

<script src="Charting/jquery.generatechart.js"></script>

<div align="center">Tasks By Status Per Consultant</div>

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. 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.

 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. 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 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.