Serenity: SSRS Integration

Created on 4 Oct 2018 · 14Comments · Source: serenity-is/Serenity

Hi,
Please anybody tried embedding a Sql Server Reporting Services report in a serenity application? Could you provide some guidance and code snippets to help me do same on a project for my reporting needs?

Regards.

Source

gfo2007

Most helpful comment

I have Integrated Report Viewer 11.0 in Serenity Sample Application with .Net 4.5 framework
You have to add below dll files in references
Microsoft.ReportViewer.Common 11.0.0.0
Microsoft.ReportViewer.ProcessingObjectModel 11.0.0.0
Microsoft.ReportViewer.WebForms 11.0.0.0
ReportViewerForMvc 1.1.1.0
[Can be downloaded from nuget package https://www.nuget.org/packages/ReportViewerForMvc/ ]

Create .aspx page with name say 'ReportViewerWebForm.aspx' with below code else can be found same file in ReportViewerForMvc nuget package content folder

<%@ Page Language="C#" AutoEventWireup="True" CodeBehind="ReportViewerWebForm.aspx.cs" Inherits="ReportViewerForMvc.ReportViewerWebForm" %>

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<style>
   .iframe {
  /*for the report viewer*/
  border: none;
  padding: 0;
  margin: 0;
  width: 100%;
  height: 100%;
}
</style>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body style="margin: 0px; padding: 0px;">
    <form id="form1" runat="server">
        <div>
            <asp:ScriptManager ID="ScriptManager1" runat="server">
                <Scripts>
                    <asp:ScriptReference Assembly="ReportViewerForMvc" Name="ReportViewerForMvc.Scripts.PostMessage.js" />
                     <%--<asp:ScriptReference Path="~/Scripts/ReportViewer/ReportViewer.js" />--%>
                </Scripts>
            </asp:ScriptManager>
            <rsweb:ReportViewer ID="ReportViewer1" runat="server" ></rsweb:ReportViewer>
        </div>
    </form>
</body>
</html>

Create Folder with name 'Reports' and create .rdlc file with name 'SampleReport.rdlc'

Add Controller and View with code below
PageController.cs

namespace SmartFactory.Samples
{
    using Microsoft.Reporting.WebForms;
    using System.Linq;
    using System.Web.Mvc;
    using System.Web.UI.WebControls;

    [Authorize, RoutePrefix("Samples/ReportViewer"), Route("{action=index}")]
    public class ReportViewerController : Controller
    {
        public ActionResult Page()
        {
            System.Collections.Generic.List<SmartFactory.AuditLog> customers = null;
            using (SmartFactory.ReportEntityModel dc = new SmartFactory.ReportEntityModel())
            {
                customers = dc.AuditLogs.OrderBy(a => a.TimeStamp).ToList();
                ReportViewer reportViewer = new ReportViewer();

                reportViewer.LocalReport.ReportPath = Request.MapPath(Request.ApplicationPath);

                reportViewer.LocalReport.DataSources.Clear();
                ReportDataSource rdc = new ReportDataSource("DataSet1", customers);

                reportViewer.LocalReport.DataSources.Add(rdc);

                reportViewer.ProcessingMode = ProcessingMode.Local;
                reportViewer.LocalReport.ReportPath += @"Reports/SampleReport.rdlc";
                reportViewer.SizeToReportContent = true;
                reportViewer.ZoomMode = ZoomMode.FullPage;
                reportViewer.Width = Unit.Pixel(1600);
                reportViewer.Height = Unit.Pixel(1200);
                ViewBag.ReportViewer = reportViewer;

                reportViewer.LocalReport.Refresh();
            }
            return View(MVC.Views.Samples.ReportViewer.Page);
        }


    }
}

Page.cshtml

@using ReportViewerForMvc 
@{
    ViewData["Title"] = "ReportViewer";
}
@*@Html.Partial("ReportViwerASPX")*@

@section Head {
    <link rel="stylesheet" href="~/Scripts/daterangepicker/daterangepicker-bs3.css" />
    <script src="~/Scripts/input-mask/jquery.inputmask.js"></script>
    <script src="~/Scripts/input-mask/jquery.inputmask.date.extensions.js"></script>
    <script src="~/Scripts/input-mask/jquery.inputmask.extensions.js"></script>
    <script src="~/Scripts/daterangepicker/moment.min.js"></script>
    <script src="~/Scripts/daterangepicker/daterangepicker.js"></script>
    <script src="~/Scripts/colorpicker/bootstrap-colorpicker.min.js"></script>
    <script src="~/Scripts/timepicker/bootstrap-timepicker.min.js"></script>
    <script src="~/Scripts/jquery.icheck.min.js"></script>
    <script src="~/Scripts/adminlte/demo.js"></script>
    <script src="~/Scripts/chartjs/Chart.min.js"></script>
    <script src="~/Scripts/adminlte/demo.js"></script>  
    @*<script src="~/Scripts/chartjs/Chart.bundle.min.js"></script>*@
}

@section ContentHeader {
    @*<h1>@ViewData["Title"]<small></small></h1>*@
}
<style>
    .chart-nodata {
        height: 205px;
        line-height: 195px;
    }

    .text-center {
        text-align: center;
    }
</style>

<div class="box box-default" id="boxId">
    <div class="box-body">
@Html.ReportViewer(ViewBag.ReportViewer as Microsoft.Reporting.WebForms.ReportViewer)
    </div><!-- /.box -->
</div><!-- /.box -->

        <script>
                $("iframe").width($('#boxId').width() - 50); 
                $("iframe").height($('#boxId').height());
        </script>

Hope this helps ...feel free to ask if any issues

TechAspirant on 5 Oct 2018

👍5

All 14 comments

Maybe the javascript call would solve it already?

We are also interested in how to facilitate this since Volkan is already thinking of a better alternative to this.

https://bpmsbi.wordpress.com/2008/09/27/advanced-javascript-in-reporting/

https://jeffprom.com/2012/08/31/use-javascript-to-open-a-second-ssrs-report-window/

controllernet on 4 Oct 2018

@controllernet thanks for your quick response but it is not what I need. I am rather trying to embed reporting services report in a serenity application. The links you have provided seem to show how to open reporting services reports on the report server using javascript.

gfo2007 on 4 Oct 2018

I've done this with Microsoft.Reporting.WinForms.LocalReport() to render a .rdlc file to PDF. Works fine unless you have nested reports...

I could provide some code if needed.

hannesb on 4 Oct 2018

@hannesb please kindly provide the code.

Regards

gfo2007 on 5 Oct 2018

Create .aspx page with name say 'ReportViewerWebForm.aspx' with below code else can be found same file in ReportViewerForMvc nuget package content folder

<%@ Page Language="C#" AutoEventWireup="True" CodeBehind="ReportViewerWebForm.aspx.cs" Inherits="ReportViewerForMvc.ReportViewerWebForm" %>

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<style>
   .iframe {
  /*for the report viewer*/
  border: none;
  padding: 0;
  margin: 0;
  width: 100%;
  height: 100%;
}
</style>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body style="margin: 0px; padding: 0px;">
    <form id="form1" runat="server">
        <div>
            <asp:ScriptManager ID="ScriptManager1" runat="server">
                <Scripts>
                    <asp:ScriptReference Assembly="ReportViewerForMvc" Name="ReportViewerForMvc.Scripts.PostMessage.js" />
                     <%--<asp:ScriptReference Path="~/Scripts/ReportViewer/ReportViewer.js" />--%>
                </Scripts>
            </asp:ScriptManager>
            <rsweb:ReportViewer ID="ReportViewer1" runat="server" ></rsweb:ReportViewer>
        </div>
    </form>
</body>
</html>

Create Folder with name 'Reports' and create .rdlc file with name 'SampleReport.rdlc'

Add Controller and View with code below
PageController.cs

namespace SmartFactory.Samples
{
    using Microsoft.Reporting.WebForms;
    using System.Linq;
    using System.Web.Mvc;
    using System.Web.UI.WebControls;

    [Authorize, RoutePrefix("Samples/ReportViewer"), Route("{action=index}")]
    public class ReportViewerController : Controller
    {
        public ActionResult Page()
        {
            System.Collections.Generic.List<SmartFactory.AuditLog> customers = null;
            using (SmartFactory.ReportEntityModel dc = new SmartFactory.ReportEntityModel())
            {
                customers = dc.AuditLogs.OrderBy(a => a.TimeStamp).ToList();
                ReportViewer reportViewer = new ReportViewer();

                reportViewer.LocalReport.ReportPath = Request.MapPath(Request.ApplicationPath);

                reportViewer.LocalReport.DataSources.Clear();
                ReportDataSource rdc = new ReportDataSource("DataSet1", customers);

                reportViewer.LocalReport.DataSources.Add(rdc);

                reportViewer.ProcessingMode = ProcessingMode.Local;
                reportViewer.LocalReport.ReportPath += @"Reports/SampleReport.rdlc";
                reportViewer.SizeToReportContent = true;
                reportViewer.ZoomMode = ZoomMode.FullPage;
                reportViewer.Width = Unit.Pixel(1600);
                reportViewer.Height = Unit.Pixel(1200);
                ViewBag.ReportViewer = reportViewer;

                reportViewer.LocalReport.Refresh();
            }
            return View(MVC.Views.Samples.ReportViewer.Page);
        }


    }
}

Page.cshtml

@using ReportViewerForMvc 
@{
    ViewData["Title"] = "ReportViewer";
}
@*@Html.Partial("ReportViwerASPX")*@

@section Head {
    <link rel="stylesheet" href="~/Scripts/daterangepicker/daterangepicker-bs3.css" />
    <script src="~/Scripts/input-mask/jquery.inputmask.js"></script>
    <script src="~/Scripts/input-mask/jquery.inputmask.date.extensions.js"></script>
    <script src="~/Scripts/input-mask/jquery.inputmask.extensions.js"></script>
    <script src="~/Scripts/daterangepicker/moment.min.js"></script>
    <script src="~/Scripts/daterangepicker/daterangepicker.js"></script>
    <script src="~/Scripts/colorpicker/bootstrap-colorpicker.min.js"></script>
    <script src="~/Scripts/timepicker/bootstrap-timepicker.min.js"></script>
    <script src="~/Scripts/jquery.icheck.min.js"></script>
    <script src="~/Scripts/adminlte/demo.js"></script>
    <script src="~/Scripts/chartjs/Chart.min.js"></script>
    <script src="~/Scripts/adminlte/demo.js"></script>  
    @*<script src="~/Scripts/chartjs/Chart.bundle.min.js"></script>*@
}

@section ContentHeader {
    @*<h1>@ViewData["Title"]<small></small></h1>*@
}
<style>
    .chart-nodata {
        height: 205px;
        line-height: 195px;
    }

    .text-center {
        text-align: center;
    }
</style>

<div class="box box-default" id="boxId">
    <div class="box-body">
@Html.ReportViewer(ViewBag.ReportViewer as Microsoft.Reporting.WebForms.ReportViewer)
    </div><!-- /.box -->
</div><!-- /.box -->

        <script>
                $("iframe").width($('#boxId').width() - 50); 
                $("iframe").height($('#boxId').height());
        </script>

Hope this helps ...feel free to ask if any issues

TechAspirant on 5 Oct 2018

👍5

@TechAspirant thanks so much for this code. Even though I managed a workaround, I will try this code as well. One quick question.

reportViewer.LocalReport.ReportPath = Request.MapPath(Request.ApplicationPath);

Where is the Request.ApplicationPath value stored in the project? web.config or some other place.

gfo2007 on 5 Oct 2018

@gfo2007 It is likely taken from the host web server (it depends on how you configured IIS).

TechAspirant on 5 Oct 2018

@TechAspirant , @hannesb ,

as this is very interesting for everybody (including myself :-)) - would it be possible for you to create a wiki article for this (1x with the solution of TechAspirant and 1x with the solution of HannesB)?

With kind regards,

John

JohnRanger on 8 Oct 2018

👍3

I have some code for this - you create you rdlc files and then the code uses that to produce the pdf it works but needs cleanup (not documented very well either) I have a zip file with it - I used it so I could pull reports from a mysql database. Yeah I know - I cheated..

I had it so I could either use an embedded dll or a file system ..

stixoffire on 20 Oct 2018

@gfo2007 did you get what you needed ? I have some preliminary code that I was doing for a project , the code works but has some extraneous code in it. (I was building a library) it is in a stage of I can do the reports but not necessarily code to standards or as clean as I would like it to be. If you have an email I could send it. Basically I created the reports using the report designer that was in MS-SQL Express with adv services, I then utilized the xml in that file - translated it how I wanted it. I would then use my code to plant the data into the place holders of the .rdp file - that way I used the RDP designed by MS-SQL as my template, and then I simply plopped my data to populate it .. my serenity app was attached to a MySQL data base. My code allows for dll packaging of the rdp files as well as file based ..

stixoffire on 15 Dec 2018

👍1

@TechAspirant
Thank you so much for this solution,
actually, can you explain a bit about
return View(MVC.Views.Samples.ReportViewer.Page);
am getting an error at ReportViewer.Page

Shraddha996 on 29 Mar 2019

Whats the error you are getting ?

On Fri 29 Mar, 2019 10:52 am Shraddha996, notifications@github.com wrote:

@TechAspirant https://github.com/TechAspirant
Thank you so much for this solution,
actually, can you explain a bit about
return View(MVC.Views.Samples.ReportViewer.Page);
am getting an error at ReportViewer.Page

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/volkanceylan/Serenity/issues/3980#issuecomment-477871748,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AmzbTfBYRfyvk22gNvm90paB-Y5RM9Zwks5vbaMqgaJpZM4XIQFb
.

TechAspirant on 29 Mar 2019

@TechAspirant , this is my code

`//using System;
//using System.Collections.Generic;
//using System.Linq;
//using System.Web;

//namespace Serene1.Modules.Accounts.Reports
//{
// public class PageController
// {
// }
//}
namespace Serene1.Accounts.Pages
{
using Microsoft.Reporting.WebForms;
using Serenity.Data;
using System;
using System.Web.Mvc;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;

[Authorize, RoutePrefix("Accounts/Reports"), Route("{action=index}")]
public class PageController : Controller
{
    private static string connectionString = ConfigurationManager.ConnectionStrings["Premier"].ToString();
    SqlConnection connection = new SqlConnection(connectionString);
    public ActionResult Index()
    {
        System.Collections.Generic.List<Serene1.Premier.Entities.TblCustomerRow> customers = null;
        using (var connection = SqlConnections.NewByKey("Premier"))
        {
            //customers = dc.AuditLogs.OrderBy(a => a.TimeStamp).ToList();
            ReportViewer reportViewer = new ReportViewer();

            reportViewer.LocalReport.ReportPath = Request.MapPath(Request.ApplicationPath);

            reportViewer.LocalReport.DataSources.Clear();
            ReportDataSource rdc = new ReportDataSource("DataSet1", customers);

            reportViewer.LocalReport.DataSources.Add(rdc);

            reportViewer.ProcessingMode = ProcessingMode.Local;
            reportViewer.LocalReport.ReportPath += @"Reports/Report1.rdlc";
            reportViewer.SizeToReportContent = true;
            reportViewer.ZoomMode = ZoomMode.FullPage;
            reportViewer.Width = Unit.Pixel(1600);
            reportViewer.Height = Unit.Pixel(1200);
            ViewBag.ReportViewer = reportViewer;

            reportViewer.LocalReport.Refresh();
        }
        return View(MVC.Views.Accounts.Reports.ReportViewer.Page);
        //return View("~/Modules/Accounts/Reports/Page.cshtml");
    }


}

and getting this error
Screenshot (23)

Shraddha996 on 29 Mar 2019

@TechAspirant Thanks for the code and its working but I couldn't find where you gave the reference of aspx file.
every time I create a new aspx file for the new report it still showing first one report

Shraddha996 on 11 Jul 2019

Was this page helpful?

0 / 5 - 0 ratings