Using SSRS In ASP.NET MVC Application

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.

29 Comments

  1. Pingback: Getting Started with SSRS on Azure « Sergey Barskiy's Blog

  2. Hi Sergey Barskiy,

    I’ve been doing some researching about SSRS and I find your blog. Thank you for this post. but honestly, I can’t follow it. I think I missed some parts or your classes naming. Could you upload the source code or make it clearer.
    Thanks so much.

    Regards

  3. Hi Sergey,

    Very nice piece of work. I am integrating SSRS reports in my MVC application in a simpler (read: less sophisticated) manner, just an ascx control called with @Html.Partial from my “ReportViewer” view.

    In the ascx I have the SSRS ReportViewer control, and in the page load method I use data passed in with the model to set up the ReportViewer’s properties such as ReportPath, etc.

    Reports render fine, but the interactive features such as column sorting and paging do not work. They work fine when I execute a report in Report Manager, but not in my application.

    Any ideas on this? I have searched the Web for a solution but so far no luck.

    Thanks!

    -Walt

  4. Using Firebug I discovered there is an error in the request: “Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed.” I examined the POST message but I don’t see where the problem is. Interestingly, all of the report controls (paging, refresh, sort) exhibit this problem, but I can export without error.

    • Without seeing the behavior, hard to say what is going on. SSRS viewer is ASPX control that needs basic ASP.NET functionality to work. I have seen it not work properly if it cannot download all the scripts it needs. You might want to check to see if you are getting 404 for any resources when you render the viewer. You might be missing the SSRS HTTP handler?

  5. Hi Sergey,
    what is ReportContext() and also can you tell me where I can find ProcessingMode.Remote and data.ReportFileName and also I haven’t fine any
    List() class or might be something geos wrong
    parametersCollection.Add(new ReportParameter(parameterName, parameter.ParameterValue, false)); this is also not found in our above code so kindly let me know then that could be
    better to understand I had do lot of google but I haven’t find any good matirial for that..

    thank you..

  6. ReprotContext is just a DbCoxtext EF class used for data access. If you want to post the code you need help with, I can try to take a look. I do not have this project on my machine though, so you will need to try to write it based on code posted above.

  7. Pingback: Using SSRS In Angular / ASP.NET MVC Application | Sergey Barskiy's Blog

  8. Love this! It is exactly what i am looking for. The only thing is when i try to load the solution it will not load AngularAspNetMvc.Web

    When i try to load that section itself it tells me it cannot load type :AngularAspNetMvc.Web.Global

    All help greatly appreciated! I have been going around in circles trying to work out this.. and your solutions seems to do it perfectly

Leave a Reply to JP Cancel reply

Your email address will not be published. Required fields are marked *