JavaScript, jQuery and Ajax for reporting on SharePoint lists

In this post, I will show you how to query two SharePoint lists using jQuery and Ajax. We will then combine the results into a summary report. I originally created this example for SP 2007 with the requirement it be completed using only client side capabilities. In addition, the customer wanted a graphical representation of the data and a drill down to details for each record, without the use of custom web parts.

The first list contains master project data.

The second list is a project status log which is populated by a workflow on the Projects List. Each time the project status is changed, an item is created in this project status list.

Now we’ll create our report using client-side scripting. I used SharePoint Designer 2007 and created a new web page at the root of the site. But you can also use a Content Editor Web Part to hold your script.

First, add a <div> to your page that will display a loading image and notification while your Ajax runs. We’ll hide this <div> once it’s done.

<div id="wait">
<p align="center"><img src="GEARS_AN.GIF" alt="Please wait..." />
<br/>Generating report..
</p>
</div>

Then add another <div> where you will display your results. I wrapped mine in a <table> so I can easily add rows with more information later.

<table class="ms-vb" cellpadding="1" border="0">
<tr><td>
<div id="listLinks"> </div>
</td></tr> 
</table> 

Now for the script. Define two JavaScript objects. One for the project list items and one for the statuses. Add an .ajaxStop function which will run when all Ajax calls complete. The .unbind function prevents it from running again if a subsequent Ajax call is made after the document loads. Finally, define what should happen when the document is ready.

<script type="text/javascript">

 var myProjArray = new Object();
 var myStatusArray = new Object();

 $(document).ajaxStop(function() {
  $(this).unbind("ajaxStop");
  createprojectstable();
 });

 $(document).ready(function() {
  getProjects();
 });

When the document is ready getProjects() runs. Since CAML joins are only supported in SharePoint 2010+, I have to make an extra Ajax call for each project to get the status items. A single call could have been made to return all status items rather than making a call for each project returned by the first. However, that list may have been much larger than needed if we were to restrict the projects returned in the first CAML query by using a WHERE clause. Your particular circumstance will dictate which approach to take.

//****************************************************************************************
//get projects from the Projects List
function getProjects(){

 var thisurl = window.location.href;
 var i = thisurl.lastIndexOf("/");
 var asmxlocation = thisurl.substr(0,i)
 asmxlocation = asmxlocation + "/_vti_bin/lists.asmx";


 var soapEnv = "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/'> \
     <soapenv:Body> \
     <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> \
      <listName>Projects List</listName> \
      <viewFields> <ViewFields> \
        <FieldRef Name='ID' /><FieldRef Name='Title' /> <FieldRef Name='ProjStatus' /> <FieldRef Name='Created' /> <FieldRef Name='Team' /> \
      </ViewFields> </viewFields> \
      <rowLimit>2000</rowLimit> \
      <query> <Query> \
        <OrderBy><FieldRef Name='Team' Ascending='TRUE' /><FieldRef Name='Title' Ascending='TRUE' /></OrderBy> \
      </Query> </query> \
     </GetListItems> \
     </soapenv:Body> \
     </soapenv:Envelope>";

$.ajax({
url: asmxlocation,
type: "POST",
dataType: "xml",
data: soapEnv,
async: true,
complete: processResult,
contentType: "text/xml; charset=\"utf-8\""
});

}

//**************************************************************************************** 
//process results from projects query
function processResult(xData, status) {

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

myProjArray[$(this).attr("ows_ID")] = [$(this).attr("ows_Title"),$(this).attr("ows_ProjStatus"),$(this).attr("ows_Created"),$(this).attr("ows_Team") ]; 

//CAML Joins are only available in SP 2010+ so we got to loop
getProjectStatus($(this).attr("ows_ID")); 

});
} 

//****************************************************************************************
//get status records for a specific project
function getProjectStatus(ProjID){

var thisurl = window.location.href;
var i = thisurl.lastIndexOf("/");
var asmxlocation = thisurl.substr(0,i)
asmxlocation = asmxlocation + "/_vti_bin/lists.asmx";


var soapEnvZ = "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/'> \
    <soapenv:Body> \
    <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'> \
      <listName>Project Status Log</listName> \
      <viewFields> <ViewFields> \
         <FieldRef Name='ID' /> <FieldRef Name='ProjectID' /> <FieldRef Name='Status' /> <FieldRef Name='Created' /> \
      </ViewFields> </viewFields> \
      <rowLimit>1000</rowLimit> \
      <query> <Query> \
        <Where> <Eq> <FieldRef Name='ProjectID' /> <Value Type='Counter'>" + ProjID + "</Value> </Eq> </Where> \
        <OrderBy><FieldRef Name='Created' Ascending='TRUE' /></OrderBy> \
      </Query> </query> \
    </GetListItems> \
    </soapenv:Body> \
    </soapenv:Envelope>"; 

$.ajax({
url: asmxlocation,
type: "POST",
dataType: "xml",
async: true,
data: soapEnvZ,
complete: processResultZ,
contentType: "text/xml; charset=\"utf-8\""
});
}

//****************************************************************************************
//process results from the status query
function processResultZ(xData, status) {
var statusarray = new Array();
var arraycounter = 0
var projID;

$(xData.responseXML).find("z\\:row").each(function() { 
   statusarray[arraycounter++] = [$(this).attr("ows_Status"),$(this).attr("ows_Created")];
   projID = parseInt($(this).attr("ows_ProjectID"));
   myStatusArray[projID] = statusarray;
}); 

}

Now that we have the projects and statuses objects with a common property, the ID of the project, we can display the data any way we wish. createprojectstable() runs when the Ajax calls have completed and creates the HTML and JavaScript for the reports. I created a horizontal bar representing the length of time in each status for each project. There is also a drop down text area of the status details for each project. This is just another table row that is hidden and made visible by clicking the table row containing the graph. Click on the different elements below to see a live demo.

 

Here’s the interesting parts of creating the report – looping through the properties of the project and statuses objects to build the output HTML. (Note that, even though they are named “array”, they are actually objects.) And finally, we’ll hide the loading image and notification <div>.

function createprojectstable(){

var outHtml = "..."

//create report headings

for (prop in myProjArray) { 
  if (!myProjArray.hasOwnProperty(prop)) { 
    continue; //The current property is not a direct property of p, so goto the next one. i.e. it is not a poperty we created but a built-in JavaScript     property 
  } 

  //create HTML for each project

  if (myStatusArray[prop] !== undefined){
    //and now create the status details for the project
  }
  outHtml += "..."
  $("#listLinks").append(outHtml); 
  $("#wait").hide(); 

  //create report footers and initiate any other report options
}

You can download the source of a working example. This one reads XML files instead of SharePoint lists but is essentially the same. I’ve only tested the code in IE 8 and Firefox 5 but it would probably be quick work to make it cross-browser compatible.

Tagged with: , ,
Posted in JavaScript and jQuery, SharePoint
6 comments on “JavaScript, jQuery and Ajax for reporting on SharePoint lists
  1. CyberPine says:

    very nice and very applicable client side reporting.

    Now .. with SharePoint Online and the lack of Design View in SPD 2013, No SSRS for SPO and how difficult it now becomes to expose SSRS and Data from On Prem it would be pure genius if some thirdparty or MS capitalizes the opportunity to develop a client side reporting with Design view, connect, drag and drop WYSIWYG type IDE.

  2. Justin Vorous says:

    I’m having an problems with the first ajax step. On complete it calls the processResult function but nothing was happening, so I added an Alert(xData.responseText); and it pops up the message: “Exception of type ‘Microsoft.SharePoint.SoapServerException’ was thrown” and the detail error string says “There is no Web named /sites/MySite/MySubSite/SitePages/_vti_bin/lists.asmx”..

    but when i alert the asmxlocation variable it shows the full “https://server/sites/MySite/MySubSite/etc etc etc” — do you know why the Soap message is dropping the server reference?

    • Dedra says:

      Hi Justin,

      You’ll need to substitute your domain and path for “server/MySite/MySubSite/SitePages/”. You can do it programmatically several ways. For example, something like this:

            var thisurl = window.location.href;
            var i = thisurl.lastIndexOf("/");
            var asmxlocation = thisurl.substr(0,i)
            asmxlocation = asmxlocation + "/_vti_bin/lists.asmx";
      

      There are other more SharePoint specific ways, but this works in almost all cases. Just make sure the lastIndexOf returns the position you need to get to “/vit_bni/lists.asmx”. Of course, you can always hard code the path too.

      • Justin Vorous says:

        Thanks for the response — but in my post the /MySite/MySubsite/ etc was just dummy words. the URL is actually correct URL (I just didn’t want to post my actual address.)

        but replacing the asmxlocation variable with the hardcoded URL actually works — the responseText now shows the list items. Oh well, at least it’s not giving me an error now!

        thanks again — and great post!

  3. Justin V says:

    Another slight issue — is there a limit on the # of ajax calls that can be made? in the processResultZ function I added a line to do a thid Ajax call and on complete it runs a ProcessItemResult function…problem is it won’t iterate through the .each(function() { piece of the new xData.response.

    I can get it to work if I skip your 2nd ajax call and go straight to my 3rd one — but trying to do all 3 seems to break it. Can we only do 2 calls?

    • Dedra says:

      Hi Justin,

      Just got back…. Have you resolved the problem? There’s no limit to the number of Ajax calls than can be made. Have you checked your async parameters to make sure your calls are finishing when expected?

Advertisement