Pages

Wednesday, July 22, 2009

Fusion Charts for SharePoint List

Most of the time when ever we have data, end users will expect some kind of reports/charts.

For example when we are using tasks, we may have to provide a pie chart indicating the different status in the pie chart. Or We may have to show a visual indicator for the percentage completion for each task item.

We can create the charts by using any of the tools on MOSS 2007:

1. Excel Services (Excel Web Access)
2. Reporting Services
3. Silver Light, etc

In order to use these features we should have MOSS 2007 License and also some environments there will be restrictions on custom coding. By keeping this points in mind I came up with this Post.

Here we will see how we can utilize the Fusion Charts for the SharePoint List (Task List). Following image shows the sample Task List:



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. Copy the following javascript into the content editor web part:

<script language="javascript">

function fnXmlHttp(AspFileName,strXML)
{
var objXMLHttp= new ActiveXObject("Microsoft.XMLHTTP");
var sResponse;
try
{
objXMLHttp.open("POST",AspFileName,false) //POSTING THE DATA
objXMLHttp.setRequestHeader("Content-Type", "text/xml");
objXMLHttp.send(strXML)
}
catch(e)
{
}
if (parseInt(objXMLHttp.Status) != 200)
{
sResponse = "N,Cannot connect to the Server.";
}
else
{
sResponse=new String(objXMLHttp.responseText);
if (sResponse == "")
{
sResponse = "N,Error at server. Please contact administrator.";
}
}
return sResponse;

}


var sXML;
var bLoad;

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"

// Request the RPC Call
sXML = fnXmlHttp(sSiteUrl + sCmdParameters,"");

// Create the XML Object Instance
var objXML = new ActiveXObject("MSXML2.DOMDocument");

// Load the XML
bLoad = objXML.loadXML(sXML);

// Check whether the XML is loaded into the document
if(bLoad)
{
// Declare all local variables
var iNotStarted = 0;
var iInProgress = 0;
var iCompleted = 0;
var iDeferred = 0;
var iWaitingonsomeoneelse = 0;
var sObjectData;

var objData = objXML.documentElement.childNodes(1);

// Loop through all the variables
for (i=0;i<objData.childNodes.length;i++)
{
var Status = objData.childNodes(i).attributes.getNamedItem("ows_Status").value;

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> ";
document.write(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".

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

22 comments:

JES said...

Hey Sridhar,
Cool application of graphical representation. however I can't get it to work. here are my parameters:
var sSiteUrl = "http://itbu.broadcom.com/erp/90Upgrade/_vti_bin/owssvr.dll?";

// This Parameter Contains List Id too
var sCmdParameters = "Cmd=Display&Query=Status&List%7B1303AC29%2D3783%2D487C%2D869F%2D310798CFA00C%7D&XMLDATA=TRUE"
// Opening Object Tag
sObjectData = http://itbu.broadcom.com/erp/90Upgrade/FusionCharts/Charts/FCF_Pie3D.swf";

Do you see anything wrong? Do you just upload the .swf files? when I put the concatenated URL in the browser it gives me an error. I'd love to use this.

Sridhar Babu Kolapalli said...

Hi,
Thanks for your comment!!!

One thing I am able to identify in the sCmdParameters is that "=" sign is missing immediately after "List" keyword.

Following is the modified sCmdParameters:

var sCmdParameters = "Cmd=Display&Query=Status&List=%7B1303AC29%2D3783%2D487C%2D869F%2D310798CFA00C%7D&XMLDATA=TRUE"

and also make sure that the following url in the browser should return an xml:

http://itbu.broadcom.com/erp/90Upgrade/_vti_bin/owssvr.dll?Cmd=Display&Query=Status&List=%7B1303AC29%2D3783%2D487C%2D869F%2D310798CFA00C%7D&XMLDATA=TRUE

JES said...
This comment has been removed by the author.
JES said...

Hi Sridhar,

Thanks for the update. Still can't get this to work. Do you see anything in the code that could be the problem? The URL is generating XML. Any help would be appreciated.


My complete Script:

function fnXmlHttp(AspFileName,strXML)
{
var objXMLHttp= new ActiveXObject("Microsoft.XMLHTTP");
var sResponse;
try
{
objXMLHttp.open("POST",AspFileName,false) //POSTING THE DATA
objXMLHttp.setRequestHeader("Content-Type", "text/xml");
objXMLHttp.send(strXML)
}
catch(e)
{
}
if (parseInt(objXMLHttp.Status) != 200)
{
sResponse = "N,Cannot connect to the Server.";
}
else
{
sResponse=new String(objXMLHttp.responseText);
if (sResponse == "")
{
sResponse = "N,Error at server. Please contact administrator.";
}
}
return sResponse;

}


var sXML;
var bLoad;

var sSiteUrl = "http://itbu.broadcom.com/erp/90Upgrade/_vti_bin/owssvr.dll?";

// This Parameter Contains List Id too
var sCmdParameters = "Cmd=Display&Query=Status&List=%7B1303AC29%2D3783%2D487C%2D869F%2D310798CFA00C%7D&XMLDATA=TRUE"

// Request the RPC Call
sXML = fnXmlHttp(sSiteUrl + sCmdParameters,"");

// Create the XML Object Instance
var objXML = new ActiveXObject("MSXML2.DOMDocument");

// Load the XML
bLoad = objXML.loadXML(sXML);

// Check whether the XML is loaded into the document
if(bLoad)
{
// Declare all local variables
var iNotStarted = 0;
var iInProgress = 0;
var iCompleted = 0;
var iDeferred = 0;
var iWaitingonsomeoneelse = 0;
var sObjectData;

var objData = objXML.documentElement.childNodes(1);

// Loop through all the variables
for (i=0;i {
var Status = objData.childNodes(i).attributes.getNamedItem("ows_Status").value;

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 = "http://itbu.broadcom.com/erp/90Upgrade/FusionCharts/Charts/FCF_Pie3D.swf";
document.write(sObjectData);
}

JES said...

The sObjectData value got truncated... here is the rest of the string.

/Charts/FCF_Pie3D.swf

Sridhar Babu Kolapalli said...

Hi,
I observed that in the blog sObjectData is not displaying the content. Since it has html tags (object tag) it is not displaying the actual content. I modified the post accordingly by replacing the "<" with "<".

Sorry for the inconvenience caused.

Anonymous said...

Another post about fusioncharts within sharepoint: http://translate.google.com/translate?client=tmpg&hl=es&u=http%3A%2F%2Fsurpoint.blogspot.com%2F2009%2F07%2Fgraficos-dinamicos-en-sharepoint.html&langpair=es|en

Anonymous said...

hi ,
i don't have MOSS
How can i use it on wss

tanks
Vittorio

Sridhar Babu Kolapalli said...

It works for both WSS 3.0 and MOSS. I implemented the same solution on both WSS and MOSS.

Anonymous said...

i 'm sorry but i'm a beginner
can you show me an example ?
i have to take data from a list that i have made previusly
and
what about the line
var = "http://localhost/sites/MOSSDemos/_vti_bin/owssvr.dll?"

vittorio

Anonymous said...

where can i find owssvr.dll?

Sridhar Babu Kolapalli said...

it comes out of the box from sharepoint.

What ever the site you are access you can just append /_vti_bin/owssvr.dll.

Please follow the steps mentioned in the post to extract the data and to show the graphs using fusion charts.

Anonymous said...

Hi
Sridhar

i'm tryng to visualize a custom list with two fields "Dato" and "Valore"
I used the code in the blog but when is getting the field values from the xml structure it does not work may be i have to use a spesific name for the field

i changed the code with this
-----------------------------------

//var Dato = objData.childNodes(i).attributes.getNamedItem("Dato").value;'
//var Valore = objData.childNodes(i).attributes.getNamedItem"("Valore").value; '
"switch(Dato)
//{
case "roma":'
//iRoma = Valore; break;'
---------------------------------
is it correct ?

can you help me ?

tanks

Vittorio

Sridhar Babu Kolapalli said...

Hi Vittorio,

May be you are refering to the incorrect name from the xml structure.

I will recommend first check xml response before parsing the xml for required fields.

You can check the response by using an alert statement as:

// Request the RPC Call
sXML = fnXmlHttp(sSiteUrl + sCmdParameters,"");

alert("xml output" + sXML);

This alert is just immediately after the xmlhttp response. Now you can check in the xml whether the correct names are referd while parsing the xml.

Parsing logic in javascript is already available in the post, please follow that.

Libby said...

After lots of staring and testing, I finally got this to work! Thank you thank you so much!

The main problem I had to work through was realizing that my List's Query Name, "ProjStatus", for this column was different from the DisplayName which was "Status"(I found this by looking at the source code for the list). So I had to rename it in the RPC "Query=ProjStatus&" as well as above the switch where it says "getNamedItem(ows_ProjStatus)". Just make sure you don't change any of the "Status" which are reserved words.

also

"In Progress" and "Completed" are switched on the parameters list.

set name='Completed' value='" + iInProgress + "'

set name='In Progress' value='" + iCompleted + "'

Hope this helps some of yall.

Libby

Anonymous said...

before the questions
let me say thanks for all your support!
with your suggetions i have built a sophisticated dashboard.
Unfortunately now I'm trying to see it from an IPAD and it doesn't work becouse it does not understand the ActiveX
In particular it doesn't unterstand the string

"var objXMLHttp= new ActiveXObject("Microsoft.XMLHTTP"); "

do you know lecterature about
how to build the xmlobject,or another way to pass dinamic parameters ?


best regards

Vittorio

SP Newbie said...

Hey Sridhar,

This article has been a fantastic help but I had wondered how you would make it apply to the other types of FusionCharts such as Column3D. I can only seem to get the Pie2D and Pie3D versions working.

Anonymous said...

I can only seem to get one FusionChart to load on the page with IE8 but with firefox they all load fine.

Each of the FusionCharts is within a CEWP which is set to Content Link the HTML.

Has anyone encountered this and know a fix?

SPNewbie

Jblue said...

I have been trying to use the Gantt Chart from FusionCharts, but no luck. I tested and tried you Pie chart and was able to get it to work. :-)
My question, Do you have a sample code for the Gantt Chart? I can not get it to work...
Thank you

Ahmed said...

Hi Sri, who are the other vendors/competitors for Charts for Fusion? For group by options any workaround other than third party tools?

Anonymous said...

Hi Sridhar,

Nice Article, appriciate a lot !

I was successfully able to generate graphs in IE 7, 8. But the same code is not working in Mozilla. Does this code (object embedding) has any compatiblity issue with Mozilla, as the values are being generated properly, only Graph is not getting displayed.

Witold said...

Hi Sridhar,

thanks a lot for sharing this article.
I have finally managed to get the fusion charts working on-line:)

If I could have one more question - data for fusion chart is calculated only from the actual view(if I have item limit set at 100items then only these 100 items are shown on the chart. If I change the item limit, the statistics will also change accordingly).
Is it possible to change sCmdParameters so that all list content will be a subject of the chart visualisation, no matter what is the default view?
Thanks in advance.