sql-server - 编写 c# 代码以创建 SSRS 数据驱动订阅
问题描述
我在 SQL 报告服务 2012 标准版下有带有参数的 SSRS 报告。我喜欢导出到 excel 并作为电子邮件中的附件发送到不同的收据,收据来自一些 SQL 查询,这意味着它是动态的。
数据驱动订阅可以做到这一点,但我有不支持数据驱动订阅的 SQL Server 2012 标准版,我无法升级,所以我正在寻找任何可以像数据驱动订阅一样完成类似工作的代码。
我发现这个链接可以解决我的问题。 http://jaliyaudagedara.blogspot.com/2012/10/creating-data-driven-subscription.html
当我通过添加服务引用“ http://mylocalserver:81/reportserver/ReportService2010.asmx ”在 Visual Studio 2015“类库”项目下尝试此代码时,我在这行代码中遇到错误。ReportingService2010SoapClient rs=新的 ReportingService2010SoapClient();
有关错误的其他信息:在 ServiceModel 客户端配置部分中找不到引用合同“ReportService2010.ReportingService2010Soap”的默认端点元素。这可能是因为没有为您的应用程序找到配置文件,或者因为在客户端元素中找不到与此合同匹配的端点元素。
在花了足够的时间使其与“类库”项目一起工作后,我决定通过添加 Web 服务引用来完成 Web 服务项目下的代码。最后经过反复试验,我在 Web 服务项目下得到了工作代码。下面的代码适用于我的本地计算机,它有 Sql server 2012 企业版,但它给了我同样的错误,说“这个版本的 Reporting Services 不支持数据驱动的报表订阅”在我的公司服务器上有 SQL server 2012 标准版。
public void DoWork()
{
ReportingService2010 rs = new ReportingService2010();
rs.Credentials = CredentialCache.DefaultCredentials;
// rs.Url = "http://mylocalserver:81/reportserver/ReportService2010.asmx";
rs.Url = "http://companyserver/reportserver/ReportService2010.asmx";
var reportPath = "/CYTYC Reports/";
string report = $"{reportPath}AllContactCIPPointsReport";
string description = "Programmatic Data Driven Subscription \"Report Server Email\" ";
//set extension as Windows File Share
ExtensionSettings settings = new ExtensionSettings();
settings.Extension = "Report Server Email";
// Set the extension parameter values.
var extensionParams = new ParameterValueOrFieldReference[8];
// var to = new ParameterFieldReference { ParameterName = "TO", FieldAlias = "PARAMS" }; // Data-driven.
var to = new ParameterValue { Name = "TO", Value = "example@gmail.com" }; // Data-driven.
extensionParams[0] = to;
var replyTo = new ParameterValue { Name = "ReplyTo", Value = "example@gmail.com" };
extensionParams[1] = replyTo;
var includeReport = new ParameterValue { Name = "IncludeReport", Value = "False" };
extensionParams[2] = includeReport;
var renderFormat = new ParameterValue { Name = "RenderFormat", Value = "HTML4.0" };
extensionParams[3] = renderFormat;
var priority = new ParameterValue { Name = "Priority", Value = "NORMAL" };
extensionParams[4] = priority;
var subject = new ParameterValue { Name = "Subject", Value = "Subsribed Report" };
extensionParams[5] = subject;
var comment = new ParameterValue { Name = "Comment", Value = "Here is the link to your report." };
extensionParams[6] = comment;
var includeLink = new ParameterValue { Name = "IncludeLink", Value = "True" };
extensionParams[7] = includeLink;
settings.ParameterValues = extensionParams;
// Create the data source for the delivery query.
var delivery = new DataSource { Name = "" };
var dataSourceDefinition = new DataSourceDefinition
{
ConnectString = "Data Source=CYTYC-LIVE;Initial Catalog=yourdatabasename",
CredentialRetrieval = CredentialRetrievalEnum.Store,
Enabled = true,
EnabledSpecified = true,
Extension = "SQL",
ImpersonateUserSpecified = false,
UserName = "username",
Password = "password"
};
delivery.Item = dataSourceDefinition;
// Create the data set for the delivery query.
var dataSetDefinition = new DataSetDefinition
{
AccentSensitivitySpecified = false,
CaseSensitivitySpecified = false,
KanatypeSensitivitySpecified = false,
WidthSensitivitySpecified = false
};
var queryDefinition = new QueryDefinition
{
CommandText = @"Your select * from Query",
CommandType = "Text",
Timeout = 45,
TimeoutSpecified = true
};
dataSetDefinition.Query = queryDefinition;
var results = new DataSetDefinition();
var oServerInfoHeader = new ServerInfoHeader();
var oTrustedUserHeader = new TrustedUserHeader();
bool changed;
string[] paramNames;
try
{
results = rs.PrepareQuery(delivery, dataSetDefinition, out changed, out paramNames);//.PrepareQuery(oTrustedUserHeader, delivery, dataSetDefinition, out results, out changed,out paramNames);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
var dataRetrieval = new DataRetrievalPlan { DataSet = results, Item = dataSourceDefinition };
// Set the event type and match data for the delivery.
const string eventType = "TimedSubscription";
const string matchData = "<ScheduleDefinition><StartDateTime>2018-06-01T14:00:00-07:00</StartDateTime><WeeklyRecurrence><WeeksInterval>1</WeeksInterval><DaysOfWeek><Monday>True</Monday><Tuesday>True</Tuesday><Wednesday>True</Wednesday><Thursday>True</Thursday><Friday>True</Friday></DaysOfWeek></WeeklyRecurrence></ScheduleDefinition>";
//const string eventType = "SnapshotUpdated";
//const string matchData = null;
//// Set the report parameter values.
//var parameters = new ParameterValueOrFieldReference[1];
//// i am retrieving value EMAIL from database and I am passing that value as my report parameter value
//var reportparam = new ParameterFieldReference { ParameterName = "yourreportparametername", FieldAlias = "PARAMS" }; // Data-driven.
//parameters[0] = reportparam;
var parameters = new ParameterValue[1];
var reportparam = new ParameterValue {Name = "yourreportparametername", Value = "yourreportparametervalue"};
parameters[0] = reportparam;
string subscriptionId = "";
try
{
subscriptionId = rs.CreateDataDrivenSubscription(report, settings, dataRetrieval, description, eventType, matchData, parameters);
//(oTrustedUserHeader, report, settings, dataRetrieval,description, eventType, matchData, parameters,out subscriptionId);
}
catch (System.Web.Services.Protocols.SoapException ex)
{
Console.WriteLine(ex.Detail.InnerText.ToString(CultureInfo.InvariantCulture));
}
}
解决方案
您没有说明为什么需要数据驱动订阅 - 常规 SSRS 订阅可以通过电子邮件发送带有设置或默认参数的 Excel 报告。
据我所知,没有任何第三方工具可以模拟数据驱动订阅,但有些用户创建了自己的订阅。
如果您只想根据条件触发订阅,则可以使用 SSIS 作业运行查询以确定是否发送,如果是则触发订阅。
如果您需要更复杂的东西(例如改变 TO/CC 收件人、更改参数值...),您需要进行更多编程。这里有几件事可以开始理论和代码:
http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/163119/
推荐阅读
- javascript - Javascript链接列表指针
- angular - 在视图准备好之前修改事件处理程序中的视图
- node.js - 在 sequelize 5 中从 findOne 访问实例方法
- python - 有没有更好的方法来计算进入 intreval 的频率?
- gruntjs - 咕噜任务副本未能将文件写入目标
- c# - 通过在 WPF 中附加空格来打印固定宽度的字符串
- android - 无论如何,是否可以在没有 root 的情况下以编程方式重新启动设备?
- java - 排除基于 maven 配置文件的嵌入式服务器
- python - 如何处理给定数据集的分类数据
- mysql - XAMPP/MYSQL - MySQL 意外关闭