Solved

Google Charts (Stacked Bar Chart) and SharePoint 2007

Posted on 2013-05-17
4
641 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
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)

786 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