XSLT Transformations

Jun 11, 2010 at 4:53 PM
Mark, I was wondering if it is possible to apply xslt to the result set returned by your webservice GetListItems function? I'm thinking of creating a dashboard type application with a series of dropdowns that the user would make selections. Then using your webservice, retrieve at most one row of data from a Sharepoint list. I want to apply xlst to the result before displaying it to the user. Thank you in advance, Jeff
Coordinator
Jun 11, 2010 at 5:08 PM

Jeff:

Thanks for posting over here. My email inbox is pretty crazy lately, and this way I don't lose things. Others can benefit, as well!

The quick answer is "yes". I know that I had a discussion a while back with someone who was taking this approach, but I can't recall who it was. I did find one other thread that mentioned using XSL in this context:
http://spservices.codeplex.com/Thread/View.aspx?ThreadId=212986

Another thing to consider is to use a DVWP with the Lists Web Service as its DataSource.  In that construct, you get the full XSL capabilities if the DVWP.

M.

Jun 14, 2010 at 4:34 PM

Hi,

might have been me you discussed that with... One way I did it was like in the thread linked above. Also made some testing with the same approach asked for here but never really finished that, but my half finished test code is like below, might be useful anyway to get started.... Probably it's better to use some jquery for doing the xml/xsl transformation instead of the browser specific code below.

/Fredrik

var countN=0;
function loadXMLDoc(dname)
{
if (window.XMLHttpRequest)
  {
  xhttp=new XMLHttpRequest();
  }
else
  {
  xhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xhttp.open("GET",dname,false);
xhttp.send("");
return xhttp.responseXML;
}

function displayResult(listName, next, last)
{	
	try{
		next = next.replace(/&/g,'&amp;').replace(/"/g,'&quot;').replace(/</g,'&lt;').replace(/>/g,'&gt;');
	}
	catch(e){
	}
	countN++;
	$().SPServices({
		operation: "GetListItems",
		listName: listName,
		webURL: "http://YourSite",
		CAMLQueryOptions: '<QueryOptions><Paging ListItemCollectionPositionNext="'+next +'" /></QueryOptions>',
		CAMLRowLimit: "10",
		completefunc: function (xData, Status) {
			var next=$(xData.responseXML).find("rs\\:data").attr("ListItemCollectionPositionNext");
			$("#WSOutput").html("").append('<a href="javascript:displayResult(\''+listName+'\', \''+next+'\')">Next</a>');
			xml=xData.responseXML;
			xsl=loadXMLDoc("test.xsl");
			// code for IE
			if (window.ActiveXObject)
			  {
			  ex=xml.transformNode(xsl);
			  $("#example").append('<div id="divNo'+countN+'" style="display:none">'+ex+'</div>');
			  var countNex=countN-1;
			  $("#divNo"+countNex).toggle('slide',{},500);
			  $("#divNo"+countN).delay(510).toggle('slide',{},500)
			  }
			// code for Mozilla, Firefox, Opera, etc.
			else if (document.implementation && document.implementation.createDocument)
			  {
			  xsltProcessor=new XSLTProcessor();
			  xsltProcessor.importStylesheet(xsl);
			  resultDocument = xsltProcessor.transformToFragment(xml,document);
			  document.getElementById("example").appendChild(resultDocument);
			  }

		}
		
	});

}


displayResult('test');

And the XSL
<xsl:stylesheet xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ddw1="http://schemas.microsoft.com/sharepoint/soap/" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
	<xsl:output method="html" indent="no"/>
	<xsl:decimal-format NaN=""/>
	<xsl:param name="dvt_apos">'</xsl:param>
	<xsl:variable name="dvt_1_automode">0</xsl:variable>
	<xsl:template match="/">
		<xsl:call-template name="dvt_1"/>
	</xsl:template>
	<xsl:template name="dvt_1">
		<xsl:variable name="dvt_StyleName">Table</xsl:variable>
		<xsl:variable name="Rows" select="/soap:Envelope/soap:Body/ddw1:GetListItemsResponse/ddw1:GetListItemsResult/ddw1:listitems/rs:data/z:row"/>
		<xsl:variable name="dvt_RowCount" select="count($Rows)" />
		<xsl:variable name="IsEmpty" select="$dvt_RowCount = 0" />
		
		<table border="0" width="100%" cellpadding="2" cellspacing="0">
			<tr valign="top">
				<xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
					<th class="ms-vh" width="1%" nowrap="nowrap"></th>
				</xsl:if>
				<th class="ms-vh" nowrap="nowrap">ows_LinkTitle</th>
			</tr>
			<xsl:call-template name="dvt_1.body">
				<xsl:with-param name="Rows" select="$Rows"/>
			</xsl:call-template>
		</table>
	</xsl:template>
	<xsl:template name="dvt_1.body">
		<xsl:param name="Rows"/>
		<xsl:for-each select="$Rows">
			<xsl:call-template name="dvt_1.rowview" />
		</xsl:for-each>
		
	</xsl:template>
	<xsl:template name="dvt_1.rowview">
		<tr>
			<xsl:if test="position() mod 2 = 1">
				<xsl:attribute name="class">ms-alternating</xsl:attribute>
			</xsl:if>
			<xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
				<td class="ms-vb" width="1%" nowrap="nowrap">
					<span ddwrt:amkeyfield="" ddwrt:amkeyvalue="string($XPath)" ddwrt:ammode="view"></span>
				</td>
			</xsl:if>
			<td class="ms-vb">
				<xsl:value-of select="@ows_Title"/>
			</td>
		</tr>
	</xsl:template></xsl:stylesheet>
Jun 17, 2010 at 7:23 PM

Thanks to all that have replied. I found a very simple solution.

1) include the jquery.xslt.js script file on your page thanks to Johann Burkard for handling all of the complexities.

2) transform the result from spservices like so

    $(function() { $('#transformResult').xslt(xData.responseXML.xml, "/url/your.xslt");});

    Where transformResult is the id of a div on the page.

Jeff

Jul 25, 2010 at 1:21 AM

Thank you for this post... It was a BIG HELP in getting some reports generated... I used U2U CAML utility (http://www.u2u.be/Res/Tools/CamlQueryBuilder.aspx) to generate the query and often to just quickly get the internal names of the column in a list... it is a BIG HELPER and time savers... I'm also now using the jQuery Transform plugin (http://plugins.jquery.com/project/Transform)... so far, it has proven to be an excellent plugin.

 

Jul 26, 2010 at 7:57 PM
aah! this is exactly what I had in mind, thx for all the replies. This is a treat for me :) - i was also thinking of taking the returned result set via GetListItems and appending it to http://www.datatables.net/ (currently I'm reading the documentation) thought it's I ask if anyone had success with it?
Jul 26, 2010 at 8:03 PM
Edited Jul 26, 2010 at 8:06 PM

i post a example for display a SharePoint-List in a DataGrid (Grid from DataTables with FixedHeader, Export to CSV, etc.)

- Bernardo -

Jul 27, 2010 at 2:18 PM
Edited Jul 27, 2010 at 2:19 PM

Hi
This is a working sample for GetListItems and Grid from Datatables.net. Tested with IE6, FireFox and Safari.
Download the zip-file from datatables.net. you will find all the .js, .css and .png files inside
Change the var  SPwebURL and SPlistName and check the the SharePoint-List Fieldnames "ows_..." and the links to the EditForm.aspx an DispForm.aspx

- TableTools will be initialized with the T in "sDom"
- FixedHeader will be initialized
          // Fixed Header
          new FixedHeader( oTable );
- This sample load all the data in a array (GridContent). The Grid will be initialized with this array. On my System the Grid is faster show-up then the orginal SharePoint-List.

- RowDetails are build with the fnFormatDetails and $('td img', oTable.fnGetNodes() ).each( function ()
  and the First Column (0) in the grid
- I use Dates in the format dd.mm.yyy. For correct sorting you must use the function like this:
  jQuery.fn.dataTableExt.oSort['ch_date-asc'],
  jQuery.fn.dataTableExt.oSort['ch_date-desc'] and
  jQuery.fn.dataTableExt.aTypes.push
- When you use oTabel.fnAddData( GridContent, false) be aware of the second parameter:
       -> boolean : optional - redraw the table or not after adding the new data (default true)
       This redraw the grid by every Add make the build of the grid very, very slow
       Use false and at the end oTable.fnDraw(); or better use my way with an array.
- I have some javascript function with the names SP....ToList for make the content "pretty"

Save the sample as DataGridSample.inc, upload to sharepoint and insert the code direct (Source Editor)
or via link (Content Link) to a Content Editor Web Part

I think thats all

*** DataTableGridSample.inc ***

 

<!-- jquery --->
<script type="text/javascript" src="js/jquery.min.js"></script>
<!-- http://www.datatables.net/ --->
<script type="text/javascript" src="js/jquery.dataTables.min.js"></script>
<!-- SPServices --->
<script type="text/javascript" src="js/jquery.SPServices.min.js"></script>
<!-- optional: http://www.datatables.net/plug-ins/ TableTools  --->
<script type="text/javascript" src="js/ZeroClipboard.js"></script>
<!-- optional: http://www.datatables.net/plug-ins/ TableTools  --->
<script type="text/javascript" src="js/TableTools.js"></script>
<!-- optional: http://www.datatables.net/plug-ins/ FixedHeader --->
<script type="text/javascript" src="js/FixedHeader.js"></script>

       <style type="text/css" media="screen">
               @import "css/demo_table_jui.css";
               @import "css/jquery-ui-1.7.2.custom.css";
               @import "css/TableTools.css";
               @import "css/jquery-ui.smoothness.css";

              /* jquery-ui.smoothness.css (http://jqueryui.com/themeroller/)
               * TableTools.css           (http://www.datatables.net/plug-ins/ TableTools -> part of the package)

               .dataTables_info { padding-top: 0; }
               .dataTables_paginate { padding-top: 0; }
               .css_right { float: right; }
       </style>

<script type="text/javascript">

  var SPwebURL = "http://mysharepoint";
  var SPlistName = "mylist";

 // **********************************************************************************************************
  // Aufbereiten Listenfelder für Anzeige
  // **********************************************************************************************************
  // Paramter
  // FieldValue = Wert der umgestellt werden soll
  // FieldType = Feldtyp (Date, Time, Boolean, Email, UserName, Text)
  // FieldDefaultValue = Wenn der Feldwert nicht vorhanden, wird dieser Wert zurückgegeben
  //
  // Rückgabe
  // Aufbereiterter Feldwert
  //
  // Aufruf
  // var strValue = SpToList( FieldValue, 'FieldType', 'FieldDefaultValue' );
  //
  // Beispiel:
  // -
  // **********************************************************************************************************
  function SpToList ( FieldValue, FieldType, FieldDefaultValue)
  {
         if (typeof FieldDefaultValue == "undefined") { FieldDefaultValue = ""; }
         if (typeof FieldType == "undefined") { FieldType = "Text"; }
         if (typeof FieldValue == "undefined") { return (FieldDefaultValue); }

         switch (FieldType) {
                case "Date":            // FieldValue = 'YYYY-MM-DD HH:MM:SS' -> Zeigt das Datum ohne Zeit und formatiert an
                     var DateSplitt = FieldValue.substr( 0, FieldValue.indexOf(" ") ).split( "-" );
                     return ( DateSplitt[2] + "." + DateSplitt[1] + "." + DateSplitt[0] );
                     break;
                case "DateSP":            // FieldValue = 'DD.MM.YYYY' -> Zeigt das Datum für SharePoint formatiert an (YYYY-MM-DD)
                     var DateSplitt = FieldValue.split( "." );
                     return ( DateSplitt[2] + "-" + DateSplitt[1] + "-" + DateSplitt[0] );
                     break;
                case "Time":            // FieldValue = 'YYYY-MM-DD HH:MM:SS' -> Zeigt die Zeit ohne Datum und formatiert an
                     return ( FieldValue.substr( FieldValue.indexOf(" ") + 1, 6) );
                     break;
                case "Boolean":         // FieldValue = '0'/'1' -> Zeigt Boolean-Feld mit Ja/Nein an, anstelle 0/1
                     if ( FieldValue == "0" ) { return ( "Nein" ); }
                     if ( FieldValue == "1" ) { return ( "Ja" ); }
                     break;
                case "Email":           // FieldValue '' -> Spezial: Email aufbereiten mit Link (Trennzeichen ;)
                     if ( FieldValue.substr( FieldValue.length - 1, 1 ) != ";" ) { FieldValue += ";"; }
                     var EmailSplit = FieldValue.split(";");
                     var EmailResult = "";
                     for(i = 0; i < EmailSplit.length; i++){
                         EmailResult += "<a href='mailto:" + EmailSplit[i] + "'>" + EmailSplit[i] + "</a>";
                     }
                     return ( EmailResult );
                     break;
                case "UserName":        // FieldValue = 'UserNo#UserName' -> Zeigt nur den Namen an
                     return ( FieldValue.substr( FieldValue.indexOf("#") + 1 ) );
                     break;
                default:                // Is Default and FieldType = 'Text'
                     return ( FieldValue );
                     break;
         }
  }
  // **********************************************************************************************************

  // Direktfunktionen
  // SpNameToList ( FieldValue );                oder SpToList ( FieldValue, 'UserName', '');
  function SpNameToList    ( FieldValue ) { return ( SpToList( FieldValue, 'UserName', ''    )); }
  // SpDateToList ( FieldValue );                oder SpToList ( FieldValue, 'Date', '');
  function SpDateToList    ( FieldValue ) { return ( SpToList( FieldValue, 'Date',     ''    )); }
  // SpTimeToList ( FieldValue );                oder SpToList ( FieldValue, 'Time', '');
  function SpTimeToList    ( FieldValue ) { return ( SpToList( FieldValue, 'Time',     ''    )); }
  // SpTextToList ( FieldValue );                oder SpToList ( FieldValue, 'Text', '');
  function SpTextToList    ( FieldValue ) { return ( SpToList( FieldValue, 'Text',     ''    )); }
  // SpBooleanToList ( FieldValue );             oder SpToList ( FieldValue, 'Boolean', 'Nein');
  function SpBooleanToList ( FieldValue ) { return ( SpToList( FieldValue, 'Boolean',  'Nein')); }
  // SpEmailToList ( FieldValue );               oder SpToList ( FieldValue, 'Email', ^');
  function SpEmailToList   ( FieldValue ) { return ( SpToList( FieldValue, 'Email',    ''    )); }

  // For CAMLQueryOption you can use the var t_CAMLQUery or make it direct by the service
  var t_CAMLQuery = "";
  // t_CAMLQuery = "<Query><Where><Neq><FieldRef Name='Status' /><Value Type='Choice'>Closed</Value></Neq></Where></Query>";

  // This is for test; it's your choice to call the edit or disp form
  var Edit = false;

  // Global var for Grid content
  var GridContent = new Array();
  // Global var for counter
  var giCount = 0;

$(document).ready(function() {

  $().SPServices({
            operation:'GetListItems',
            async: false,
            webURL: SPwebURL,
            listName: SPlistName,
            CAMLViewFields:'',
            CAMLQuery: t_CAMLQuery,
            CAMLRowLimit: 0,
            CAMLQueryOptions:'<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions>',
            completefunc: function (xData,Status){

                // very import: use '[nodeName=z:row]'
                $(xData.responseXML).find('[nodeName=z:row]').each(function(){

                          if ( Edit )
                          {
                                   // Edit
                                   t_Title = "<a href='" + SPwebURL + "/Lists/Pendenzen/EditForm.aspx?ID=" +
                                          $(this).attr("ows_ID") +
                                          "&Source=" +
                                          window.location + "'>" +
                                          "<img src='/_layouts/images/ITTASK.GIF' border='0' align='middle'> " +
                                          $(this).attr("ows_LinkTitle") +
                                          "</a>";
                          } else {
                                   // Display
                                   t_Title = "<a href='" + SPwebURL + "/Lists/Pendenzen/DispForm.aspx?ID=" +
                                          $(this).attr("ows_ID") +
                                          "&Source=" +
                                          window.location + "'>" +
                                          "<img src='/_layouts/images/ITTASK.GIF' border='0' align='middle'> " +
                                          $(this).attr("ows_LinkTitle") +
                                          "</a>";
                          }

                          GridContent[giCount] = new Array(12);
                          GridContent[giCount][0] = '<img src="images/details_open.png">';
                          GridContent[giCount][1] = $(this).attr("ows_ID");
                          GridContent[giCount][2] = t_Title+ "<br><br>" + SpTextToList($(this).attr("ows_ToDo"));
                          GridContent[giCount][3] = SpTextToList($(this).attr("ows_Title"));
                          GridContent[giCount][4] = SpTextToList($(this).attr("ows_ToDo"));
                          GridContent[giCount][5] = SpBooleanToList($(this).attr("ows_Attachments"));
                          GridContent[giCount][6] = $(this).attr("ows__UIVersionString");          //     "ows_owshiddenversion"
                          GridContent[giCount][7] = SpDateToList($(this).attr("ows_Created"));
                          GridContent[giCount][8] = SpToList( $(this).attr("ows_Created"), "Time", "" );
                          GridContent[giCount][9] = SpNameToList($(this).attr("ows_Author"));      // "ows_Created_x0020_Date= 4;#2009-07-27 15:33:14"
                          GridContent[giCount][10] = SpDateToList($(this).attr("ows_Modified"));
                          GridContent[giCount][11] = SpTimeToList($(this).attr("ows_Modified"));
                          GridContent[giCount][12] = SpNameToList($(this).attr("ows_Editor"));

                          giCount++;
                });

            }
  });

  // oTabel.fnAddData( GridContent, true);
  // oTable.fnDraw();

});

</script>

<script type="text/javascript">

  $("#tabl_wrapper").css("width","100%");
  $("#tabl").css("width","100%");

  // Global variable for the DataTables object
  var oTable;

  /* Sorting Date */
  jQuery.fn.dataTableExt.oSort['ch_date-asc']  = function(a,b) {
        var chDatea = a.split('.');
        var chDateb = b.split('.');

        var x = (chDatea[2] + chDatea[1] + chDatea[0]) * 1;
        var y = (chDateb[2] + chDateb[1] + chDateb[0]) * 1;

        return ((x < y) ? -1 : ((x > y) ?  1 : 0));
  };

  jQuery.fn.dataTableExt.oSort['ch_date-desc'] = function(a,b) {
        var chDatea = a.split('.');
        var chDateb = b.split('.');

        var x = (chDatea[2] + chDatea[1] + chDatea[0]) * 1;
        var y = (chDateb[2] + chDateb[1] + chDateb[0]) * 1;

        return ((x < y) ? 1 : ((x > y) ?  -1 : 0));
  };

  /* Detection Date */
  jQuery.fn.dataTableExt.aTypes.push(
        function ( sData )
        {
                if (sData.match(/^(0[1-9]|[12][0-9]|3[01])\.(0[1-9]|1[012])\.(19|20|21)\d\d$/))
                {
                        return 'ch_date';
                }
                return null;
        }
  );

  /* Formating function for row details */
  function fnFormatDetails ( nTr )
  {
        var aData = oTable.fnGetData( nTr );
        var sOut = ''
        // sOut += '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:250px;">';
        sOut += '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:5px;">';
        sOut += '<tr><td>ID                       :</td><td>' + aData[1]  + '</td></tr>';
        sOut += '<tr><td>Titel / ToDo             :</td><td>' + aData[2]  + '</td></tr>';
        sOut += '<tr><td>Titel                    :</td><td>' + aData[3]  + '</td></tr>';
        sOut += '<tr><td>ToDo                     :</td><td>' + aData[4]  + '</td></tr>';
        sOut += '<tr><td>Attachements vorhanden   :</td><td>' + aData[5] + '</td></tr>';
        sOut += '</table>';

        sOut += 'Version ' + aData[6] + '<br>';
        sOut += 'Erstellt am ' + aData[7] + ' ' + aData[8] + ' von ' + aData[9] + '<br>';
        sOut += 'Zuletzt ge&auml;ndert am ' + aData[10] + ' ' + aData[11] + ' von ' + aData[12] + '<br>';
        return sOut;
  }

  $(document).ready(function() {

        //                 "sDom": 'T<"clear"><"top"ifl<"clear">>rt<"bottom"p<"clear">>',

        oTable=$('#splist').dataTable( {
                "oLanguage": {
                          "sProcessing": "Bitte warten...",
                          "sLengthMenu": "_MENU_ Einträge anzeigen",
                          "sLengthMenu": '<select><option value="10">10</option><option value="25">25</option><option value="50">50</option><option value="100">100</option><option value="150">150</option><option value="-1">All</option></select> Einträge anzeigen',
                          "sZeroRecords": "Keine Einträge vorhanden.",
                          "sInfo": "_START_ bis _END_ von _TOTAL_ Einträgen",
                          "sInfoEmpty": "0 bis 0 von 0 Einträgen",
                          "sInfoFiltered": "(gefiltert von _MAX_  Einträgen)",
                          "sInfoPostFix": "",
                          "sSearch": "Suchen",
                          "sUrl": "",
                          "oPaginate": {
                                  "sFirst":    "Erster",
                                  "sPrevious": "Zurück",
                                  "sNext":     "Nächster",
                                  "sLast":     "Letzter"
                          }
                },
                "bPaginate": true,
                "bJQueryUI": true,
                "bLengthChange": true,
                "bAutoWidth": false,
                "bFilter": true,
                "bSort": true,
                "bInfo": true,
                "sPaginationType": "full_numbers",
                "iDisplayLength": 25,
                "sDom": 'T<"fg-toolbar ui-widget-header ui-corner-tl ui-corner-tr ui-helper-clearfix"lfr>t<"fg-toolbar ui-widget-header ui-corner-bl ui-corner-br ui-helper-clearfix"ip>',

                "aaData": GridContent,
                "aoColumns": [
                  {"sTitle":"Detail",                              "sClass": "center", "bSortable":false, "bVisible":true, "sWidth": "35px"},
                  {"sTitle":"Id",                                  "sClass": "center", "bSortable":true,  "bVisible":true, "sWidth": "35px"},
                  {"sTitle":"Titel / ToDo",                        "sClass": "left",   "bSortable":false, "bVisible":true, "sWidth": "200px"},
                  {"sTitle":"Titel",                               "sClass": "left",   "bSortable":true,  "bVisible":true, "sWidth": "105px"},
                  {"sTitle":"ToDo",                                "sClass": "center", "bSortable":true,  "bVisible":true, "sWidth": "30px"},
                  {"sTitle":"28 Hide-Attachments vorhanden",                           "bSortable":false, "bVisible":false},
                  {"sTitle":"29 Hide-Version",                                         "bSortable":false, "bVisible":false},
                  {"sTitle":"30 Hide-Erstellt am Datum",                               "bSortable":false, "bVisible":false},
                  {"sTitle":"31 Hide-Erstellt am Zeit",                                "bSortable":false, "bVisible":false},
                  {"sTitle":"32 Hide-Erstellt durch",                                  "bSortable":false, "bVisible":false},
                  {"sTitle":"33 Hide-Ge&auml;ndert am Datum",                          "bSortable":false, "bVisible":false},
                  {"sTitle":"34 Hide-Ge&auml;ndert am Zeit",                           "bSortable":false, "bVisible":false},
                  {"sTitle":"35 Hide-Ge&auml;ndert durch",                             "bSortable":false, "bVisible":false}
                ],
                "aaSorting": [ [1,'asc'], [2,'asc'] ],
                "bProcessing": true,
                "bStateSave": true,
                "bServerSide": false
          });

          // Fixed Header
          new FixedHeader( oTable );

        /* Add event listener for opening and closing details
         * Note that the indicator for showing which row is open is not controlled by DataTables,
         * rather it is done here
         */
        $('td img', oTable.fnGetNodes() ).each( function () {
                $(this).click( function () {
                        var nTr = this.parentNode.parentNode;
                        if ( this.src.match('details_close') )
                        {
                                /* This row is already open - close it */
                                this.src = "images/details_open.png";
                                oTable.fnClose( nTr );
                        }
                        else
                        {
                                /* Open this row */
                                this.src = "images/details_close.png";
                                oTable.fnOpen( nTr, fnFormatDetails(nTr), 'details' );
                        }
                } );
        } );

  } );

</script>

<table id="splist" class="display">
 <tbody id="tablebody">
  <!-- GetListsItem write data here -->
 </tbody>
</table>

***

- Bernardo -

 

Jul 28, 2010 at 3:11 AM

Bernardo I owe a big thanks to you :) I would have definitely not been able to get the DataTable working w/o your code. Though it did take me some time to customize your code but got it working, I will play around with it to see if I need any further help. thanks once again.

Aug 31, 2010 at 11:54 PM

Hi new2all,

 

Is it possible to see your code?

Apr 16, 2011 at 2:54 AM

Hi Sharepointgig - sorry for a very very late reply, I'm coming back to the sharepoint world and this forum after a good 8 month break. Below is the code I have used.

Now i need some help :) I hope you or someone can help me. the below code loads all the data from the sharepoint list into the DOM in one AJAX call thereby slowing the initial load and degrading performance. can someone help me with making the CAMLRowlimit based on data tables paginaiton? Datatables has its own pagination option "sPaginationType": "full_numbers"

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Your Request</title>

<!-- jquery --->
<script type="text/javascript" src="../../jQuery/jquery-1.4.3.js"></script>
<!-- http://www.datatables.net/ --->
<script type="text/javascript" src="../../jQuery/dataTables-1.7.3/media/js/jquery.dataTables.min.js"></script>
<!-- SPServices --->
<script type="text/javascript" src="../../jQuery/jquery.SPServices-0.5.6.min.js"></script>
<!-- optional: http://www.datatables.net/plug-ins/ TableTools  --->
<script type="text/javascript" src="../../jQuery/dataTables-1.6/extras/TableTools/media/ZeroClipboard/ZeroClipboard.js"></script>
<!-- optional: http://www.datatables.net/plug-ins/ TableTools  --->
<script type="text/javascript" src="../../jQuery/dataTables-1.6/extras/TableTools/media/js/TableTools.js"></script>
<!-- optional: http://www.datatables.net/plug-ins/ FixedHeader --->
<script type="text/javascript" src="../../jQuery/dataTables-1.6/extras/FixedHeader/js/FixedHeader.js"></script>


<style type="text/css" media="screen">
@import url('../../jQuery/dataTables-1.6/media/css/demo_table_jui_admin.css');
@import url('../../jQuery/dataTables-1.6/media/css/tablebody_admin.css');            
@import "../../jQuery/jquery_ui/development-bundle/themes/smoothness/jquery-ui-1.8.2.custom_admin.css";
@import url('../../jQuery/dataTables-1.6/extras/TableTools/media/css/TableTools_admin.css');

</style>

</head>

<body>


<script type="text/javascript">

  var SPwebURL = "http://sitename"; //replace the sitename with your site e.g http://spsite1
  var SPlistName = "Submit Request";//just specify the list name, no need to include 'Lists' if they are in the Lists folder

  function SpToList ( FieldValue, FieldType, FieldDefaultValue)
  {

         if (typeof FieldDefaultValue == "undefined") { FieldDefaultValue = ""; }
         if (typeof FieldType == "undefined") { FieldType = "Text"; }
         if (typeof FieldValue == "undefined") { return (FieldDefaultValue); }

         switch (FieldType) {
                case "Date":            // FieldValue = 'MM/DD/YYYY HH:MM:SS' -> Zeigt das Datum ohne Zeit und formatiert an
                     var DateSplitt = FieldValue.substr( 0, FieldValue.indexOf(" ") ).split( "-" );
                     return ( DateSplitt[1] + "/" + DateSplitt[2] + "/" + DateSplitt[0] );
                     break;
                case "DateSP":            // FieldValue = 'MM/DD/YYYY' -> Converts it to SharePoint format (YYYY-MM-DD)
                     var DateSplitt = FieldValue.split( " " );
                     return ( DateSplitt[1] + "/" + DateSplitt[2] + "/" + DateSplitt[0] );                    
                     break;
                case "Time":            // FieldValue = 'YYYY-MM-DD HH:MM:SS'
                     return ( FieldValue.substr( FieldValue.indexOf(" ") + 1, 6) );
                     break;
                case "Boolean":         // FieldValue = '0'/'1' -> Boolean-Field Yes/No
                     //alert("fieldValue: " + FieldValue)
                     if ( FieldValue == "0" ) { return ( "No" ); }
                     if ( FieldValue != "0" ) { return ( "Yes" ); }                    
                     break;
                case "Email":           // FieldValue '' -> Email
                     if ( FieldValue.substr( FieldValue.length - 1, 1 ) != ";" ) { FieldValue += ";"; }
                     var EmailSplit = FieldValue.split(";");
                     var EmailResult = "";
                     for(i = 0; i < EmailSplit.length; i++){
                         EmailResult += "<a href='mailto:" + EmailSplit[i] + "'>" + EmailSplit[i] + "</a>";
                     }
                     return ( EmailResult );
                     break;
                case "Link":           // FieldValue ''
                //alert("fieldValue: " + FieldValue)
                     if ( FieldValue.substr( FieldValue.length - 1, 1 ) != ";")                                         
                     var LinkSplit = FieldValue.split(";#");
                     var newline = '\r\n';
                     var target = '=_blank';                    
                     var LinkResult = "";
                     for(i = 0; i < LinkSplit.length; i++){
var lastIndex  = LinkSplit[i].lastIndexOf("/")                       
                        LinkResult += "<a target='" + target + "' href='" + LinkSplit[i] + "'>" + LinkSplit[i].substring(lastIndex+1) + "</a><br/>" ;
                     }
                     if (FieldValue == "0" ) { return ( "No" ); }                     
                     return ( LinkResult );
                     break;
                case "UserName":       
                     return ( FieldValue.substr( FieldValue.indexOf("#") + 1 ) );
                     break;
                default:                // Is Default and FieldType = 'Text'
                     return ( FieldValue );
                     break;
         }
  }
  // **********************************************************************************************************


  // SpNameToList ( FieldValue );                oder SpToList ( FieldValue, 'UserName', '');
  function SpNameToList    ( FieldValue ) { return ( SpToList( FieldValue, 'UserName', ''    )); }
  // SpDateToList ( FieldValue );                oder SpToList ( FieldValue, 'Date', '');
  function SpDateToList    ( FieldValue ) { return ( SpToList( FieldValue, 'Date',     ''    )); }
  // SpDateSPToList ( FieldValue );                oder SpToList ( FieldValue, 'DateSP', '');
  function SpDateSPToList   ( FieldValue ) { return ( SpToList( FieldValue, 'DateSP',     ''    )); }
  // SpTimeToList ( FieldValue );                oder SpToList ( FieldValue, 'Time', '');
  function SpTimeToList    ( FieldValue ) { return ( SpToList( FieldValue, 'Time',     ''    )); }
  // SpTextToList ( FieldValue );                oder SpToList ( FieldValue, 'Text', '');
  function SpTextToList    ( FieldValue ) { return ( SpToList( FieldValue, 'Text',     ''    )); }
  // SpBooleanToList ( FieldValue );             oder SpToList ( FieldValue, 'Boolean', 'No');
  function SpBooleanToList ( FieldValue ) { return ( SpToList( FieldValue, 'Boolean''No')); }
  // SpEmailToList ( FieldValue );               oder SpToList ( FieldValue, 'Email', ^');
  function SpEmailToList   ( FieldValue ) { return ( SpToList( FieldValue, 'Email',    ''    )); }
 
  function SpLinkToList   ( FieldValue ) { return ( SpToList( FieldValue, 'Link',    ''    )); }
 
   
 

  // For CAMLQueryOption you can use the var t_CAMLQUery or make it direct by the service
  var t_CAMLQuery = "";
  //t_CAMLQuery = "<Query><Where><Neq><FieldRef Name='Status' /><Value Type='Choice'>Closed</Value></Neq></Where></Query>";

  // This is for test; it's your choice to call the edit or disp form
  var Edit = true;
 
 

  // Global var for Grid content
  var GridContent = new Array();
  // Global var for counter
  var giCount = 0;



  $().SPServices({
            operation:'GetListItems',
            async: false,
            webURL: SPwebURL,
            listName: SPlistName,
            CAMLViewFields:'',
            CAMLQuery: t_CAMLQuery,
            CAMLRowLimit: 0,
            ///////// ALL Admin Views and associated GUIDS for those Views are below ////////
           
            viewName: "{D6C3FD3B-BD4C-4D78-ADE5-4E3553631B4D}",
            //i.e. All Developers Under Josh
            //View Name: View-AdminAjaxJosh           
            //View File Name: View-PageAdminAjaxJosh.aspx
           
CAMLQueryOptions:'<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns><IncludeAttachmentUrls>TRUE</IncludeAttachmentUrls></QueryOptions>',
            completefunc: function (xData,Status){

                // very import: use '[nodeName=z:row]'               
                $(xData.responseXML).find('[nodeName=z:row]').each(function(){

                          if ( Edit )
                          {
                                   // Edit
                                   var target = '_parent';
                                   var style =
                                 
                                                  
                                  t_Title =    "<a  target='" + target + "' href='" + SPwebURL + "/Submit Request/EditFormAdmin.aspx?ID=" +
                                          $(this).attr("ows_ID") +
                                          "&Source=/Submit Request/AdminPages/AdminAjaxNew.aspx"
                                          //window.parent.location +
                                           "'>" +
                                          "<img src='/_layouts/images/EDIT.GIF' border='0' align='middle'> "  +
                                          $(this).attr("ows_Title") +
                                          "</a>"
                                         
                                         
                                                    
                                  t_Print =    "<a  target='" + target + "' href='" + SPwebURL + "/Submit Request/AdminPages/AdminEditRequestPrintPreview.aspx?AspXPage=g%5F01308C1CCDA54350BB0F50CCC502CC5C:%2540ID%3D" +
                                          $(this).attr("ows_ID") +
                                          //"&Source=/Submit Request/AdminPages/AdminAjax.aspx" + 
                                          //window.parent.location +
                                           "'>" +
                                          "<img src='http://bi-uat/Submit Request/AdminPages/print.gif' border='0' align='middle'> "  +
                                          //$(this).attr("ows_Title") +
                                          "</a>"

                                         
                                         
                                         
                          } else {
                                   // Display
                                  t_Title = "<a href='" + SPwebURL + "/Submit Request/WorkQueueView.aspx?ID=" +                                 
                                          $(this).attr("ows_ID") +
                                          //"&Source=" +
                                          window.location + "'>" +
                                          "<img src='/_layouts/images/ITTASK.GIF' border='0' align='middle'> " +
                                          $(this).attr("ows_Title") +
                                          "</a>"
                          }



                          GridContent[giCount] = new Array(25);
                          GridContent[giCount][0] = '<img src="../../jQuery/dataTables-1.6/media/images/details_open.png">';
                         
                           GridContent[giCount][1] = $(this).attr("ows_ID");
                       
                          GridContent[giCount][2] = SpDateToList($(this).attr("ows_Created"));
                         
  GridContent[giCount][3] = t_Title+ "<br><br>" + t_Print

                          GridContent[giCount][4] = SpTextToList($(this).attr("ows_Status"));                         
                         
                          GridContent[giCount][5] = SpTextToList($(this).attr("ows_Priority"));
                         
                          GridContent[giCount][6] = SpNameToList($(this).attr("ows_Requestor"));                         
                         
  GridContent[giCount][7] = SpTextToList($(this).attr("ows_Description_x0020_of_x0020_Reque"));
 
  GridContent[giCount][8] = SpDateToList($(this).attr("ows_Requested_x0020_Delivery_x0020_D"));
 
  GridContent[giCount][9] = SpNameToList($(this).attr("ows_Assigned_x0020_To"));

                          GridContent[giCount][10] = SpBooleanToList($(this).attr("ows_Attachments"));//yes/no
                         
                          GridContent[giCount][11] = SpLinkToList($(this).attr("ows_Attachments"));
                         
                          GridContent[giCount][12] = SpDateToList($(this).attr("ows_Modified"));
                         
                          GridContent[giCount][13] = SpTimeToList($(this).attr("ows_Modified"));
                         
                          GridContent[giCount][14] = SpNameToList($(this).attr("ows_Editor"));
                         
                          GridContent[giCount][15] = SpTextToList($(this).attr("ows_Delivery_x0020_Method"));
                         
                          GridContent[giCount][16] = SpTextToList($(this).attr("ows_Business_x0020_Needs"));
                         
                          GridContent[giCount][17] = SpTextToList($(this).attr("ows_Frequency"));
                         
                          GridContent[giCount][18] = SpTextToList($(this).attr("ows_Subject_x0020_Area"));
                         
                          GridContent[giCount][19] = SpTextToList($(this).attr("ows_Request_x0020_Type"));
                         
                          GridContent[giCount][20] = SpTextToList($(this).attr("ows_Desired_x0020_Fields"));
                         
                          GridContent[giCount][21] = SpTextToList($(this).attr("ows_Criteria"));
                         
                          GridContent[giCount][22] = SpTextToList($(this).attr("ows_Special_x0020_Instructions"));
                         
                          GridContent[giCount][23] = SpTextToList($(this).attr("ows_JIRA_x0020_Id"));
                         
                          GridContent[giCount][24] = SpTextToList($(this).attr("ows_Audience"));
                         
                          GridContent[giCount][25] = SpTextToList($(this).attr("ows_Department_x002f_Area"));
                         

                          giCount++;
                });

}


});

</script>


<script type="text/javascript">

  $("#tabl_wrapper").css("width","100%");
  $("#tabl").css("width","100%");

  // Global variable for the DataTables object
  var oTable;

  /* Sorting Date */
  jQuery.fn.dataTableExt.oSort['ch_date-asc']  = function(a,b) {
        var chDatea = a.split('.');
        var chDateb = b.split('.');

        var x = (chDatea[2] + chDatea[1] + chDatea[0]) * 1;
        var y = (chDateb[2] + chDateb[1] + chDateb[0]) * 1;

        return ((x < y) ? -1 : ((x > y) ?  1 : 0));
  };

  jQuery.fn.dataTableExt.oSort['ch_date-desc'] = function(a,b) {
        var chDatea = a.split('.');
        var chDateb = b.split('.');

        var x = (chDatea[2] + chDatea[1] + chDatea[0]) * 1;
        var y = (chDateb[2] + chDateb[1] + chDateb[0]) * 1;

        return ((x < y) ? 1 : ((x > y) ?  -1 : 0));
  };

  /* Detection Date */
  jQuery.fn.dataTableExt.aTypes.push(
        function ( sData )
        {
                if (sData.match(/^(0[1-9]|[12][0-9]|3[01])\.(0[1-9]|1[012])\.(19|20|21)\d\d$/))
                {
                        return 'ch_date';
                }
                return null;
        }
  );

  /* Formating function for row details */
  // unncommment the lines below if you want the fields to display //
  function fnFormatDetails ( nTr )
  {
        var aData = oTable.fnGetData( nTr );
        var sOut = ''
        //sOut += '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:250px;">';
        //sOut += '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:5px;">';
        sOut += '<table cellpadding="5" border="1" style="width:900px;">';
        //sOut += '<tr><td>Requested Delivery Date       :</td><td>' + aData[8]  + '</td></tr>';
        //sOut += '<tr><td>Created                  :</td><td>' + aData[2]  + '</td></tr>';
        //sOut += '<tr><td>Request Title            :</td><td>' + aData[3]  + '</td></tr>';
        //sOut += '<tr><td>Attachements                 :</td><td>' + aData[4]  + '</td></tr>';
        //sOut += '<tr><td>Priority                 :</td><td>' + aData[5]  + '</td></tr>';
        //sOut += '<tr><td>Anticipated Completion Date        :</td><td>' + aData[6]  + '</td></tr>';
        //sOut += '<tr><td>Actual Completion Date             :</td><td>' + aData[7]  + '</td></tr>';
        //sOut += '<tr><td>Assigned To                   :</td><td>' + aData[8]  + '</td></tr>';
        //sOut += '<tr><td>Status                        :</td><td>' + aData[9]  + '</td></tr>';
        //sOut += '<tr><td>Requestor                      :</td><td>' + aData[10]  + '</td></tr>';
        //sOut += '<tr><td>Attachments                   :</td><td>' + aData[11]  + '</td></tr>';
        //sOut += '<tr><td>Requested Delivery Date                   :</td><td>' + aData[7]  + '</td></tr>';
        //sOut += '<tr><td>Audience                   :</td><td>' + aData[29]  + '</td></tr>';
        //sOut += '<tr><td>Department/Area                   :</td><td>' + aData[30]  + '</td></tr>';
        //sOut += '<tr><td>Description of Request     :</td><td>' + aData[7]  + '</td></tr>';
        //sOut += '<tr><td>Delivery Method        :</td><td>' + aData[19]  + '</td></tr>';
        //sOut += '<tr><td>Business Needs             :</td><td>' + aData[20]  + '</td></tr>';
        //sOut += '<tr><td>Frequency                 :</td><td>' + aData[21]  + '</td></tr>';
        //sOut += '<tr><td>Subject Area             :</td><td>' + aData[22]  + '</td></tr>';
        //sOut += '<tr><td>Request Type             :</td><td>' + aData[23]  + '</td></tr>';
        //sOut += '<tr><td>Desired Fields             :</td><td>' + aData[24]  + '</td></tr>';
        //sOut += '<tr><td>Criteria                 :</td><td>' + aData[25]  + '</td></tr>';
        //sOut += '<tr><td>Special Instructions     :</td><td>' + aData[26]  + '</td></tr>';
        //sOut += '<tr><td>JIRA Id                 :</td><td>' + aData[27]  + '</td></tr>';      
        sOut += '</table>';
        return sOut;
  }

  $(document).ready(function() {

       

TableToolsInit.oFeatures = {
     "bCsv": true,
     "bXls": true,
     "bCopy": true,
     "bPrint": false
    

};
         TableToolsInit.sSwfPath = "../../jQuery/dataTables-1.6/extras/TableTools/media/swf/ZeroClipboard.swf";

        oTable=$('#splist').dataTable( {
               
                "fnInitComplete": function() { $('#processingDiv').hide(); },
               
                "oLanguage": {
                          "sProcessing": "Loading...",
                          "sLengthMenu": "_MENU_ Showing",
                          "sLengthMenu": 'Show <select><option value="5">5</option><option value="10">10</option><option value="15">15</option><option value="20">20</option><option value="25">25</option><option value="-1">All</option></select> Requests',
                          "sZeroRecords": "No Requests Found",
                          "sInfo": "_START_ to _END_ of _TOTAL_ Requests",                        
                          "sInfoFiltered": "(filtered from _MAX_ Requests)",                        
                          "sInfoPostFix": "",
                          "sSearch": "Search",
                          "sEmptyTable": "No Requests found",
                          "sUrl": "",                         
                          "oPaginate": {
                                  "sFirst":    "First",
                                  "sPrevious": "Previous",
                                  "sNext":     "Next",
                                  "sLast":     "Last"
                          }
                },
                "bPaginate": true,
                "bJQueryUI": true,
                "bLengthChange": true,
                "bAutoWidth": false,
                "bFilter": true,
                "bSort": true,
                "bInfo": true,               
                "sPaginationType": "full_numbers",
                "iDisplayLength": 5,
"sDom":'T<"toolbar"><""<"clear"><"fg-toolbar ui-widget-header ui-corner-tl ui-corner-tr ui-helper-clearfix"lfr>>t<""<"clear"><"fg-toolbar ui-widget-header ui-corner-tl ui-corner-tr ui-helper-clearfix"ip>>',



                "aaData": GridContent,
                "aoColumns": [
                  {"sTitle":"Detail",                              "sClass": "center", "bSortable":false, "bVisible":true},
                  {"sTitle":"ID",                              "sClass": "center", "bSortable":true, "bVisible":true},
                  {"sTitle":"Created",                                  "sClass": "center", "bSortable":true"bVisible":true},
                  {"sTitle":"Request Title",                             "sClass": "left", "bSortable":false"bVisible":true},
                  {"sTitle":"Status",                       "sClass": "center", "bSortable":true, "bVisible":true, "sWidth":"150px"},
                  {"sTitle":"Priority",                         "sClass": "center", "bSortable":true"bVisible":true, "sWidth":"150px"},
                  {"sTitle":"Requestor",                            "sClass": "center", "bSortable":true"bVisible":true, "sWidth":"200px"},                 
                  {"sTitle":"Description of Request",        "bVisible":false},                 
                  {"sTitle":"8 Hide-Requested Delivery Date",         "sClass": "center", "bSortable":true"bVisible":false, "sWidth":"150px"},                 
                  {"sTitle":"Assigned To",              "sClass": "center", "bSortable":true"bVisible":true, "sWidth":"200px"},
                  {"sTitle":"ATCHMT",    "sClass": "center", "bSortable":true"bVisible":true, "sWidth":"50px"},
  {"sTitle":"11 Hide-Author",                          "bSortable":false"bVisible":false},
  {"sTitle":"12 Hide-Modified",                          "bSortable":false"bVisible":false},
  {"sTitle":"13 Hide-Modified",                          "bSortable":false"bVisible":false},
  {"sTitle":"14 Hide-Editor",                          "bSortable":false"bVisible":false},
  {"sTitle":"15 Hide-Delivery Method",                          "bSortable":false"bVisible":false},
  {"sTitle":"16 Hide-Business Needs",                          "bSortable":false"bVisible":false},
  {"sTitle":"17 Hide-Frequency",                          "bSortable":false"bVisible":false},
  {"sTitle":"18 Hide-Subject Area",                          "bSortable":false"bVisible":false},
  {"sTitle":"19 Hide-Request Type",                          "bSortable":false"bVisible":false},
  {"sTitle":"20 Hide-Desired Fields",                          "bSortable":false"bVisible":false},
  {"sTitle":"21 Hide-Criteria",                          "bSortable":false"bVisible":false},                              
  {"sTitle":"22 Hide-Special Instructions",                          "bSortable":false"bVisible":false},
  {"sTitle":"23 Hide-JIRA Id",                          "bSortable":false"bVisible":false},            
  {"sTitle":"24 Hide-Audience",                                         "bSortable":false, "bVisible":false},
                  {"sTitle":"25 Hide-Department Area",                               "bSortable":false, "bVisible":false}
                  ],
                "aaSorting": [ [1,'desc'], [2,'asc'] ],               
                "bProcessing": true,
                "bStateSave": true,
                "bServerSide": false,
                "bRetrieve": true              
         
          });
         
         
$("div.toolbar").html('<strong>Admin Only - View for New Requests.</strong><br>To edit the request, please click on the Request Title hyperlink.  You will be directed to the Edit Request page.  Click on the Detail button to view get a quick overview of the request.');

//<br><br> TIP: Sort multiple columns simultaneously by holding down the shift key and clicking additional column headers.<br>.css("font-family", "Tahoma", "font-size", "5pt");

          // Fixed Header
          new FixedHeader( oTable );

        /* Add event listener for opening and closing details
         * Note that the indicator for showing which row is open is not controlled by DataTables,
         * rather it is done here
         */

        $('td img', oTable.fnGetNodes() ).each( function () {
                $(this).click( function () {
                        var nTr = this.parentNode.parentNode;
                        if ( this.src.match('details_close') )
                        {
                                /* This row is already open - close it */
                                this.src = "../../jQuery/dataTables-1.6/media/images/details_open.png";
                                oTable.fnClose( nTr );
                        }
                        else
                        {
                                /* Open this row */
                                this.src = "../../jQuery/dataTables-1.6/media/images/details_close.png";
                                oTable.fnOpen( nTr, fnFormatDetails(nTr), 'details' );
                        }
                } );
        } );

  } );

</script>

<table id="splist" class="display">
<tbody id="tablebody">
  <!-- GetListsItem write data here -->
</tbody>
</table>
<div id="processingDiv">Please wait while content is being loaded...<br>
<br>
<img src="../../jQuery/dataTables-1.6/images/ajax-loader3.gif" alt="loading" width="32" height="32"></div>

</body>

</html>

 have used