Solved

Google Charts (Stacked Bar Chart) and SharePoint 2007

Posted on 2013-05-17
4
633 Views
Last Modified: 2013-05-19
I am trying to crate a stacked bar chart using google charts.  It's supposed to look like this http://jsfiddle.net/GTpgA/1/


Here's my code and i'm pulling data from a sharepoint list.  After a lot of debugging and alerts, the error is somewhere here...

      data.addRow('FY12',dataValues[0][1], null, null);      
      data.addRow('FY12',null, dataValues[0][2], dataValues[0][3]);      
      data.addRow(null,null,null,null);
      data.addRow('FY13',dataValues[1][1], null, null);      
      data.addRow('FY13',null, dataValues[1][2], dataValues[1][3]);      


Here's the full code.....

// Build next six charts same style
function buildPlannedActual(Charts) {

    var chartTitle = "";
    var projectName = "";
    var beginFY = "";
    var endFY = "";
	var LastFY = "";
	var LastFY = "";

    // get max min values
    var maxVAxis = 50;
    var minVAxis = 0;
    var step = 5;
    var countVAxis = (maxVAxis - minVAxis) / step + 1;

    	  var dataValues = [
	  ['FY 12', 0, 0, 0],           
      ['FY 13', 0, 0, 0]     
     ];	


    var sourceType = getSource(Charts);
	var varSource;
	 
	 switch (sourceType) {
		case "SAC":
		varSource = "SAC";
		break;
		case "JAC":
		varSource = "JAC";
		break;
		case "RAC":
		varSource = "RAC";
		break;
}
  
    $().SPServices({
        operation: "GetListItems",
        listName: "ChartConfigValues",
        async: false,
        CAMLQuery: "<Query><Where><Eq><FieldRef Name='Charts' /><Value Type='Lookup'>" + Charts + "</Value></Eq></Where></Query>",
        completefunc: function (xData, Status) {
            var numRecords = parseInt($(xData.responseXML).SPFilterNode('rs:data').attr('ItemCount'), 10);
            alert("numRecords: "+numRecords);
            if (numRecords > 0) {
                $(xData.responseXML).SPFilterNode('z:row').each(function () {
                    projectName = formatStr($(this).attr("ows_ProjectName"));
                    var fiscalYear = formatStr($(this).attr("ows_FiscalYear"));
                    chartTitle = $(this).attr("ows_Title") + fiscalYear;
                    var prevYear = fiscalYear - 1;
                    beginFY = prevYear + "-10-01"; 
                    endFY = fiscalYear + "-09-30"; 
					LastFY = prevYear -1 + "-10-01"; 
                    LastFY = fiscalYear -1 + "-09-30";
                });
            }

        }
    });

	 $().SPServices({
		operation: "GetListItems",
		async: false,
		listName: "StartingSites",
		CAMLViewFields: "<ViewFields><FieldRef Name='"+varSource+"PlannedDate'></FieldRef></ViewFields>",     
		CAMLQuery: "<Query><Where><And><Eq><FieldRef Name='ProjectName' /><Value Type='Lookup'>"+ projectName + "</Value></Eq><And><Geq><FieldRef Name='"+varSource+"PlannedDate' /><Value Type='DateTime'>" + beginFY + "</Value></Geq><Leq><FieldRef Name='"+varSource+"PlannedDate' /><Value Type='DateTime'>" + endFY + "</Value></Leq></And></And></Where></Query>",
		completefunc: function (xData, Status) {			
			var numRecords = parseInt($(xData.responseXML).SPFilterNode('rs:data').attr('ItemCount'), 10);
			alert("Planned: "+numRecords);
			if (numRecords > 0)
			{
				// Loop through each item 
				$(xData.responseXML).SPFilterNode('z:row').each(function() {
					// Get Sum for Rc Projected Site Months
					dataValues[0][1] ++;
				});
			}

		}		
	});
	
	 $().SPServices({
		operation: "GetListItems",
		async: false,
		listName: "CurrentSites",
		CAMLViewFields: "<ViewFields><FieldRef Name='"+varSource+"CertainDate'></FieldRef></ViewFields>",     
		CAMLQuery: "<Query><Where><And><Eq><FieldRef Name='ProjectName' /><Value Type='Lookup'>"+ projectName + "</Value></Eq><And><Geq><FieldRef Name='"+varSource+"CertainDate' /><Value Type='DateTime'>" + beginFY + "</Value></Geq><Leq><FieldRef Name='"+varSource+"CertainDate' /><Value Type='DateTime'>" + endFY + "</Value></Leq></And></And></Where></Query>",
		completefunc: function (xData, Status) {			
			var numRecords = parseInt($(xData.responseXML).SPFilterNode('rs:data').attr('ItemCount'), 10);
			if (numRecords > 0)
			{
				$(xData.responseXML).SPFilterNode('z:row').each(function() {
					dataValues[0][2] ++;								
				});
			}

		}		
	});	
	  
$().SPServices({
	operation: "GetListItems",
	async: false,
	listName: "CurrentSites",
	CAMLViewFields: "<ViewFields><FieldRef Name='"+varSource+"CertainDate'></FieldRef></ViewFields>",
	CAMLQuery: "<Query><Where><And><Eq><FieldRef Name='ProjectName' /><Value Type='Lookup'>" + projectName + "</Value></Eq><And><Geq><FieldRef Name='"+varSource+"CertainDate' /><Value Type='DateTime'>" + LastFY + "</Value></Geq><Leq><FieldRef Name='"+varSource+"CertainDate' /><Value Type='DateTime'>" + LastFY + "</Value></Leq><And><IsNull><FieldRef Name='"+varSource+"CertainDate' /></IsNull></And></And></And></Where></Query>",
	completefunc: function (xData, Status) {
		var numRecords = parseInt($(xData.responseXML).SPFilterNode('rs:data').attr('ItemCount'), 10);
		if (numRecords > 0) {
			$(xData.responseXML).SPFilterNode('z:row').each(function () {
			dataValues[0][3] ++;	
			});
		}
	}
});

	 $().SPServices({
		operation: "GetListItems",
		async: false,
		listName: "StartingSites",
		CAMLViewFields: "<ViewFields><FieldRef Name='"+varSource+"PlannedDate'></FieldRef></ViewFields>",     
		CAMLQuery: "<Query><Where><And><Eq><FieldRef Name='ProjectName' /><Value Type='Lookup'>"+ projectName + "</Value></Eq><And><Geq><FieldRef Name='"+varSource+"PlannedDate' /><Value Type='DateTime'>" + LastFY + "</Value></Geq><Leq><FieldRef Name='"+varSource+"PlannedDate' /><Value Type='DateTime'>" + LastFY + "</Value></Leq></And></And></Where></Query>",
		completefunc: function (xData, Status) {			
			var numRecords = parseInt($(xData.responseXML).SPFilterNode('rs:data').attr('ItemCount'), 10);
			alert("Planned: "+numRecords);
			if (numRecords > 0)
			{
				// Loop through each item 
				$(xData.responseXML).SPFilterNode('z:row').each(function() {
					dataValues[1][1] ++;
				});
			}

		}		
	});
	
	 $().SPServices({
		operation: "GetListItems",
		async: false,
		listName: "CurrentSites",
		CAMLViewFields: "<ViewFields><FieldRef Name='"+varSource+"CertainDate'></FieldRef></ViewFields>",     
		CAMLQuery: "<Query><Where><And><Eq><FieldRef Name='ProjectName' /><Value Type='Lookup'>"+ projectName + "</Value></Eq><And><Geq><FieldRef Name='"+varSource+"CertainDate' /><Value Type='DateTime'>" + LastFY + "</Value></Geq><Leq><FieldRef Name='"+varSource+"CertainDate' /><Value Type='DateTime'>" + LastFY + "</Value></Leq></And></And></Where></Query>",
		completefunc: function (xData, Status) {			
			var numRecords = parseInt($(xData.responseXML).SPFilterNode('rs:data').attr('ItemCount'), 10);
			if (numRecords > 0)
			{
				// Loop through each item 
				$(xData.responseXML).SPFilterNode('z:row').each(function() {
					
					// Get Sum of Actual Site Months
					dataValues[1][2] ++;								
				});
			}

		}		
	});	

	  // Get ER_Site_Current list items filtered by Program Name and FY dates (Anticipated-still need to figure this out) (FY12)
		$().SPServices({
		operation: "GetListItems",
		async: false,
		listName: "CurrentSites",
		CAMLViewFields: "<ViewFields><FieldRef Name='"+varSource+"CertainDate'></FieldRef></ViewFields>",
		CAMLQuery: "<Query><Where><And><Eq><FieldRef Name='ProjectName' /><Value Type='Lookup'>" + projectName + "</Value></Eq><And><Geq><FieldRef Name='"+varSource+"CertainDate' /><Value Type='DateTime'>" + LastFY + "</Value></Geq><Leq><FieldRef Name='"+varSource+"CertainDate' /><Value Type='DateTime'>" + LastFY + "</Value></Leq><And><IsNull><FieldRef Name='"+varSource+"CertainDate' /></IsNull></And></And></And></Where></Query>",
		completefunc: function (xData, Status) {
			var numRecords = parseInt($(xData.responseXML).SPFilterNode('rs:data').attr('ItemCount'), 10);
			if (numRecords > 0) {
				$(xData.responseXML).SPFilterNode('z:row').each(function () {	
				dataValues[1][3] ++;	
				});
			}
		}
	});		

 // Initialize data object to hold chart data
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Name');
    data.addColumn('number', 'Planned RIP');
    data.addColumn('number', 'Actual RIP');
    data.addColumn('number', 'Anticipated RIP');
    
//NOT TO SELF: CREATE QUERIES FOR FY13 ALSO		
	data.addRow('FY12',dataValues[0][1], null, null);	
	data.addRow('FY12',null, dataValues[0][2], dataValues[0][3]);	
	data.addRow{null,null,null,null);
	data.addRow('FY13',dataValues[1][1], null, null);	
	data.addRow('FY13',null, dataValues[1][3], dataValues[1][3]);	
		

   /* $.each(dataValues, function (index, value) {																											
        data.addRow([dataValues[index][0], Number(dataValues[index][1]), Number(dataValues[index][2]), Number(dataValues[index][3])]);
    });    */

    var options = {
        title: chartTitle,
        titleTextStyle: { color: 'black', fontName: '"Arial"', fontSize: '15' },
        legend: { textStyle: { color: 'black', fontName: '"Arial"', fontSize: '12' } },
        tooltip: { textStyle: { fontName: '"Arial"' } },
        colors: ["#FFFF00", "green", "blue", "#FF8000", "Red"],
        backgroundColor: {
            fill: '#D8D8D8',
            strokeWidth: '0'
        },
        vAxis: {
            title: "Number of Sites", format: '#',
            titleTextStyle: { color: 'black', fontName: '"Arial"', fontSize: '16' },
            gridlines: { count: countVAxis, color: 'black' },
            minValue: minVAxis,
            maxValue: maxVAxis
        },
        hAxis: { title: "Month", titleTextStyle: { color: 'black', fontName: '"Arial"', fontSize: '16' } },
        seriesType: "bars",
        isStacked: true,    
    };
    var chart = new google.visualization.ComboChart(document.getElementById(Charts));
    chart.draw(data, options);

}

Open in new window



Any help would be great.  Thanks!
0
Comment
Question by:Isaac
  • 2
4 Comments
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39177173
What is your error?
What if you try it this way:

var val1 = dataValues[0][1];
var val2 = dataValues[0][2];
var val3 = dataValues[0][3];

data.addRow('FY12', val1, null, null);
data.addRow('FY12', null, val2, val3);

val1 = dataValues[1][1];
val2 = dataValues[1][2];
val3 = dataValues[1][3];

data.addRow('FY13', val1, null, null);
data.addRow('FY13', null, val2, val3);

Open in new window


Giannis
0
 
LVL 5

Author Comment

by:Isaac
ID: 39177586
That did not work.

The error I get is 'Object expected'
0
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 500 total points
ID: 39178448
Hi,
please try this:
	data.addRow(['FY12',dataValues[0][1], null, null]);	
	data.addRow(['FY12',null, dataValues[0][2], dataValues[0][3]]);	
	data.addRow([null,null,null,null]);
	data.addRow(['FY13',dataValues[1][1], null, null]);	
	data.addRow(['FY13',null, dataValues[1][3], dataValues[1][3]]);	

Open in new window

In one line you had a opening { than a (
Second, the addRow expects an array of values, not 4 parameters.
HTH
Rainer
0
 
LVL 5

Author Closing Comment

by:Isaac
ID: 39179528
Great!  Thanks!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

There are a couple ways to attach a JavaScript function to dynamically created elements. You can make a new script for each element as it’s created or you can use delegation. Delegation allows a single script that is added at page creation to mat…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now