Google Table Chart

TheInnovator

unread,

May 20, 2014, 1:30:23 PM5/20/14

to google-visua...@googlegroups.com

Any help with this would be much appreciated.
I have a dashboard that is supposed to render a google table based on the month and year but it does not work correctly.
The data from one table gets transferred to another table and when you click "Submit" multiple times, it adds up the numbers in the table.

http://isaac.issharepoint.com/Web%20Part%20Page/508Dashboard.aspx

Here's my list:
http://isaac.issharepoint.com/Lists/508%20Dashboard%20Data/AllItems.aspx

Here's my code:

<style>

.Alternate

{

background-color:#BDBDBD

}

.Footer

{

font-weight:bold;

}

#vizTbl tr:last-child {

font-weight: bold;

}

#vizTbl tr:last-child td {

background-color: #a7d5b3;

}

</style>

google.load("visualization", "1", {packages:["corechart", "table"]});

anArray = new Array();

lanArray = new Array();

$(function() {

$('#autoThisMonth').monthpicker();

$('#autoLastMonth').monthpicker();

$('#manThisMonth').monthpicker();

$('#manLastMonth').monthpicker();

$('#autoLastMonth').change(function() {

autoLastMonthDate = $('#autoLastMonth').val();

});

// AND HERE

$('#autoThisMonth').change(function() {

autoThisMonthDate = $('#autoThisMonth').val();

});

var i=0;

$().SPServices({

operation: "GetListItems",

async: false,

listName: "508 File Types",

CAMLViewFields: "<ViewFields><FieldRef Name='Title'></FieldRef></ViewFields>",

CAMLQuery: "<Query><OrderBy><FieldRef Name='Title' /></OrderBy></Query>",

completefunc: function (xData, Status) {

//alert(xData.responseXML.xml);

$(xData.responseXML).SPFilterNode("z:row").each(function() {

scannedItems = new Object();

scannedItems.Title = $(this).attr("ows_Title");

scannedItems.totalPages = 0;

scannedItems.totalPassed = 0;

scannedItems.totalFailed = 0;

scannedItems.score = 0;

anArray[i] = scannedItems;

lanArray[i] = scannedItems;

i++;

});

}

});

function getMonthsFunc()

{

var spltDte = autoThisMonthDate.split("/");

var monthDte = spltDte[0];

var yearDte = spltDte[1];

/*--------------------------------------------------------------------This Month Automatic-------------------------------------------------------------------------*/

var monthWord = convertMonthIndex(Number(monthDte));

$().SPServices({

operation: "GetListItems",

async: false,

listName: "508 Dashboard Data",

CAMLViewFields: "<ViewFields><FieldRef Name='Title'></FieldRef><FieldRef Name='Year'></FieldRef><FieldRef Name='FileType'></FieldRef><FieldRef Name='TotalFailed'></FieldRef><FieldRef Name='TotalPassed'></FieldRef><FieldRef Name='TotalScanned'></FieldRef><FieldRef Name='score'></FieldRef></ViewFields>",

CAMLQuery: qry(monthWord, yearDte), //Function to call query

completefunc: function (xData, Status) {

$(xData.responseXML).SPFilterNode("z:row").each(function() {

for (var x=0; x < anArray.length; x++)

{

var fileType = getDte($(this).attr("ows_FileType"));

if (fileType == anArray[x].Title)

{

anArray[x].totalPages += Number($(this).attr("ows_TotalScanned"));

anArray[x].totalPassed += Number($(this).attr("ows_TotalPassed"));

anArray[x].totalFailed += Number($(this).attr("ows_TotalFailed"));

anArray[x].score = Number($(this).attr("ows_TotalPassed"))/Number($(this).attr("ows_TotalScanned"));

x++;

}

});

}

});

//This Month Automatic

var thisAtutoMonthDataTbl = new google.visualization.DataTable();

thisAtutoMonthDataTbl.addColumn('string', 'File Type');

thisAtutoMonthDataTbl.addColumn('number', 'Total Pages');

thisAtutoMonthDataTbl.addColumn('number', 'Total Passed');

thisAtutoMonthDataTbl.addColumn('number', 'Total Failed');

thisAtutoMonthDataTbl.addColumn('number', 'Score');

//Data for table chart

$.each(anArray, function (index, value) {

thisAtutoMonthDataTbl.addRow([anArray[index].Title, Number(anArray[index].totalPages), Number(anArray[index].totalPassed), Number(anArray[index].totalFailed), anArray[index].score*100]);

});

var table = new google.visualization.Table(document.getElementById('ThisAutoMonth'));

var tblOption = {

showRowNumber: true,

height:305,

width:200,

alternatingRowStyle: true,

cssClassNames: {

tableRow: 'Alternate',

selectedTableRow: 'Footer'

}

};

table.draw(thisAtutoMonthDataTbl, tblOption);

/*--------------------------------------------------------------------This Month Automatic-------------------------------------------------------------------------*/

/*--------------------------------------------------------------------Last Month Automatic-------------------------------------------------------------------------*/

var spltDte = autoLastMonthDate.split("/");

var monthDte = spltDte[0];

var yearDte = spltDte[1];

var monthWord = convertMonthIndex(Number(monthDte));

$().SPServices({

operation: "GetListItems",

async: false,

listName: "508 Dashboard Data",

CAMLViewFields: "<ViewFields><FieldRef Name='Title'></FieldRef><FieldRef Name='Year'></FieldRef><FieldRef Name='FileType'></FieldRef><FieldRef Name='TotalFailed'></FieldRef><FieldRef Name='TotalPassed'></FieldRef><FieldRef Name='TotalScanned'></FieldRef><FieldRef Name='score'></FieldRef></ViewFields>",

CAMLQuery: qry(monthWord, yearDte), //Function to call query

completefunc: function (xData, Status) {

$(xData.responseXML).SPFilterNode("z:row").each(function() {

for (var x=0; x < lanArray.length; x++)

{

var fileType = getDte($(this).attr("ows_FileType"));

if (fileType == lanArray[x].Title)

{

lanArray[x].totalPages += Number($(this).attr("ows_TotalScanned"));

lanArray[x].totalPassed += Number($(this).attr("ows_TotalPassed"));

lanArray[x].totalFailed += Number($(this).attr("ows_TotalFailed"));

lanArray[x].score = Number($(this).attr("ows_TotalPassed"))/Number($(this).attr("ows_TotalScanned"));

x++;

}

});

}

});

//This Month Automatic

var lastAtutoMonthDataTbl = new google.visualization.DataTable();

lastAtutoMonthDataTbl.addColumn('string', 'File Type');

lastAtutoMonthDataTbl.addColumn('number', 'Total Pages');

lastAtutoMonthDataTbl.addColumn('number', 'Total Passed');

lastAtutoMonthDataTbl.addColumn('number', 'Total Failed');

lastAtutoMonthDataTbl.addColumn('number', 'Score');

//Data for table chart

$.each(lanArray, function (index, value) {

lastAtutoMonthDataTbl.addRow([lanArray[index].Title, Number(lanArray[index].totalPages), Number(lanArray[index].totalPassed), Number(lanArray[index].totalFailed), lanArray[index].score*100]);

});

var tableLMA = new google.visualization.Table(document.getElementById('LastAutoMonth'));

var tblOptionLMA = {

showRowNumber: true,

height:305,

width:200,

alternatingRowStyle: true,

cssClassNames: {

tableRow: 'Alternate',

selectedTableRow: 'Footer'

}

};

tableLMA.draw(lastAtutoMonthDataTbl, tblOptionLMA);

/*--------------------------------------------------------------------Last Month Automatic-------------------------------------------------------------------------*/

}

function qry(M,Y)

{

var qry = "<Query>" +

"<Where>" +

"<And>" +

"<Eq>" +

"<FieldRef Name='Title' />" +

"<Value Type='Text'>"+M+"</Value>" +

"</Eq>" +

"<Eq>" +

"<FieldRef Name='Year' />" +

"<Value Type='Text'>"+Y+"</Value>" +

"</Eq>" +

"</And>" +

"</Where>" +

"</Query>";

return qry;

}

function getSplitArray(monthType)

{

var spltDte = monthType.split("/");

var monthDte = spltDte[0];

var yearDte = spltDte[1];

//Array is returned and dteSplit[0] will contain the month and dteSplit[1] will contain the year

return spltDte;

}

function convertMonthIndex(monthDte)

{

switch(monthDte) {

case 1:

monthD = "January";

break;

case 2:

monthD = "February";

break;

case 3:

monthD = "March";

break;

case 4:

monthD = "April";

break;

case 5:

monthD = "May";

break;

case 6:

monthD = "June";

break;

case 7:

monthD = "July";

break;

case 8:

monthD = "August";

break;

case 9:

monthD = "September";

break;

case 10:

monthD = "October";

break;

case 11:

monthD = "November";

break;

case 12:

monthD = "December";

break;

}

return monthD;

}

function getDte(str)

{

var res = str.split("#");

var str2 = res[1];

return str2;

}

</script>

<tr>

<td colspan="3">Automatic Scan Results</td>

</tr>

<tr>

<td style="width: 210px">This Month: <input type="text" id="autoThisMonth" style="background-color:#99FFCC"></td>

<td style="width: 238px">Last Month: <input type="text" id="autoLastMonth" style="background-color:#99FFCC"></td>

<td><button type="text" id="submitAutoDates" onclick="getMonthsFunc(); return false;">Submit</button></td>

</tr>

<tr>

<td>PIE CHARTPIE CHART</td>

</tr>

</table>

Andrew Gallant

unread,

May 20, 2014, 2:30:16 PM5/20/14

to google-visua...@googlegroups.com

Here is your problem:

scannedItems = new Object();
scannedItems.Title = $(this).attr("ows_Title");
scannedItems.totalPages = 0;
scannedItems.totalPassed = 0;
scannedItems.totalFailed = 0;
scannedItems.score = 0;		

anArray[i] = scannedItems;
lanArray[i] = scannedItems;

You are creating an object scannedItems, and then passing that object to both anArray and lanArray. Javascript passes objects by reference, so anArray[0] is the same object as lanArray[0]:

anArray[0].totalPages = 5;
console.log(anArray[0].totalPages) // 5
console.log(lanArray[0].totalPages) // 5
lanArray[0].totalPages = 10;
console.log(anArray[0].totalPages) // 10
console.log(lanArray[0].totalPages) // 10

So when you update your totals in each AJAX call, you are actually adding them to *both* arrays. The solution to this is to use separate objects:

anArray.push({
    Title: $(this).attr("ows_Title"),
    totalPages: 0,
    totalPassed: 0,
    totalFailed: 0,
    score: 0
});
lanArray.push({
    Title: $(this).attr("ows_Title"),
    totalPages: 0,
    totalPassed: 0,
    totalFailed: 0,
    score: 0
});

<span class="Apple-tab-s
...

Isaac Sogunro

unread,

May 20, 2014, 3:52:55 PM5/20/14

to google-visua...@googlegroups.com

AHHH.......

I never would have caught that. Thanks a lot.

I've been battling this for hours.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/aXrnZniQ7Es/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

--
-Isaac-

http://twitter.com/#!/feedy0urmind
You are today where your thoughts have brought you; you will be tomorrow where your thoughts take you.
- James Allen

Isaac Sogunro

unread,

May 21, 2014, 9:47:09 AM5/21/14

to google-visua...@googlegroups.com

I modified my code with your suggestions but it still doubles the numbers when "Submit" is clicked more than once.

Andrew Gallant

unread,

May 21, 2014, 1:24:37 PM5/21/14

to google-visua...@googlegroups.com

Yes, because you are incrementing the data in the arrays:

anArray[x].totalPages += Number($(this).attr("ows_TotalScanned"));
anArray[x].totalPassed += Number($(this).attr("ows_TotalPassed"));
anArray[x].totalFailed += Number($(this).attr("ows_TotalFailed"));

Replace the += with = to overwrite instead of increment. You should probably test for ows_TotalScanned = 0 to avoid a divide by 0 error in the score:

anArray[x].score = (Number($(this).attr("ows_TotalScanned")) == 0) ? null : Number($(this).attr("ows_TotalPassed"))/Number($(this).attr("ows_TotalScanned"));

Repeat these changes for the lanArray segment as well.

That solves the increment part, but you still have to clear the old data in case the new query doesn't return results for those rows, so you should increment over the arrays and 0 out all of the values before making the AJAX calls again.

...

Reply all

Reply to author

Forward