SSRS Report Subscription using C#

Introduction

Report subscription is a feature to view/email the report on a specific time. User can create customized Report subscription by using C#.

Here, I have used Reports Service to create subscription for SSRS Reports.

Background

A Reporting Services subscription is a configuration that delivers a report at a specific time or in response to an event, and in a file format that you specify.

For example, every Wednesday, save the MonthlySales.rdl report as a Microsoft Word document to a file share. Subscriptions can be used to schedule and automate the delivery of a report and with a specific set of report parameter values.

You can create multiple subscriptions for a single report to vary the subscription options.

Using the Code

Add Service reference of Reporting Service By using the following steps:

• Right click References
• Select Add Service Reference
• Write address of Report Service like http://sqlsvr/ReportServer/ReportService2010.asmx?wsdl
• Write Namespace and click OK

Report subscription can be scheduled at any day and time. We will create the Scheduled XML and use it for creating report subscription.

Here, we are creating a function CreateScheduleXML which returns string in the XML format which is used for scheduling the subscription.

private string CreateScheduleXML() {
           string StartDateTime = "2015-04-22T19:51:00.000+05:30";
           string weeksInterval = "1";
           string weekDays = "Wednesday";
           string scheduleXML = @"<ScheduleDefinition>
                                       <StartDateTime>" +StartDateTime+ @"</StartDateTime>
                                       <WeeklyRecurrence>
                                           <WeeksInterval>"+weeksInterval+@"</WeeksInterval>
                                           <DaysOfWeek>
                                               <"+weekDays+@">true</"+weekDays+@">
                                           </DaysOfWeek>
                                       </WeeklyRecurrence>
                                   </ScheduleDefinition>";
           return scheduleXML;
       }

In the above function, user can pass StartDateTime, weeksInterval, weekDays values as parameter. It will depends on the user's usage.

We are creating CreateDataSourceDefinition, which stores the definition or information about the DataSource connection string. CreateDataSourceDefinition function is used for Create Data Source and Create Data Set.

private DataSourceDefinition CreateDataSourceDefinition() {
            DataSourceDefinition dataSourceDefinition = new DataSourceDefinition();
            dataSourceDefinition.ConnectString = "data source=(local);initial catalog=Reporting-DB";
            dataSourceDefinition.CredentialRetrieval = CredentialRetrievalEnum.Store;
            dataSourceDefinition.Enabled = true;
            dataSourceDefinition.EnabledSpecified = true;
            dataSourceDefinition.Extension = "SQL";
            dataSourceDefinition.ImpersonateUserSpecified = false;
            dataSourceDefinition.UserName = "sa";
            dataSourceDefinition.Password = "P@s$w0rD";
            return dataSourceDefinition;
        }

We will use CreateDataSourceDefinition function for Create Data Source. We are creating function CreateDataSource for creating Data Source.

private DataSource CreateDataSource() {
           DataSource delivery = new DataSource();
           delivery.Name = "";
           DataSourceDefinition dataSourceDefinition = CreateDataSourceDefinition();
           delivery.Item = dataSourceDefinition;
           return delivery;
       }

We will also create Data Set for the Report Subscription. We are creating function CreateDataSet for creating Data Set.

private DataSetDefinition CreateDataSet() {
            ReportingService2010SoapClient rs = new ReportingService2010SoapClient();
           rs.ClientCredentials.Windows.AllowedImpersonationLevel =
               System.Security.Principal.TokenImpersonationLevel.Impersonation;
           rs.Open();
           TrustedUserHeader t = new TrustedUserHeader();
           DataSource delivery = CreateDataSource();
           DataSetDefinition dataSetDefinition = new DataSetDefinition();
           // Create the fields list.
           Field[] fieldsList = new Field[2];
           fieldsList[0] = new Field();
           fieldsList[0].Name = "ReportSubTitle";
           fieldsList[0].Alias = "ReportSubTitle";
           fieldsList[1] = new Field();
           fieldsList[1].Name = "ReportTitle";
           fieldsList[1].Alias = "ReportTitle";
           dataSetDefinition.AccentSensitivitySpecified = false;
           dataSetDefinition.CaseSensitivitySpecified = false;
           dataSetDefinition.KanatypeSensitivitySpecified = false;
           dataSetDefinition.WidthSensitivitySpecified = false;
           dataSetDefinition.Fields = fieldsList;
           QueryDefinition queryDefinition = new QueryDefinition();
           queryDefinition.CommandText = "Select ReportTitle,ReportSubTitle from ReportDetail";
               queryDefinition.CommandType = "Text";
           queryDefinition.Timeout = 45;
           queryDefinition.TimeoutSpecified = true;
           dataSetDefinition.Query = queryDefinition;
           DataSetDefinition results = new DataSetDefinition();
           bool changed;
           string[] paramNames;
           try {
               rs.PrepareQuery(t, delivery, dataSetDefinition, out results, out changed, out paramNames);
           } catch (SoapException e) {}
           return results;
       }

The output of CreateDataSet function is DataSetDefinition, which is used for Create Data Driven Plan. DataDrivenPlan is used for Create Data Driven Report Subscription.

private DataRetrievalPlan CreateDataDrivenPlan() {
            DataRetrievalPlan dataRetrieval = new DataRetrievalPlan();
            dataRetrieval.DataSet = CreateDataSet();
            dataRetrieval.Item = CreateDataSourceDefinition();
            return dataRetrieval;
        }

After creating CreateScheduleXML, CreateDataSourceDefinition, CreateDataSource, CreateDataSet, CreateDataDrivenPlan we will use outputs for create subscription.

I am creating two types of subscription here.

1. Windows File Subscription

Below a function is created for Create window file subscription:

private void FileSubscription() {
            ReportingService2010SoapClient rs = new ReportingService2010SoapClient();
            rs.ClientCredentials.Windows.AllowedImpersonationLevel = 
            	System.Security.Principal.TokenImpersonationLevel.Impersonation;
            rs.Open();
            TrustedUserHeader t = new TrustedUserHeader();

            string report = "/Reports/PaymentDetail";
            string desc = "Send to Document Library";
            string eventType = "TimedSubscription";
            string scheduleXml = CreateScheduleXML();

            ParameterValue[] extensionParams = new ParameterValue[7];
            extensionParams[0] = new ParameterValue();
            extensionParams[0].Name = "PATH";
            extensionParams[0].Value = 
              @"\\SharedPath\Software_Developer\ReportingLocation\Sandeep_Tripathi/";

            extensionParams[1] = new ParameterValue();
            extensionParams[1].Name = "FILENAME";
            extensionParams[1].Value = "NegativePaymentVarianceDetail";

            extensionParams[2] = new ParameterValue();
            extensionParams[2].Name = "FILEEXTN";
            extensionParams[2].Value = "True"; // It should be None, Overwrite, or AutoIncrement.
            //None: The FileName remains the same & Reporting Services 
            //doesn't overwrite the existing file should it already exists.
            //Overwrite: When you want to existing file to be replaces.
            //AutoIncrement: Use AutoIncrement to append an incrementing number 
            //to the filename - this leaves all pervious files on the drive.

            extensionParams[3] = new ParameterValue();
            extensionParams[3].Name = "USERNAME";
            extensionParams[3].Value = "userName";

            extensionParams[4] = new ParameterValue();
            extensionParams[4].Name = "PASSWORD";
            extensionParams[4].Value = "p@sSw0rD";

            extensionParams[5] = new ParameterValue();
            extensionParams[5].Name = "RENDER_FORMAT";
            extensionParams[5].Value = "PDF"; 
            // It should be XML, CSV, IMAGE, PDF, HTML4.0, HTML3.2, MHTML or EXCEL

            extensionParams[6] = new ParameterValue();
            extensionParams[6].Name = "WRITEMODE";
            extensionParams[6].Value = "Overwrite";

            ExtensionSettings extSettings = new ExtensionSettings();
            extSettings.ParameterValues = extensionParams;
            extSettings.Extension = "Report Server FileShare";

            string subid = "";
            try {
                rs.CreateSubscription(t, report, extSettings, desc, 
                                      eventType, scheduleXml, null, out subid);
                ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "subscription Created");
            } catch (SoapException ex) {
                throw ex;
            }
        }

2. Data Driven Subscription

Below function is used for create Data Driven Subscription. We can also use some part of the below code for Email Subscription.

private void DataDrivenSubscription() {
            string report = "/Reports/PaymentDetail";
            string description = "Send to Document Library";
            ReportingService2010SoapClient rs = new ReportingService2010SoapClient();
            rs.ClientCredentials.Windows.AllowedImpersonationLevel = 
                       System.Security.Principal.TokenImpersonationLevel.Impersonation;
            rs.Open();
            TrustedUserHeader t = new TrustedUserHeader();
            ExtensionSettings settings = new ExtensionSettings();
            settings.Extension = "Report Server Email";
            // Set the extension parameter values.
            ParameterValueOrFieldReference[] extensionParams = new ParameterValueOrFieldReference[8];
            ParameterFieldReference to = new ParameterFieldReference();
            to.ParameterName = "TO";
            to.FieldAlias = "mailIDTo@mail.com";
            extensionParams[0] = to;
            ParameterValue replyTo = new ParameterValue();
            replyTo.Name = "ReplyTo";
            replyTo.Value = "replyTo@mail.com";
            extensionParams[1] = replyTo;
            ParameterValue includeReport = new ParameterValue();
            includeReport.Name = "IncludeReport";
            includeReport.Value = "False";
            extensionParams[2] = includeReport;
            ParameterValue renderFormat = new ParameterValue();
            renderFormat.Name = "RenderFormat";
            renderFormat.Value = "HTML4.0";
            extensionParams[3] = renderFormat;
            ParameterValue priority = new ParameterValue();
            priority.Name = "Priority";
            priority.Value = "NORMAL";
            extensionParams[4] = priority;
            ParameterValue subject = new ParameterValue();
            subject.Name = "Subject";
            subject.Value = "Your Payment report";
            extensionParams[5] = subject;
            ParameterValue comment = new ParameterValue();
            comment.Name = "Comment";
            comment.Value = "Here is the link to your report.";
            extensionParams[6] = comment;
            ParameterValue includeLink = new ParameterValue();
            includeLink.Name = "IncludeLink";
            includeLink.Value = "True";
            extensionParams[7] = includeLink;
            settings.ParameterValues = extensionParams;
            DataSource delivery = CreateDataSource();
            DataRetrievalPlan dataRetrieval = CreateDataDrivenPlan();
            string eventType = "TimedSubscription";
            string matchData = CreateScheduleXML();
            ParameterValueOrFieldReference[] parameters = new ParameterValueOrFieldReference[2];
            ParameterFieldReference empID = new ParameterFieldReference();
            empID.ParameterName = "ReportSubTitle";
            empID.FieldAlias = "ReportSubTitle";
            parameters[0] = empID;
            ParameterValue reportYear = new ParameterValue();
            reportYear.Name = "ReportTitle";
            reportYear.Value = "ReportTitle";
            parameters[1] = reportYear;
            try {
                string SubscriptionID;
                rs.CreateDataDrivenSubscription(t, report, settings, 
                dataRetrieval, description, eventType, matchData, parameters, out SubscriptionID);
                ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "subscription Created");
            } catch (SoapException e) {
            }
        }

After creating Report Subscription, one can easily view this information either on the Report or in the Database.

There is an inbuilt Database ReportServer which stores all the information related to the SSRS Reports. You can easily view the Subscription detail into Subscriptions tables.

select * from Subscriptions

You can view the Data Source, Data Set, and Scheduled information by using the following SQL Queries.

Select * from DataSource
Select * from DataSets
Select * from Schedule

Points of Interest

I want to call the BIDS features specially SSIS & SSRS from the C# code. I use the reporting services to create the Report subscription.