Introduction
In this post, I shall extend our custom Roles Based Access Control (RBAC) framework defined in Part 1 to include dynamic role based reporting. Roles Based Access Control (RBAC) is a method of regulating access to an application's features or resources based on the roles of individual users within an organization. Roles are defined according to job requirements, authority, and responsibility within the organization. However, implementing RBAC comes with its challenges. It takes time and effort to determine the permissions each role will be assigned but there are a great number of benefits. The burden of system administration can be severely decreased; roles can be easily created, changed, or discontinued as the needs of the organization evolve, without having to individually update the privileges for every user. Role-Based Access Control (RBAC) is now widely accepted as the best practice for setting such controls by the majority of large organizations.
The extended framework will provide functionality to enable the application system administrator to define application reports and associate each report with a user role thus controlling which reports are accessible by which users. The report definition process simply requires a report name/description, database stored procedure name and report filter parameters (optional). The default report results template (cshtml) used to present the report results to the end user can be swapped out with your custom designed template. The entire process for defining a report, including database stored procedure creation, can be delegated away from the application developer. The report definition can be delegated to the application system administrator and the database stored procedure creation can be delegated to the database administrator. The extended framework will present a list of reports based on the user’s role(s); each report can then be displayed via a UI screen with the option of being filtered before finally displaying the report results.
Background
Developing applications for any organization will require, at some point, a reporting facility (whether it be to screen or printer) in order to report on the data stored in the application’s database. In most organizations, it’s common that not all users are permitted access to every report defined within the system. Generally speaking, it’s typical to permit access to a subset of reports for each role thus controlling which reports are accessible by which users where users can be associated with many roles. Our reporting framework extension has been purposely designed to be flexible enabling our default reporting solution to be swapped out for an alternative vendor’s solution without affecting the underlying RBAC reporting functionality. In order to demonstrate the framework's flexibility, our default reporting solution will be substituted to integrate with Microsoft’s SQL Server Reporting Services (SSRS) solution.
In either case, regardless of the underlying reporting solution, our reports shall remain dynamic driven by report parameters defined in the application’s report definition.
Extending the Database
In order to extend our framework to incorporate dynamic role based reporting, we will need to add a new report table and corresponding link table to our original RBAC database. Our updated Entity-Relationship (ER) diagram and Database Schema diagram are detailed below where newly appended reporting entities have been shaded yellow.
RBAC Entity-Relationship Diagram
RBAC Database Schema Diagram
The ROLES table in our RBAC data model forms the kingpin for role based access to which all ‘custom’ entities can be associated. This enables functionality driven via role based access to be generated by simply adding new tables to our RBAC data model linked to the Roles table (as illustrated above) and surfacing the required functionality in our RBACUser class.
RBAC SQL Schema Update Script
The sample project available for download contains two SQL script files named ‘RBAC_FullSchema.sql’ and ‘RBAC_UpdateSchema.sql’. The former script creates a new RBAC database schema whereas the later script updates an existing RBAC database schema, created in part 1, to include only the new reporting tables.
Each script will create the tables REPORTS, PARAMETERS, LNK_ROLE_REPORT and LNK_REPORT_PARAMETER including all necessary table constraints (table constraints and link tables not shown in the following SQL script listing).
CREATE TABLE [dbo].[REPORTS](
[Report_Id] [int] IDENTITY(1,1) NOT NULL,
[LastModified] [datetime] NOT NULL,
[Inactive] [bit] NOT NULL,
[ReportName] [nvarchar](150) NOT NULL,
[ReportDescription] [nvarchar](max) NULL,
[Template] [nvarchar](50) NULL,
[StoredProcedureName] [nvarchar](50) NULL,
CONSTRAINT [PK_REPORTS] PRIMARY KEY CLUSTERED
(
[Report_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
...
CREATE TABLE [dbo].[PARAMETERS](
[Parameter_Id] [int] IDENTITY(1,1) NOT NULL,
[ParameterName] [nvarchar](30) NOT NULL,
[ParameterType] [nvarchar](50) NULL,
[DisplayLabel] [nvarchar](50) NULL,
[Required] [bit] NULL
CONSTRAINT [PK_PARAMETERS] PRIMARY KEY CLUSTERED
(
[Parameter_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Extending our Entity Framework (EF) RBAC Model
Now that we have updated our SQL database schema, we need to extend our Entity Framework (EF) database context model to reflect the new tables REPORTS, PARAMETERS and associated link tables. The sections of code shaded grey (see pdf version of this article) illustrate the additional code required for RBAC Reporting when extending the original framework. If you are implementing RBAC as a new project or integrating into an existing project that is not already using RBAC, ignore the highlighted sections and refer to the framework as a whole in the sample project available for download.
using System;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
public partial class RBAC_Model : DbContext
{
public RBAC_Model()
: base("name=RBAC_Model")
{
}
public virtual DbSet<PERMISSION> PERMISSIONS { get; set; }
public virtual DbSet<ROLE> ROLES { get; set; }
public virtual DbSet<USER> USERS { get; set; }
public virtual DbSet<REPORT> REPORTS { get; set; }
public virtual DbSet<PARAMETER> PARAMETERS { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<PERMISSION>()
.HasMany(e => e.ROLES)
.WithMany(e => e.PERMISSIONS)
.Map(m => m.ToTable("LNK_ROLE_PERMISSION").MapLeftKey("Permission_Id").MapRightKey("Role_Id"));
modelBuilder.Entity<ROLE>()
.HasMany(e => e.USERS)
.WithMany(e => e.ROLES)
.Map(m => m.ToTable("LNK_USER_ROLE").MapLeftKey("Role_Id").MapRightKey("User_Id"));
modelBuilder.Entity<REPORT>()
.HasMany(e => e.ROLES)
.WithMany(e => e.REPORTS)
.Map(m => m.ToTable("LNK_ROLE_REPORT").MapLeftKey("Report_Id").MapRightKey("Role_Id"));
modelBuilder.Entity<PARAMETER>()
.HasMany(e => e.REPORTS)
.WithMany(e => e.PARAMETERS)
.Map(m => m.ToTable("LNK_REPORT_PARAMETER").MapLeftKey("Parameter_Id").MapRightKey("Report_Id"));
}
}
[Table("REPORTS")]
public partial class REPORT
{
public REPORT()
{
PARAMETERS = new HashSet<PARAMETER>();
ROLES = new HashSet<ROLE>();
}
[Key]
public int Report_Id { get; set; }
public DateTime LastModified { get; set; }
public bool Inactive { get; set; }
public string ReportName { get; set; }
public string ReportDescription { get; set; }
public string Template { get; set; }
public string StoredProcedureName { get; set; }
public virtual ICollection<ROLE> ROLES { get; set; }
public virtual ICollection<PARAMETER> PARAMETERS { get; set; }
}
[Table("PARAMETERS")]
public partial class PARAMETER
{
public PARAMETER()
{
REPORTS = new HashSet<REPORT>();
}
[Key]
public int Parameter_Id { get; set; }
public bool Required { get; set; }
public string ParameterName { get; set; }
public string ParameterType { get; set; }
public string DisplayLabel { get; set; }
public virtual ICollection<REPORT> REPORTS { get; set; }
}
[Table("ROLES")]
public partial class ROLE
{
public ROLE()
{
PERMISSIONS = new HashSet<PERMISSION>();
USERS = new HashSet<USER>();
REPORTS = new HashSet<REPORT>();
}
[Key]
public int Role_Id { get; set; }
[Required]
public string RoleName { get; set; }
public string RoleDescription { get; set; }
public bool IsSysAdmin { get; set; }
public DateTime? LastModified { get; set; }
public virtual ICollection<PERMISSION> PERMISSIONS { get; set; }
public virtual ICollection<USER> USERS { get; set; }
public virtual ICollection<REPORT> REPORTS { get; set; }
}
We have introduced two new classes into our RBAC model called ‘REPORT’ and ‘PARAMETER’; the ‘ROLE’ class has been refactored to include a REPORTS property of type ICollection<REPORT>. This property will contain the reports associated with the user’s role(s).
Extending our RBACUser Class
Now that we have updated our database context model to include the new reporting tables, we need to modify our RBACUser class to expose a Reports property and functionality to provide a user’s reporting permissions.
Note: Code snippets presented in this article are minimal intended to illustrate additional code and to avoid code repetition defined in part 1. The sections of code shaded grey (see pdf version of this article) illustrate additional code added to the original framework for RBAC Reporting. Refer to the sample project available for download to view the code listing as a whole and in the context of the project.
public class RBACUser
{
public int User_Id { get; set; }
public bool IsSysAdmin { get; set; }
public string Username { get; set; }
private List<UserRole> Roles = new List<UserRole>();
public RBACUser(string _username)
{
this.Username = _username;
this.IsSysAdmin = false;
GetDatabaseUserRolesPermissions();
}
private void GetDatabaseUserRolesPermissions()
{
using (RBAC_Model _data = new RBAC_Model())
{
USER _user = _data.USERS.Where(u => u.Username == this.Username).FirstOrDefault();
if (_user != null)
{
this.User_Id = _user.User_Id;
foreach (ROLE _role in _user.ROLES)
{
UserRole _userRole = new UserRole { Role_Id = _role.Role_Id, ... };
foreach (PERMISSION _permission in _role.PERMISSIONS)
{
_userRole.Permissions.Add(
new PERMISSION { Permission_Id = _permission.Permission_Id, ... });
}
foreach (REPORT _report in _role.REPORTS)
{
_userRole.Reports.Add(
new REPORT { Report_Id = _report.Report_Id, ... });
}
this.Roles.Add(_userRole);
if (!this.IsSysAdmin)
this.IsSysAdmin = _role.IsSysAdmin;
}
}
}
}
public List<REPORT> GetReports()
{
List<REPORT> _retVal = new List<REPORT>();
foreach (UserRole _role in this.Roles)
{
foreach (REPORT _report in _role.Reports)
{
if (_report.Inactive == false)
{
if (!_retVal.Contains(_report))
{
_retVal.Add(_report);
}
}
}
}
return _retVal;
}
}
public class UserRole
{
public int Role_Id { get; set; }
public string RoleDescripton { get; set; }
public List<PERMISSION> Permissions = new List<PERMISSION>();
public List<REPORT> Reports = new List<REPORT>();
}
The RBACUser class has been extended to include the new function called GetReports() which returns the available reports based on the user’s role(s).
RBAC Reporting Solution
Our reporting solution comprises of four main sections as illustrated in the diagram below. The RBACUser class has now been extended to expose the class method called GetReports which returns a collection of REPORT objects associated with the user’s role(s) hence role based reporting.
|
|
The sample project available for download drives the illustrated flow diagram from the main Reports menu. Clicking on the Reports menu, where permitted, invokes the ReportsController.Index controller action method; this method invokes the GetReports class method in the RBACUser class and displays the returned reports in the corresponding controller action view, process 1.
From the available user reports, the user has the option of selecting a report. The controller action Preview is invoked in the ReportsController class passing the Report_Id of the selected report, identified by process 2. The displayed ‘Report Filter Preview’ page has the REPORT object passed as the view’s model; the REPORT object contains a PARAMETERS property which lists the associated report filter parameters (defined via Reports administration). The ‘Report Filter Preview’ page displays the report’s filter parameters including report name/description. The user has the option to provide values for the selected report’s filter parameters, where applicable, before running the report or the option to select another report to preview from the user report list.
Clicking the ‘Generate Report’ button on the ‘Report Filter Preview’ for the selected report amalgamates each filter parameter’s name-value pair into a single string value before invoking the ReportsController.Execute controller action method; the single string value is passed via the rawParams parameter. The controller action method extracts each name-value pair in the rawParams parameter to be used as filter parameters, identified by process 3. The stored procedure is executed using the filter values entered by the user.
|
The resulting report data is merged with the controller view “DefaultResultsTemplate.cshtml” to generate a report results page to be passed back to the user, identified by process 4. Report data is generated by calling the database stored procedure assigned to the report as part of the report administration process.
Associating filter parameters with a report is optional and reports can be associated with database stored procedures that have no input parameters defined and therefore do not require filter parameters to be associated with the report. Additionally, custom result template files (.cshtml) can be used in place of the default results template “DefaultResultsTemplate.cshtml” for each report. However, any custom result template files must be complied into the application before deploying otherwise a runtime error will be generated when the controller action attempts to merge the report data with a specified view template that doesn’t exist.
We need to amalgamate each filter parameter’s name-value pair into a single string and then pass this value as a single string so as to keep the parameters dynamic hence only ever needing the one function; function overloading would not work reliably even when using client slide function parameter mapping in the JavaScript function that invokes the server side logic identified by process 3.
Finally, we have the ability to export the displayed report results as a CSV file identified by process 5.
ReportsController.cs Code Listing
Our RBAC reporting solution has been implemented as a new controller named Reports in order to detached reporting functionality away from the Administrative functionality contained in the Admin controller. However, the reports administration is part of the administrative controller.
The following listing details our main controller action methods for our reporting solution illustrated in the previous flow diagram. We will take a closer look at the ExecuteReportviaSP method at a later stage.
ReportsController.cs
[RBAC]
public class ReportsController : CommonControllerBase
{
private RBAC_Model Database = new RBAC_Model();
public ActionResult Index()
{
return View(this.GetReports());
}
public ActionResult Preview(int id)
{
return View(this.GetReports().Where(p => p.Report_Id == id).FirstOrDefault());
}
[HttpGet]
public ActionResult Execute(int id, string rawParams)
{
return ExecuteReportviaSP(this.GetReports().Where(p => p.Report_Id == id).FirstOrDefault(), rawParams);
}
[HttpPost]
public ActionResult ExportData(FormCollection form)
{
List<dynamic> _list = TempData["ModelData"] as List<dynamic>;
try
{
int _recordsExported = DynamicDataExport2CSV.Export(_list);
return RedirectToAction("Error", "Unauthorised", new RouteValueDictionary(
new { _errorMsg = string.Format("Records Exported: {0}", _recordsExported) }));
}
catch (Exception ex)
{
return RedirectToAction("Error", "Unauthorised", new RouteValueDictionary(
new { _errorMsg = ex.Message }));
}
}
}
Index.cshtml View Code Listing for ReportsController.Index() Action Method
The following listings detail our main controller action method’s view; the controller action method passes a collection of REPORT objects to the view Index.cshtml. Recall from part 1 that every controller action must return an action result in response to a browser request. The controller will search for a view filename of the same name as the action method’s name unless we explicitly specify an alternative view to render.
|
|
Clicking the Reports menu option, where permitted, displays the ‘User Reports Summary’ page which details a list of reports available to the user based on the user’s role(s). The ReportsController.Index controller action method is invoked.
|
ReportsController.cs
public ActionResult Index()
{
return View(this.GetReports());
}
|
|
Since we haven’t specified a view to render, the invoked controller action method name will be used as the view name hence the view Index.cshtml will be used.
|
Index.cshtml
@model IEnumerable<REPORT>
@{
ViewBag.Title = "Reports";
}
<script type="text/javascript">
$(document).ready(function () {
$(":input[type='button']").button();
$("#expanderHead").click(function () {
$("#expanderContent").slideToggle();
if ($("#expanderSign").text() == "+") {
$("#expanderSign").html("−")
}
else {
$("#expanderSign").text("+")
}
});
});
function GetReportPreview(Report_Id) {
$("#expanderHead").click();
$('#preview').html('<h2>Please wait, loading Report...</h2>');
$.get('/Reports/Preview?report_id=' + Report_Id, function (data) {
$('#preview').html(data);
});
};
</script>
<div id="expanderHead">Report List <span id="expanderSign" style="color: Black; font-size: larger">-</span></div>
<div id="expanderContent" style="padding-left: 30px">
<fieldset style="padding: 1em; font: 100%; color: Black; border-color: Black; border-style: solid">
<legend><strong>User Reports Summary (Role Based)</strong></legend>
Select a report from the list above
<table id="ReportTable">
<tbody>
<ul>
@foreach (var item in Model)
{
<tr>
<td width="10px"></td>
<td>
<li>
<a onclick="GetReportPreview(@item.Report_Id);">@item.ReportName</a>
<br />@Html.DisplayFor(modelItem => item.ReportDescription)
</li>
</td>
</tr>
}
</ul>
</tbody>
</table>
</fieldset>
</div>
<br />
<div id="preview" style="text-align: center;" />
The user can select any report from the list. If the report has filter parameters defined, the user will be presented with the ‘Report Filter Preview’ page displaying each defined filter parameter. Where filter parameters have been defined as ‘mandatory’, the user will not be able to progress to the Report Results page until all ‘mandatory’ fields have been entered. Where filter parameters have been defined as ‘non-mandatory’, the user has the option of leaving the value blank and subsequently be ignored where the value will and not passed across to the stored procedure.
Preview.cshtml View Code Listing for ReportsController.Preview() Action Method
The following listings detail our main controller action method’s view for the ‘Report Filter Preview’ page; the controller action Preview is invoked in the ReportsController class passing the Report_Id of the selected report. The controller action Preview passes the REPORT object as the view’s model; the REPORT object contains a PARAMETERS property which lists the associated report filter parameters (defined via Reports administration).
ReportsController.cs
public ActionResult Preview(int report_id)
{
return View(this.GetReports().Where(p => p.Report_Id == report_id).FirstOrDefault());
}
Preview.cshtml
@model REPORT
<script type="text/javascript">
$(document).ready(function () {
jQuery.ajaxSettings.traditional = true;
$(":input[type='button']").button();
$("#ReportPreview").click(function () {
GetReportParameters2Execute();
});
});
function GetReportParameters2Execute() {
var ReportURL = '/Execute?report_id=' + $("#Report_Id").val();
var FilterParameters = "";
var bContinue = true;
$('.filter-input').each(function (i, obj) {
if ((this == null || $(this).val() == "") & $(this).attr('required') == 'required') {
alert($(this).attr('displaylabel') + ' must be entered');
bContinue = false;
return false;
}
else {
FilterParameters = FilterParameters + "\\" + $(this).attr('id') + "=" + $(this).val();
}
});
if (bContinue == false)
return false;
FilterParameters = FilterParameters.substring(1);
$('#preview').html('<h2>Please wait, generating report...</h2>');
ReportURL = ReportURL + '&rawParams=' + FilterParameters;
$.get('/Reports' + ReportURL, function (data) {
$('#preview').html(data);
});
};
</script>
<input type="hidden" name="ReportId" id="ReportId" value="@Model.Report_Id" />
<div style="text-align: left; background-color: Black; font-size: 1.2em; color: #fff;">
<strong>Report Title:</strong> @Html.DisplayFor(model => model.ReportName)<br />
<br />
@{if (Model.ReportDescription != null)
{
<strong>Description:</strong> @Html.DisplayFor(model => model.ReportDescription)
}
}
</div>
<br />
@{if (Model.PARAMETERS.Count > 0)
{
<div class="row" style="text-align: left;">
<strong>
Please enter optional report parameters below and then click 'Generate Report' to display your report results...<br />
* - Indicates a required field
</strong>
</div>}
}
<div class="panel" style="text-align: left;">
<br />
@Html.HiddenFor(model => model.Report_Id)
@Html.Partial("_filterControls", Model)
<br />
<input type="button" value="Generate Report" name="ReportPreview" id="ReportPreview" />
</div>
<div class="row">
<div id="reportError">
</div>
</div>
|
|
The Preview.cshtml view invokes the _filterControls.cshtml partial view via the @Html.Partial syntax. A partial view is a way to create re-usable components in MVC and avoid repeating the same block of code in a different views; a partial view can be likened to a function. Therefore, the code block contained in the _filterControls.cshtml partial view can be appended to form part of another view via the @Html.Partial("_filterControls", Model) syntax.
The partial view uses the PARAMETERS property in the REPORT object that was passed as the model. The foreach loop iterates each PARAMETER object contained in the PARAMETERS property and renders a form control that is displayed as a filter parameter to the user.
|
_filterControls.cshtml
@model REPORT
<script>
$(function () {
$('[datepicker]').datepicker();
});
</script>
<table>
@foreach (var _parameter in Model.PARAMETERS)
{
<tr>
<td>
@{
string controlLabel = _parameter.DisplayLabel;
if (_parameter.Required)
{
controlLabel = controlLabel + "*";
}
}
@Html.Raw(controlLabel)
</td>
<td>
@{
string control = string.Format("<input type='{0}' id='{1}' name='{1}' displaylabel='{2}'
class='filter-input'", "text", _parameter.ParameterName, _parameter.DisplayLabel);
if (_parameter.Required)
{
control += " required='required'";
}
if (_parameter.ParameterType.ToLower() == "date" || _parameter.ParameterType.ToLower() == "datetime")
{
control += "datepicker";
}
control += control + " />";
@Html.Raw(control)
}
</td>
</tr>
}
</table>
The resulting ‘Report Filter Preview’ page displays the report’s associated filter parameters. The filter parameters are defined for the report via the Reports administration demonstrated in the ‘Sample Project’ section. Filter parameters defined as mandatory must be entered otherwise the user will be presented with validation error messages.
When defining filter parameters via the 'Reports Administration' option, you must use the same parameter names as defined in the database stored procedure otherwise an error will be generated when the application tries to execute the stored procedure. Generally speaking, the database administrator will specify the stored procedure’s parameter names; we just need to ensure that we use the same names for the report’s filter parameter names. Consider the following database stored procedure definition.
CREATE PROCEDURE [dbo].[spTest]
@Manufacturer VARCHAR(30) = null,
@Model VARCHAR(30) = null,
@fromSaleDate [VARCHAR] (19) = null,
@toSaleDate [VARCHAR] (19) = null
AS
BEGIN
DECLARE @SQL varchar(1000)
SET @SQL = 'SELECT ManufacturerName,
Model,
CAST(EngineSize AS decimal(10,1)) AS [EngineSize],
EnginePower,
FuelType,
DATENAME(Month, DATEADD(Month, DATEPART(Month, Date), 0) - 1) AS [Month],
SUM(Price) AS [Total Sales],
COUNT(*) AS [Total Orders]
FROM [SALES]'
...
...
SET @SQL = @SQL + 'GROUP BY DATEPART(m, Date), ManufacturerName, Model, EngineSize, ... '
SET @SQL = @SQL + 'ORDER BY ManufacturerName, Model, DATEPART(m, Date), EngineSize, ...'
EXEC(@SQL)
END
The stored procedure ‘spTest’ defines four parameters that we must surface to the user via the ‘Report Filter Preview’ page.
NOTE: The above example database stored procedure queries the demo ‘SALES’ database table which has no correlation with the RBAC data model. Fundamentally, the example stored procedure denotes a stored procedure defined in your database querying your database tables that can be surfaced as an application report via our RBAC framework without the need of recompiling the application.
Now let’s consider the example report filter parameter ‘Vehicle Manufacturer’ illustrated below; report filter parameters are stored in the PARAMETERS table where each parameter is linked to the REPORTS table via the LNK_REPORT_PARAMETER link table. The ‘Vehicle Manufacturer’ filter parameter relates to the Sales Summary report which has specified the report’s database stored procedure name as ‘spTest’.
SELECT p.* FROM PARAMETERS p
JOIN LNK_REPORT_PARAMETER lnk ON lnk.Parameter_Id=p.Parameter_Id
JOIN REPORTS r ON r.Report_Id=lnk.Report_Id
WHERE r.ReportName='Sales Summary'
If we were to define the ‘Vehicle Manufacturer’ ParameterName as ‘ManufacturerName’ instead of ‘Manufacturer’, we would receive the following error when attempting to generate the report. This is because the stored procedure is unable to map the passed input parameter ‘ManufacturerName’ to any of the defined stored procedure parameters.
In this instance, the ‘Vehicle Manufacturer’ ParameterName would need to be defined as ‘Manufacturer’ hence matching the stored procedure’s parameter name.
DefaultResultsTemplate.cshtml View Code Listing for ReportsController.Executre() Action Method
The following listings detail the controller action method’s view; the controller action Execute is invoked in the ReportsController class passing the Report_Id of the selected report and user entered filter parameters, where applicable. The controller action method extracts each name-value pair in the rawParams parameter to be used as filter parameters. The stored procedure is executed using the filter values entered by the user. If filter parameters have not been defined for the report, the rawParams parameter is passed as an empty string value thus no parameters are passed to the stored procedure.
ReportsController.cs
[HttpGet]
public ActionResult Execute(int report_id, string rawParams)
{
return ExecuteReportviaSP(this.GetReports().Where(p => p.Report_Id == report_id).FirstOrDefault(), rawParams);
}
private ActionResult ExecuteReportviaSP(REPORT _report, string rawParams, string _defaultReportTemplate = "DefaultResultsTemplate")
{
List<dynamic> _list = new List<dynamic>();
string _reportName = _report.Template;
try
{
_list = CommonSql.ExecuteStoredProcedure(_report, rawParams, this);
}
catch (Exception ex)
{
return RedirectToAction("Error", "Unauthorised", new RouteValueDictionary(new { _errorMsg = ex.Message }));
}
string _targetFile = string.Format("{0}/{1}.cshtml", Server.MapPath("~/Views/Reports"), _reportName);
if (!System.IO.File.Exists(_targetFile))
{
_reportName = _defaultReportTemplate;
}
return View(_reportName, _list);
}
The ExecuteReportviaSP function, called from the controller action Execute, runs the static function CommonSql.ExecuteStoredProcedure which is responsible for extracting each name-value pair in the rawParams parameter and executing the database stored procedure defined for the report. The database stored procedure results are returned as a collection of dynamic objects; the collection is passed to the view to render the results to screen.
The ExecuteStoredProcedure function is defined as a part of the static class CommonSql which is decoupled away from the application model in order to return dynamic data and make the class reusable. This function is relatively simple utilizing the SqlCommand .NET framework class to execute the specified stored procedure and associated parameters.
DefaultResultsTemplate.cshtml
@model List<dynamic>
<div id="printableArea" style="text-align: left;">
<h2>
@ViewBag.ReportName
</h2>
|
|
The DynamicDataRow is an agnostic class primarily used to decouple the class away from the application model thus creating an agnostic object. We will take a closer look at this class at a later stage.
|
Custom Report Results Template
If you require report customizing, it is possible to ‘swap-out’ the standard results template with your own customized version. However, you will need to add a new view at design time, customize, re-compile and then redeploy the application. You can then apply your new template for your report.
Let’s consider our ‘Sales Summary’ report which defaults to the ‘DefaultResultsTemplate’ view. We will introduce a new view in the Reports folder named ‘customSalesSummary’ where we will customize the view template.
Using Solution Explorer in Visual Studio, click on the ‘Reports’ folder located in your application’s ‘Views’ folder. Right-click and select ‘Add >> View…’. The following dialog will be displayed. Enter the view name as ‘customSalesSummary’ and click ‘Add’.
A new view will be added to the Reports folder. Customize your view and save the changes. If you are customizing your view to make use of images, make sure you include the images in your project otherwise the images won’t be deployed when you deploy your application. Using Solution Explorer in Visual Studio, locate your images folder in your project (normally inside the ‘Content’ folder) and add your image(s). Recompile your application and redeploy.
NOTE: Before running our report, we will need to update the ‘Sales Summary’ definition to specify our new template via the Reports menu located under the System Administration menu.
customSalesSummary.cshtml
@model List<dynamic>
<img src="@Url.Content("~/Content/Images/customizedlogo.png")" align="left" ... />
<div id="printableArea" style="text-align: left;">
<h1>
@ViewBag.ReportName
</h1>
(@Model.Count() matching records)
<script type="text/javascript">
$(document).ready(function () {
$("#ResultsTable tr:even").css("background-color", "#EBF0FF");
$("#ResultsTable tr:odd").css("background-color", "#ffffff");
});
</script>
<div>
<table id="ResultsTable" class="audittable" style="width: 100%">
<thead>
@foreach (DynamicDataRow item in Model)
{
foreach (DynamicDataObject col in item.Columns)
{
<th>
@col.Name
</th>
}
break;
}
</thead>
@foreach (DynamicDataRow item in Model)
{
<tr>
@foreach (DynamicDataObject col in item.Columns)
{
<td>
@if (col.DataType == "money")
{
@Convert.ToDecimal(col.Value).ToString("#,##0.00");
}
else
{
@col.Value
}
</td>
}
</tr>
}
</table>
</div>
<h2>Total Sales £ @GetSalesTotal(Model).ToString("#,##0.00")</h2>
<br />
</div>
@functions {
public decimal GetSalesTotal(List<dynamic> _obj)
{
decimal _retVal = 0;
try
{
foreach (DynamicDataRow item in _obj)
{
_retVal += Convert.ToDecimal(item.GetColumnValue(6));
}
}
catch (Exception)
{
throw;
}
return _retVal;
}
}
We have now customized our Sales Summary report using a custom report results template. We can change back to the default report results template at any time and vice versa by simply changing the report definition. No application recompile or deployment is necessary as the view already exists within the application binary.
DynamicDataRow Class
The DynamicDataRow class maintains a list of DynamicDataObject objects; the DynamicDataObject object specifies properties to represent a column’s name, value and data type. The column name property is used as the report column heading, value property for the report column value and data type property used for formatting purposes. The illustrated ‘Sales Summary’ report formats the ‘Total Sales’ column using the comma separator; we can determine if the column represents a ‘money’ value via the data type property and format accordingly.
DynamicDataExport.cs
public class DynamicDataObject
{
public readonly string Name;
public readonly string Value;
public readonly string DataType;
public DynamicDataObject(string _colName, string _colValue, string _colType = "string")
{
Name = _colName;
Value = _colValue;
DataType = _colType;
}
}
public class DynamicDataRow
{
public List<DynamicDataObject> Columns = new List<DynamicDataObject>();
public int ColumnCount
...
public void AddColumn(string _columnName, dynamic _columnValue, string _columnDataType)
...
public string GetColumnValue(string _columnName, string _defaultValue = "")
...
public string GetColumnValue(int _idx)
...
public int GetColumnValueAsInt(string _columnName, int _defaultValue = 0)
...
public string GetColumnName(int _idx)
...
}
Both classes can be extended by adding custom properties and functions. The function CommonSql.ExecuteStoredProcedure populates the DynamicDataRow object which represents a single row from the returned database result set. The function returns a collection of DynamicDataRow objects passed as List<dynamic> to the underlying view as the view model as illustrated in the following code snippet.
CommonSql.cs
public static List<dynamic> ExecuteStoredProcedure(REPORT _report, string _rawParams, Controller _controller)
{
List<dynamic> _dataRows = new List<dynamic>();
string[] _parameters = _rawParams.Split('\\');
foreach (string _param in _parameters)
...
sqlConn.Open();
SqlDataReader dbReader = command.ExecuteReader();
while (dbReader.Read())
{
DynamicDataRow _row = new DynamicDataRow();
for (int i = 0; i < dbReader.FieldCount; i++)
{
_row.AddColumn(dbReader.GetName(i), DbUtil.GetValue(dbReader, i), dbReader.GetDataTypeName(i).ToString());
}
_dataRows.Add(_row);
}
sqlConn.Close();
}
DynamicDataExport2CSV Class
The DynamicDataExport2CSV class will export a collection of DynamicDataRow objects saved as plain text file using comma-separated values (CSV) where each record consists of a single line of one or more fields, separated by commas. Each line in the text file represents a record.
DefaultResultsTemplate.cshtml
@using (Html.BeginForm("ExportData", "Reports", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
TempData["ModelData"] = Model;
...
<input type="submit" id="exportdata" value="Export Results to CSV" />
}
ReportsController.cs
[HttpPost]
public ActionResult ExportData(FormCollection form)
{
List<dynamic> _data = TempData["ModelData"] as List<dynamic>;
try
{
int _recordsExported = DynamicDataExport2CSV.Export(_data);
return RedirectToAction("Error", "Unauthorised", new RouteValueDictionary(new { _errorMsg = string.Format("Records Exported: {0}", _recordsExported) }));
}
catch (Exception ex)
{
return RedirectToAction("Error", "Unauthorised", new RouteValueDictionary(new { _errorMsg = ex.Message }));
}
}
DynamicDataExport.cs
public class DynamicDataExport2CSV : DynamicDataExportBase
{
public static int Export(List<dynamic> objList, string _filename = "DataExport")
{
int _retVal = 0;
try
{
if (objList != null && objList.Count > 0)
{
SendHttpContextHeaderInfo(_filename);
WriteRowData(objList[0], true);
foreach (DynamicDataRow obj in objList)
{
WriteRowData(obj);
}
HttpContext.Current.Response.End();
_retVal = objList.Count;
}
}
catch (Exception ex)
{
throw ex;
}
return _retVal;
}
private static void WriteRowData(dynamic obj, bool _columnNames = false)
{
StringBuilder _data = new StringBuilder();
foreach (DynamicDataObject _column in obj.Columns)
{
if (_columnNames)
AddComma(_column.Name, _data);
else
AddComma(_column.Value, _data);
}
_data = RemoveLastComma(_data);
HttpContext.Current.Response.Write(_data.ToString());
HttpContext.Current.Response.Write(Environment.NewLine);
}
}
Clicking the ‘Export Results to CSV’ button will export the collection of DynamicDataRow objects, which represent the report results, to a plain text file. The DynamicDataExport2CSV class and associated DynamicData objects are simple implementations promoting extensibility. Where possible, collections of DynamicDataRow objects are passed as List<dynamic> so as to avoid enforcing ‘type safe’ parameter passing thus providing the ability to extend the system with the minimum level of effort required to implement the change.
NOTE: By not enforcing ‘type safe’ parameter passing, you can make use of the DynamicDataExport2CSV class passing your custom object collection without the need to change the function’s type signature; modifications to the function body will be required in order to reference your custom object’s properties and/or functions. The pitfall of passing parameters as dynamic is that the compiler will not ‘detect’ referenced object properties that don’t exist during compilation and an exception will be thrown as and when that line of code is executed.
DECLARE @SQL varchar(1000)
SET @SQL = 'SELECT ManufacturerName,
Model,
CAST(EngineSize AS decimal(10,1)) AS [EngineSize],
EnginePower,
FuelType,
DATENAME(Month, DATEADD(Month, DATEPART(Month, Date), 0) - 1) AS [Month],
SUM(Price) AS [Total Sales],
COUNT(*) AS [Total Orders]
FROM [SALES]'
...
...
SET @SQL = @SQL + 'GROUP BY DATEPART(m, Date), ManufacturerName, Model, EngineSize, EnginePower, FuelType '
SET @SQL = @SQL + 'ORDER BY ManufacturerName, Model, DATEPART(m, Date), EngineSize, EnginePower, FuelType'
EXEC(@SQL)
List<dynamic> _list = CommonSql.ExecuteStoredProcedure(_report, rawParams, this);
DynamicDataExport2CSV.Export(_list);
The exported CSV file will comprise of data returned from the database stored procedure including column headings. The exported CSV file can then be imported by most spreadsheet programs as pictured above.
The key benefit of our reporting solution is that our 'default' report template is not tied to one specific data model thus offering increased code reusability. The 'default' report template will display the output from any stored procedure without any changes needed to the report template, even if the stored procedure is changed at a later date to return additional columns. In the vast majority of cases, application generated reports generally require to display column based tables reflecting data stored in our database. A column based table is the most common form of report output. A column based table is a sequential, two-dimensional list. This doesn't imply that the data in the table is simple or uncomplicated; you can present a great deal of data in a table. Database stored procedures typically drive the report logic where several tables are joined and/or cross referenced in order to return the required data. However, we do have the ability to customize reports where additional logic can be coded into our ‘presentation’ report template layer or specific formatting based on the column data type.
In particular, our RBAC reporting framework offers a reporting solution which will serve our reporting requirements the majority of the time. Our solution is vendor independent and doesn't require 'additional' licenses. It will work with almost any data source (Sql Server, MySql etc) and can be extended to fulfil specific project requirements. Additionally, we have the ability to export our report data where the export feature can be granted to specific roles and/or permissions using our custom ‘HasRole’ and ‘HasPermission’ methods exposed in our RBACUser class.
Alternative Vendor Reporting Solution
We are now going to modify our reporting solution to integrate with Microsoft’s SQL Server Reporting Services (SSRS) solution. SSRS is a server-based reporting platform from Microsoft that allows us to create and manage a wide variety of report types and deliver them in a range of formats. SSRS enables users to quickly and easily generate reports containing tables and graphs, or more complex data visualizations, using charts, maps and sparklines. SSRS provides the complete environment to create and publish sophisticated reports ready for viewing.
We can present our finished reports directly from the Reporting Services website, called Report Manager, or we can view them directly in our web application using the ASP.NET ReportViewer web control.
SQL Server Reporting Services (SSRS) Overview
An SSRS deployment must be associated with a SQL Server instance as this is where the report data will be sourced. We will also need a location for the Report Server Web Service, which can be on the same server as the databases or on a different server. On whichever server we choose, we will have access to a Report Manager website that allows us to deploy and manage reports. Once a report has been published, the end user sends an HTTP request for a report including any required parameters. The SSRS server finds the metadata of the report and sends a request for data to the data source. The data returned by the data source is merged with the report definition into a report. As the report is generated, it is returned to the client.
RBAC Integration with SSRS
This section does not go into the details of installing or configuring SSRS but rather assumes you have SSRS configured correctly and running. This section will explain how we can tweak the RBAC reporting solution to integrate with your SSRS solution in order to view reports directly in your web-based application featuring the RBAC framework.
The user selects a report from the list of available reports. If the report has filter parameters defined, the user will be presented with the ‘Report Filter Preview’ page displaying each defined filter parameter as before. Clicking the ‘Generate Report’ button on the ‘Report Filter Preview’ will amalgamate each filter parameter’s name-value pair to the report URL string. The URL string contains the location for the Report Server Web Service and name of the report. The 'finalized' URL string is sent as a HTTP request to SSRS where the resulting report is returned and displayed in a separate browser window. No controller action method is executed this time and instead the filter parameters are appended to the URL and requested via the JavaScript function ‘GetSSRSReportParameters2Execute()’ in the client browser. The 'finalized' URL takes for format of:-
"http://.../ReportServer/Pages/ReportViewer.aspx?salessummary¶m1=abc¶m2=123"
where 'http://…/ReportServer/Pages/ReportViewer.aspx' represents the location for the Report Server Web Service and stored as a setting in the configuration file, 'salessummary' represents the report name and stored in the 'Template' field in the REPORTS table for the given report and each filter parameter is associated as before.
GetSSRSReportParameters2Execute() JavaScript Function
The following listing details the JavaScript function GetSSRSReportParameters2Execute() located in the Preview.cshtml. The ‘Report Filter Preview’ page is rendered by the controller action Preview in the ReportsController and passes the REPORT object as the view’s model.
Web.config
="1.0"
<configuration>
<appSettings>
<add key="UnobtrusiveJavaScriptEnabled" value="true"/>
<add key="ReportViewerUrl" value="http://yourreportserver/ReportServer/Pages/ReportViewer.aspx"/>
...
</appSettings>
...
ReportsController.cs
public ActionResult Preview(int report_id)
{
ViewBag.ReportUrl = ConfigurationManager.AppSettings.Get("ReportViewerUrl");
return View(this.GetReports().Where(p => p.Report_Id == report_id).FirstOrDefault());
}
Preview.cshtml
@model REPORT
<input type="hidden" name="SSRSUrl" id="SSRSUrl" value="@ViewBag.ReportUrl" />
<input type="hidden" name="ReportId" id="ReportId" value="@Model.Report_Id" />
<input type="hidden" name="SSRSReportName" id="SSRSReportName" value="@Model.Template" />
<script type="text/javascript">
$(document).ready(function () {
jQuery.ajaxSettings.traditional = true;
$(":input[type='button']").button();
$("#ReportPreview").click(function () {
GetSSRSReportParameters2Execute();
});
});
function GetSSRSReportParameters2Execute() {
var SSRSReportURL = $("#SSRSUrl").val();
var FilterParameters = "";
var bContinue = true;
$('.filter-input').each(function (i, obj) {
if ((this == null || $(this).val() == "") & $(this).attr('required') == 'required') {
alert($(this).attr('displaylabel') + ' must be entered');
bContinue = false;
return false;
}
else {
if ($(this).val() != "")
FilterParameters = FilterParameters + "&" + $(this).attr('id') + "=" + $(this).val();
}
});
if (bContinue == false)
return false;
SSRSReportURL = SSRSReportURL + "?" + $("#SSRSReportName").val() + FilterParameters;
window.open(SSRSReportURL, '', 'scrollbars=no,menubar=no,resizable=yes,toolbar=no,
location=no,status=no');
};
</script>
...
The sections of code shaded grey (see pdf version of this article) illustrate ‘added’ code to integrate with SSRS. The Preview controller action has been modified to pass the ReportViewerUrl configuration setting via the ViewBag parameter ReportUrl. The ‘Preview.cshtml’ template has been modified to include the new JavaScript function GetSSRSReportParameters2Execute(). The ‘Generate Report’ button has had its ‘onclick()’ event assigned to execute the new function.
The GetSSRSReportParameters2Execute() function is executed when the ‘Generate Report’ button is pressed. The function builds a URL by appending the passed ReportViewerUrl configuration setting, the selected report’s SSRS report name and entered filter parameters displayed on the ‘Report Filter Preview’ page together. The resulting URL is sent as a HTTP request to SSRS where the resulting report is returned and displayed in a separate browser window.
NOTE: An alternative approach to storing application settings in the application’s configuration file (Web.config) is to store the settings in a database table. In either case, we can surface these settings via our application enabling modifications to be saved back to the database table or configuration file.
Report Generation via SSRS
Making the above modifications, we are now able to integrate and generate reports via SSRS as illustrated below. The ‘Sales Summary’ report demonstrated in previous sections has been reconstructed in SSRS using SQL Server Report Builder.
|
|
If we now click our ‘Generate Report’ button, our report is generated via SSRS and displayed in a new browser window.
|
Sample Project
The sample project available for download builds on the original sample project (available in Part 1) to include dynamic role based reporting and subsequent roles/reports maintenance. The sample project contains a Reports menu located under the System Administration menu. Clicking the Reports menu loads the ‘Reports Summary’ page, illustrated below, enabling application report CRUD maintenance to be undertaken in addition to role association.
Application Reports
To define a new application report, click on the Reports menu located under the System Administration menu. The Reports table will be displayed showing each application report defined. From this screen, you will be able to define new reports, edit or delete existing reports.
To define a new application report, simply click on the ‘Create Report’ button to load the following screen.
Populate each field as required and ensure the database stored procedure name entered into the StoredProcedureName field exists or will exist in the database when the application is deployed.
Report Role Assignment
Once an application report has been defined, user roles and filter parameters can be assigned to the report using the edit icon associated with the report.
Roles can be associated and disassociated with a report at any time. Individual roles are assigned to the report by selecting the role from the dropdown and press the ‘Add Role’ button; unwanted roles can be unassigned using the trash icon. Likewise, report filter parameters can be associated with a report via the ‘Add Parameter’ button.
Report Filter Parameter Assignment
Filter parameters can be created and deleted at any time for a report. Simply click on the ‘Add Parameter’ button to extend the ‘Report Filter Parameters’ panel as illustrated below.
Populate each field as required and ensure the ‘Parameter Name’ matches the stored procedure’s parameter name to which we will be passing the value to be filtered on. To cancel the adding of the current filter parameter, click on the cancel icon. Click the ‘Add Parameter’ button a second time to store the value in the database and associated with the report. Unwanted parameters can be removed using the trash icon.
Demonstration Sales Table
The sample project available for download contains a SQL script file named ‘RBAC_CarSalesDemoSchema.sql’. This script creates a table called ‘SALES’, populates the table with sample data and creates a database stored procedure named ‘spTest’. The ‘SALES’ table and accompanying stored procedure are representative of tables/stored procedures in your database and are not associated with the RBAC model. Therefore, you do not need to run this SQL script when integrating RBAC into your project as the tables you will report upon will already exist in your database.
CREATE TABLE [dbo].[SALES](
[Sale_Id] [int] IDENTITY(1,1) NOT NULL,
[Date] [date] NOT NULL DEFAULT GETDATE(),
[Price] [money] NOT NULL DEFAULT 0,
[ManufacturerName] [nvarchar](30) NOT NULL,
[Model] [nvarchar](30) NOT NULL,
[Colour] [nvarchar](30) NOT NULL,
[EngineSize] decimal(3,1) NOT NULL,
[EnginePower] [int] NOT NULL,
FuelType] [nvarchar](10) NOT NULL,
[LHD] [bit] NOT NULL default 0,
CONSTRAINT [PK_SALES] PRIMARY KEY CLUSTERED
(
[Sale_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[spTest]
@Manufacturer VARCHAR(30) = null,
@Model VARCHAR(30) = null,
@fromSaleDate VARCHAR (19) = null,
@toSaleDate VARCHAR (19) = null
AS
BEGIN
DECLARE @SQL varchar(1000)
SET @SQL = 'SELECT ManufacturerName,
Model,
CAST(EngineSize AS decimal(10,1)) AS [EngineSize],
EnginePower,
FuelType,
DATENAME(Month, DATEADD(Month, DATEPART(Month, Date), 0) - 1) AS [Month],
SUM(Price) AS [Total Sales],
COUNT(*) AS [Total Orders]
FROM [SALES] '
...
SET @SQL = @SQL + 'GROUP BY DATEPART(m, Date), ManufacturerName...'
SET @SQL = @SQL + 'ORDER BY ManufacturerName, Model, DATEPART(m, Date)...'
EXEC(@SQL)
END
GO
The sample project defines a report called ‘Sales Summary’ which uses the stored procedure ‘spTest’ to return sales data. Since our stored procedure defines input parameters, our ‘Sales Summary’ report defines the same parameters which are displayed on the ‘Report Filter Preview’ page enabling the user to filter the report results.
However, where database stored procedure input parameters have default values defined such as null (ie @Model VARCHAR(30) = null), we can omit the parameter from our application report definition. In such cases, the parameter will not be displayed on the ‘Report Filter Preview’ page and will be passed as the default value inside the stored procedure. This is also true when a parameter has been defined for our report but no value supplied by the user via the ‘Report Filter Preview’ page.
Adding RBAC to Existing MVC Applications
Adding the RBAC framework to an existing application will require the steps outlined in part 1 followed by the steps outlined below:-
|
1. Using Solution Explorer in Visual Studio, create a new folder in your project called ‘Common’ and add the CommonSql.cs and DynamicDataExport.cs files by right-clicking on the newly created folder and selecting the ‘Add >> Existing Item…’. If your project already contains a comparable folder, simply add these files to the folder already present and reference accordingly.
2. Using Solution Explorer in Visual Studio, add the ReportsController.cs file to your ‘Controllers’ folder by right-clicking and selecting the ‘Add >> Existing Item…’. This controller contains the controller actions to display and preview reports available to the user in addition to the report export feature. Replace the existing AdminController.cs file with the updated version; this version contains additional controller action methods for the administration of reports including report parameter administration.
3. Add the files PARAMETER.cs and REPORT.cs to your ‘Models’ folder in the same manner as above. These files define the data model types REPORT and PARAMETER which correspond to our database schema (see RBAC Database Schema Diagram).
4. Using Solution Explorer in Visual Studio, add the ‘Views >> Admin’ files detailed in the RBACDemo project opposite to the corresponding views folder in your project. The additional view files relate to the controller action methods contained in the AdminController.cs file and are for the administration of reports.
5. Using Solution Explorer in Visual Studio, create a new folder in your application’s ‘Views’ folder called ‘Reports’ and add the view files as detailed in the RBACDemo project by right-clicking on the newly created folder and selecting ‘Add >> Existing Item…’. These views are associated with the Reports controller actions. If your project already contains a Reports controller, you can rename the RBAC Reports controller and move the associated views to the new corresponding folder. Links which reference the Reports controller will need changing to reference the newly named controller. These links are contained in both the controller itself and the corresponding views.
6. Replace the existing UnauthorisedController.cs file with the updated version; this version contains the additional controller action method Error(string _errorMsg) used to display messages back to the user. The additional view file Error.cshtml relating to this controller action method needs to be added to the corresponding views folder. Using Solution Explorer in Visual Studio, add this file to the ‘Views >> Unauthorised’ folder as detailed opposite.
|
|
DataTables Table plug-in for jQuery
DataTables is a free open source plug-in for the jQuery library provided by SpryMedia Ltd free of charge to use in your application in any way (including commercial projects). See http://www.datatables.net for further details. It is a highly flexible tool that will add advanced interaction controls to any HTML table. This makes the DataTables plug-in an ideal candidate for our custom sales table adding two important features required for any report; column sorting and table filtering.
We now have the ability to filter our table using the search box provided by the DataTables plug-in. Simply type a value and the plug-in will filter the results; we can then sort by column type.
DataTables Configuration
DataTables has only one Javascript library dependency, jquery.dataTables.js, which uses the styling defined in the CSS file jquery.dataTables.css. The CSS file is optional, but it provides default styling for your table to make it look good with minimum effort. To integrate DataTables into your application, simply add the two files to your application's Content folder as detailed below and reference accordingly in the _Layout.cshtml file.
|
1. Using Solution Explorer in Visual Studio, create a new folder in your Content folder called ‘css’ and add the jquery.dataTables.css file by right-clicking on the newly created folder and selecting the ‘Add >> Existing Item…’. Locate the file and add accordingly.
2. Using Solution Explorer in Visual Studio, create a new folder in your Content folder called ‘js’ and add the jquery.dataTables.js file by right-clicking on the newly created folder and selecting the ‘Add >> Existing Item…’. Locate the file and add accordingly.
3. Using Solution Explorer in Visual Studio, add the image files (x7) shown opposite to your ‘Images’ folder by right-clicking and selecting the ‘Add >> Existing Item…’. Locate the files and add accordingly.
4. NOTE: The sample project already contains the required files since the above steps were undertaken for the purpose of the demo. The outlined steps are for reference purposes in order to identify which files are used by the DataTables plug-in including their location.
|
|
_Layout.cshtml Configuration
The jquery.dataTables.css and jquery.dataTables.js files require to be referenced in the _Layout.cshtml file as follows.
_Layout.cshtml
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>RBAC Demo</title>
<link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css" />
@Scripts.Render("~/bundles/modernizr")
<!---->
<script src="@Url.Content("~/Scripts/jquery-1.10.2.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery-ui-1.10.3.custom.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery-common.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery-validate.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery-validate.unobtrusive.js")" type="text/javascript"></script>
<!—DataTables library references -->
<script src="~/Content/js/jquery.dataTables.js"></script>
<link href="~/Content/css/jquery.dataTables.css" rel="stylesheet" />
</head>
<body>
...
NOTE: If you wish to remove the DataTables plug-in, simply remove the library files (x2) from your project and associated reference links in the _Layout.cshtml file as highlighted above.
Initialising DataTables
For DataTables to be able to enhance our HTML ‘Car Sales’ table, we must initialise the plug-in within our customSalesSummary.cshtml template as detailed below:
customSalesSummary.cshtml
<script type="text/javascript">
$(document).ready(function () {
$('#ResultsTable').dataTable();
});
</script>
That's it! DataTables will add ordering, searching, paging and information to your table by default, giving your end users the ability to find the information they want as quickly as possible.
Recalculate ‘Total Sales’ via Table Filtering using Custom DOM Events
DataTables provides the ability to fire custom DOM events which can be listened for, and then acted upon to allow event driven actions.
|
|
If there is a requirement to have the ‘Total Sales’ label recalculated as we filter the table’s records, we will need to register a call back function with the DataTables custom ‘on draw’ table event in order to be notified as and when this event is fired. The custom ‘on draw’ table event is fired when the table is filtered.
|
Once a call back function has been registered, we will be notified each time the table’s ‘on draw’ event triggers. Our call back function will contain code to update the ‘Total Sales’ form label. The label will be updated with a recalculated sales value based on the data provided by the filtered records.
The following code registers a call back function triggered when the DataTables custom ‘on draw’ table event is fired. The call back function invokes the calculateColumnTotal(index) function which returns the total for a specified table column. The total is formatted using commas as the ‘Thousand’ separator before being returned to the call back function.
<script type="text/javascript">
function calculateColumnTotal(index) {
var total = 0;
$('table tr').each(function () {
var cellvalue = $('td', this).eq(index).text();
var value = Number(cellvalue.replace(/[^0-9\.]+/g, ""));
if (!isNaN(value)) {
total += value;
}
});
return total.toString().replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,");
}
$(document).ready(function () {
...
var table = $('#ResultsTable').DataTable();
table.on('draw', function () {
$("#SalesTotal").html("<h2>Total Sales £" + calculateColumnTotal(6) + "</h2>");
});
});
</script>
The calculateColumnTotal(index) function utilises jQuery to return a total for a given table column. The target column is identified via the passed column index (ie column index 6 representing sales data in our example). The call back function updates the ‘Total Sales’ form label using the value returned from the calculateColumnTotal(index) function.
Conclusion
The extended framework forms an ideal foundation for any intranet application that requires dynamic self-contained Roles Based Access Control (RBAC) or dynamic Roles Based Reports (RBR).
Most organizations will require, at some point, a reporting facility to report on the data stored in the application’s database and the capability to export those results to Comma Separated Values (CSV) standard file format, a common data exchange format that is widely supported by consumer, business, and scientific applications. Generally speaking, not all users should be permitted access to every report defined within the system or permission to export data from a report. The extended framework will enable the control over which reports, regulated via the application’s system administrator, are accessible by which users based on the user’s role(s) and the control of who has permission to export data from the report. Where data is exported and leaked by ‘untrustworthy’ users, you then have absolutely no control over your data. The theft of confidential information has been an extensive issue for many businesses. Should confidential information about a business' customers or finances fall into the hands of a competitor, a business and its customers could suffer widespread, irreparable financial loss, as well as damage to the company's reputation. Protecting confidential information is a business requirement and in many cases also an ethical and legal requirement.
The framework has been purposely designed to be flexible enabling our default reporting solution to be swapped out for an alternative vendor’s solution without affecting the underlying RBAC reporting functionality. Role-Based Access Control (RBAC) is now well recognized as the best practice for setting such controls by the majority of organizations.
This solution is particularly suited for corporate intranet applications where limited access to the production web server is granted. In the next and final post, the article will extend the framework to cover RBAC in ASP.NET MVC web applications using username/password authentication via HTTPS and OAuth.
