Solved

Google Charts (Stacked Bar Chart) and SharePoint 2007

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
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)
The viewer will learn the basics of jQuery including how to code hide show and toggles. 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…

895 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

17 Experts available now in Live!

Get 1:1 Help Now