Unveiling the Layers: Delving into the Sitecore Tech Stack: Building Custom Analytics Report – Part3

In the previous posts of this blog series I discussed the business needs for building a custom analytics report and I showed how you can collect data into MongoDB and aggregate this data into the reporting SQL server database, now it’s time to display data as a report and of course adding this report to the existing reports under experience analytics.

Before starting you will need to install Sitecore Rocks plugin to your Visual Studio IDE, you can download it from here

If you missed the first two posts you can check them from here:

Now as you can from the following screen shot, I opened my solution code to the right and sitecore rocks connected to my site on the left.

What we are going to do here is to create a simple SPEAK application in which two main steps:

· Call a controller that access the reporting database to get the information we need.

· Display this data using the SPEAK app

You may (not) know that Sitecore SPEAK is the new UI that replace the old Sheer UI, it is being used partially since sitecore 7.2 but it becomes popular with Sitecore 8, if you are new to Sitecore SPEAK you may consider taking a look to the following:

· https://mhwelander.net/2014/06/27/speak-for-newbies-part-1-creating-a-new-application/

· https://cmsview.wordpress.com/2015/10/07/sitecore-8-for-beginners-creating-speak-application/

Now let’s start by adding a new SPEAK component, as you can see from the following screen shots:

You can see that Sitecore rocks will allow you to create sitecore item immediately after doing the above, see the following screen shot:

In the next you will need to update JS file by adding a new AJAX call to hit backend controller action in which you access the reporting database to get your information and return them using a simple JSON object.

Following is the code snippet for both the JS file and cshtml file of the SPEAK component:

MyReport.JS

	// This section will add the expierence analytics filters to the report.
	require.config({
	paths: {
	experienceAnalytics: "/sitecore/shell/client/Applications/ExperienceAnalytics/Common/Layouts/Renderings/Shared/ExperienceAnalytics"
	}
	});
	// Analytics Filters End.

	define(["sitecore", "experienceAnalytics"], function (Sitecore, ExperienceAnalytics) {
	var model = Sitecore.Definitions.Models.ControlModel.extend({
	initialize: function (options) {
	this._super();

	// get expierence analytics filters information
	var dateRange = ExperienceAnalytics.getDateRange(),
	subsite = ExperienceAnalytics.getSubsite();

	if (dateRange && subsite) {
	var app = this;
	app.set("ReportData", '');
	var dateFromValue = ExperienceAnalytics.convertDateFormat(dateRange.dateFrom);
	var dateToValue = ExperienceAnalytics.convertDateFormat(dateRange.dateTo);
	var subsiteValue = subsite;
	var reportFilter = { dateFromString: dateFromValue, dateToString: dateToValue, subsite: subsiteValue };
	// call the method that will get data from reporting database.
	app.getReportListData(app, reportFilter);
	}
	},
	getReportListData: function (app, reportFilter) {
	// Ajax call to get the required data.
	jQuery.ajax({
	type: "GET",
	dataType: "json",
	data: { 'reportType': 'banners', 'dateFromString': reportFilter.dateFromString, 'dateToString': reportFilter.dateToString, 'subsite': reportFilter.subsite, 'sortfield': reportFilter.sortfield, 'sortorder': reportFilter.sortorder },
	url: "/api/sitecore/Reports/MyReport", // controller action
	cache: false,
	success: function (data) {
	app.set("ReportData", data);
	},
	error: function () {
	if (window.console && console.log)
	console.log("There was an error in GetReportListData() function!");
	}
	});
	}
	});

	var view = Sitecore.Definitions.Views.ControlView.extend({
	initialize: function (options) {
	this._super();
	}
	});

	Sitecore.Factories.createComponent("MyReport", model, view, ".sc-MyReport");
	});

view raw MyReport.js hosted with ❤ by GitHub

MyReport.cshtml

	@using Sitecore.Mvc
	@using Sitecore.Mvc.Presentation
	@using Sitecore.Web.UI.Controls.Common.UserControls
	@model RenderingModel
	@{
	var rendering = Html.Sitecore().Controls().GetUserControl(Model.Rendering);
	rendering.Class = "sc-MyReport";
	rendering.Requires.Script("client", "MyReport.js");

	var htmlAttributes = rendering.HtmlAttributes;
	}
	<div @htmlAttributes>
	<table id="report-table" class="sc-table table">
	<thead>
	<tr>
	<th class="sc-table-head">field0</th>
	<th class="sc-table-head" data-fieldname="field1">field1</th>
	<th class="sc-table-head" data-fieldname="field2">field2</th>
	<th class="sc-table-head" data-fieldname="field3">field3</th>
	</tr>
	</thead>
	The "Report List" is the
	<tbody data-bind="foreach: ReportData">
	<tr>
	<td class="ventilate">
	<span data-bind="text:field0 >"></span>
	</td>
	<td class="ventilate">
	<span data-bind="text: field1"></span>
	</td>
	<td class="ventilate">
	<span data-bind="text: field2"></span>
	</td>
	<td class="ventilate">
	<span data-bind="text: field3"></span>
	</td>
	</tr>
	</tbody>
	</table>
	</div>

view raw MyReport.cshtml File hosted with ❤ by GitHub

Following is a code sample of the controller back-end code:

	public class ReportsController : Controller
	{
	public class MyReportObject
	{
	public string Field0 { set; get; }
	public string Field1 { set; get; }
	public string Field2 { set; get; }
	public string Field3 { set; get; }
	}

	public ActionResult MyReport()
	{
	Var MyData = GetSQLServerReportingDatabaseData();

	List<MyReportObject> _source = new List<MyReportObject>()
	foreach (var item in MyData)
	{
	_source.Add(new MyReportObject()
	{
	Field0 = item.Fields["Field0"].Value.ToString(),
	Field1 = item.Fields["Field1"].Value.ToString(),
	Field2 = item.Fields["Field2"].Value.ToString(),
	Field3 = item.Fields["Field3"].Value.ToString()
	});
	}

	return Json(_source, JsonRequestBehavior.AllowGet);
	}
	}