SSRS Report Set-Up Using VSIX Installer And Execution In Client Side Using Entity Framework

Introduction

The most commonly used 3 types of reports are mentioned below.

1. Local SSRS Report.
2. Server SSRS Report.
3. Crystal Report.

Local SSRS Report

The report is configured to execute in a local system. Its extension is RDLC (Report Definition Language Client-side).

Server SSRS Report

The report execution is possible on the server side. Its extension is RDL. RDL is a file extension for an XML file used by Microsoft SQL Server reporting services. RDL stands for Report Definition Language.

RDL is used for SQL Server Reporting Services and RDLC are used in Visual Studio for client-side reporting. The implementation and editor are almost identical. RDL stands for Report Definition Language and RDLC Report Definition Language Client-side. 

Crystal Report

Crystal Reports is a business intelligence application used to create custom reports from a variety of data sources. It was developed by BusinessObjects and marketed by SAP SE.

Description

In this session, I will show you the steps to set-up the SSRS report locally and its execution in client side using MVC and Entity Framework.

Prerequisites

• Visual Studio 2017 Version 15.7.3
• Microsoft .NET Framework Version 4.6
• Microsoft SQL Server 2016
• SQL Server Management Studio v17.7

Report Set-up using VSIX installer and NUGET

VSIX is a Visual Studio extension installer. The VSIX file is the unit of deployment. VSIX installer installs the contents of the file to the right location after downloading and updating the extensions from ToolsExtensions and Updates.

Go to Visual Studio Marketplace and search for Microsoft Rdlc Report Designer for Visual Studio in Tools > Extesnsions and Updates. After downloading the extensions it can be installed using VSIX installer in Visual Studio 2017 IDE Package.

After installing extensions, re-open the project and Go to Add New Item > Visual C# Items section. Now, we can see both reports and reporting wizard are there.

 

Issue During RDLC Extension installation using VSIX Installer

 

During installation of extension using VSIX Installer, I was facing a issue like the installer has failed to install the package successfully. Please check the log file.

 

 

 

The way to solve this issue

 

Just update the Visual Studio 2017 using Visual Studio Installer. Then try to download the extension package and the VSIX Installer will install it succesfully.

 

Report Viewer and its assembly file

 

Now we need to add Report Viewer Control for Web from NuGet. Its Version is 14.0.0.0. After installing this package you can find out some auto added DLLs in your project and some auto added assemblies code in web.config file.

 

 

Other Way To Download and Install Extensions and Packages

• Visual Studio Market Place >>

Steps To Be Followed,

 

Step 1

 

I have created a table named "Profile".

 

Sql Syntax for table creation with sample records,

GO
/****** Object:  Table [dbo].[Profile]    Script Date: 10-09-2018 00:31:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Profile](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Profile] 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
INSERT [dbo].[Profile] ([ID], [Name]) VALUES (1, N'Satya')
GO
INSERT [dbo].[Profile] ([ID], [Name]) VALUES (2, N'Kulu')
GO
INSERT [dbo].[Profile] ([ID], [Name]) VALUES (3, N'Satyaprakash')
GO
INSERT [dbo].[Profile] ([ID], [Name]) VALUES (4, N'Samantaray')
GO
INSERT [dbo].[Profile] ([ID], [Name]) VALUES (5, N'Samaal')
GO
INSERT [dbo].[Profile] ([ID], [Name]) VALUES (6, N'Satyaprakash Samantaray')
GO
INSERT [dbo].[Profile] ([ID], [Name]) VALUES (7, N'Suketu')
GO
INSERT [dbo].[Profile] ([ID], [Name]) VALUES (8, N'Disilva')
GO
INSERT [dbo].[Profile] ([ID], [Name]) VALUES (9, N'Dinesh Kartik')
GO
INSERT [dbo].[Profile] ([ID], [Name]) VALUES (10, N'Ram Kashyapa')
GO

Step 2

 

Create an entity data model (.edmx) named SatyaModel.edmx using table Profile to fetch records.

 

Step 3

 

The Entity Framework is now configured and hence now we can create a Controller and write code to fetch the records from the Profile Table.

 

Code Ref

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace SSRSUsingEF.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            SatyaDBEntities1 entities = new SatyaDBEntities1();
            return View(from Profile in entities.Profiles.Take(10)
                        select Profile);
        }
     }
}

Code Description

 

Inside the Index Action method, the Top 10 Customer records are fetched and returned to the View using SatyaDBEntities1 Data context class of entity data model.

 

Step 4

 

Right Click on the Controller class and click on the Add View option in order to create a View for the Controller. The Name of the View is set to Index, the template option is set to Empty, the Model class is set to Profile Entity that one we have generated using Entity Framework and finally the Data context class is set to SatyaDBEntities1.

 

Code Ref

@model  IEnumerable<SSRSUsingEF.Profile>

@{
    ViewBag.Title = "Index";
}


<style>
    table {
        font-family: arial, sans-serif;
        border-collapse: collapse;
        width: 100%;
    }

    td, th {
        border: 1px solid #dddddd;
        text-align: left;
        padding: 8px;
    }

    tr:nth-child(even) {
        background-color: #dddddd;
    }

</style>

<body>
    <h4>Profile</h4>
    <hr/>
    <table align="center" border="1" cellpadding="4" cellspacing="4">
        <tr>
            <th style="background-color: Yellow;color: blue">Profile ID</th>
            <th style="background-color: Yellow;color: blue">Profile Name</th>
        </tr>
        @foreach (Profile profile in Model)
        {
        <tr>
            <td>@profile.ID</td>
            <td>@profile.Name</td>
        </tr>
        }
    </table>
    <br/>
    <a href="~/RDLC/PrintReport.aspx">Print Report</a>
</body>

Code Description

 

Inside the View, in the very first line, the Profile Entity is declared as IEnumerable which specifies that it will be available as a Collection. For displaying the records, an HTML Table is used. A loop will be executed over the Model which will generate the HTML Table rows with the Customer records. There is an HTML Anchor Link which redirects the User to the PrintReport.aspx used for displaying the RDLC Report.

 

Here I have added some style to HTML table for a better UI look for the end user.

 

Step 5 - Setup RDLC Report in ASP.Net MVC 

 

Right click the Project in the Solution Explorer and click Add and then New Item and then select Report Wizard and set a name for the RDLC Report. 

 

Once you click Add button in the above dialog, the following dialog appears where you will need to create a DataSet for the RDLC Report.

First, you need to set a Name for the DataSet and then you need to click New button in order to configure the Data Source.

 

Then you will need to select the SatyaDBConnectionString and click Next button. Now you need to choose the Table(s) which will be used to populate the DataSet for the RDLC Report and then click Finish button.

 

The DataSet is now configured and now we can proceed further by clicking the Next button. You will need to choose the Fields to be displayed in the RDLC report. This can be done by simple drag and drop of Field from Available fields box to the Values box.

 

This dialog will ask to choose the Layout. Finally, we need to choose the style, i.e. color and theme of the Report.

 

Once you click Finish button, your RDLC Report should look as below with available data sources and datasets.

 

 

 

During the creation of the RDLC report, one other file will be generated called "SatyaDBDataSet.xsd". Here, SatyaDBDataSet is nothing but the name of the Data Sources associated with RDLC file.  You can change/delete the dataset.xsd all you want without directly effecting the .RDLC. The report stores the fields used to design it within its xml, and the name of the datatables. So at runtime, it just wants you to hand it a datasource with the same name as the one you designed it with.

 

Deleting the SatyaDBDataSet.xsd will not create problem while deployment to the test server or production server because report contains the references in it’s xml file . 

 

Step 6

 

RDLC Report works only with a RDLC Report Viewer control which is available only in ASP.NET Web Forms and hence for displaying a RDLC Report, you will need to add a Web Forms page. Now in the ASP.Net Web Forms page, you will need to add an RDLC Report Viewer control from the ToolBox.

 

It comes with namespace Microsoft.ReportViewer.WebForms and mainly it is the component of SSRS to build the local reports and also display the reports published on the Reporting Server. 

 

The Reportviewer controls runs in two processing modes, namely Local and Remote mode. The Processing mode determines whether the report processing occurs locally or on a report server. In local mode, the report processing is done locally which means the processing is done on the application server where the asp.net is hosted. In server mode the report processing in done on Reporting server where the report is published, we just need to mention the URL of the published report. 

 

Code Ref

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="PrintReport.aspx.cs" Inherits="SSRSUsingEF.RDLC.PrintReport" %>

<%@Register Assembly="Microsoft.ReportViewer.WebForms, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>


<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
        <rsweb:ReportViewer ID="ReportViewer1" runat="server" AsyncRendering="false"></rsweb:ReportViewer>
    </form>
</body>
</html>

Code Description

 

The HTML Markup consists of an ASP.Net AJAX Script Manager control and ASP.NET Report Viewer control. Before that you shoud mention the report viewer webform assembly reference else reportviewer is unable to work.

<%@Register Assembly="Microsoft.ReportViewer.WebForms, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
   <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
   <rsweb:ReportViewer ID="ReportViewer1" runat="server" AsyncRendering="false">
</rsweb:ReportViewer>

Step 7

 

Apply code in the code behind file of PrintReport.aspx.cs.

 

Code Ref

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Reporting.WebForms;


namespace SSRSUsingEF.RDLC
{
    public partial class PrintReport : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            ReportViewer1.ProcessingMode = ProcessingMode.Local;
            ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/MyReport.rdlc");
            SatyaDBEntities1 entities = new SatyaDBEntities1();
            ReportDataSource datasource = new ReportDataSource("DataSet1", (from profile in entities.Profiles.Take(10)
                                                                             select profile));
            ReportViewer1.LocalReport.DataSources.Clear();
            ReportViewer1.LocalReport.DataSources.Add(datasource);
        }
    }
}

Code Description

 

You will need to import the following namespace for report execution.

using Microsoft.Reporting.WebForms;

Inside the Page Load event, the RDLC Report is populated with the records of the database using Entity Framework.

 

Here I mentioned the report path.

ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/MyReport.rdlc");

Then using object of data context class SatyaDBEntities1, we can fetch 10 records from the table associated with the entity data model. Here DataSet1 is nothing but the name of the name of the datasets associated with an RDLC report.

SatyaDBEntities1 entities = new SatyaDBEntities1();
            ReportDataSource datasource = new ReportDataSource("DataSet1", (from profile in entities.Profiles.Take(10)
                                                                             select profile));

Download Source Code

• Link to the source code

OUTPUT

 

Initially, during the page load, the HTML table with records will be shown using ID and NAME. HTML Table is displaying Entity Framework records.

 

 

After clicking on the Print Report link, the rdlc report will be shown with the name part only. RDLC Report is displaying Entity Framework records.

 

 

SUMMARY

• What the VSIX Installer is.
• Report Viewer from Nuget Gallery and associate assembly files and DLLs.
• RDLC execution using entity framework in MVC.
• Set-up of RDLC from scratch in Visual Studio 2017.