Click here to Skip to main content
Click here to Skip to main content

Programmatically Playing With SSRS Subscriptions

, 29 Apr 2009
Rate this:
Please Sign up or sign in to vote.
This article demonstrates how can you dynamically handle SQL Server Reporting Services Subscriptions without using the SSRS interface at all.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

saanj
Software Developer (Senior)
United Kingdom United Kingdom
A SharePoint Kid playing in Kolkata, India.
 
Working with one of the leading software company and currently working with SharePoint technologies.
 
Enjoys Cricket, National & World Music.
 
Favourite band include Linkin Park, Beatles, Oasis, Match Box 20, Noori, Nirvana, Nickelback etc.
Follow on   Twitter

Comments and Discussions

 
QuestionSSRS Sharepoint Integrated Mode PinmemberMember 1042766826-Nov-13 8:22 
GeneralI need to refer Subscription Id in Deliver method. PinmemberUsman Shaik13-Oct-10 23:18 
GeneralGreat Post PinmemberAndrew Boudreau1-May-09 9:03 
GeneralRe: Great Post Pinmembersaanj3-May-09 20:28 
GeneralRe: Great Post PinmemberAndrew Boudreau5-May-09 5:48 
GeneralRe: Great Post Pinmembersaanj5-May-09 18:57 
GeneralRe: Great Post PinmemberLalitha.nov2030-Sep-09 21:42 
GeneralRe: Great Post Pinmembersaanj1-Oct-09 1:43 
Hi,
Are you going to use File Shared Subscriptions?
 
Regards
Saanj
 
There is no foolish question, there is no final answer...

QuestionCreate 50 DataDrivensubscriptions programitically PinmemberVijuyaju11-Sep-12 5:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 29 Apr 2009
Article Copyright 2009 by saanj
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid