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 ReportViewerForMvcNote: While installing ReportViewForMVC, nuget package must have added a new file "ReportViewerWebForm.aspx", we will keep it same and not change anything on it.
Here is the image of my example database table which we will be using in the article
You can create this database and table data with the following scripts
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)
Buy us a coffee
Become a Patron