In this blog I will describe my ideas on how to integrate SQL Server reports in ASP.NET MVC applications. I would like to have as seamless of an integration as possible given the constraints in place. For example, the only web based report viewer for SSRS is the one that has been shipping with Web Forms (ASPX). Now that we have problem statement down, let’s get on with a high level solution I would like to implement.
- I would like to integrate reports into the existing application
- I would like to show them in an overlay, not wanting to popup additional browser window and having to deal with popup issues in general
- I would like to make the report viewing safe, trying to reveal as little as possible to the user or technical observer who could use Fiddler for example.
Here is high level outline of my answer to these issues
- I will have a parameter values collecting view in MVC app
- I will log the data about selected report into the database along with parameter values, using ajax call to my controller.
- I will associate that information with a GUID
- Controller will return a url with the guid back to my java script method. Then the method will popup jQuery dialog with an embedded iFrame and set its source to the url passed back from controller
- I will pass that GUID as a query parameter to ASPX page that will host Web Form report viewer control.
- I will have web form get the report information from the database along with parameters, then configure report viewer.
That is it. Now, let’s see some code.
To log report request into the database we just need a couple of tables: request header and request parameters. I am using entity framework code first, so my tables could look something like this:
public class ReportRequest { public int ReportRequestId { get; set; } [StringLength(50)] public string ReportFileName { get; set; } public Guid UniqueId { get; set; } public ICollection<ReportRequestParameter> Parameters { get; set; } }
public class ReportRequestParameter { public int ReportRequestParameterId { get; set; } [StringLength(50)] public string ParameterName { get; set; } public string ParameterValue { get; set; } [Required] public int ReportRequestId { get; set; } [ForeignKey("ReportRequestId")] public ReportRequest ReportRequest { get; set; } }
This part is pretty easy. Let me now build report center screen that lists all the reports in the system and allows user to print a report. To support this I just need to have a list of all reports in the system and their parameters. Again, two tables will suffice. In parameters table I need the following information:
- Parameter name (title I will use in a view)
- SSRS parameter name(s). I can just separate them via a pipe for example. This allows me to build complex parameter partial views that can return multiple parameters and their values.
- Partial view I can use to get the data for a parameter.
So, my plan is to let a user select a report, then build a UI by looking at each parameter and building a partial view for it. Here is an example of one of those partial views:
@model MyApp.Reports.ReportParameter <div class="display-label"> @Model.ParameterName </div> <div class="editor-field"> <select id="@(Model.ReportParameterNames)" name="@(Model.ReportParameterNames)" style="min-width: 200px"> <option value="1" selected="selected">Option 1</option> <option value="2">Option 2</option> <option value="3">Option 3</option> </select> </div>
This builds a drop down list control with three options. One key thing to notice is that I am using ID and name that corresponds to a parameter name, I will later use it to build parameters.
Now the report center view (I am coming in with a report already selected) may look something like the following
@model MyApp.Reports.Report @using (Html.BeginForm()) { @Html.HiddenFor(model => model.ReportID) @Html.HiddenFor(model => model.ReportFileName) foreach (var parameter in Model.ReportParameters) { { Html.RenderPartial(parameter.PartialViewName, parameter); } } <br/> <button id="runReportButton" >Report Preview</button> <div id="reportPreviewDiv" style="display: none"> <iframe id="reportViewFrame" width="100%" height="100%" ></iframe> </div> <script src="@Url.Content("~/Scripts/app.js")" type="text/javascript"></script>
As you can see, I am building a form from multiple partial views, then I am using jQuery ajax to submit that to my controller:
reports: {
submitReportRequest: function () {
$.ajax({
url: "/Report/RunReport",
type: "POST",
dataType: 'json',
data: $('form').serializeArray(),
success: function (data, textStatus, jqXhr) {
var closeButton = {};
closeButton[‘Close’] = function () {
$(this).dialog("close");
$("#reportViewFrame").attr("src", "about:blank");
};
$('#reportPreviewDiv').dialog({
autoOpen: true,
title: ‘Report Preview’,
width: 880,
height: 800,
modal: true,
resizable: true,
autoResize: false,
buttons: closeButton,
open: function (event, ui) { $("#reportViewFrame").attr("src", data); }
});
},
error: app.handleAjaxError
});
return false;
}
}
Report class in scripts above is simply following best practices for java script, using name spaces to separate all the functions.
Now my controller method looks something like this:
[HttpPost]
public JsonResult RunReport(ReportData formData)
{
Guid id = Guid.NewGuid();
using (var context = new ReportContext())
{
var reportRequest =
new ReportRequest
{
ReportFileName = formData.ReportFileName,
UniqueId = id
};
context.ReportRequests.Add(reportRequest);
foreach (var key in formData.Keys)
{
var parameter =
new ReportRequestParameter
{
ParameterName = key,
ParameterValue = formData[key],
ReportRequest = reportRequest
};
context.ReportRequestParameters.Add(parameter);
}
context.SaveChanges();
}
return Json(string.Format("/Reports/ReportForm.aspx?r={0}", id.ToString()));
}
My form data class that contains all the information submitted by the form. I created a custom class for it and a custom model binder to automate some data binding and keep me from referring to context inside the controller.
[ModelBinder(typeof(ReportDataModelBinder))] public class ReportData : Dictionary<string, string> { public int ReportID { get; internal set; } public string ReportFileName { get; internal set; } }
And here is the binder
public class ReportDataModelBinder : IModelBinder { public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext) { var request = controllerContext.HttpContext.Request; var returnValue = new Program.ReportData(); foreach (var key in request.Form.AllKeys) { if (key.StartsWith("@")) { returnValue.Add(key, request.Form[key]); } else if (key == "ReportID") { returnValue.ReportID = int.Parse(request.Form[key]); } else if (key == "ReportFileName") { returnValue.ReportFileName = request.Form[key]; } } return returnValue; } }
As use can see, I am following convention to start all report (RDL) parameters with an @ sign. Now all the data submitted through he form that starts with it must be a parameter. In general, using Model binders is highly encouraged in MVC applications because it gives you an ability to cleanly incorporate custom data that is sent from client to the server in any controllers to avoid writing the same code many times.
This ends the MVC portion of the solution. Now, I just create a brand new ASPX page and add it to the project. Just use Add New Item menu. Once form is added, you must update the routes in your MVC applications to ignore things that end with .aspx. You do so in global.asax
public static void RegisterRoutes(RouteCollection routes) { routes.IgnoreRoute("{resource}.axd/{*pathInfo}"); routes.IgnoreRoute("{resource}.aspx/{*pathInfo}"); routes.MapRoute( "Default", // Route name "{controller}/{action}/{id}", // URL with parameters new { controller = "Home", action = "Index", id = UrlParameter.Optional } // Parameter defaults ); }
The form itself is pretty simple, it just has report viewer control in it:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ReportForm.aspx.cs" Inherits="MyApp.Reports.ReportForm" %> <%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="reportForm" runat="server"> <asp:ScriptManager ID="ScriptManager1" runat="server"> </asp:ScriptManager> <div> <rsweb:ReportViewer ID="mainReportViewer" runat="server" Width="700px" Height="600px"> </rsweb:ReportViewer> </div> </form> </body> </html>
Make sure to include ScriptManager – report viewer control needs it. The code behind is just a simple, I am getting the report data and parameter data then setting up my control
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { var requestID = Request.QueryString["r"];
ReportRequest request;
using (var context = new ReportContext())
{
request = context.ReportRequests.Include("Parameters").Where(one => one.UniqueId == id).FirstOrDefault();
}mainReportViewer.ServerReport.ReportServerUrl = new Uri(ConfigurationManager.AppSettings["ReportServerUrl"]); mainReportViewer.ServerReport.ReportPath = string.Format(ConfigurationManager.AppSettings["ReportPath"], (data.ReportFileName)); mainReportViewer.ProcessingMode = ProcessingMode.Remote; mainReportViewer.ShowParameterPrompts = false; mainReportViewer.ShowRefreshButton = false; mainReportViewer.ShowWaitControlCancelLink = false; mainReportViewer.ShowBackButton = false; mainReportViewer.ShowCredentialPrompts = false; var parametersCollection = new List<ReportParameter>(); foreach (var parameter in request.Parameters) { var parameterName = parameter.ParameterName; if (parameterName.StartsWith("@")) { parameterName = parameterName.Substring(1); } parametersCollection.Add(new ReportParameter(parameterName, parameter.ParameterValue, false)); } mainReportViewer.ServerReport.SetParameters(parametersCollection); mainReportViewer.ServerReport.Refresh(); } }
Now, we have to add a Http handler to the web.config file:
<system.webServer> <validation validateIntegratedModeConfiguration="false"/> <modules runAllManagedModulesForAllRequests="true"/> <handlers> <add name="ReportViewerWebControlHandler" preCondition="integratedMode" verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" /> </handlers> </system.webServer>
Wow, that was a lot of steps, but you are not ready to run reports inside your MVC application. There are a few enhancements I would also like to suggest
- Delete report request data once it is retrieved. This makes our approach more secure, where the same url cannot be used multiple times
- Play with height / width. You cannot use 100% height/width on the report control because this does not work properly in all the browsers.
- Remove direct dependency on DbContext by introducing a service or a repository between forms and data.
- You can probably refine ReportData class, maybe convert it into an object with a dictionary property.
- You want to actually implement global error handler, I just left a stub in my script
- You want to implement some sort of ‘please wait’ window while your ajax is running.
Enjoy and let me know what you think.
[Update 12/29/2013] – I created new post that has downloadable project. You can take a look at it here. This project is using Angular, but most code is borrowed from this post’s examples.