Using CreateSubscription and the Reporting Service API

In our last Reporting Services article we used the ListChildren method to display a tree list of reports available to a user. In this article we will tackle the CreateSubscription method, which requires substantially more code to get running. There are more parameters to pass, and the parameter types are more complex. In addition, the extensibility of Reporting Services means you’ll need to dig a little harder to come out with the correct incantation for the delivery provider parameters.

This article assumes a web reference to the Reporting Services Web API exists. If you need assistance setting up the reference refer to the text and resources in the previous article.

CreateSubscription points to a single report in the report server database.  The report server will execute and then deliver the report based on settings you provide. Delivery mechanisms shipped with the RTM version include e-mail delivery, and file share delivery.

Let’s take a look at the CreateSubscription method and walk through creating each parameter.

public string CreateSubscription(
   string Report,
   [Namespace].ExtensionSettings ExtensionSettings,
   string Description,
   string EventType,
   string MatchData,
   [Namespace].ParameterValue[] Parameters
);

The first parameter is an easy: just pass the full path and report name for the report you want to subscribe to, i.e.

@"/SampleReports/Company Sales"

Extension Settings

Each delivery extension will require a set of name-value pairs as parameters. Some parameters are required, others are optional. It is possible to uncover the exact parameters required by each extension programmatically using the web service method GettExtensionSettings.

<asp:DropDownList id="ddlExtensions" runat="server">
    <asp:ListItem Value="Report Server Fileshare" Selected="True">
        Report Server Fileshare
    </asp:ListItem>
    <asp:ListItem Value="Report Server Email">
      Report Server Email
    </asp:ListItem>
</asp:DropDownList>

<asp:Button id="btnDumpExtensionSettings" runat="server" Text="Dump Extension Settings">
</asp:Button>

<table cellpadding="2" border="2" cellspacing="0">
    <asp:Repeater id="rpSettings" runat="server">
        <HeaderTemplate>
            <tr bgcolor="#cccc99">
                <td><b>Name</b></td>
                <td><b>Required</b></td>
                <td><b>Encrypted</b></td>
                <td><b>Valid Values</b></td>
            </tr>
	</HeaderTemplate>
	
        <ItemTemplate>
	     <tr bgcolor="#F0F0F0">
                 <td><%# ((ExtensionParameter)Container.DataItem).Name %>
                 </td>
                 <td><%# ((ExtensionParameter)Container.DataItem).Required.ToString() %>
                 </td>
                 <td><%# ((ExtensionParameter)Container.DataItem).Encrypted.ToString() %>
                 </td>
                 <td><%# DumpValidValues(((ExtensionParameter)Container.DataItem).ValidValues) %>
                 </td>
	     </tr>
	</ItemTemplate>
    </asp:Repeater>
</table>

In the code behind:

private void btnDumpExtensionSettings_Click(object sender, System.EventArgs e)
{
    ReportingService service = new ReportingService();
    service.Credentials = System.Net.CredentialCache.DefaultCredentials;
    
    ExtensionParameter[] extensionParameters;
    extensionParameters = service.GetExtensionSettings(ddlExtensions.SelectedValue);

    rpSettings.DataSource = extensionParameters;
    rpSettings.DataBind();          
}

protected string DumpValidValues(ValidValue[] validValues)
{
    string result = " ";

    if(validValues != null)
    {
        StringBuilder sb = new StringBuilder();

        for(int i = 0; i < validValues.Length; i++)
        {
            sb.Append(validValues[i].Value);
            if(i < validValues.Length - 1)
            {
                sb.Append(", ");
            }
        }           
        result = sb.ToString();
    }

    return result;
}               

The delivery email extension should produce the following table of values.

To see how to put these to use, let’s take a look at the method to setup the extension parameters for creating a file share delivery.

private ExtensionSettings GetExtensionSettings()
{
    ParameterValue[] extensionParams = new ParameterValue[7];

    for(int i = 0; i < extensionParams.Length; i++)
        extensionParams[i] = new ParameterValue();

    extensionParams[0].Name = "FILENAME";
    extensionParams[0].Value = "sales";

    extensionParams[1].Name = "FILEEXTN";
    extensionParams[1].Value = "true";

    extensionParams[2].Name = "PATH";
    extensionParams[2].Value = @"\\REPORTING\Reports";

    extensionParams[3].Name = "RENDER_FORMAT";
    extensionParams[3].Value = "HTML4.0";

    extensionParams[4].Name = "WRITEMODE";
    extensionParams[4].Value = "AutoIncrement";

    extensionParams[5].Name = "USERNAME";
    extensionParams[5].Value = USERNAME;

    extensionParams[6].Name = "PASSWORD";
    extensionParams[6].Value = PASSWORD;

    ExtensionSettings extensionSettings = new ExtensionSettings(); 
    extensionSettings.Extension = EXTENSION_FILESHARE;
    extensionSettings.ParameterValues = extensionParams;

    return extensionSettings;
}

In the above code, the username and password for the delivery extension are the credentials the server will use to access the network resource. You can find documentation on the rest of the parameters in the Reporting Services books online under Reporting Services Delivery Extension Settings.

EventType and MatchData

The contents of the MatchData parameter entirely depend on the EventType parameters. For SnapshotUpdated subscriptions the value will be null (empty). For timed subscriptions the contents of MatchData will contain an XML representation of a ScheduleDefinition object (unless you are using a shared schedule, in which case you can pass just the ID of the shared schedule).

In our example we will setup a timed subscription. Let’s take a look at a top level method used to create the XML string we will pass to the web service.

private string GetMatchData()
{
    ScheduleDefinition schedule = new ScheduleDefinition();
    schedule.StartDateTime = DateTime.Now;
    schedule.EndDateSpecified = false;       
    schedule.Item = GetPattern();
    
    XmlDocument xmlDoc = GetScheduleAsXml(schedule);        
    return xmlDoc.OuterXml;
}

A ScheduleDefinition contains just a few items to populate, including properties to specify when a subscription starts and optionally ends. The Item property is of type RecurrencePattern, and you will need to instantiate one of the following five class derived from RecurrencePattern to populate the property:

• MinuteRecurrence
• DailyRecurrence
• WeeklyRecurrence
• MonthlyRecurrence
• MonthlyDOWRecurrence

The object you pick will depend on the timing of the subscription (does the subscription need to deliver the report every day, or once a month?). In this example, let’s build a schedule to deliver our report on the last Friday of every third month.

private RecurrencePattern GetPattern()
{
    MonthlyDOWRecurrence pattern = new MonthlyDOWRecurrence();
    pattern.WhichWeekSpecified = true;
    pattern.WhichWeek = WeekNumberEnum.LastWeek;
    
    MonthsOfYearSelector months = new MonthsOfYearSelector();
    months.March = true;
    months.June = true;
    months.September = true;
    months.December = true;
    pattern.MonthsOfYear = months;

    DaysOfWeekSelector days = new DaysOfWeekSelector();
    days.Friday = true;
    pattern.DaysOfWeek = days;

    return pattern;
}

The next step is to turn our schedule into a string of XML. The exact string we will need to pass in the MatchData parameter will look like the following:

<?xml version=\"1.0\"?>
<ScheduleDefinition 
    xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"
    xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">
  <StartDateTime
     xmlns=\"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices\">
    2004-04-11T21:30:31.6725360-04:00
  </StartDateTime>
  <MonthlyDOWRecurrence 
      xmlns=\"http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices\">
    <WhichWeek>LAST_WEEK</WhichWeek>
    <DaysOfWeek>
      	<Sunday>false</Sunday>
	<Monday>false</Monday>
	<Tuesday>false</Tuesday>
	<Wednesday>false</Wednesday>
	<Thursday>false</Thursday>
	<Friday>true</Friday>
	<Saturday>false</Saturday>
    </DaysOfWeek>
    <MonthsOfYear>
	<January>false</January>
	<February>false</February>
	<March>true</March>
	<April>false</April>
	<May>false</May>
	<June>true</June>
	<July>false</July>
	<August>false</August>
	<September>true</September>
	<October>false</October>
	<November>false</November>
	<December>true</December>
    </MonthsOfYear>
  </MonthlyDOWRecurrence>
</ScheduleDefinition>

The following method uses an XmlSerializer object to create the XML.

private XmlDocument GetScheduleAsXml(ScheduleDefinition schedule)
{
    MemoryStream buffer = new MemoryStream();
    XmlSerializer xmlSerializer = new XmlSerializer(typeof(ScheduleDefinition));
    xmlSerializer.Serialize(buffer, schedule);
    buffer.Seek(0, SeekOrigin.Begin);
                    
    XmlDocument doc = new XmlDocument();        
    doc.Load(buffer);   
// patch up WhichWeek
    XmlNamespaceManager ns = new XmlNamespaceManager(doc.NameTable);
    ns.AddNamespace("rs", 
            "http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices");

    XmlNode node = 
        doc.SelectSingleNode(
             "/ScheduleDefinition/rs:MonthlyDOWRecurrence/rs:WhichWeek", ns
        );
    if(node != null)
    {
        switch (node.InnerXml)
        {
            case "FirstWeek":
                node.InnerXml = "FIRST_WEEK"; break;
            case "SecondWeek":
                node.InnerXml = "SECOND_WEEK"; break;
            case "ThirdWeek":
                node.InnerXml = "THIRD_WEEK"; break;
            case "FourthWeek":
                node.InnerXml = "FOURTH_WEEK"; break;
            case "LastWeek":
                node.InnerXml = "LAST_WEEK"; break;
        }
    }

    return doc;
}

Notice we need to massage the WhichWeek element. This is due to a bug in the RTM version of reporting services and will probably not be needed after a future release or update.

Putting all the methods above together results in the following code:

private void btnCreateSub_Click(object sender, System.EventArgs e)
{
    string report = @"/SampleReports/Company Sales";
    string description = "Sample company sales subscription";
    string eventType = "TimedSubscription";
    string matchData = GetMatchData();      
    ExtensionSettings extSettings = GetExtensionSettings();
    

    ParameterValue[] parameters = null;

    ReportingService service = new ReportingService();
    service.Credentials = System.Net.CredentialCache.DefaultCredentials;
    service.CreateSubscription(report, extSettings, description, eventType, matchData, parameters );
}

The final parameter to CreateSubscription is a list of parameters specific to the report you are subscribing to. Since the report we are subscribing to does not require parameters we can pass an empty value.

Conclusion

-- by K. Scott Allen (scott @ odetocode.com)