Microsoft.SqlServer.ReportingServices2005.ReportingService2005 rsSubscription = new Microsoft.SqlServer.ReportingServices2005.ReportingService2005(); rsSubscription.Credentials = rs.GetCredential(); rsSubscription.Url = "http://10.57.40.70/ReportServer/ReportService2005.asmx"; //the name of the report for which the DDS is to be created string report = " /Reports/Invoice/InvoiceTest"; string description = "Programmatic Data Driven Subscription for Windows File Share"; //set extension as Windows File Share Microsoft.SqlServer.ReportingServices2005.ExtensionSettings settings = new Microsoft.SqlServer.ReportingServices2005.ExtensionSettings(); settings.Extension = "Report Server FileShare"; //set extension parameters values //we need to set the values for the different names for files, //path where the files will be delivered, the rendering format //for the report, the user name and //password which has write access to shared folder Microsoft.SqlServer.ReportingServices2005.ParameterValueOrFieldReference[] extensionParams = new Microsoft.SqlServer.ReportingServices2005.ParameterValueOrFieldReference[5]; //this parameter takes the name for the file created with report same as the//sales order id. Microsoft.SqlServer.ReportingServices2005.ParameterFieldReference fnm = new Microsoft.SqlServer.ReportingServices2005.ParameterFieldReference(); string timeStamp = DateTime.Now.ToString(); fnm.ParameterName = "FILENAME"; fnm.FieldAlias = "RevRecPrepaidTransfer@" + timeStamp; extensionParams[0] = fnm; //this path should follow UNC standard Microsoft.SqlServer.ReportingServices2005.ParameterValue Path = new Microsoft.SqlServer.ReportingServices2005.ParameterValue(); Path.Name = "PATH"; Path.Value = @"\\10.57.40.70\Report_files"; extensionParams[1] = Path; Microsoft.SqlServer.ReportingServices2005.ParameterValue renderFormat = new Microsoft.SqlServer.ReportingServices2005.ParameterValue(); renderFormat.Name = "RENDER_FORMAT"; renderFormat.Value = "HTML4.0"; extensionParams[2] = renderFormat; Microsoft.SqlServer.ReportingServices2005.ParameterValue uname = new Microsoft.SqlServer.ReportingServices2005.ParameterValue(); uname.Name = "USERNAME"; uname.Value = "Username"; extensionParams[3] = uname; Microsoft.SqlServer.ReportingServices2005.ParameterValue password = new Microsoft.SqlServer.ReportingServices2005.ParameterValue(); password.Name = "PASSWORD"; //password.Value = "<password>"; password.Value = "password@123"; extensionParams[4] = password; //this step will assign all the parameter values settings.ParameterValues = extensionParams; //create data source Microsoft.SqlServer.ReportingServices2005.DataSource delivery = new Microsoft.SqlServer.ReportingServices2005.DataSource(); delivery.Name = ""; Microsoft.SqlServer.ReportingServices2005.DataSourceDefinition dataSourceDef = new Microsoft.SqlServer.ReportingServices2005.DataSourceDefinition(); dataSourceDef.ConnectString = @"data source=100.40.45.65;initial catalog=DbTest;user id=User;password=User;"; dataSourceDef.CredentialRetrieval = Microsoft.SqlServer.ReportingServices2005.CredentialRetrievalEnum.Store; dataSourceDef.Enabled = true; dataSourceDef.EnabledSpecified = true; dataSourceDef.Extension = "SQL"; dataSourceDef.ImpersonateUserSpecified = false; dataSourceDef.UserName = "User"; dataSourceDef.Password = "User"; delivery.Item = dataSourceDef; Microsoft.SqlServer.ReportingServices2005.Field[] fieldlist = new Microsoft.SqlServer.ReportingServices2005.Field[1]; fieldlist[0] = new Microsoft.SqlServer.ReportingServices2005.Field(); fieldlist[0].Name = "BookingNumber"; fieldlist[0].Alias = "BookingNumber"; //create data set Microsoft.SqlServer.ReportingServices2005.DataSetDefinition dataSetDefinition = new Microsoft.SqlServer.ReportingServices2005.DataSetDefinition(); dataSetDefinition.AccentSensitivitySpecified = false; dataSetDefinition.CaseSensitivitySpecified = false; dataSetDefinition.KanatypeSensitivitySpecified = false; dataSetDefinition.WidthSensitivitySpecified = false; //dataSetDefinition.Fields = fieldlist; Microsoft.SqlServer.ReportingServices2005.QueryDefinition queryDefition = new Microsoft.SqlServer.ReportingServices2005.QueryDefinition(); queryDefition.CommandText = "select top 1 Invoice_Id from DbTest1 order by Invoice_id Desc"; queryDefition.CommandType = "Text"; queryDefition.Timeout = 30; queryDefition.TimeoutSpecified = true; dataSetDefinition.Query = queryDefition; Microsoft.SqlServer.ReportingServices2005.DataSetDefinition results = new Microsoft.SqlServer.ReportingServices2005.DataSetDefinition(); bool changed; string[] paramNames; try { results = rsSubscription.PrepareQuery(delivery, dataSetDefinition, out changed, out paramNames); } catch (SoapException ex) { //MessageBox.Show(ex.Detail.InnerText.ToString()); } Microsoft.SqlServer.ReportingServices2005.DataRetrievalPlan dataRetrieval = new Microsoft.SqlServer.ReportingServices2005.DataRetrievalPlan(); dataRetrieval.DataSet = results; dataRetrieval.Item = dataSourceDef; //set event type and match data //with matchdata we need to specify details like schedule for the subscription string EventType = "TimedSubscription"; string matchData = @"<scheduledefinition>"; matchData += @"<startdatetime>2012-10-25T18:35:00-07:00</startdatetime><weeklyrecurrence><weeksinterval>1</weeksinterval>"; matchData += @"<daysofweek>"; matchData += @"<monday>True</monday><tuesday>True</tuesday><wednesday>True</wednesday><thursday>True</thursday><friday>True</friday>"; matchData += @"</daysofweek></weeklyrecurrence></scheduledefinition>"; //set report parameter value //the parameter value is based on the field salesorderid created in the field list //thus the parameter will have as many values as the number of records in the //table OrderForDDS and those many reports will be delivered to the windows file //share Microsoft.SqlServer.ReportingServices2005.ParameterValueOrFieldReference[] parameters = new Microsoft.SqlServer.ReportingServices2005.ParameterValueOrFieldReference[1]; Microsoft.SqlServer.ReportingServices2005.ParameterFieldReference param = new Microsoft.SqlServer.ReportingServices2005.ParameterFieldReference(); Microsoft.SqlServer.ReportingServices2005.ParameterValue pvalue = new Microsoft.SqlServer.ReportingServices2005.ParameterValue(); pvalue.Label = "vcBookingNum"; pvalue.Name = "vcBookingNum"; pvalue.Value = "12345"; parameters[0] = pvalue; try { string subscriptionID = rsSubscription.CreateDataDrivenSubscription(report, settings, dataRetrieval, description, EventType, matchData, pvalue); MessageBox.Show(subscriptionID); } catch (SoapException ex) { MessageBox.Show(ex.Detail.InnerText.ToString()); }
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)