Create and display SSRS Report in ASP.NET MVC (Example with code)

In this article, I have explained how you can create and view SSRS reports in ASP.NET MVC, step by step with an example, also inlcudes demo project to download.

1.Create a new project in your Visual Studio(File-> New->Project->From web(Left pane)-> Select "Asp.Net Web project"(right pane)) .  

    a) Enter a Name, Click "Ok"
    b) Select "MVC" template from project and click "Ok"

2. In your new Project, Install these packages for Report Viewer, using Nuget Package manager(For Nuget Package manager console, browse Tools-> Nuget Package Manager-> Nuget package manager console) console, then enter these commands :

Install-Package Microsoft.Report.Viewer -Version 11.0.0

Install-Package Microsoft.ReportViewer.Runtime.WebForms -Version 12.0.2402.15

Install-Package ReportViewerForMvc

USE [master]
GO
/****** Object:  Database [Students]    Script Date: 10/18/2018 4:48:19 PM ******/
CREATE DATABASE [Students]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Students', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2\MSSQL\DATA\Students.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Students_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2\MSSQL\DATA\Students_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Students] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Students].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Students] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [Students] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [Students] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [Students] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [Students] SET ARITHABORT OFF 
GO
ALTER DATABASE [Students] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Students] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [Students] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Students] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Students] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Students] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Students] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [Students] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [Students] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [Students] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [Students] SET  DISABLE_BROKER 
GO
ALTER DATABASE [Students] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [Students] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [Students] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [Students] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [Students] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [Students] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [Students] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [Students] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [Students] SET  MULTI_USER 
GO
ALTER DATABASE [Students] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [Students] SET DB_CHAINING OFF 
GO
ALTER DATABASE [Students] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [Students] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
USE [Students]
GO
/****** Object:  Table [dbo].[Student_details]    Script Date: 10/18/2018 4:48:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Student_details](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[Email] [varchar](50) NULL,
	[Class] [varchar](50) NULL,
 CONSTRAINT [PK_Student_details] PRIMARY KEY CLUSTERED 
(
	[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
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Student_details] ON 

INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (1, N'Suresh Wahi', N'suresh_wahi@gmail.com', N'Class X')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (2, N'Ramesh taurani', N'ramesh.taurani@gmail.com', N'Class X')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (3, N'Preetam', N'preeetam@gmail.com', N'Class XI')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (4, N'Surendra', N'Surendra@gmail.com', N'Class X')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (5, N'Martin ', N'Martin@gmail.com', N'Class XI')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (6, N'Maria', N'maria@gmail.com', N'Class X')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (7, N'Preeti', N'preeti@gmail.com', N'Class X')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (1004, N'Joseph', N'Joseph@gmail.com', N'Class X')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (1005, N'Santa', N'santa@gmail.com', N'Class XI')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (1006, N'Will', N'will@gmail.com', N'Class XI')
INSERT [dbo].[Student_details] ([Id], [Name], [Email], [Class]) VALUES (1007, N'Martin', N'martin@gmail.com', N'Class XI')
SET IDENTITY_INSERT [dbo].[Student_details] OFF
USE [master]
GO
ALTER DATABASE [Students] SET  READ_WRITE 
GO

•  Right Click on the folder -> Select "Add" -> Add new item "Dataset" - name it properly
• Now you must see server explorer , click on it and add new connection to connect to your database, as shown below
  
  Configure to connect to your database, and then just drag and drop your database table/stored procedure, and you will see output like below
  
• Right Click on "reports" folder -> add new item - go to reporting - > select "report" option -> name it properly -> Click "OK"
  
  

  Note: If you are not able to find Reports in your Visual Studio templates, you may have to install it first.You can install it by navigating to "tools"-> Select "Extension and Updates" -> Search "Report" ->Click on  Install for "Microsoft reporting services projects" and "Microsoft RDLC report Designer for Visual Studio" both -> Close Visual Studio, it will start and then Re-start VS now, you must see the option.

• Now, open your Report1.rdlc, From the "report data" tab select "Data sets", if there is nothing, right click and add Data-set
  
  Once you select dataset , drag/drop column in the report1.rdlc (Note: Above image shows different columns than database used in the example.)
• add/select other columns(which you need), that will be displayed in report.

4. Navigate to HomeController.cs and use the below code (If there is no controller, please right-click on your 'Controllers' Folder -> Select "Add"-> Select "Controlelr"> Select MVC 5 Controller – Empty > click Add)

C# Source Code for the controller would be as below:

using Microsoft.Reporting.WebForms;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Mvc;
using System.Web.UI.WebControls;

namespace SSRSInMVC.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
           
           
            return View();
        }


        SSRSInMVC.Report.Report ds = new SSRSInMVC.Report.Report();
        public ActionResult ReportStudent()
        {
            ReportViewer reportViewer = new ReportViewer();
            reportViewer.ProcessingMode = ProcessingMode.Local;
            reportViewer.SizeToReportContent = true;
            reportViewer.Width = Unit.Percentage(900);
            reportViewer.Height = Unit.Percentage(900);

            var connectionString = ConfigurationManager.ConnectionStrings["SSRSInMVC.Properties.Settings.StudentsConnectionString"].ConnectionString;


            SqlConnection conx = new SqlConnection(connectionString);
            SqlDataAdapter adp = new SqlDataAdapter("SELECT * FROM Student_details", conx);

            adp.Fill(ds, ds.Student_details.TableName);

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


            ViewBag.ReportViewer = reportViewer;

            return View();
        }
    }
}

Explanation of above code: in the above code we are getting values from database, load the url in report view using code

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

5. Add View ReportStudent.cshtml, with the following source

@using ReportViewerForMvc;
@{
    ViewBag.Title = "Report Student";
}
<br/>
<br />

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

As you can see in the above code we are referencing ReportViewerForMVC in the above view, with the help of which we are rendering reports in our view.

6.We are done now, you can run the project in your browser, navigate to "/Home/ReportStudent" and you will get the output as needed, here is the output

Note: If you are getting only first value from your database table, Go to "report1.rdlc" -> Right Click any column "Expr" -> Select "Expression" -> Change "First(Fields!ColumName.Value,"DataSet1")" to "Fields!ColumName.Value".

If you want to open report in new tab, instead of showing it in the view, simply add anchor link in the view

@{
    ViewBag.Title = "Report Student";
}
<br/>
<br />

<a href="/ReportViewerWebForm.aspx" target="_blank">open report in new tab</a>

It will open report in new tab when clicked, but I haven't changed anything on C# Controller ActionMethod "ReportStudent", we will keep it same.

That's it, we are done, you can also download sample project with scripts(111Mb).

You may also like to read:

Aggregate Functions in SQL Server (SUM, AVG, COUNT, MIN, MAX)

Is sql server management studio free to use?

SQL server connection string examples in C# & XML