Use getlistitems on a loop to create a KPI summary

Sep 10, 2012 at 7:26 PM

Hello,

I'm trying to get numerical count totals from a sharepoint list, where certain criteria are met - this is litterally driving me crazy so its nice to have finally found somewhere that may be able to help!

I've just test the following code which is working nicely:

<script type="text/javascript" src="http://mynationwideteam/sites/fin_comms_tech_serv/Javascript/jquery-1.8.1.min.js"></script>
<script type="text/javascript" src="http://mynationwideteam/sites/fin_comms_tech_serv/Javascript/jquery.SPServices-0.7.1a.min.js"></script>
<script language="javascript" type="text/javascript">

$(document).ready(function() {
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "EM Exception Log",
    CAMLViewFields: "<ViewFields><FieldRef Name='EM Exception Log' /></ViewFields>",
CAMLQuery: "<Query><Where><Neq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Neq></Where></Query>",

    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
        var liHtml = "<li>" + $(this).attr("ows_Title") + "</li>";
        $("#tasksUL").append(liHtml);
      });
    }
  });
});
</script>
<ul id="tasksUL"/>
But what I'd like to know is how to change the code so that rather than printing the list values, it instead loops through them all. Firstly for each list item found add it to a variable called "NoLines". Then For each of those lines, whose "Period" value matches that of a html drop down list control named "Period1" (Contains numerical values), then add 1 to the count variable "NoInPeriod". 
I'll then use this example to place these and other values into a data table that basically will show number of items with a status of open, number of items logged in period and number of items logged year to date. 
Thanks in advance for any help you can offer... sharepoint is proving to be a headache! 
Sep 10, 2012 at 8:27 PM
Edited Sep 10, 2012 at 8:30 PM

I think I've deciphered what you are getting at. I've modified your script.  It's not going to work as is though. You haven't provided enough information to come close, however, I'm thinking you'll be able to follow the logic and modify it to complete your task.  Obviously the script isn't tested, so proceed with caution and let me know if you need some more help.

<script type="text/javascript" src="http://mynationwideteam/sites/fin_comms_tech_serv/Javascript/jquery-1.8.1.min.js"></script>
<script type="text/javascript" src="http://mynationwideteam/sites/fin_comms_tech_serv/Javascript/jquery.SPServices-0.7.1a.min.js"></script>
<script language="javascript" type="text/javascript">

$(document).ready(function() {
	var numOfLines = 0,
		numOfPeriod = 0
	; //local vars

	$().SPServices({
		operation: "GetListItems",
		async: false,
		listName: "EM Exception Log",
		CAMLViewFields: "<ViewFields><FieldRef Name='EM Exception Log' /><FieldRef Name='PeriodColumn' /></ViewFields>", //FYI ~~~~ The EM Exception Log isn't valid b/c it's not a valid staic name. Static names contain no spaces.
		CAMLQuery: "<Query><Where><Neq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Neq></Where></Query>",

		completefunc: function (xData, Status) {
			var $periodCtrl = $("#period-drop-down"),
				periodValue = $periodCtrl.val()
			;
			
			$(xData.responseXML).SPFilterNode("z:row").each(function( i, el ) {
				var $node = $(this)
				; //local vars
				
				//Total number of lines counter
				numOfLines++;
				
				//count number of lines in period
				if ( periodValue === $node.attr("PeriodColumn") ) {
					numOfPeriod++;
				}
			});
			
			var output = "<table><tr><td><h3>Total Number of Lines: " + numOfLines + "</h3></td></tr><tr><td><h3>Total Number Within Period: " + numOfPeriod + "</h3></td></tr></table>";
			//Append output to the DOM.
			$("#results").append( output );
		}
	});
});
</script>

<div id='results'></div>

Cheers,
Matt 

Sep 10, 2012 at 9:22 PM

Hi Matt,

Thanks for your fast response :)

I've tried to simplify it by taking the drop down part out for a second.

If I comment this line out :

//count number of lines in period
      if ( periodValue === $node.attr("EM_REPORTING_PERIOD") ) {
numOpenedInPeriod++;}

It works (but obviously returns 0 for numOpenedInPeriod).

Also - your right about the Static Name ... I changed it to title and it still output as expected. The actual colum name is now "Exception Summary", but if you look at the URL when editing the column it still says: "_layouts/FldEdit.aspx?List=%7BFD2B7D85%2D34E1%2D4AB8%2D8369%2DF31B04109B5F%7D&Field=Title" so I assume thats why Title works ?!

So based on that here's the same URL for ("EM_REPORTING_PERIOD") ... maybe I need to refer to it using "EM%5FREPORTING%5FPERIOD"?? 

"_layouts/FldEdit.aspx?List=%7BFD2B7D85%2D34E1%2D4AB8%2D8369%2DF31B04109B5F%7D&Field=EM%5FREPORTING%5FPERIOD"


Thank you for your help on this,
Josh
<script type="text/javascript" src="http://mynationwideteam/sites/fin_comms_tech_serv/Javascript/jquery-1.8.1.min.js"></script>
<script type="text/javascript" src="http://mynationwideteam/sites/fin_comms_tech_serv/Javascript/jquery.SPServices-0.7.1a.min.js"></script>
<script language="javascript" type="text/javascript">

$(document).ready(function() {
   //define local variables

   var numOfLines = 0, numOpenedInPeriod = 0 ;
   var periodValue = 2; // this will be set by drop down on going

  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "EM Exception Log",
    CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
CAMLQuery: "<Query><Where><Neq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Neq></Where></Query>",

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

      numOfLines++;

//count number of lines in period
      if ( periodValue === $node.attr("EM_REPORTING_PERIOD") ) {
	numOpenedInPeriod++;}

      //alert(numOpenedInPeriod);

      //alert(numOfLines);

      });

var output = "<table><tr><td><h3>Total Number of Lines: " + numOfLines + "</h3></td></tr> + <tr><td><h3>Total Open In Period 2: " + numOpenedInPeriod + "</h3></td></tr></table>";


//Append output to the DOM.
$("#results").append( output );

   }
  });
});
</script>
<div id='results'></div>
<script type="text/javascript">// <![CDATA[ $(document).ready(function() { //define local variables var numOfLines = 0, numOpenedInPeriod = 0 ; var periodValue = 2; // this will be set by drop down on going $().SPServices({ operation: "GetListItems", async: false, listName: "EM Exception Log", CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>", CAMLQuery: "<Query><Where><Neq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Neq></Where></Query>", completefunc: function (xData, Status) { $(xData.responseXML).SPFilterNode("z:row").each(function() { numOfLines++; //count number of lines in period if ( periodValue === $node.attr("EM_REPORTING_PERIOD") ) { numOpenedInPeriod++;} //alert(numOpenedInPeriod); //alert(numOfLines); }); var output = " +

Total Number of Lines: " + numOfLines + "

Total Open In Period 2: " + numOpenedInPeriod + "

"; //Append output to the DOM. $("#results").append( output ); } }); }); // ]]></script>
Sep 10, 2012 at 10:17 PM
Edited Sep 10, 2012 at 10:20 PM

It's always a good thing to remove variables until you have a good working example. Smart move!

In regards to your column name, if you use special characters when creating the column initially, you'll get weird static names. What I've always used was http://people.w3.org/rishida/tools/conversion/ to convert the URL static name.  In your case, it translates to: EM_REPORTING_PERIOD

You get weird encoding b/c an underscore needs to be represented like that in a URL; similar to a space: %20

The reason why you are getting 0 for numInPeriod is the code has a threequals comparison. I was expecting the value to be coming from your drop-down, which would end up being represented as a string. Since you set it manually, it was set as a number. All XML that comes back is in string format, so it would not pass the threequal check. 

Try this and see if you get a good result:

 

<script type="text/javascript">// <![CDATA[
	$(document).ready(function() {
	//define local variables

	var numOfLines = 0, numOpenedInPeriod = 0 ;
	var periodValue = 2; // this will be set by drop down on going

		$().SPServices({
		operation: "GetListItems",
		async: false,
		listName: "EM Exception Log",
		CAMLViewFields: "<ViewFields><FieldRef Name='Title' /><FieldRef Name='EM_REPORTING_PERIOD' /></ViewFields>",
		CAMLQuery: "<Query><Where><Neq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Neq></Where></Query>",

		completefunc: function (xData, Status) {
			$(xData.responseXML).SPFilterNode("z:row").each(function() {
				var $node = $(this);
				
				numOfLines++;

				//count number of lines in period
				if ( periodValue === ( $node.attr("EM_REPORTING_PERIOD") * 1 ) /*notice the coersion to a number */ ) ) {
					numOpenedInPeriod++;
				}
				//alert(numOpenedInPeriod);
				//alert(numOfLines);
			});

			var output = "TOTAL NUMBER OF LINES: " + NUMOFLINES + "\n TOTAL OPEN IN PERIOD 2: " + NUMOPENEDINPERIOD;
			//Append output to the DOM.
			$("#results").append( output );

			}
		});
	});
// ]]></script>

<div id='results'></div>

 

Cheers,
Matt 

Sep 11, 2012 at 9:02 AM

Morning Matt,

I've tried adding (* 1) as you suggest but it still doesn't display any result. (Litterally the content Editor Viewpart Collapses with no content).

Comment this bit out and it does display the table & the two results. One returns 178, the other 0.

//count number of lines in period
      if ( periodValue === ($node.attr("EM_REPORTING_PERIOD") * 1 )) {
numOpenedInPeriod++;}

This leeds me to believe that this is the cause of the issue still, but I don't understand why. - would it work to input the variable as a string instead perhaps ?!

Thanks

Sep 11, 2012 at 9:08 AM

I tried adding: alert($node.attr("EM_REPORTING_PERIOD")); ... which also stopped it working so I assume that this is the bit causing the issue?

Sep 11, 2012 at 9:33 AM

- It gives an alert of "Undefined"

Sep 11, 2012 at 9:58 AM

(Sorry for all the posts - edit doesn't work for me)

I've debugged the script and attributes shows an invalid number of parameters under the items folder. (When looking at the locals) - I've taken a screenshot but notice there's no attachment feature on this forum so can't show you!

It has a length of 21 though so it looks as though it is populated but not with the list column names as there is more than 21 columns.

Column (click to edit) Type Used in
Agreed SLA Single line of text E Form
AUD_VALUE Number E Form
Audience Lookup E Form
BLANK Number E Form
Communication_Plan Multiple lines of text E Form
Customer Choice E Form
D8_Lookup Calculated (calculation based on other columns) E Form
Date Created Date and Time E Form
Date_Closed Date and Time E Form
Date_RCA_Complete Date and Time E Form
EM Key contact Person or Group E Form
EM_REPORTING_PERIOD Number E Form
EM_REPORTING_PERIOD_CLOSED Number E Form
EMURLs1 Single line of text  
EMURLs2 Hyperlink or Picture  
Exception Details Multiple lines of text E Form
Exception Status Choice E Form
Exception Summary Single line of text E Form
Exception Type Choice E Form
FBS_TEAM Lookup E Form
FixTime Lookup E Form
FT_VALUE Number E Form
IF_ID Lookup E Form
Impact Choice E Form
Key Contacts for resolution Single line of text E Form
Line_Manager Person or Group E Form
Line_Manager_Approval Yes/No E Form
LT Solution Multiple lines of text E Form
LT Target Resolution Date Date and Time E Form
LT_Milestones Multiple lines of text E Form
MAT_VALUE Number E Form
Materiality Lookup E Form
Other Impact Single line of text E Form
Other Root Cause Single line of text E Form
Other Workstreams Impacted Choice E Form
Possible_Causes Multiple lines of text E Form
Possible_Solutions Multiple lines of text E Form
PRIORITY_VALUE Calculated (calculation based on other columns) E Form
RCA Completed and uploaded Yes/No E Form
Rem_Email_Count Number E Form
Root Cause Choice E Form
SEVERITY Single line of text E Form
ST Solution Multiple lines of text E Form
ST Target Resolution Date Date and Time E Form
ST_Milestones Multiple lines of text E Form
UA_VALUE Number E Form
Users Affected Lookup E Form
Workaround Multiple lines of text E Form
Created By Person or Group  
Modified By Person or Group
Sep 11, 2012 at 2:27 PM

OOPS!!! That's what I get for not testing scripts... Change that line that is coming back undefined to:

$node.attr("ows_EM_REPORTING_PERIOD")

That should be all you need.

 

Cheers,
Matt 

Sep 11, 2012 at 5:27 PM

... So Simple!

I took a different approach which seems to have worked, but it isn't anywhere near as tidy!

 

<script type="text/javascript" src="http://mynationwideteam/sites/fin_comms_tech_serv/Javascript/jquery-1.8.1.min.js"></script>
<script type="text/javascript" src="http://mynationwideteam/sites/fin_comms_tech_serv/Javascript/jquery.SPServices-0.7.1a.min.js"></script>
<script language="javascript" type="text/javascript">

var TotalExceptionsLogged = 0;
var ItemsLoggedInPeriod = 0;
var ItemsClosedInPeriod = 0;
var ItemsClosedTotal = 0;
var ActiveExceptions = 0; // Will Be Calculated


var periodValue = 2; // this will be set by drop down on going
var output1 = "";
var output2 = "";
var output3 = "";
var output4 = "";
var output5 = ""; 


//QUERY 4 - Items Closed Total---------------------------

$(document).ready(function() {
   
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "EM Exception Log",
    CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
CAMLQuery: "<Query><Where><Neq><FieldRef Name='EM_REPORTING_PERIOD_CLOSED' /><Value Type='Number'>0</Value></Neq></Where></Query>",

    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
        var $node = $(this);
      ItemsClosedTotal++;

      });

output4 = ItemsClosedTotal;
//Append output to the DOM.
$("#results4").append( output4 );

   }
  });
});





//QUERY 3 - Items Closed This period---------------------------

$(document).ready(function() {
   
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "EM Exception Log",
    CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
CAMLQuery: "<Query><Where><Eq><FieldRef Name='EM_REPORTING_PERIOD_CLOSED' /><Value Type='Number'>" + periodValue + "</Value></Eq></Where></Query>",

    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
        var $node = $(this);
      ItemsClosedInPeriod++;

      });

output3 = ItemsClosedInPeriod;
//Append output to the DOM.
$("#results3").append( output3 );

   }
  });
});



//QUERY 2 - Items Logged This period---------------------------

$(document).ready(function() {
   
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "EM Exception Log",
    CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
CAMLQuery: "<Query><Where><Eq><FieldRef Name='EM_REPORTING_PERIOD' /><Value Type='Number'>" + periodValue + "</Value></Eq></Where></Query>",

    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
        var $node = $(this);
      ItemsLoggedInPeriod++;

      });

output2 = ItemsLoggedInPeriod;
//Append output to the DOM.
$("#results2").append( output2 );

   }
  });
});


//QUERY 1 - Total Number of Items Logged---------------------------

$(document).ready(function() {
   
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "EM Exception Log",
    CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
CAMLQuery: "<Query><Where><Neq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Neq></Where></Query>",

    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
        var $node = $(this);
      TotalExceptionsLogged++;

      });

output1 = TotalExceptionsLogged;
//Append output to the DOM.
$("#results1").append( output1 );

//calculate active exceptions 

ActiveExceptions = (TotalExceptionsLogged - ItemsClosedTotal);
output5 = ActiveExceptions;

//Append output to the DOM.
$("#results5").append( output5 );

//Publish periodValue for use in text
$("#results6").append( "New exceptions raised in period " + periodValue );

//Publish periodValue for use in text
$("#results7").append( "Exceptions resolved in period " + periodValue );

   }
  });
});


</script>
<div id='results'>
</div>

 


Sep 11, 2012 at 5:42 PM

OK, so now I just need to sort out the drop down list to change the period variable.

Can I do this within the javascript? - Then pass the code through to another output variable and put the DIV tag in above the output table?

Previously I've used ASP forms & that has an auto post back option so that when the user changes the value the data refreshes. Does javascript have teh same feature?

Thanks for all your help Matt - really is appreciated!

Sep 12, 2012 at 1:52 PM

Yes, you can get the value that you want and then call your function. Since you are going to return all of the rows for totalling purposes, I'd make on call to the web service and get all of your items.  Within your .each() iteration is where you would want to handle all of your calculations.  It's going to be very similar code to the example I provided above.  

Here's an example of how to add a click handler to your <select> and then calling a function:

$("yourSelectControlSelector").on("change", function() {
var $this = $(this)
;

someAwesomeFunction( $this.find("option:selected").val() );
});



function somAwesomeFunction( period ) {
alert( "This is the period selected: " + period );
}

Here's a working example in a fiddle:
http://jsfiddle.net/iOnline247/dRRJN/

Let me know if you need more help. You have made a lot of progress it appears!

Cheers,
Matt