Friday, November 18, 2011

How to embed an SSRS report in ASP.Net 4.0

So, you’ve made this snazzy report that you would like to embed into your website.  There are many options when you choose to do this with Report Viewer (The default control for rendering SSRS).
The scope of this article is to explain how you can add a report to your site.  It assumes you already know how to use BIDS to create a report and deploy it to the server.  At this point we have the report deployed and can view it through the reportserver URL.  This assures us that the report, its DataSet, Query Parameters, and generally functionality of the report are working correctly.
We need to start in the web.config.  This is where we need to define the assembly for the report viewer, the handler, and our URL to the report server.  In this example, I’ll add the report URL parameters to the AppSettings section of the ConfigSection.
Web.config
<add key="urlReportServer" value="http://www.someserver.com/ReportServer"/>
<add key="rptReportName" value="/Folder/ReportName"/>
<add key="proxyDomainName" value="Domain"/>
<add key="proxyUserName" value="someUserName"/>
<add key="proxyPwd" value="somePassword"/>

We will use these setting while we are trying to generate the report on the ASP.Net side.
urlReportServer: The general URL to the report server (Typical structure used).
rptReportName: It follows the structure of Folders then Report Name.  So if I had a weeklySums report in the Sales folder it would be /Sales/weeklySums
proxyDomainName: This is the domain (workgroup) name that the SSRS server resides in.  Used for authentication purposes.
proxyUserName: A username that has privledges to generate the report.
proxyPwd: The password for the above named user.


After setting these keys, we need to let ASP.Net know what assembly to use.  We can omit some parameters from the statement, given after the example:
<add assembly="Microsoft.ReportViewer.WebForms, 
Version=10.0.0.0, Culture=neutral, 
PublicKeyToken=B03F5F7F11D50A3A"/>
<add assembly="Microsoft.ReportViewer.Common, 
Version=10.0.0.0, 
Culture=neutral, 
PublicKeyToken=B03F5F7F11D50A3A"/>
Ok, here’s the deal.  You can omit the Version, Culture, and PublicKeyToken parameters if you do not know them.  What happens when it goes looking for the assembly is that if the Version is not defined, it will use the first Version that it comes across.  This is true for all assemblies.  So if you are trying to use a feature in 9.0 but do not define it and it finds 8.0 first, you will end on a runtime error and beat your head against the wall for the rest of the night.
The last change we need to make to the web.config is in the httpHandler or Handlers section (depending on what IIS version you are using).
<httpHandlers>
  <add path="Reserved.ReportViewerWebControl.axd" verb="*"
    type="Microsoft.Reporting.WebForms.HttpHandler, 
    Microsoft.ReportViewer.WebForms, Version=10.0.0.0, 
    Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" validate="false"
  />
</httpHandlers>
**** And/Or ****
<handlers>
  <add name="ReportViewerWebControlHandler"
    preCondition="integratedMode"
    verb="*" path="Reserved.ReportViewerWebControl.axd"
    type="Microsoft.Reporting.WebForms.HttpHandler, 
    Microsoft.ReportViewer.WebForms, Version=10.0.0.0, 
    Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
  />
</handlers>
This completes our tasks for the web.config.  Now we need to move into the .aspx (html) file. 
The web form (.aspx)
Our first order of business is to add the reference to the ReportView assembly (If you drag and drop the control from the toolbox, it should add the reference automatically).
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, 
Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
    Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
It should be noted that this reference will be added when you drag the control from the toolbox onto the webform. 
From there we need to add the Script Manager.  Without the script manager, the report control will tell you that you need to add it to the form in order to use the Report Viewer control.  Essentially you will want to add it, for the fact that it will truncate your scripts and Report Viewer uses a lot of scripts to generate the reports.
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="AjaxToolkit" %>


There is just one last item of business to attend to when it comes to the web form.  That is to simply add the report viewer control.  In the toolbox it is located under the tab Reports.
<rsweb:ReportViewer ID="rvManagement" runat="server" Visible="false" 
  ProcessingMode="remote" Width="100%" SizeToReportContent="True" 
  ShowParameterPrompts="false">
</rsweb:ReportViewer>
This needs a little explanation.  I turn visibility off for the report initially because I generate my own report parameters.  Report parameters are those parameters that exist at the top of the report when looking at the report in BIDS.  Using drop downs and generating content through stored procedures, I create my own parameters and thus, can turn off the ShowParameterPrompts at the top of the report.  This makes the report seem to embed into the website a lot better (in my humble opinion).
The code that accompanies this article takes into account that the report parameters have been turned off.  Thus if you are using this code on your own website, you will need to create drop downs and populate them using the same stored procedures as with the report (thus you get the same data for the parameters).
ProcessingMode refers to whether the SQL server is physically located on the same machine (or virtual machine) as the web server.  In most every case this will be “remote”.  However, the account you have setup (in the web.config proxyUsername) must have remote privileges to the report server.
SizeToReportContent parameter tries to allow the report viewer to create equal length pages for the report.  So if you have a report with 36 records, it will divide the report into 6 rows of 6 pages (NOTE: This example is used to explain the behavior of the report viewer parameter – It will not exactly mimic this functionality if there are 36 rows.  I used a small number to explain the functionality.  With 36 rows it would simply display either 13 rows or the entire 36 rows).  You lose control over the height of the report however, it is better for formatting purposes.  Otherwise set this property to false and define a height for the report.


The .aspx.vb (code behind)
The first order of business is because we have set the visibility of the report to false, we need to a trigger to render the report.  On the .aspx page (under the parameter drop downs) there needs to be a button.  Usually in the text parameter, I have “Generate Report”.  So now we need to define the event in the code behind.
Protected Sub btnGenerateReport_Click(ByVal sender As Object
        ByVal e As System.EventArgsHandles btnGenerateReport.Click
  Dim secNum As New Microsoft.Reporting.WebForms.ReportParameter( & _
        "SectionNum", ddlSectionNumber.SelectedValue.ToString())
  Dim secName As New Microsoft.Reporting.WebForms.ReportParameter( & _
        "SecName", ddlSectionName.SelectedValue.ToString())
  Dim policyName As New Microsoft.Reporting.WebForms.ReportParameter( & _
        "policy_nm", ddlPolicy.SelectedValue.ToString())
  Dim riskRank As New Microsoft.Reporting.WebForms.ReportParameter( & _
        "risk_rank", ddlRiskRank.SelectedValue.ToString())
  Dim actionOwner As New Microsoft.Reporting.WebForms.ReportParameter( & _
        "a_owner", ddlActionOwner.SelectedValue.ToString())
  Dim deadline As New Microsoft.Reporting.WebForms.ReportParameter( & _
        "deadline_date", ddlDeadlineDate.SelectedValue.ToString())
  Dim colRP() As Microsoft.Reporting.WebForms.ReportParameter = {secNum, & _
        secName, policyName, riskRank, actionOwner, deadline}
  Dim credProxy As Microsoft.Reporting.WebForms.IReportServerCredentials = & _
        New ManagementReportCredentials()
  rvManagement.ServerReport.ReportServerCredentials = credProxy
  rvManagement.ServerReport.ReportServerUrl = New & _
        Uri(ConfigurationManager.AppSettings("urlReportServer"))
  rvManagement.ServerReport.ReportPath = & _
        ConfigurationManager.AppSettings("rptReportName ")
  rvManagement.ServerReport.SetParameters(colRP)
  rvManagement.ServerReport.Refresh()
  rvManagement.Visible = True
  End Sub
Ok to explain the method.  First I am Dimensioning Query Parameters for the report.  This will feed into the report and generate the report based on those parameters.  Basically, if your SP calls for parameters, they need to be drop downs on the web form and passed through the report viewer as a parameter.
Then we create our IReportServerCredentials based on a class we are going to write a little further down in the article.  We get the server name and report name from the web.config.  We then feed it the parameters as an array (here it is named colRP for column report parameters). We refresh the report to show the queried result with the updated parameters and then make the report visible.
Now comes the big challenge (just kidding – actually it’s easy) to supply our credentials.
In the same code behind, right under the End Class statement we will create another class that will provide our credentials based on what type of authentication we are using (Forms, Windows … etc…)
Public Class ManagementReportCredentials
    Implements Microsoft.Reporting.WebForms.IReportServerCredentials
    Private strUserName As String
    Private strPassWord As String
    Private strDomainName As String
    Public Sub New(ByVal UserName As StringByVal PassWord As StringByVal DomainName As String)
        strUserName = UserName
        strPassWord = PassWord
        strDomainName = DomainName
    End Sub
    Public Sub New()
        strUserName = ConfigurationManager.AppSettings.Get("proxyUserName")
        strPassWord = ConfigurationManager.AppSettings.Get("proxyPwd")
        strDomainName = ConfigurationManager.AppSettings.Get("proxyDomainName")
    End Sub
    Public ReadOnly Property ImpersonationUser() As System.Security.Principal.WindowsIdentity Implements Microsoft.Reporting.WebForms.IReportServerCredentials.ImpersonationUser
        Get
            Return Nothing
        End Get
    End Property
    Public ReadOnly Property NetworkCredentials() As System.Net.ICredentials Implements Microsoft.Reporting.WebForms.IReportServerCredentials.NetworkCredentials
        Get
            Return New System.Net.NetworkCredential(strUserName, strPassWord, strDomainName)
        End Get
    End Property
    Public Function GetFormsCredentials(ByRef authCookie As System.Net.CookieByRef userName As StringByRef password As StringByRef authority As StringAs Boolean Implements Microsoft.Reporting.WebForms.IReportServerCredentials.GetFormsCredentials
        authCookie = Nothing
        password = authority = Nothing
        Return False
    End Function
End Class
We Impletement the class with a reference to IReportServerCredentials.  If we are using a forms based authentication then we would use the Sub New that accepts parameters.  In this case we are using the AppSettings in the web.config.  Otherwise, for Windows authentication, we could use the NetworkCredentials method.  Depending on what authentication you are using determines which method you wish to use.  For simplicity, we are using the AppSettings in the New method.


Now that we have it setup, we can run it in debug and it should generate the report once we click our trigger button.


Happy .Netting - Saleh

3 comments:

  1. Chad - I know this is an older post but I just wanted to let you know it was extremely helpful to me this past week. So... just saying thanks for taking the time to put this walkthrough together. You saved me a ton of trouble.

    ReplyDelete
  2. What needs to be done to use the parameter controls rendered by the ReportViewer? I have many reports and don't want to build a page for each one. I've tried setting ShowParameterPrompts="true" and can see the controls however when I click the View Report button, the report does not get rendered - it appears that the ReportViewer isn't even sending the values I entered to the ReportServer.

    ReplyDelete