[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

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!
0
Isaac
Asked:
Isaac
  • 2
1 Solution
 
Ioannis ParaskevopoulosCommented:
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
 
IsaacSharePoint Client Side DeveloperAuthor Commented:
That did not work.

The error I get is 'Object expected'
0
 
Rainer JeschorCommented:
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
 
IsaacSharePoint Client Side DeveloperAuthor Commented:
Great!  Thanks!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now