Programmatically Playing With SSRS Subscriptions

• Download source code - 255 KB

Introduction

SQL Server Reporting Services offers programmatically handling various report subscriptions. You can read specific subscriptions and change them in the code-behind as required. I assume that you are already aware about the subscription mechanism in Reporting Services. For some of you who are not, subscription in Reporting Services is nothing but an automated service (SQL Server job) defined and set by you to deliver reports at specific times or in specific events. You also define in which format the report will be presented to the user. Now, whatever subscription properties you have set, everything will be stored in the Report Server database. The Schedule, Subscriptions, and ReportSchedule tables contain all those information.

Implementation

You can download the code provided with this article. In this article, I am not going to discuss the full code, rather I will emphasize more on the key points.

First, create a Windows or Web Application. Add a Web Reference to ReportingService2006. If SSRS is installed on your system, then you can easily find ReportService2006.asmx in the following URL:

http://servername/_vti_bin/ReportServer/ReportService2006.asmx

Please note that servername has to be replaced by the actual Report Server URL. If Report Server is not installed on your system, then you can find this Web Service in a remote Report Server using the above mentioned URL (server name has to be replaced).

ActiveState active = null;
ParameterValueOrFieldReference[] extensionParams = null;
ExtensionSettings extSettings = null;
ParameterValue[] values; a
string desc = string.Empty;
string eventType = string.Empty;
string matchData = string.Empty;

Call the GetSubscriptionProperties method of the ReportingService2006 Web Service by passing the subscription ID of the subscription which needs to be changed:

ReportingService2006 rs = new ReportingService2006();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.GetSubscriptionProperties(txtSubscriptionID.Text.Trim(), out extSettings, 
   out desc, out active, out status, out eventType, out matchData, out values);   

Different parameter details for this method are as follows:

• Subscription ID: The unique id of the subscription which needs to be modified.
• Extension Settings: The report delivery extension and its configurable settings. This is an output parameter.
• Description: Contains some meaningful description which will be displayed to the user. This is an output parameter.
• ActiveState: Returns the ActiveState of the specified subscription. This is an output parameter.
• Status: The status of the Subscription and an output parameter.
• EventType: Returns the type of event that triggers the subscription. This is an output parameter.
• MatchData: Returns XML data specific to the report execution and delivery scheduling process. This is an output parameter.
• ParameterValue[]: A collection of different report parameters for the report. This is also an output parameter.

To get all the extension settings returned from the Web Service, you can use the following code:

ParameterValueOrFieldReference[] extensionParams = extSettings.ParameterValues;
foreach (ParameterValueOrFieldReference extensionParam in extensionParams)
{
    if (((ParameterValue)extensionParam).Name.Equals("TO"))
    {
        txt_TO.Text = ((ParameterValue)extensionParam).Value;
    }
    if (((ParameterValue)extensionParam).Name.Equals("CC"))
    {
        txt_CC.Text = ((ParameterValue)extensionParam).Value;
    }
    if (((ParameterValue)extensionParam).Name.Equals("BCC"))
    {
        txt_BCC.Text = ((ParameterValue)extensionParam).Value;
    }
    if (((ParameterValue)extensionParam).Name.Equals("ReplyTo"))
    {
        txt_ReplyTo.Text = ((ParameterValue)extensionParam).Value;
    }
    if (((ParameterValue)extensionParam).Name.Equals("Subject"))
    {
        txt_Sub.Text = ((ParameterValue)extensionParam).Value;
    }
    if (((ParameterValue)extensionParam).Name.Equals("Comment"))
    {
        txt_Comment.Text = ((ParameterValue)extensionParam).Value;
    }
    if (((ParameterValue)extensionParam).Name.Equals("Priority"))
    {
        txt_Priority.Text = ((ParameterValue)extensionParam).Value;
    }
}

As I wrote earlier, the matchdata parameter returns the XML string which needs to be parsed first in order to change or modify it. You can always fire a select statement in the Subscription table on the ReportServer database to see how the matchdata column looks like. But, the pre-requisite is that you have to create a report subscription first. Understand the XML definition of the match data in different schedule durations, and then parse and bind it to a different control as per your requirement.

After binding all information retrieved from ReportingService2006, it is your turn to change the data as per the requirement. Now, to save the subscription, please note that you need to again create an XML string that containing the modified report scheduling definition. Finally, call the SetSubscriptionProperties method of the ReportingService2006 web service.

rs.SetSubscriptionProperties(subscriptionID, extSettings, desc, 
                             eventType, xmlScheduling, values);

It will update the Subscription definition in the ReportServer database. It is very important to be note that the matchdata which is generated dynamically should be consistent with the exact definition. There are five types of schedule durations, which are Once, Hourly, Daily, Hourly, Weekly, and Monthly. For each different schedule duration, the XML schema is more or less different. So, it is my suggestion to you not to use the SetSubscriptionProperties method to change the subscription with out understanding the matchdata format for different schedule durations. Otherwise, report subscription may be corrupted by improper matchdata.

Notes & References

The following link can be very useful during the implementation: http://technet.microsoft.com/en-us/library/reportservice2006.reportingservice2006_members.aspx.

You can visit my technical blog at: http://tech-motive.blogspot.com.