Bind SharePoint List Data Into JQuery Datatable Using Content Search Web Part (CSWP) In SharePoint Server 2013

In the first step, let's create the Authoring and Publishing Site Collections. Authoring Site collection allows authors to manage their website content while the Publishing Site collection reviews the content from the authoring site collection.

Open the Authoring Site Collection.

Create a list named “Employee Details” that contains Employee name, Employee designation, Employee ID, Employee designation, Blood group, Email, and Mobile.

Let’s go and create a control and a template to display the items.

Control Template

It provides the overall structure (layout) of the HTML elements along with start and end tags.
Item Template

It renders the items from the list, and contains text and pictures.

Now, let's create two display templates for displaying a bootstrap carousel menu control and a menu item.

Code – Datatable Control Template

Upload the script and CSS into style library.

Add the scripts and CSS into Control template.

<script>
$includeScript(this.url, "~sitecollection/Style%20Library/datatable/jquery.dataTables.min.css");
$includeCSS(this.url, "~sitecollection/Style%20Library/datatable/jquery.dataTables.min.js");
</script>

Now, let's define the HTML layout to the Control template.

<table id="datatable" class="display">
<thead>
<tr>
<th>Employee ID</th>
<th>Employee Name</th>
<th>Designation</th>
<th>Blood Group</th>
<th>Email</th>
<th>Mobile</th>
</tr>
</thead>
<tbody>
_#= ctx.RenderItems(ctx) =#_
</tbody>
</table>

The full code looks like the following.

<html xmlns:mso="urn:schemas-microsoft-com:office:office" xmlns:msdt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
<head>
<title>Datatable Control</title>
<!--[if gte mso 9]><xml>
<mso:CustomDocumentProperties>
<mso:TemplateHidden msdt:dt="string">0</mso:TemplateHidden>
<mso:MasterPageDescription msdt:dt="string">Display a Accordion Control</mso:MasterPageDescription>
<mso:ContentTypeId msdt:dt="string">0x0101002039C03B61C64EC4A04F5361F385106601</mso:ContentTypeId>
<mso:TargetControlType msdt:dt="string">;#SearchResults;#Content Web Parts;#</mso:TargetControlType>
<mso:HtmlDesignAssociated msdt:dt="string">1</mso:HtmlDesignAssociated>
<mso:HtmlDesignConversionSucceeded msdt:dt="string">True</mso:HtmlDesignConversionSucceeded>
<mso:CrawlerXSLFile msdt:dt="string"></mso:CrawlerXSLFile>
<mso:HtmlDesignPreviewUrl msdt:dt="string"></mso:HtmlDesignPreviewUrl>
<mso:HtmlDesignStatusAndPreview msdt:dt="string">http://technologygeeks/sites/publishing/_catalogs/masterpage/Display Templates/Content Web Parts/banner/bannercontrol.html, Conversion successful.</mso:HtmlDesignStatusAndPreview>
</mso:CustomDocumentProperties>
</xml><![endif]-->
</head>
<body>
<script>
$includeCSS(this.url, "~sitecollection/Style%20Library/datatable/jquery.dataTables.min.css");
$includeScript(this.url, "~sitecollection/Style%20Library/datatable/jquery.dataTables.min.js");
$includeScript(this.url, "~sitecollection/Style%20Library/datatable/jquery-1.8.2.min.js");
</script>
<div>
<!--#_if (!$isNull(ctx.ClientControl) && !$isNull(ctx.ClientControl.shouldRenderControl) && !ctx.ClientControl.shouldRenderControl()){return "";}
ctx.ListDataJSONGroupsKey = "ResultTables";
ctx["CurrentItems"] = ctx.ListData.ResultTables[0].ResultRows;
var siteURL = SP.PageContextInfo.get_siteAbsoluteUrl();
AddPostRenderCallback(ctx, function() {
$.getScript(siteURL + "");
$('#datatable').dataTable();
});
_#-->
<table id="datatable" class="display">
<thead>
<tr>
<th>Employee ID</th>
<th>Employee Name</th>
<th>Designation</th>
<th>Blood Group</th>
<th>Email</th>
<th>Mobile</th>
</tr>
</thead>
<tbody>
_#= ctx.RenderItems(ctx) =#_
</tbody>
</table>
</div>
</body>
</html>

Datatable Item Template

Let’s go to Item template and declare the managed properties and necessary variables.

<!--#_
var siteURL = SP.PageContextInfo.get_siteServerRelativeUrl();
var itemIdx = ctx.CurrentItemIdx+1;
var title = $getItemValue(ctx, "Title");
var empid = $getItemValue(ctx, "Employee ID");
var empname = $getItemValue(ctx, "Employee Name");
var designation = $getItemValue(ctx, "Employee Designation");
var blood = $getItemValue(ctx, "Blood Group");
var email = $getItemValue(ctx, "Email);
var mobile = $getItemValue(ctx, "Mobile);
var esActive ="";
_#-->

Render the HTML structure with the list item information.

<tr>
<td>_#= empid =#_</td>
<td>_#= title =#_</td>
<td>_#= designation =#_</td>
<td>_#= blood =#_</td>
<td>_#= email =#_</td>
<td>_#= mobile =#_</td>
</tr>

Overall, the Item template code looks like the following.

<html xmlns:mso="urn:schemas-microsoft-com:office:office" xmlns:msdt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Datatable Item</title>
<!--[if gte mso 9]><xml>
<mso:CustomDocumentProperties>
<mso:TemplateHidden msdt:dt="string">0</mso:TemplateHidden>
<mso:MasterPageDescription msdt:dt="string">Displays an Datatable Item.</mso:MasterPageDescription>
<mso:ContentTypeId msdt:dt="string">0x0101002039C03B61C64EC4A04F5361F385106603</mso:ContentTypeId>
<mso:TargetControlType msdt:dt="string">;#SearchResults;#Content Web Parts;#</mso:TargetControlType>
<mso:HtmlDesignAssociated msdt:dt="string">1</mso:HtmlDesignAssociated>
<mso:ManagedPropertyMapping msdt:dt="string">'Title':'Title','Employee ID':'Employee ID','Employee Name':'Employee Name','Employee Designation':'Employee Designation','Blood Group':'Blood Group','Email':'Email','Mobile':'Mobile'</mso:ManagedPropertyMapping>
<mso:CrawlerXSLFile msdt:dt="string"></mso:CrawlerXSLFile>
<mso:HtmlDesignPreviewUrl msdt:dt="string"></mso:HtmlDesignPreviewUrl>
<mso:HtmlDesignConversionSucceeded msdt:dt="string">True</mso:HtmlDesignConversionSucceeded>
<mso:HtmlDesignStatusAndPreview msdt:dt="string">http://technologygeeks/sites/publishing/_catalogs/masterpage/Display Templates/Content Web Parts/banner/BannerItem.html, Conversion successful.</mso:HtmlDesignStatusAndPreview>
</mso:CustomDocumentProperties>
</xml><![endif]-->
</head>
<body>
<div>
<!--#_
var siteURL = SP.PageContextInfo.get_siteServerRelativeUrl();
var itemIdx = ctx.CurrentItemIdx+1;
var title = $getItemValue(ctx, "Title");
var empid = $getItemValue(ctx, "Employee ID");
var empname = $getItemValue(ctx, "Employee Name");
var designation = $getItemValue(ctx, "Employee Designation");
var blood = $getItemValue(ctx, "Blood Group");
var email = $getItemValue(ctx, "Email");
var mobile = $getItemValue(ctx, "Mobile");
var esActive ="";
_#-->
<tr>
<td>_#= empid =#_</td>
<td>_#= title =#_</td>
<td>_#= designation =#_</td>
<td>_#= blood =#_</td>
<td>_#= email =#_</td>
<td>_#= mobile =#_</td>
</tr>
</div>
</body>
</html>

Upload the display template under Site Settings - > Master page and page layouts -> Display Templates -> Content Web Parts.

Now, we are going to insert some data into Employee details list from Authoring Site Collection.

Run the Search Service application "Crawl" now.

Now, let’s go to the SharePoint page.

Add a CSWP into this page.

Map a search query to get the result from the Authoring Site Collection.

Click Edit Web Part -> change query.

URL - http://technologygeeks/sites/authoring/Lists/Employee%20details/

Provide the number of items to display.

Choose the display templates.

Click OK. Under property mapping section, map the managed properties of the Employee name, Employee designation, Employee ID, Blood group, Email, and Mobile.

Click "OK" to complete the setup.

The following is the final result.

Provide Employee name for search.

Note - The result has been displayed using Search Service. You need to run the Search Service application.

After adding the content into the SharePoint list, the result will be successfully displayed into the page.

Datatable Reference

https://datatables.net/

3 Comments

Join the conversation! Your thoughts help the community grow.

Prashant Dikonda8y ago
Hi, my results rows from search query is more than 10000+, But datatable showing only overall with paging as mentioning 'Number of items to show' like max 50. what about other results
Ravi Kandel9y ago
Awesome Thanks for sharing
- Vinodh Narayanan9y ago
  Thanks for you valuable comment friend