Horizontal bar graph with SPGetListItem

Dec 19, 2011 at 5:55 PM

Hi,

I was wondering if anyone has experience with creating a horizontal bar graph from the GetListItem operation.  

In a discussion board list, there is a 'Replies' (internal name: ItemChildCount) column.  Instead of showing the numerical value, I would like to show an "activity bar".  The bar increases as the number of replies increases.  Any thoughts? 

Below is what I currently have scripted:

<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script>
<script type="text/javascript" language="javascript"  src="../SiteAssets/Scripts/jquery.SPServices-0.6.2.min.js"></script>
<script language="javascript" type="text/javascript">
$(document).ready(function() {
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "Idea Board",
    CAMLViewFields: "<ViewFields Properties='True' />",   
    CAMLRowLimit: 5, 
	completefunc: function (xData, Status) {
      $(xData.responseXML).find("[nodeName='z:row']").each(function() {
        var activity =  $(this).attr("ows_ItemChildCount").split(";#")[1] + "<br>" + "<br>";
        $("#ideaboardactivityUL").append(activity);
      });
    }
  });
});
</script>
<ul id="ideaboardactivityUL"/>

Coordinator
Dec 19, 2011 at 6:01 PM

I've done this before and have an example on my demo site:
http://www.sympraxisconsulting.com/Demos/Demo%20Pages/DVWPBarChart.aspx

The example is based on doing the work in a DVWP, but you could certainly do exactly the same sort of thing in script.

BTW, read my "Important Note" on the home page about the

.find("[nodeName='z:row']"

notation.

M.

Dec 19, 2011 at 6:54 PM

Hi Marc,

I am not following the code in DVWP.  Is there an example in script?   

Coordinator
Dec 19, 2011 at 6:55 PM

Nope, that's the only example I have. you might want to look at some of the nice graphing jQuery plugins out there. I haven't used any, but I know that there are some good ones.

M.

Dec 19, 2011 at 8:21 PM

I'm using this one right now in conjunction with SPServices:

http://code.google.com/apis/chart/interactive/docs/gallery/columnchart.html

What you'll find is that every library is a bit different, so the data that you return from GetListItems, you'll need to format it in a way that the library can use.  It all "depends"...  ;-)

Cheers,

Matt

Dec 21, 2011 at 7:31 PM

Matt,

Thanks for the link.  I am not getting any successful displays of the charts when linked with SPServices.  Do you have any examples?  

Thanks

Dec 21, 2011 at 8:20 PM
Edited Apr 13, 2012 at 3:55 AM

asf

Dec 21, 2011 at 10:06 PM
Thanks Matt.  I've worked with the google charts and got a better understanding.
But I still can not get the SPservice to link up with my chart. Could you try to debug this for me if you have chance?
It seems like you have had success. I would greatly appreciate your help!

<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script>
<script type="text/javascript" language="javascript"  src="../SiteAssets/Scripts/jquery.SPServices-0.6.2.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script language="javascript" type="text/javascript">

      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn('number', 'Replies');
        data.addRows(replies); 
        
        var chart = new google.visualization.BarChart(document.getElementById('chart_div'));
        chart.draw(data, {width: 215, height: 240,
               		colors: ['#0a2463'],
               	});     
        }

$(document).ready(function() {
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "Idea Board",
    CAMLViewFields: "<ViewFields Properties='True' />",   
    CAMLRowLimit: 5, 
	completefunc: function (xData, Status) {
      $(xData.responseXML).find("[nodeName='z:row']").each(function() {
      
    	var replies = new String($(this).attr("ows_ItemChildCount").split(";#")[1]);
	        
        var activity =  replies + "<br>" + "<br>";
        $("#ideaboardactivityUL").append(activity);
      });
    }
  });
});

</script>
<ul id="ideaboardactivityUL"/>
<div id="chart_div"></div>

Dec 21, 2011 at 11:20 PM

Looking at the script, it appears you are running into a timing issue.  You have the SPService call wrapped in $(doc).ready.  That will fire a lot later than the Google stuff.  You are trying to pass "replies" as your dataset to the chart.  At the time you are making the call to the Google chart, "replies" hasn't been defined at all.  

From your script it's not very clear what you are trying to graph either.  It appears you are just appending html to the page.  The Google bar chart accepts an array of arrays as it's dataset.  You'll need to build up that in your completefunc and then make a call to a function that generates the chart.  You'll see in my example how I get around Google's implementation of using a callback function. I just simply nested it inside my function that I called.

 

Cheers,

Matt

Dec 22, 2011 at 3:11 PM

I have used google charts to create a pie chart...it is not as extensive as what you are trying to do...but here is an example that works for me.

 

Here is my code that sets up the graph:

The first thing it does is call getSurveyData which uses SPServices to create the array.

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
  <script type="text/javascript">
      
 var chartData = new Array();
 var chartData = getSurveyData();
 
    google.load("visualization", "1", {packages:["corechart"]});
 google.setOnLoadCallback(drawChart);     
 function drawChart() {       
 var data = new google.visualization.DataTable();
 data.addColumn('string', 'Survey');       
 data.addColumn('number', 'Counts');        

data.addRows(chartData);
   
 var options = {
 width: 250,
 height: 200,
 legend:{position:'top'}, 
 chartArea:{left:10,top:30,width:"90%",height:"75%"} ,
 is3D:'true',      
 title: ''
   };

 var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
 chart.draw(data, options);      }   

      
  </script>
 
  <div id="chart_div"></div>

 

Now here is the SPServices utility:

this function basically does a select count(*) groupby...which I had to build by myself, since there is no GroupBy in CAML -- SP2007.  then it takes the totals and puts them into an array that is used to Google Charts.

function getSurveyData() {

var query = "<Query><Where>"
          + "<IsNotNull><FieldRef Name='Type_x0020_your_x0020_question_x' />"
          + "</IsNotNull>"
          + "</Where>"
          + "<OrderBy><FieldRef Name='Type_x0020_your_x0020_question_x' Ascending='True'/>"
          + "</OrderBy>"
          + "</Query>";

var questions = new Array();         
var count = new Array();
var i=0;
var respCount = 0;
var lastVal = '';
 $().SPServices({
         operation: "GetListItems",
         listName: "NYRes",
         async: false,
         CAMLQuery: query,
    //     CAMLViewFields: "<ViewFields Properties='True'/>",
         completefunc: function (xData, Status) {
//           alert(xData.responseText);
   $(xData.responseXML).SPFilterNode("z:row").each(function() {
               rowID = $(this).attr("ows_ID")
               response = $(this).attr("ows_Type_x0020_your_x0020_question_x")
               
               if (lastVal  == '') {lastVal = response;}
              
               if (response == lastVal) {             
                respCount = respCount + 1; 
                } else {
                questions[i] = lastVal;
                count[i] = respCount;                
              // reset values (respCount = 1 since we need to count this row
                respCount  = 1;
                i = i + 1;
                lastVal = response      
                }
               
          });  // end of each function
       }  // end of completefunc
  });  // end of SPServices

// provide last value updates
 questions[i] = lastVal;
 count[i] = respCount;

var myData = new Array(i+1);
 for (j=0; j<i+1;j++) {
  myData[j]=new Array(2);
 
  myData[j][0]=questions[j];
  myData[j][1]=count[j];
 
 }
 return myData;

} // end of function getSurveyData

 

Dec 22, 2011 at 8:44 PM

OK i cleaned up my code, but it is still not displaying the chart.  Am I missing something?  Thank you for your help!

<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script>
<script type="text/javascript" language="javascript"  src="../SiteAssets/Scripts/jquery.SPServices-0.6.2.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
  <script type="text/javascript">
      
 var replies = new Array();
 var replies = getReplyData();
 
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn('number', 'Replies');
        data.addRows(replies); 
        
        var chart = new google.visualization.BarChart(document.getElementById('chart_div'));
        chart.draw(data, {width: 215, height: 240,
               		colors: ['#0a2463'],
               	});     
        }
 
</script>
  
  <div id="chart_div"></div>
  
<script type="text/javascript">  
function getReplyData() {

$().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "Idea Board",
    CAMLViewFields: "<ViewFields Properties='True' />",   
    CAMLRowLimit: 5, 
	completefunc: function (xData, Status) {
//           alert(xData.responseText);
   $(xData.responseXML).SPFilterNode("z:row").each(function() {
                              
               var activity =  $(this).attr("ows_ItemChildCount").split(";#")[1]
               
                               
          });  // end of each function
       }  // end of completefunc
  });  // end of SPServices

// provide last value updates
 numberofreplies[i] = activity;
 

var myData = new Array();
  myData[0]=numberofreplies[i];
  
return myData;

}) // end of function getReplyData
</script>
Dec 23, 2011 at 12:47 PM

This script shouldn't work....  There are errors throughout it.  What I would suggest you do is get one portion of this script working and then build on top of it and add more complexity.

Another tip:  .SPFilterNode() was introduced in 0.7 and will not work in previous versions of SPServices.

   $(xData.responseXML).SPFilterNode("z:row").each(function() {
                              
               var activity =  $(this).attr("ows_ItemChildCount").split(";#")[1]
               
                               
          });  // end of each function

should be (if you are using any version lower than 0.7)

$(xData.responseXML).find("[nodeName='z:row']").each(function() {          
               var activity = (this).attr("ows_ItemChildCount").split(";#")[1]                   
});  // end of each function

Also, your variable numberofreplies doesn't seem to have been created anywhere in your script.  Your best bet is going to be opening up the browser's debugging tools and figuring out what exactly is going on with your script.

Cheers,

Matt