How to Incorporate SSRS Report into MVC 5 Web Application

For traditional ASP.Net Web Form application, you can use ReportViewer control to show your SSRS report. However, for ASP.Net MVC application, you can't use the control in Razor page. Even you can add Web Form into MVC application, these Web Forms are difficult to have similar look and fell as your normal Razor page.

Fortunately, there is a ReportViewer for MVC tool which can help you to resolve this problem.

At first, you need to install the tool from NuGet. Run following command in the Package Manage Console to install the tool:

PM> Install-Package ReportViewerForMvc

Then add reference in your controller to Microsoft.Reporting.WebForms:

using Microsoft.Reporting.WebForms;

Create a new instance of ReportViewer:

ReportViewer reportViewer = new ReportViewer();
reportViewer.ProcessingMode = ProcessingMode.Remote;

reportViewer.ServerReport.ReportPath = "/AdventureWorks 2012/Sales_by_Region";
reportViewer.ServerReport.ReportServerUrl = new Uri("http://localhost/ReportServer/");

ViewBag.ReportViewer = reportViewer;

Above is for the situation that you put all your report on your SQL Server. However, every time your application access those reports, which usually required authorization, you would offer user name and password. To avoid this situation, you can put all your report to your local along with your web application. For this purpose, you need to change the report viewer's ProcessingMode.

        public ActionResult Report2()
        {
            SqlConnection connect = new SqlConnection(connectionString);
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            SqlCommand cmd = connect.CreateCommand();
            cmd.CommandText = 
               @"SELECT  DimOrganization.OrganizationName, DimDepartmentGroup.DepartmentGroupName, DimDate.CalendarYear, DimDate.EnglishMonthName, DimDate.MonthNumberOfYear, SUM(FactFinance.Amount) AS SubTotalAmount
                 FROM FactFinance INNER JOIN
                     DimDate ON FactFinance.DateKey = DimDate.DateKey INNER JOIN
                     DimDepartmentGroup ON FactFinance.DepartmentGroupKey = DimDepartmentGroup.DepartmentGroupKey INNER JOIN
                     DimOrganization ON FactFinance.OrganizationKey = DimOrganization.OrganizationKey
                 GROUP BY DimOrganization.OrganizationName, DimDepartmentGroup.DepartmentGroupName, DimDate.CalendarYear, DimDate.EnglishMonthName, DimDate.MonthNumberOfYear
                 Order by   DimDate.CalendarYear,  DimDate.MonthNumberOfYear";
            dataAdapter.SelectCommand = cmd;
            DataSet dataSet = new DataSet();

            connect.Open();
            dataAdapter.Fill(dataSet);
            connect.Close();
            ReportViewer reportViewer = new ReportViewer()
            {
                SizeToReportContent = true,
                Width = System.Web.UI.WebControls.Unit.Percentage(100),
                Height = System.Web.UI.WebControls.Unit.Percentage(100),
            };
            reportViewer.ProcessingMode = ProcessingMode.Local;

            reportViewer.LocalReport.ReportPath = Request.MapPath(Request.ApplicationPath) + @"Reports\Report2.rdlc";
            reportViewer.LocalReport.DataSources.Add(new ReportDataSource("DataSet1", dataSet.Tables[0]));

            ViewBag.ReportViewer = reportViewer;

            return View();
        }

Note the report defined file is RDLC file not RDL file. You can just simply rename the file format. RDLC file and RDL file shared same XML Schema, and RDLC file may contains empty values.

Since the RDLC doesn't contain any data source information to increase the flexibility, you need to supply data source within your controller. In addition, you need to use same data set name as your SSRS report, for above code, DataSet1 is used as the SSRS report defined. Because the DataSet1 actually used sql script, so SQLConnection and SQLAdapter are applied here to fill the dataset.

To make the report view area variable for your browser, you need to add parameters when creating the ReportViewer class:

            ReportViewer reportViewer = new ReportViewer()
            {
                SizeToReportContent = true,
                Width = System.Web.UI.WebControls.Unit.Percentage(100),
                Height = System.Web.UI.WebControls.Unit.Percentage(100),
            };

The last is for the view. Add reference at first.

@using ReportViewerForMvc;

Using the building html helper the report in your page.

@Html.ReportViewer(ViewBag.ReportViewer as Microsoft.Reporting.WebForms.ReportViewer)

Resource:
https://reportviewerformvc.codeplex.com/wikipage?title=Getting%20Started
http://stackoverflow.com/questions/1079162/when-to-use-rdlc-over-rdl-reports