SharePoint Visifire Charting Extended – Rollup Charting
October 8, 2010 Leave a comment
Having followed Doug Perkes’ post on creating charts from SharePoint Custom Lists, I had a need to extend the scripts functionality to create charts that rolled up content from numerous lists of the same content type that were dispersed around a site collection. If you haven’t looked into Visifire charting, it’s a great way to add some visual content to dashboard pages using Silverlight, JavaScript, and JQuery.
Creating rollups with the content from multiple lists with the same content type is relatively straight forward using the spdatasource control and DataSourceMode=”CrossList”, see the msdn blog post for help. This will give your users access to all the of list items in lists across your site collection with the same content type. For example, you could create a roll up of all the ‘tasks due today’ from all tasks lists across your sites and subsites. But what about putting in a nice chart to add some visual appeal? The spdatasource didn’t work with the visifire charting requirements so that’s why I looked into Doug’s post and modifying Doug’s scripts to suit.
Here are the steps to set this up:
1. Create a custom list with the following fields: urlValue(I just renamed the title field for this one), listID, and listView all of type ‘Single line of text’. I use this list to store all of the lists that I want to include in the chart(s). So if I want to include four lists I would have four list items in this list like the following:
| Site | ListID | ViewID |
| SiteA | {E824AFE9-4431-4969-A39E-A902C42F7F2C} | {A8319615-60B3-40A5-929A-308BED8D954A} |
| SiteB | {5C4C127D-0A68-43CA-94AC-8D452D9B4DD5} | {B77AC8B2-44E9-486D-B9A7-345E7D6534FA} |
| SiteC | {0BD78163-9EFD-4278-BB21-F15768E9D235} | {8F0C08CD-E227-4388-A071-59DDE78132AA} |
| SiteD | {226C61C6-6EF5-4F20-98FB-F783417E1EC3} | {99DB0A21-4DBF-455B-B233-E277955E9798} |
The main idea here is that we will use JQuery to retrieve this list (master list) from our site and use it to retrieve the content lists for our charts. This allows you to add/remove lists to the charts without having to modify the script in the future, we can do that in SharePoint or even have users do that for you.
2. Set up a Web Part page with the javascript references and placeholders for the charts just like in Doug’s post. You would post the following code in the Content Editor Web Part:
<script type="text/javascript" src="/ssrc/visifire.js"></script> <script type="text/javascript" src="/ssrc/My.Custom.Chart.js"></script> <h3>Visifire Chart</h3> <table> <tr> <td> </td> <div id="<span class=">VisifireChart1"></div> <td> </td> <div id="<span class=">VisifireChart2"></div> </tr> <tr> <td> </td> <div id="<span class=">VisifireChart3"></div> <td> </td> <div id="<span class=">VisifireChart4"></div> </tr> </table>
Even better would be to have this saved in a html file located in a Script library somewhere on your site and have the CEWP reference it by url. Also, I have the JQuery reference in my masterpage, but if you don’t you will need to add the reference to the JQuery library. Something like:
<script type="text/javascript" src="/ssrc/jquery-1.4.2.min.js"></script>
3. Next we’ll copy the following code into a text file and name it ‘My.Custom.Chart.js’ and save it to your script library (see comments in the code to understand what’s happening):
$(document).ready(
function() {
/*Initialize some arrays that we'll use to hold the url, listID,
and listView values and the resulting xml after the ajax call*/
var listNameArray = new Array();
var listViewArray = new Array();
var urlArray = new Array();
var xDataArray = new Array();
/*This sets the master list. This is the SharePoint list that contains
list items with the properties of url, listID, and listView.*/
var masterList = '{FD7DBAF7-C111-401F-97DE-5C9991CAE62E}';
var masterView = '{E8BEE90D-D97D-4A76-8836-1D2ADEDFC306}';
var masterUrl = '';
/*Here we make our first ajax call to retrieve the SharePoint list data
from our master list. We also read each line and store the resulting properties
in our arrays.*/
var xData1 = soapCall(masterList, masterView, masterUrl);
i=0;
listData = $(xData1.responseXML);
$(listData).find("z\\:row").each(function() {
urlArray[i] = $(this).attr('ows_LinkTitle');
listNameArray[i] = $(this).attr('ows_ListID');
listViewArray[i] = $(this).attr('ows_ViewID');
i++;
});
/*Next we loop through our listNameArray array to make ajax calls for all of the
lists in our SharePoint master list. These objects are stored in the xDataArray.*/
for(var i=0; i
xDataArray[i] = soapCall(listNameArray[i],listViewArray[i],urlArray[i]);
}
/*Now that we have all of the list data in our xDataArray, we process the results
and generate the charts by calling the processResult function.*/
processResult(xDataArray);
}
);
function soapCall(list, view, urlValue) {
var listData = '';
var listStatus = '';
/*This SOAP request is almost the same as in Doug's Post, but since we're making
several SOAP requests to retrieve several SharePoint Lists, it follows to make it a
function. We pass in the listID, viewID, and url values for our master list on its
first call. Then we repeatedly call this function with our arrays containing the
parameters for our data lists. Each time we return the data object containing the xml.*/
var soapEnv =
"<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/'> \
<soapenv:Body> \
<GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> \
<listName>" + list + "</listName> \
<viewName>" + view + "</viewName> \
<viewFields /> \
</GetListItems> \
soapenv:Body> \
soapenv:Envelope>";
$.ajax({
url: urlValue + "/_vti_bin/lists.asmx",
type: "POST",
dataType: "xml",
data: soapEnv,
async: false,
complete: function(data) {
listData = data;
listStatus = status;
},
error: function(request,error) {
},
contentType: "text/xml; charset=\"utf-8\""
});
return listData;
}
function processResult(xDataArray) {
/*This function receives an array of objects containing our SharePoint lists in xml
and we generate our charts with it. Instead of passing in an object, as in Doug's Post,
we are passing in an array of objects so we need to iterate through it and generate another
array with the list xml data. */
var listArray = new Array();
for(var i=0; i
listArray[i]= $(xDataArray[i].responseXML);
}
var dataPoints = '';
var chartXmlString = '';
/*We use the same functions to Group the datapoints, generate the xml for visifire, and then
render the chart(s). Again each have been modified slightly to recieve an array instead of a
single listData element. Some additional fields are added which are explained in the functions.*/
dataPoints = getGroupedDataPoints(listArray, 'ows_Integration_x0020_Site', 'Count', null, false, '#YValue');
chartXmlString = getXaml('Site Summary', 'Pie', dataPoints, 280, 280, 'Center', 'Bottom');
renderChart(chartXmlString, 'VisifireChart1', 280, 280);
dataPoints = getGroupedDataPoints(listArray, 'ows_Category', 'Count', null, false, '#YValue');
chartXmlString = getXaml('Issue Summary', 'Pie', dataPoints, 280, 280, 'Center', 'Bottom');
renderChart(chartXmlString, 'VisifireChart2', 280, 280);
dataPoints = getGroupedDataPoints(listArray, 'ows_IssueStatus', 'Count', null, false, '#Percentage%');
chartXmlString = getXaml('Status Summary', 'Pie', dataPoints, 280, 280, 'Center', 'Bottom');
renderChart(chartXmlString, 'VisifireChart3', 280, 280);
dataPoints = getGroupedDataPoints(listArray, 'ows_Assigned', 'Count', 'ows_Assigned', false, '#YValue');
chartXmlString = getXaml('Issue Assignment', 'Pie', dataPoints, 280, 280, 'Center', 'Bottom');
renderChart(chartXmlString, 'VisifireChart4', 280, 280);
}
function renderChart(xaml, divId, sizeWidth, sizeHeight) {
/*Here we additionally take in the width and height of the chare to be generated. This allows us
to generate charts of different size.*/
var vChart = new Visifire("/Scripts/SL.Visifire.Charts.xap", sizeWidth, sizeHeight );
vChart.setWindowlessState(true);
vChart.setDataXml(xaml);
vChart.render(divId);
}
function getGroupedDataPoints(listArray, groupByField, groupType, valueField, insertTotal, labelType) {
var valueArray = new Array();
var keyArray = new Array();
/*Here we loop through our array of SharePoint list xml and group the information for the charts.*/
for(var i=0; i
$(listArray[i]).find("z\\:row").each(function() {
var myKey = $(this).attr(groupByField);
/*If one of our data list items fields contain a null value we can just skip to the next xml list item.
This was a problem if you don't require a field to contain information is SharePoint and you want to
chart that field.*/
if (myKey != null) {
if (jQuery.inArray(myKey, keyArray) == -1) {keyArray[keyArray.length] = myKey; valueArray[keyArray.length] = 0;}
switch(groupType) {
case 'Count':
if (valueArray[jQuery.inArray(myKey, keyArray)] == null || isNaN(valueArray[jQuery.inArray(myKey, keyArray)])) {valueArray[jQuery.inArray(myKey, keyArray)] = 0;}
valueArray[jQuery.inArray(myKey, keyArray)]++;
break;
case 'Sum':
var val = $(this).attr(valueField);
if (val != null && !isNaN(val)) {
if (valueArray[jQuery.inArray(myKey, keyArray)] == null) { valueArray[jQuery.inArray(myKey, keyArray)] = 0; }
valueArray[jQuery.inArray(myKey, keyArray)]+= parseFloat(val);}
break;
break;
}}
});
}
var dataPoints = "";
var totalValue = 0;
/*Here we are looping through to create a the datapoints for visifire. We've also passed into the function the labelType.*/
for(var i=0; i<keyArray.length; i++) {
var label = keyArray[i].replace("&", "&");
dataPoints += '';
totalValue += valueArray[i];
}
if (insertTotal) {
dataPoints = '' + dataPoints;
}
return dataPoints;
}
function getXaml(title, chartType, dataPoints, sizeWidth, sizeHeight, legHAlign, legVAlign) {
/*Additionally we passed in the width and height of the charts and legend alignment values.*/
var chartXmlString = ''
+'xmlns:vc="clr-namespace:Visifire.Charts;assembly=SLVisifire.Charts" Width="' + sizeWidth + '" Height="' + sizeHeight + '" BorderThickness="1" BorderBrush="Silver" Theme="Theme1" Watermark="True" SmartLabelEnabled="True">'
+'<vc:Chart.Titles>'
+'<vc:Title Text="' + title + '" HorizontalAlignment="Left" FontSize="16" FontFamily="Trebuchet MS" FontWeight="Bold" />'
+'</vc:Chart.Titles>'
+'<vc:Chart.Legends>'
+'HorizontalAlignment="' + legHAlign + '" VerticalAlignment="' + legVAlign + '" />'
+'</vc:Chart.Legends>'
+'<vc:Chart.AxesY>'
+'<vc:Axis AxisType="Primary" />'
+'</vc:Chart.AxesY>'
+'<vc:Chart.Series>'
+'DataSeries ShowInLegend="True" RenderAs="' + chartType + '" AxisYType="Primary" >'
+'<vc:DataSeries.DataPoints>'
+ dataPoints
+'</vc:DataSeries.DataPoints>'
+'</vc:DataSeries>'
+'</vc:Chart.Series>'
+'</vc:Chart>';
return chartXmlString;
}
4. You’ll have to follow Doug’s post to determine your field names that you’ll use in the charts with Fiddler. Also pay close attention to the urlValue you store in the Sharepoint master list, it’s used in the SOAP request and has “/_vti_bin/lists.asmx” appended to it to request the SharePoint lists web service for each of your data lists.
Marc's SharePoint Notes 