Thursday, October 22, 2015

Using ASP.NET Web API as the Data Source for SSRS Reports

Imagine that you want to grab data for your SQL Server Reporting Service Report from calling an end point in ASP.NET Web API. This is a post to show how you can achieve it.

Let’s start from scratch. I have created ASP.NET Web API application from Visual Studio, and there I have the following controller named EmployeesController.
public class EmployeesController : ApiController
{
    public HttpResponseMessage GetEmployees()
    {
        List<Employee> employees = Employee.GetEmployees();
 
        return this.Request.CreateResponse(HttpStatusCode.OK, employees);
    }
}
 
public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
 
    public static List<Employee> GetEmployees()
    {
        return new List<Employee>()
        {
            new Employee() { Id = 1, FirstName = "Jaliya", LastName = "Udagedara" },
            new Employee() { Id = 2, FirstName = "John", LastName = "Smith" },
            new Employee() { Id = 3, FirstName = "Jane", LastName = "Smith" },
        };
    }
}
In the GetEmployees() action, I am returning a HttpResponseMessage with my List of Employees.

Now let’s create a simple SSRS Report using SQL Server Report Builder. I am opening up the SQL Server Report Builder, and from the Report Data toolbox, I am adding a new Data Source.
image
Report Data
In the Data Source dialog, for the Data Source Name, you can give what ever a name you want. For the Connection Type, I am selecting XML and for the Connection String, I am simply providing my Web API employees end point url.
image
Data Source
I am clicking on OK, and adding a new DataSet. Again for the DataSet Name, you can give any name, and I am selecting the “Use a dataset embedded in my report.” radio button. For the Data Source, I am selecting my previously created Data Source.
image
Data Set
And now I am clicking on the Query Designer. And from there, let’s click on the Execute Query button to see what happens.
image
Query Designer
I am thrown with a error, “Data at the root level is invalid.”. This is basically because since we have used Data source connection type as XML, SSRS is requesting data in XML format. And the Web API is retuning data in JSON format. To resolve the issue, we can simple add following line of code in the Web API Application_Start method.
GlobalConfiguration.Configuration.Formatters.XmlFormatter.MediaTypeMappings.Add(new QueryStringMapping("type", "xml", new MediaTypeHeaderValue("application/xml")));

This will modify the Web API content negotiation by checking if the request has a query string named type and it’s value, set to xml, and then the response will be sent in “application/xml”.

Now let’s modify the Connection String, in the Data source, as follows.
image
Data Source
And in the DataSet, from the Query Designer, let’s execute the query and see.
image
Query Designer
Now we are receiving the data as expected. Once you click on OK, it will notify that the query is blank. Doesn’t matter and let’s click on OK. Now you can see that the fields are generated in the DataSet and next step is to create the report. So that’s about it.

I am uploading the full sample to my OneDrive, and you can try it yourself.


Happy Coding.

Regards,
Jaliya

Tuesday, October 20, 2015

Visual C# Technical Guru - September 2015

Results of Microsoft TechNet Guru Awards for the month of September, 2015 is out and had the pleasure of judging set of great articles under Visual C# category.

The TechNet Guru Awards celebrate the technical articles on Microsoft TechNet.

Post in WikiNinjas Official Blog,
image
Visual C# Technical Guru - September 2015
Happy Coding.

Regards,
Jaliya

Wednesday, October 14, 2015

Using WebForms ReportViewer Control Inside AngularJS Single Page Application

WebForms ReportViewer control can be used to render reports deployed to SQL Server Reporting Servcies (SSRS) Report Server or reports which exists in local machine inside a web application. In this post, let’s see how we can use the WebForms ReportViewer control inside a AngularJS Single Page Application to render reports deployed in SSRS Report Server.

I have a SPA running with Visual Studio created using one of my previous posts “Creating an Empty ASP.NET Project Powered by AngularJS using Visual Studio”. And I have set of reports deployed to SSRS Report Server already (Here I am not going to explain how you can deploy reports to SSRS Report Server). Let’s extend the the same application to include the ReportViewer inside Home page. My plan is to add a ReportViewer control inside ASP.NET Web Form and embed that particular page inside home page using an iframe.

So for that let’s start by adding a WebForm to the project. In my example, I am creating a folder inside App folder named pages and adding the WebForm there and naming it as Viewer.aspx.
image
Add WebForm
Now on the designer of Viewer.aspx, I am adding a ReportViewer control, from the Visual Studio Toolbox.
image
ReportViewer
So this is how my Viewer.aspx looks like after adding the ReportViewer.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Viewer.aspx.cs" Inherits="AngularJSSample.App.pages.Viewer" %> 

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=12.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>
 
        <div style="height: 600px;">
            <rsweb:ReportViewer ID="reportViewer" runat="server" Width="100%" Height="100%"></rsweb:ReportViewer>
        </div>
    </form>
</body>
</html>
I have done some styling here, you can also apply whatever the styles you need. Also I have added a script manager, because the ReportViewer web control requires a script manager on the web form. Now inside the Viewer.aspx code behind, we need to add the code for processing the report.
public partial class Viewer : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (!IsPostBack)
            {
                string reportId = Request.QueryString["id"].ToString();
 

                string reportServerUrl = ConfigurationManager.AppSettings["ReportServerURL"];
                string domain = ConfigurationManager.AppSettings["rsDomain"];
                string userName = ConfigurationManager.AppSettings["rsUserName"];
                string password = ConfigurationManager.AppSettings["rsPassword"];
                string reportPath = ConfigurationManager.AppSettings["ReportPath"];
 
                reportViewer.ServerReport.ReportServerUrl = new Uri(reportServerUrl);
                reportViewer.ServerReport.ReportServerCredentials = new ReportCredentials(userName, password, domain);
                reportViewer.ServerReport.ReportPath = string.Format(reportPath, reportId);
                reportViewer.ProcessingMode = ProcessingMode.Remote;
                reportViewer.ShowCredentialPrompts = false;
                reportViewer.ServerReport.Refresh();
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}
Here the page will receive a URL parameter through a query string which specifies the report id to render. Basically the report id would be the report name which is deployed to SSRS Report Server. And I am retrieving following values from the web.config file.
<appSettings>
    <add key="ReportServerUrl" value="REPORT_SERVER_URL" /> <!--http://localhost/ReportServer etc.-->
    <add key="rsDomain" value="DOMAIN" />
    <add key="rsUserName" value="USERNAME" />
    <add key="rsPassword" value="PASSWORD" />
    <add key="ReportPath" value="/PATH_TO_REPORT_FOLDER/{0}" />
</appSettings>
You can find your ReportServerUrl from the SQL Server Reporting Services Configuration Manager.

image
SQL Server Reporting Services Configuration Manager
For ReportServerCredentials, I have the following the class ReportCredentials which is an  implementation of IReportServerCredentials.
public class ReportCredentials : IReportServerCredentials
{
    public ReportCredentials(string userName, string password, string domain)
    {
        UserName = userName;
        Password = password;
        Domain = domain;
    }
 
    public WindowsIdentity ImpersonationUser
    {
        get
        {
            return null;
        }
    }

    public ICredentials NetworkCredentials
    {
        get
        {
            return new NetworkCredential(UserName, Password, Domain);
        }
    }
 
    private string UserName { get; set; }
    private string Password { get; set; }
    private string Domain { get; set; }
 
    public bool GetFormsCredentials(out Cookie authCookie, out string userName, out string password, out string authority)
    {
        authCookie = null;
        userName = password = authority = null;
        return false;
    }
}
Here since I am not using any impersonation, ImpersonationUser is null and since I am not using forms authentication GetFormsCredentials method is returning false.

Next step is to show the list of reports and embed the Viewer.aspx web form inside the html page.
<div>
    <div ng-repeat="report in reports">
        <input type="button" ng-click="openReport(report.id)" value="{{report.name}}" />
    </div>
    
    <form class="form-horizontal" style="height: 100%;">
        <div style=" height:100%; background-color: transparent">
            <iframe src="{{'http://localhost:29986/App/pages/Viewer.aspx?id=' + reportId | trustAsResourceUrl}}" style="width:100%;height:600px"></iframe>
        </div>
    </form>
</div>
Here I have set of reports where I am ng-repeating and when clicking on an item, I am invoking the openReport method. Basically it will assign the report id to the model variable which I am passing as a query parameter to Viewer.aspx. On click on report items, it will make the iframe source different, which will load the particular report.
.controller('homeController', function ($scope) {
    $scope.message = "Now viewing home!";
 
    $scope.reports = [
       {
           id: 'Report1',
           name: 'Report 1'
       },
       {
           id: 'Report2',
           name: 'Report 2'
       },
    ]; 

    $scope.reportId = $scope.reports[0].id; 

    $scope.openReport = function (reportId)
    {
        $scope.reportId = reportId;
    }
})
Even you can get the available reports by calling the SSRS Report Server Web Service.

I have the following directive as well, to make Strict Contextual Escaping for the web forms absolute url.
.filter('trustAsResourceUrl', ['$sce', function ($sce) {
    return function (val) {
        return $sce.trustAsResourceUrl(val);
    };
}]);

So that’s it. Now let’s run the application.
image
Report 1
image
Report 2
I am uploading the full code sample to my OneDrive. Do check that out!


Happy Coding.

Regards,
Jaliya