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 :
- 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)