Programmatically Playing With SSRS Subscriptions

SQL Server Reporting Services offers programmatically handling various report subscriptions. You can read the specific subscriptions and change those in 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 aware about this, I would like to write that subscription in reporting services is nothing but a automated service (sql server job) defined and set by you to deliver reports at specific time 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 ReportServer database. Schedule, Subscriptions and ReportSchedule tables contain alll those informations.

Now create a Windows or Web Application. Add a Web Reference towords
ReportingService2006. If SSRS is isntalled on your system then you can easily find the ReportService2006.asmx in the following url:

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

If report server is not installed on your system then you can find this web service in any remote report server using the above mentioned url (server name has to be replaced).

Create an object of the ReportingService2006 web service. Declare the following variables in order to get the retrieved subscription properties:

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 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 : It is the unique id of the subscription which needs to be modified.
Extension Settings: It is the report deilvery extension and its configurable settings. It is an output parameter.
Description: It contains some meaningful description which will be displayed to the user. It is an output parameter.
ActiveState: It returns the ActiveState of the specified subscription. It is an output parameter. Status: It is the status of the Subscription and an output parameter.
EventType: It returns the type of event that triggers the subscription. It is an output parameter.
MatchData: It returns xml data specific to the report execution and deilvery scheduling process. It is an output parameter.
ParameterValue[]: It is the collection of different report parameters for the report. It is also an output parameter.

To get all 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, matchdata parameter returns 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 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 with different schedule durations and the parse and bind it to the different control as per your requirement.

After binding all informations retrieved from ReportingService2006, its your turn to change the data as per the requirement. Now to save the subscription, please note that you need to again create a xml string that contains the modified report scheduling defenition. Finally call the
SetSubscriptionProperties method of the ReportingService2006 web service.

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

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

Following link may be useful during your implementation:

http://technet.microsoft.com/en-us/library/reportservice2006.reportingservice2006_members.aspx

All the best.