Pages

Tuesday, August 4, 2009

JQuery to Build Fusion Charts for SharePoint List

This is very much similar to my earlier post "Fusion Charts for SharePoint List" where we used JavaScript for extracting the data from SharePoint List.

Here I will be explaining the similar concept using JQuery. As everyone aware that JQuery is getting very famous and even Microsoft announced that JQuery will be available as part of the Visual Studio 2010 with intellisence too.

Most of the times our customers expect to develop the charts with no time for preparing lots of dashboards or metrics. Lets consider that we have a Task List with all the tasks assigned to different users and are at the different status like:

1. Not Started
2. In Progress
3. Completed
4. Deferred
5. Waiting for someone else

At this point of time we may get lots of request for building the charts for this list data. I will be concentrating on generating the Pie Chart for Different Status available in this Task List, to identify how many tasks are there at different statuses. Following is the sample image showing the List Data:



Following are the steps to implement the fusion charts:
1. Download Fusion Charts and upload to a document library. In this case I created a separate document library called FusionCharts where all the fusion charts are uploaded.
2. Come up with the RPC Protocol URL for extracting the data from the task list.
In our case following is the URL:

http://localhost/sites/MOSSDemos/_vti_bin/owssvr.dll?Cmd=Display&Query=Status&List=%7B7C6C7072%2D12B5%2D4B05%2D90A4%2D052160CC8F74%7D&XMLDATA=TRUE


Note that we are extracting only the Status field from the Task List.
And the List id is the Task List Guiid within the SharePoint Site.

3. Download JQuery script from the site and upload to the Shared Documents.

4. Copy the following javascript which uses JQuery Script into the content editor web part:

<!-- JScript from shared documents -->
<script type="text/javascript" src="/Shared Documents/jquery-1.3.2.min.js"></script>

<!-- Div tag for appending the graph -->
<div id="divGraph"></div>

<script type="text/javascript">

// Declare all local variables
var iNotStarted = 0;
var iInProgress = 0;
var iCompleted = 0;
var iDeferred = 0;
var iWaitingonsomeoneelse = 0;
var sObjectData = "";

// Call the function once the document is loaded
$(document).ready(function() {

var sSiteUrl = "http://localhost/sites/MOSSDemos/_vti_bin/owssvr.dll?";

// This Parameter Contains List Id too
var sCmdParameters = "Cmd=Display&Query=Status&List=%7B7C6C7072%2D12B5%2D4B05%2D90A4%2D052160CC8F74%7D&XMLDATA=TRUE"


// Ajax call using a JavaScript
$.ajax({
url: sSiteUrl + sCmdParameters,
type: "GET",
dataType: "xml",
data: "",
complete: processResult,
contentType: "text/xml; charset=\"utf-8\""
});

});



function processResult(xData, status) {

//alert(xData.responseXML.xml);


var NS = '#RowsetSchema'
$(xData.responseXML).children(1).children(1).children().each(function() {

var Status = $(this).attr("ows_Status");

switch (Status) {
case "Not Started":
iNotStarted = iNotStarted + 1; break;
case "In Progress":
iInProgress = iInProgress + 1; break;
case "Completed":
iCompleted = iCompleted + 1; break;
case "Deferred":
iDeferred = iDeferred + 1; break;
case "Waiting on someone else":
iWaitingonsomeoneelse = iWaitingonsomeoneelse + 1; break;
default:
break;
}

});

// Create the Object for the Fusion Chart
// Opening Object Tag
sObjectData = "<object classid=\"clsid:D27CDB6E-AE6D-11cf-96B8-444553540000\" ";
sObjectData += "codebase=\"http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0\" ";
sObjectData += "width=\"400\" ";
sObjectData += "height=\"300\" ";
sObjectData += "id=\"Pie3D\"> ";

// Setting Parameters
sObjectData += "<param name=\"movie\" value=\"http://localhost/sites/MOSSDemos/FusionCharts/FusionCharts/FCF_Pie3D.swf?chartWidth=400&chartHeight=300\"> ";
sObjectData += "<param name=\"FlashVars\" value=\"&dataXML=<graph caption='Tasks By Status Percentage' ";
sObjectData += "xAxisName='Site' yAxisName='Qty by Site' showNames='1' decimalPrecision='1' showPercentageInLabel='1' formatNumberScale='0' ";
sObjectData += "pieYScale='70' pieBorderAlpha='40' pieFillAlpha='80' pieSliceDepth='15'> ";
sObjectData += "<set name='Not Started' value='" + iNotStarted + "' color='#660033' link='' /> ";
sObjectData += "<set name='Completed' value='" + iInProgress + "' color='#3399ff' link='' /> ";
sObjectData += "<set name='In Progress' value='" + iCompleted + "' color='#99cc99' link='' /> ";
sObjectData += "<set name='Deferred' value='" + iDeferred + "' color='#00ff99' link='' /> ";
sObjectData += "<set name='Waiting for someone else' value='" + iWaitingonsomeoneelse + "' color='#000033' link='' /> ";
sObjectData += "</graph>\"> ";
sObjectData += "<param name=\"quality\" value=\"high\"> ";
sObjectData += "</object> ";


$("#divGraph").append(sObjectData);

}

</script>

Following are the lines to be changed in the script:
A. Location of the FCF_Pie3D.swf file within the "object" tag.
B. Site Url in the variable "sSiteUrl".
C. Task List Id in the variable "sCmdParameters".
D. Path of the JScript (Assumed it is available in the Shared Documents)

With this, A Pie chart representations the Task Data within the SharePoint List as shown below:

23 comments:

Alex said...

Hi,

It is possible to filter out the results. I have a list of status' that is 3500 lines long. I would like to split it up into a stacked 3d column chart split up by another category called "Module."

Doing this only gives me a long long list of status':
http://siteURL/_vti_bin/owssvr.dll?Cmd=Display&Query=Pass%5fx002f%5fFail&List=%7BE12316E2%2D6E38%2D4161%2DB022%2D9B16428B2211%7D&XMLDATA=TRUE

Sridhar Babu Kolapalli said...

Hi Alex,

We can filter the values in different ways as described below:

1. Creating a view for a module and providing the view id(View=guid of the view) as a parameter in the query string (we should not pass the query parameter).

2. Instead of using owssvr.dll, we can go with the web services (lists.asmx) for the corresponding list where we can pass the query as part of the soap request.

You can go with any of the above approaches which we implemented in our projects too...

out on a limb said...

Hi,

Great solution. I've been looking for something like this for a long time.

Can you advise how / whether it is possible to display two CEWPs with these charts on the same web part page at the same time? I've tried various methods but always end with one of the CEWPs being empty. If I do manage to display two charts, the second is always a duplicate of the first.

Also, I cant get the &View=GUID option to have any effect. I'm picking up the GUID from the View Settings URL and including in the Display parameters, but all I get returned is an empty document.

var sCmdParameters = "Cmd=Display&List=%7BBA5C80E3%2D1150%2D43BD%2DBAFE%2D528A6F83AD91%7D&View=%7BE67D1A75%2D74CA%2D42AB%2DBBD3%2D68930A99795B%7D&XMLDATA=TRUE" ;

Am I missing something?

Thanks in advance

Ben

Sridhar Babu Kolapalli said...

Hi Ben,

We can have multiple Content Editor Web Parts. Here we are building the graph (<object>) dynamically and assigning as inner HTML to the div tag. This means that if we have mutiple graphs we have to create multiple div tags with different names in the CEWPs.

I tried the similar solution in one of my project where we are displaying mutiple diffrent kinds charts (Fusion Charts).

Comming to the second question, View=GUID "Specifies the GUID of the list view to use. If null, the default view is used."
Whenever we are passing the Query paramete in the url, view parameter will not be considered. If the Query parameter is not specified then request will be considered with specified view (guid) or a default view.

You can get more info wrt Display method in the Microsoft Site @
http://msdn.microsoft.com/en-us/library/ms416599.aspx

Steven said...

We have really found your thoughts very helpfull, but we are trying how to get data from a list created by Infopath. We have done this with the Switch(case) example you provided. But given our limited experience we do not know how to get data from multiple columns to build a multiseries bar chart. Do you have any examples or guidance. We have zero experience with JQuery

Sridhar Babu Kolapalli said...

HI Alex,

After getting the complete list of Status, yes we can break up the status depending on the module (We have to extract both Status and Modile from the List). This has to be implemented in the javascript logic before providing as input to the fusion chart.

Sridhar Babu Kolapalli said...

Hi Steven,

Thanks for your comments. Yes we can use the fields in the infopath for these charts too.

You have to publish all the list of required fields as a extended properties to the list created using InfoPath form.

Once fields are available as extended properties you can follow the approach mention in the blog to generate the charts.

Please let me know if you need any further assistance, we don't need to be expert in JQuery. With the steps mentioned here are enough for generating the charts.

Kaiz0ku ChaGo said...

Hi,

Thanks, this was very helpful to come up with fusioncharts. However I have noticed that if I don't use the &Query=SomeField argument then the xml output I will get is not always updated.

Is there any walkaround?

Thanks

David.

Kenny said...

I having issues with the data not updating without me loading the RPC link in another window. Is there a known reason for this?

Chris said...

Can this code be modified for the Gantt chart?

Sridhar Babu Kolapalli said...

Hi Chris,

Definitely we can modify this code to get the Gantt Chat.

Before modifying this please check whether Gantt Chat suffice your requirements...
We can also customize its CSS using a simple javascript and custom CSS styles.

Sridhar Babu Kolapalli said...

I mean Gantt Chat view which is available OOTB...

pavan said...

Hey Shridar,

I like this post and kinda implemented it, but the only issue i have been facing is, it isn't pulling the information/ table contents from the list, instead if i give values directly in the code that you gave, its producing me a nice chart.

I think i have the problem with the list address.. could you please elaborate me on where and how to pull this URL, and

FYI: i am using WSS, not MOSS

bobby said...

Hey, great post...I'm still learning a lot everyday about jquery and javascript.

I'm having some problems though...When I edit the script, nothing happens? I've followed all the steps, and can't seem to figure out why it's not working.

I noticed that the sObjectDate Class ID doesn't change in your tutorial:

sObjectData = "<object classid=\"clsid:D27CDB6E-AE6D-11cf-96B8-444553540000\" ";


Am I supposed to change this? Right now, when I click save in My Source Editor, Nothing happens...Please Help!

-Bobby

Anonymous said...

trhtyj

Anonymous said...

vsdfg

Anonymous said...

fadf

Charlie said...

Great Stuff! Do you have an example of how you can do this with a "Multi-Series Column 3D"?

cvharrison said...

Hi Sridhar!

Great information!

Can this same technique be used to build Column fusion charts?

Thanks!

Anonymous said...

I'm having the same issue as Kenny: "I having issues with the data not updating without me loading the RPC link in another window. Is there a known reason for this?"

Thanks,

Mike

Anonymous said...

Figured it out.. I replaced the method "GET" with "POST" and the chart will refresh when the browser is refreshed.

// Ajax call using a JavaScript
$.ajax({
url: sSiteUrl + sCmdParameters,
type: "POST",
dataType: "xml",
data: "",
complete: processResult,
contentType: "text/xml; charset=\"utf-8\""
});


Mike

Anonymous said...

Hi Sridhar,

I need to access the list using account userid and password, how can i send the login credentials from your code ?

kenzie jones said...

Thanks for the above article. It help me a lot.I discussed your article with my friends also. You did a good job but we have no experience regarding JQuery.

digital signature