June 8, 2011 at 6:41 am
How can SSRS be configured so when a report runs an no records are retrieved. Thus the total record count at the end of the report reads "0". That the report does not get sent to via email to the customer.
June 8, 2011 at 6:55 am
AFAIk there is no setting for this within the standard subscriptions, and while you can set a report to display something different if no results are returned using the NoRows propety of the tablix region this doesn't prevent the end users receiving lots of emails with empty reports attached
If you call the reports using code you can then set it to send them off conditionally using something like this to call the subscription for the specified report, all it does it add an event for SQLAgent:
EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=CAST(r.SubscriptionID AS VARCHAR(40))
FROM ReportServer.dbo.ReportSchedule r
This way of calling reports can also be useful if you need to re-issue a load of reports and don't want to have to manually change the date settings on the subscriptions
June 15, 2011 at 12:16 pm
AFAlk
I am not able to call the report via report manager. Could you please direct me to where I can incorporate the code you have provided in order to accomplish this. A little more direction would be very helpfull.
Thank you - you response is very much appreciated.
June 16, 2011 at 1:55 am
Hi
Are you using data driven subscriptions to send the reports out? If so it is just a matter of applying a bit more logic to the query that produces the dataset that the subscription is based on. You would need to incorporate the query that forms the report's main data set to determine whether data exists for a each set of parameters that need to be executed. In the subscription dataset you would only return records of the parameter combinations that yield data. Those combinations that do not will have no record and will not produce an execution of the report and will not be emailled out.
Hope this makes sense.
Ben
June 16, 2011 at 4:49 am
Yes, that would also work and be a more straightforward way to accomplish this
As we are using Standard Edition where I am, data driven subscriptions are not available hence resorting to code to manage this situation
June 16, 2011 at 6:30 am
I am using VS 2008 R1 Professional BIDS-
MS .NET Framwork 5.5 SP1 and the reporting services configuaration tool that comes with it.
Not sure if this means that it is data drivin or not.
June 16, 2011 at 6:36 am
tony.aguanno (6/16/2011)
I am using VS 2008 R1 Professional BIDS-MS .NET Framwork 5.5 SP1 and the reporting services configuaration tool that comes with it.
Not sure if this means that it is data drivin or not.
DDS comes with sql server ENTERPRISE.
Connect to sql server and run this : SELECT @@VERSION.
I have standard version and here's what it says :
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
It should say enterprise edition right before Windows to have access to data driven subscriptions.
June 16, 2011 at 6:49 am
Thank you I do have the Enterprise ed. But I am still not sure where to go to implement what is being discussed.
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
June 16, 2011 at 6:54 am
tony.aguanno (6/16/2011)
Thank you I do have the Enterprise ed. But I am still not sure where to go to implement what is being discussed.Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
Can't help you there... I never used DDS.
June 16, 2011 at 6:59 am
You all have been very helpful sending me in the right direction - just going to have to continue to research DDS now Thanks
June 16, 2011 at 1:37 pm
Good luck 🙂
As Ben said earlier, the trick will be in making sure that the code you use for the DDS checks for the existence of valid data, so as to only populate the DDS fields for parameters that return information.
Once set up you can manage the DDS through the Report Manager webpage just as you would regular subscriptions, which makes it all a lot neater than having to resort to code based workarounds like what I have implemented (with a lot of help from SSC) at work
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply