Report Service 2008 R2 - Delivery Extension User Control

• Download source files - 117.1 KB

Introduction

Creation of a reporting service custom delivery extension is straightforward and there are many examples available over the Internet for reference (Print Delivery, FTP Delivery on CodePlex). But all of them create a custom server control if you want to take user input (like Rendering Format, Location, etc.) during subscription creation. Since creating a server control is both time consuming and tedious, I will explain in this article how to add a user control (.ascx) instead. A user control is relatively easy to create, debug, test, and format.

Background

For those who want to understand what a Delivery Extension is, please go through the following MSDN articles:

• Preparing to Implement a Delivery Extension
• Implementing a Delivery Extension

Basically, to create a delivery extension, you need to implement three Interfaces: ISubscriptionBaseUIUserControl, IDeliveryExtension, and IExtension. If you want to implement a non UI extension, IDeliveryExtension and IExtension are the required Interfaces. But if you want to capture some information from the user, you also have to implement ISubscriptionBaseUIUserControl. Apart from that, the class should inherit from the System.Web.UI.WebControls.WebControl class.

Using the Code

The attached solution has two projects:

1. Extensions - The main project with all the required class files and user control. My custom delivery extension saves the report to the physical file location, then emails the rendered report and after that, FTPs the same report to the FTP server. The attached code has delivery to email and FTP commented out. You can un-comment it if you want that the provided reporting service has all the required configuration like FTP server, User Name, Password, Email Server, Sender and Email To, etc.
2. UI Test - This is the test project to test if the user control is working fine in a normal ASPX page.

The .NET Framework for both the projects is 3.5. Do not use 4.0. That is not supported by SQL Server Reporting Services 2008 R2. The first DLL (Extensions) needs to be a strong assembly. Sign in the assembly and extract the Public Key and Public Blob from the .snk file. To extract the public key, open Visual Studio command prompt and use Strong Name Tool (SN.exe).

sn -p [path of snk file created while signing the assembly] [path of new public.snk file to be created]

sn -t [path of the public snk file created above]

Copy the public key blob and save it in a text file. It will be used later on to deploy the extension on the reporting service.

Deploying the Delivery Extension

We just need to deploy only one DLL (Extensions) to the report server. The other DLL is only for testing, so just ignore it.

1. Copy Extensions.dll to C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin and C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Bin
2. Copy the Custom User Control ExtensionUserControl.ascx to the C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Pages folder.

Configure Delivery Extension

Note: Before modifying the config file, create the copy of the original config file so that it can be reverted if required.

Open the rsreportserver.config file from C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config and add a new extension under Extensions -> Delivery and under Extension -> Delivery UI.

<Extension Name="FTP Email FileShare" Type="Extensions.FtpDeliveryExtension,Extensions">
    <MaxRetries>3</MaxRetries>
    <SecondsBeforeRetry>900</SecondsBeforeRetry>
    <Configuration>
        <RSFTPEmailFileShareConfig>
            <ExportPath>E:\ReportSubscription</ExportPath>
            <FTPServer></FTPServer>
            <FTPPort></FTPPort>
            <FTPUserName></FTPUserName>
            <FTPPassword></FTPPassword>
            <FTPDefaultFolder>CCX_Report</FTPDefaultFolder>
            <SMTPServer></SMTPServer>
            <SMTPSendUsing></SMTPSendUsing>
            <SMTPToAddress></SMTPToAddress>
        </RSFTPEmailFileShareConfig>
    </Configuration>
</Extension>

Also at both locations, provide your FTP server, SMTP server, or SMTP Send using and SMTP To address details if you want to test the delivery extension with Mail and FTP delivery. Otherwise, leave it like that.

Open the rsmgrpolicy.config file from C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\rsmgrpolicy.config and the rsservpolicy.config file from C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rssrvpolicy.config.

Add a new Codegroup element at the end with the following details to give FULL trust to your assembly:

<CodeGroup
	class="UnionCodeGroup"
	version="1"
	PermissionSetName="FullTrust"
	Name="Custom_FTPDeliveryExtension_Strong_Name"
	Description="This code group grants FTP Delivery full trust. ">
  <IMembershipCondition
	class="StrongNameMembershipCondition"
	version="1"
	PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100253fe4877d545619e033
	  2231a9527fdf8fc023f1f52e5956ac8254cc29c91323db7b0c493dff2386879f6f081d3c8b836b916953a906d938629100712f37
	  595f081f1709c1946e3c7a2800303e963ece5228f3f134175fc5155b8bc4ff933dc167c75d7491e656109ee183d13da8c92f05c244b8647971593354c09d7c218fab"
                            />
</CodeGroup>

Here, PublicKeyBlob is the public key extracted use SN tool above.

Testing

This is how my custom deliver extension looks like on creating a new subscription.

Implementation

The Custom Delivery Class Definition

public class FtpDeliveryExtension : WebControl, 
    ISubscriptionBaseUIUserControl, IDeliveryExtension, IExtension
{
}

Here, our custom delivery class name is FTPDeliveryExtension and it implements three Interfaces and inherits from WebControl.

ISubscriptionBaseUIUserControl and WebControl Implementation

The FTPDeliveryExtension inherits the WebControl class. The RenderUIControls method is called during control initialization and loads ExtensionUserControl.ascx from the report manager's pages folder. After the control is loaded, it is added to the Controls collection for rendering.

public FtpDeliveryExtension()
{
    base.Init += new EventHandler(FtpDeliveryExtension_Init);
}

void FtpDeliveryExtension_Init(object sender, EventArgs e)
{
    RenderUIControls();
}

private void RenderUIControls()
{
    userControl = (ExtensionUserControl)this.Page.LoadControl("ExtensionUserControl.ascx");            
    Controls.Add(userControl);
}

The user data property of the ISubscriptionBaseUIUserControl interface is used by report server to get the information that will be filled by the user in the custom form. For example, in our case, Folder Path and Render Format are two pieces of information that we want the user to provide while creating the subscription. The UserData property gets/sets the user provided information in the report server during create/edit of the report subscription.

public Setting[] UserData
{
    get
    {
        SubscriptionSettingsData subscriptionData = new SubscriptionSettingsData();
        subscriptionData.WindowFolderLocation = userControl.FolderLocation;
        subscriptionData.RenderFormat = userControl.SelectedRenderFormat;
        subscriptionData.RenderExtension = userControl.RenderFormatExtension;
        return subscriptionData.ToSettingArray();
    }
    set
    {
        SubscriptionSettingsData subscriptionData = new SubscriptionSettingsData();
        subscriptionData.SetFromSettings(value);
    }
}

Here, the custom SubscriptionSettingData class is created that is used to return the settings array and to save the settings value. User control properties like FolderLocation, SelectedRenderFormat have the input submitted by the user. The implementation of this class is there in the attached code.

IExtension Interface Implementation

That is pretty straight forward. Localized Name is the name of the extension. In this case it is "FTP Email FileShare". The SetConfiguration method reads the configuration section (RSFTPEmailFileShareConfig) created above from the RSReportServer.Config file:

#region IExtension Implementation and Common
public string LocalizedName
{
    get { return _localizedName; }
}

public void SetConfiguration(string configuration)
{
    try
    {
        XmlDocument doc = new XmlDocument();
        Common.LoadXMLConfiguration(doc, configuration);
        if (doc.DocumentElement.Name == "RSFTPEmailFileShareConfig")
        {
            foreach (XmlNode node in doc.DocumentElement.ChildNodes)
            {
                string str;
                if (((str = node.Name) != null) && (str == "ExportPath"))
                {
                    _exportPath = node.InnerText;
                }
                if (((str = node.Name) != null) && (str == "FTPServer"))
                {
                    _ftpServer = node.InnerText;
                }
                if (((str = node.Name) != null) && (str == "FTPPort"))
                {
                    _ftpPort = node.InnerText;
                }
                if (((str = node.Name) != null) && (str == "FTPUserName"))
                {
                    _ftpUserName = node.InnerText;
                }
                if (((str = node.Name) != null) && (str == "FTPPassword"))
                {
                    _ftpPassword = node.InnerText;
                }
                if (((str = node.Name) != null) && (str == "FTPDefaultFolder"))
                {
                    _ftpDefaultFolder = node.InnerText;
                }
                if (((str = node.Name) != null) && (str == "SMTPServer"))
                {
                    _smtpServer = node.InnerText;
                }
                if (((str = node.Name) != null) && (str == "SMTPSendUsing"))
                {
                    _smtpSendUsing = node.InnerText;
                }
                if (((str = node.Name) != null) && (str == "SMTPToAddress"))
                {
                    _smtpToAddress = node.InnerText;
                }
            }
        }
    }
    catch (Exception exception)
    {
        throw new ApplicationException("Error reading RS Configuration", exception);               
    }
    finally
    {
    }
}
#endregion

IDeliveryExtension Interface Implementation

There are multiple properties and methods, but the most important is the Deliver method. This method actually delivers the report to the user based on the contents of the notification.

public bool Deliver(Notification notification)
{
    notification.Retry = false;
    notification.Status = "Processing...";
    try
    {
        Setting[] userData = notification.UserData;
        SubscriptionSettingsData data = new SubscriptionSettingsData();
        data.SetFromSettings(notification.UserData);
        DeliverToRequiredLocation(notification, data);
        notification.Status = "Success";
    }
    catch (Exception e)
    {
        notification.Status = "Error: " + e.Message;
        //notification.Retry = true;
    }
    finally
    {
        notification.Save();
    }
    return notification.Retry;
}

private void DeliverToRequiredLocation(Notification notification, SubscriptionSettingsData settings)
{
    char qualfier = '"';
    char delim = ',';
    string renderFormat = string.IsNullOrEmpty(settings.RenderFormat) || 
      settings.RenderFormat.Equals("Text") ? "CSV" : settings.RenderFormat;
    string fileExtension = string.IsNullOrEmpty(settings.RenderExtension) ? 
                           ".Csv" : settings.RenderExtension;
    //string deviceInfo = string.Format("<DeviceInfo><FieldDelimiter>{0}<" + 
      "/FieldDelimiter><Qualifier>{1}</Qualifier></DeviceInfo>", delim, qualfier);
    RenderedOutputFile _outputFile = notification.Report.Render(renderFormat, null)[0];

    //Render Report to Export Path First
    string fileName = DateTime.Now.ToString("dd-MM-yy-HH:mm") + "_" + notification.Report.Name ;
    fileName = Common.GetValidFileName(fileName) + fileExtension;
    string filePath = "";
    if (string.IsNullOrEmpty(settings.WindowFolderLocation))
    {
        filePath = Path.Combine(_exportPath, fileName);
    }
    else
    {
        filePath = Path.Combine(settings.WindowFolderLocation, fileName);
    }
    _outputFile.Data.Seek(0L, SeekOrigin.Begin);
    byte[] arr = new byte[((int)_outputFile.Data.Length) + 1];
    _outputFile.Data.Read(arr, 0, (int)_outputFile.Data.Length);
    MemoryStream stream = new MemoryStream(arr);
    File.WriteAllBytes(filePath, arr);
    
    //For now do not deliver it anywhere
    //SendMail(notification, stream, fileName);            
    //FtpFileToServer(settings, fileName, filePath);
}

Here, report is rendered based on the user provided format and folder location is saved during subscription creation.

ExtensionUserControl Implementation

This is the simplest among all. Just create a user control in a separate project, test it, and add it to your delivery extension class library project. Keep the namespace same for simplicity.

 <%@ Control Language="C#" AutoEventWireup="true" CodeBehind="ExtensionUserControl.ascx.cs"
    Inherits="Chenoa.CCX.Extensions.ExtensionUserControl" %>
<table>
    <tr>
        <td>
            <asp:Label ID="lblWindowsFolder" runat="server" Text="Folder Path: "></asp:Label>
        </td>
        <td>
            <asp:TextBox ID="txtWindowsFolder" runat="server" Width="300px"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            <asp:Label ID="lblRenderFormat" runat="server" Text="Render Format: "></asp:Label>
        </td>
        <td>
            <asp:DropDownList ID="dropDownRenderFormat" runat="server">
            <asp:ListItem Text="CSV" Value=".Csv" Selected="True"></asp:ListItem>
            <asp:ListItem Text="Excel" Value=".Xls"></asp:ListItem>
            <asp:ListItem Text="PDF" Value=".Pdf"></asp:ListItem>
            <asp:ListItem Text="Text" Value=".Txt"></asp:ListItem>
            </asp:DropDownList>
        </td>
    </tr>    
</table>

ExtensionUserControl.ascx.cs code:

public partial class ExtensionUserControl : System.Web.UI.UserControl
{
    public string FolderLocation
    {
        get { return txtWindowsFolder.Text.Trim(); }
        set { txtWindowsFolder.Text = value; }
    }

    public string SelectedRenderFormat
    {
        get { return dropDownRenderFormat.SelectedItem.Text; }
        set { dropDownRenderFormat.Items.FindByText(value).Selected = true; }
    }

    public string RenderFormatExtension
    {
        get { return dropDownRenderFormat.SelectedItem.Value; }
    }

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void btnClick_Click(object sender, EventArgs e)
    {
        lblMessage.Text = string.Format("Folder Path is {0}", txtWindowsFolder.Text);
    }
}

Phew!!.. That's it.

Conclusion

It may look like too much, but believe me, it is easier than creating a server control where each and every table/row/cell, etc., has to be created. This is a simple user control but can become complex if you want to collect device information based on the report format.

The attached code has the working delivery extension and the user control. The sample config file is also there for your reference. Try it out and let me know if it can be improved further. Meanwhile, I will work on it as a full fledged project. Till now, it was just a POC.