SQL Server Reporting Services 2005 - 如何处理空报表

I was wondering if it is possible to not attach Excel sheet if it is empty, and maybe write a different comment in the email if empty.

我想知道如果它是空的,是否可以不附加Excel工作表,如果空的话可能在电子邮件中写一个不同的注释。

When I go to report delivery options, there's no such configuration.

当我去报告递送选项时,没有这样的配置。

Edit: I'm running SQL Server Reporting Services 2005.

编辑:我正在运行SQL Server Reporting Services 2005。

Some possible workarounds as mentioned below:

一些可能的解决方法如下所述:

MSDN: Reporting Services Extensions

MSDN:Reporting Services扩展

NoRows and NoRowsMessage properties

NoRows和NoRowsMessage属性

I should look into these things.

我应该研究这些事情。

4 个解决方案

#1

I believe the answer is no, at least not out of the box. It shouldn't be difficult to write your own delivery extension given the printing delivery extension sample included in RS.

我相信答案是否定的,至少不是开箱即用的。考虑到RS中包含的打印传送扩展示例,编写自己的传送扩展应该不难。

#2

Yeah, I don't think that is possible. You could use the "NoRows" property of your table to display a message when no data is returned, but that wouldn't prevent the report from being attached. But at least when they opened the excel file it could print out your custom message instead of an empty document.

是的,我不认为这是可能的。您可以使用表的“NoRows”属性在没有返回数据时显示消息,但这不会阻止报告附加。但至少当他们打开excel文件时,它可以打印出自定义消息而不是空文档。

#3

Found this somewhere else...

在其他地方发现了......

I have a clean solution to this problem, the only down side is that a system administrator must create and maintain the schedule. Try these steps:

我对这个问题有一个干净的解决方案,唯一的缺点是系统管理员必须创建和维护计划。请尝试以下步骤:

Create a subscription for the report with all the required recipients.

使用所有必需的收件人为报告创建订阅。
Set the subscription to run weekly on yesterday's day (ie if today is Tuesday, select Monday) with the schedule starting on today's date and stopping on today's date. Essentially, this schedule will never run.

将订阅设置为在昨天的每周运行(即如果今天是星期二,请选择星期一),时间表从今天的日期开始,并在今天的日期停止。从本质上讲,这个时间表永远不会运行。
Open the newly created job in SQL Management Studio, go to the steps and copy the line of SQL (it will look something like this: EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='1c2d9808-aa22-4597-6191-f152d7503fff')

在SQL Management Studio中打开新创建的作业,转到步骤并复制SQL行(它看起来像这样:EXEC ReportServer.dbo.AddEvent @ EventType ='TimedSubscription',@ EventData ='1c2d9808-aa22-4597 -6191-f152d7503fff')
Create your own job in SQL with the actual schedule and use something like:

使用实际计划在SQL中创建自己的作业,并使用以下内容:

IF EXISTS(SELECT your test criteria...)

如果存在(选择您的测试标准......)

BEGIN

EXEC ReportServer.dbo.AddEvent @EventType=... etc.

EXEC ReportServer.dbo.AddEvent @EventType = ...等

END

#4

I have had success with using a Data-Driven Subscription and a table containing my subscribers, with the data-driven subscription query looking like this:

我使用数据驱动的订阅和包含我的订阅者的表已经取得了成功,数据驱动的订阅查询如下所示:

SELECT * FROM REPORT_SUBSCRIBERS WHERE EXISTS (SELECT QUERY_FROM_YOUR_REPORT)

In the delivery settings, the recipient is the data column containing my email addresses.
If the inner query returns no rows, then no emails will be sent.

在传递设置中,收件人是包含我的电子邮件地址的数据列。如果内部查询未返回任何行,则不会发送任何电子邮件。

For your purposes, you can take advantage of the "Include Report" and "Comment" delivery settings.
I imagine that a data-driven subscription query like this will work for you:

出于您的目的,您可以利用“包含报告”和“评论”递送设置。我想像这样的数据驱动订阅查询对你有用:

SELECT 'person1@domain.com; person2@domain.com' AS RECIPIENTS,
CASE WHEN EXISTS (REPORT_QUERY) THEN 'TRUE' ELSE 'FALSE' END AS INCLUDE_REPORT,
CASE WHEN EXISTS (REPORT_QUERY) THEN 'The report is attached' ELSE 'There was no data in this report' END AS COMMENT

Then use those columns in the appropriate fields when configuring the delivery settings for the subscription.

然后在配置订阅的传递设置时,在相应字段中使用这些列。

#1