Link to home
Start Free TrialLog in
Avatar of Isaac
IsaacFlag for United States of America

asked on

Google Charts (Stacked Bar Chart) and SharePoint 2007

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!
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

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
Avatar of Isaac

ASKER

That did not work.

The error I get is 'Object expected'
ASKER CERTIFIED SOLUTION
Avatar of Rainer Jeschor
Rainer Jeschor
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Isaac

ASKER

Great!  Thanks!