#18 SQL Reporting Viewer on page passing credentials

This code presents a report viewer on an asp.net page and allows you to call a class that encapsulates the needed calls to get & pass credentials, report path, report name and parms to the SQL Reporting Server. The more static parameters like report server and authenticating ID & password are read in from the web.config file.

asp.net page code holding ReportViewer control:

<%@ Register Assembly=”Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”
Namespace=”Microsoft.Reporting.WebForms” TagPrefix=”rsweb” %>

<rsweb:ReportViewer ID=”ReportViewer1″ BackColor=”#efefef” runat=”server” Width=”100%” Height=”800px”>
</rsweb:ReportViewer>

asp.net.cs code

protected void launchReport()
{
SRS _srs = new SRS();
List<ReportParameter> paramList = new List<ReportParameter>();
paramList.Add(new ReportParameter("Username", "***", false));
paramList.Add(new ReportParameter("ClientID", "****", false));
_srs.launchReportInViewer(ref ReportViewer1, "ReportFolder", "ReportName", paramList);

}

SRS class used to wrap the calls to the report server. This will require you add a reference to Microsoft.ReportViewer.WebForms.dll in the GAC or add into your /Bin dir. (check C:\Program Files\Microsoft Visual Studio 8\ReportViewer to locate the dlls if not installed in the GAC)

using System;
using System.Configuration;
using Microsoft.Reporting.WebForms;
using System.Collections.Generic;

/// <summary>
/// Summary description for SQL Reporting Viewer class
/// </summary>
public class SRS
{
public SRS ()
{

}

public void launchReportInViewer(ref ReportViewer RV, string sFolder, string sReportName, List<ReportParameter> paramList)
{
RV.ServerReport.ReportServerCredentials   = new ReportViewerCredentials();
RV.ServerReport.ReportServerUrl           = new Uri(ConfigurationSettings.AppSettings["ReportServer"].ToString());
RV.ServerReport.ReportPath                = @"/" + sFolder + @"/" + sReportName;
RV.ServerReport.SetParameters(paramList);
RV.ShowRefreshButton                      = true;
RV.ShowPageNavigationControls             = true;
RV.ShowToolBar                            = true;
RV.Visible                                = true;
RV.ProcessingMode                         = ProcessingMode.Remote;
}

}

Credit due for ReportServerCredentials class beloworiginal link This class was converted to C# and slightly modified. Please note the GetFormsCredentials method has to be implemented to meet the interface definition but is stubbed out.

ReportViewerCredentials class:
using Microsoft.Reporting.WebForms;
using System.Net.Security;
using System.Net;
using System.Configuration;

public class ReportViewerCredentials : IReportServerCredentials
{
private string _userName;
private string _password;
private string _domain;

public ReportViewerCredentials()
{
_userName   = ConfigurationSettings.AppSettings["ReportViewerUser"].ToString();
_password   = ConfigurationSettings.AppSettings["ReportViewerPassword"].ToString();
_domain     = ConfigurationSettings.AppSettings["ReportViewerDomain"].ToString();
}

public ReportViewerCredentials(string userName, string password, string domain)
{
_userName = userName;
_password = password;
_domain = domain;
}

public System.Security.Principal.WindowsIdentity ImpersonationUser
{
get { return null; }
}

public System.Net.ICredentials NetworkCredentials
{
get { return new NetworkCredential(_userName, _password, _domain); }
}

/// <summary>
/// Not Used
/// </summary>
public bool GetFormsCredentials(out System.Net.Cookie authCookie, out string userName, out string password, out string authority)
{
userName = _userName;
password = _password;
authority = _domain;
authCookie = null;
return false;
}

}

web.config entries:

<appSettings>
<add key="ReportViewerUser" value="***"/>
<add key="ReportViewerPassword" value="***"/>
<add key="ReportViewerDomain" value="***"/>
<add key="ReportServer"  value="***"/>
</appSettings>

#03 SQL Reporting Link Rendering A Report as a PDF

Once you create a SQL Reporting Services report and deploy (or upload the .rdl and .rds files) you can reference the report via a link and render it as a pdf (or Excel) via the following pattern (assumes no ssl)

http://ServerName/ReportServer?%2fFolderName%2fReportName&parm1=value&rs%3aCommand=Render&rs%3AFormat=PDF

Variables (note ‘ReportServer’ is a constant)

• ServerName – Server housing SQL Reporting Services
• FolderName – Typically the name of your Visual Studio Project
• ReportName – name of the .rdl file defining your report
• parm1=value  (ignore if not passing parms) You can add as many as you want like &parm2=value2…)

Note: The last phrase tells the report server to render as a pdf: 3aCommand=Render&rs%3AFormat=PDF You can change that last parm (Format=) to Excel from PDF to render a spreadsheet (assuming Excel is installed on your client PC)

This link can be placed in an ASP.net page or user control and redirected via the Response.Redirect command (often fired via a button click event/delegate) using string concatenation to add in any variables like a date range etc.  You could also use a Sharepoint webpart connected to some filter fields controlling any parameters sent just like a ASP.net page or user control. Another idea is that the link can be added as a static link on the Dashboard in Vision.