SSRS Local Reports in Angular Apps on Azure Web Sites

I was recently working on an app that needed to provide reporting functionality in addition to regular data entry.  I blogged in the past about SSRS in Angular apps, and I used SSRS server reports (.rdl files) for that demo.  This works really well on locally installed application, but to support SSRS on Azure requires companies to maintain SQL server instance in a virtual machine, which means some infrastructure work.  In the app I was working on reporting needs were light and did not require really the horse power of a standalone SQL Server.  So, I decided  to go with another SSRS feature – local reports.  You can just bing SSRS local reports for more information.

In a gist, to create a local report you just need to create a dataset (blast from the past), then create new report in Visual Studio (new item dialog, type report in search box), then assign your dataset as the data source.  Then you can design a report.  Here is how my solution is laid out

image

I am putting reports into Reports folder in my web site project.  I put datasets under my data access project, into their own reports folder.  Just trying to keep assets organized.

One super important point.  I am using Dataset just to present the data to the report, and report to present the data to the user.  I keep all other logic and data access code out this path with local reports.

Here is what my dataset looks like.

image       

And here is how my repository fills this dataset with data.

 

public UserListDataSet GetUserListDataSet(IEnumerable<ReportRequestParameter> parameters) { var reportRequestParameters = parameters as IList<ReportRequestParameter> ?? parameters.ToList(); if (reportRequestParameters.Any()) { var activeOnly = reportRequestParameters[0].ToString() == "1"; var inactiveOnly = reportRequestParameters[0].ToString() == "0"; var set = new UserListDataSet(); var query = Context.Contacts.OrderBy(a => a.LastName); if (activeOnly) { query = (IOrderedQueryable<Contact>)query.Where(a => a.IsActive); } if (inactiveOnly) { query = (IOrderedQueryable<Contact>)query.Where(a => a.IsActive == false); } var data = from oneContact in query select new { oneContact.ContactId, oneContact.FirstName, oneContact.LastName, oneContact.ContactType.Name }; foreach (var oneContact in data) { set.Contacts.AddContactsRow( oneContact.FirstName, oneContact.LastName, oneContact.ContactId, oneContact.Name); } return set; } throw new ArgumentException("GetUserListDataSet received no parameters."); }

 

My parameters are passed into the repository in a generic way, the same way they are collected by user interface. You can read my old post on the details of parameter gathering. There is only one parameter on this report, so I can just take the first parameter and interpret its value.

Then I get the data from two tables, finally pumping it into the first table in my dataset, Contacts table.

From my Angular app, I just need to call something on the server that renders the report. Just for a good measure I am going to add two options: show preview windows or show PDF. In order to render a viewer I create an ASPX page in my MVC app, just like in my last post, but interactive with local report is a bit different from server reports.

I do not need to know the server URL. I just need to call my repository to get the dataset, then assign it to my report. My page still has report viewer control, and I need to reference Microsoft.ReportViewer.WebForms in my project to make it work.

The HTML is very simple:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ReportForm.aspx.cs" Inherits="AngularAspNetMvc.Web.ViewsStatic.ReportForm" %> <!DOCTYPE html> <%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <meta name="description" content="Report page" /> </head> <body> <form id="reportForm" runat="server"> <asp:ScriptManager ID="ScriptManager1" runat="server"> </asp:ScriptManager> <div style="overflow: hidden"> <rsweb:ReportViewer ID="mainReportViewer" SizeToReportContent="True" runat="server"> </rsweb:ReportViewer> </div> </form> </body> </html>

Remember, you cannot put this HTML file in Views folder, or MVC runtime will try to interpret it. Instead, I create ViewsStatic folder.

Now it is time to write some code behind.

using System; using System.Data; using System.Web.UI; using AngularAspNetMvc.Data.Models; using AngularAspNetMvc.DataAccess.Reports; using AngularAspNetMvc.DataAccess.Repository; using Microsoft.Reporting.WebForms; namespace AngularAspNetMvc.Web.ViewsStatic { public partial class ReportForm : Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Guid requestID = new Guid(Request.QueryString["r"]); ReportRequest request; using (var repository = new ReportsRepository()) { request = repository.GetRequest(requestID); } if (request != null) { DataSet data; using (var reportsRunRepository = new ReportsRunRepository()) { data = reportsRunRepository.GetReportData(request); } mainReportViewer.ProcessingMode = ProcessingMode.Local; mainReportViewer.LocalReport.ReportPath = Server.MapPath("~/Reports/" + request.ReportFileName); mainReportViewer.ShowParameterPrompts = false; mainReportViewer.ShowRefreshButton = false; mainReportViewer.ShowWaitControlCancelLink = false; mainReportViewer.ShowBackButton = false; mainReportViewer.ShowCredentialPrompts = false; mainReportViewer.LocalReport.DataSources.Add(new ReportDataSource("DataSet1", data.Tables[0])); mainReportViewer.KeepSessionAlive = false; mainReportViewer.AsyncRendering = false; } } } } }

Let’s review this code. I am getting report request details first, including report id and parameter values. This way I can just pass in request ID instead of a ton of data. Then I am creating an instance of report repository and get my dataset.

Finally, I am setting up report viewer in local mode. Report path is the path to my RDLC file, report definition file. I am hiding all the buttons except export. I am also turning off session to avoid errors during export, since I do not want to use sessions.

This is it. From Angular app I just need to set the source of iFrame to the URL of my report viewer form plus request ID as parameter.

runReport = () => { this.reportsService.createRequest(this.report, (result: string) => { if (result) { this.report.UniqueId = result; this.reportSource = this.globalsService.baseUrl + "ViewsStatic/ReportForm.aspx?r=" + this.report.UniqueId; } }); } runPdfReport = () => { this.reportsService.createRequest(this.report, (result: string) => { if (result) { this.report.UniqueId = result; this.reportSource = this.globalsService.baseUrl + "ViewsStatic/ReportHandler.ashx?r=" + this.report.UniqueId; } }); }

You see two methods here, one for the viewer form above, the other for PDF export. I am using standard HTTP handler for the export, with you can add from new item dialog by typing handler into the search box. Pick an item with ashx extension.

Now I am writing code behind for the handler.

 

using System; using System.Data; using System.Web; using AngularAspNetMvc.Data.Models; using AngularAspNetMvc.DataAccess.Reports; using AngularAspNetMvc.DataAccess.Repository; using Microsoft.Reporting.WebForms; namespace AngularAspNetMvc.Web.ViewsStatic { public class ReportForm1 : IHttpHandler { public void ProcessRequest(HttpContext context) { Guid requestID = new Guid(context.Request.QueryString["r"]); ReportRequest request; using (var repository = new ReportsRepository()) { request = repository.GetRequest(requestID); } if (request != null) { DataSet data; using (var reportsRunRepository = new ReportsRunRepository()) { data = reportsRunRepository.GetReportData(request); } var report = new LocalReport(); report.ReportPath = context.Server.MapPath("~/Reports/" + request.ReportFileName); report.DataSources.Add(new ReportDataSource("DataSet1", data.Tables[0])); var reportData = report.Render("PDF"); context.Response.BinaryWrite(reportData); context.Response.ContentType = "application/pdf"; } } public bool IsReusable { get { return false; } } } }

 

I am interacting with standard SSRS classes included in Microsoft.ReportViewer.WebForms, namely LocalReport. Once I create PDF export as a byte array, I am streaming it into the response and set content type to let the browser know. Many browsers now support PDF natively, which makes it very convenient for us, developers.

Now it is time to deploy my app to Azure. As soon as I did that and try to preview a report, I got an error about an assembly missing. I am using VS 2015, and only Microsoft.ReportViewer.WebForms assembly is available in Add Reference dialog. However, we need more:

 

image

 

I searched my machine, but could not find reference assemblies for Microsoft.ReportViewer.Common and Microsoft.ReportViewer.ProcessingObjectModel. How did I find them?

Go to command prompt, change folder to c:\windows\assembly, then search for the DLLs. You will find them, then just copy them to a folder and reference in your project. Quite painful, but works.

The next error will popup when you try to export report as PDF. The stack trace will contain code from report viewer control. After digging for while I found out that free tier on Azure Web sites does not support PDF export.

SO, I had to switch to Basic tier to make this work.

This is the final step.

A few things to note in the downloadable source code zip file.

  • Sample project is using TypeScript
  • I updated the old project to use Angular Controller As syntax.
  • To run locally you need to have SQL Server (or Express) running and change connection string to match.
  • Restore NuGet packages in Visual Studio
  • Reread my old post to fill in the gaps.

Enjoy.