0

I am looking for a tip, trick or hack that will allow a return of an empty byte array, an out warning flag or some other indicator to let the caller of ReportExecution2005.RenderReport() be aware that no data was returned for the report.

Ross Bush
  • 14,648
  • 2
  • 32
  • 55

2 Answers2

0

Use visibility to mimic this. Check count of your dataset and if its 0 dont show anything and then also create a text box that says no data available or something like that and set the visibility to only show when count is 0. Hope this make sense.

B Woods
  • 580
  • 3
  • 7
  • 21
  • Thanks for the info, however, this will not work for my case. I do not need to be able to show or hide report items based on an empty dataset. I am trying to figure out if it is possible to return something in the ReportExecution.ExecutionInfo or the warnings out parameters of the ReportExecution.Render() function to indicate that my key dataset returned no data. I am in a situation where I am sending notifications and I am trying to lessen the burden on the service agent by doing a count check/report render in one step. – Ross Bush Feb 19 '16 at 19:48
  • Oh sorry I misunderstood what you were trying to do here. Could you jsut encapsulate the function in one that you create? – B Woods Feb 19 '16 at 19:51
  • Yes, and I have. The problem is that I need to call pretty much the same stored procedure as the one the report uses in order to get an accurate record count. So instead of performing a long check for record count then calling ssrs when there is data, just call ssrs and be done with it. However, I need to be able to know if ssrs returned no data. I'm kind of stuck and going to backtrack to find of a faster way to get the record count. – Ross Bush Feb 19 '16 at 20:33
  • I don't get the problem. Perhaps if you shared your current code that you use to execute the procedure/query and render the report, I'd understand better. As soon as you have a dataset you can call `.Any()`which should give you the wanted information, this can be called even before sending it to the report. Why exactly do you want the caller to know if the report will have data, what do you want to do with this information other than let your user know that the report is empty? Instead of placing the rendering in a void method you can have a return statement to pass down any info you want. – Oceans Feb 22 '16 at 07:32
  • I have a stored procedure that SSRS report uses to render. I need a count to determine if the report should render. The content of the report is stuffed into and email and queued. These notifications are scheduled and by a service agent to be sent out so efficiency is important. The stored procedure used to count is not that efficient. I would like to use the result of the render so I can perform the count/render in one action. If there is nothing to report return empty, however, the function is part of the ssrs web service and I am calling the Render method...perhaps a custom extension – Ross Bush Feb 25 '16 at 04:21
0

I believe that I know what you're doing now, but correct me if I'm wrong.

You created the dataset and the required parameters in the report and set the stored procedure there. Then you just pass the parameters from code and then you just render the report which causes SSRS to automatically fill the dataset with respect to the parameters.

This is a good approach but this causes you to have no knowledge of the datasource until the report is rendered.

I believe the solution to your problem is to generate the datasource outside the reporter, so executing the procedure from code. Then you have access to any information about the dataset before sending it to the reporter. So if the procedure returned no data this allows you to not render the report at all. And when you do render the report, you won't gave to execute the procedure anymore (efficiency).

  • The easiest way to achieve this is to use the Local Reporting Services (often refered to as rdlc).

    There is no need to use a shared datasource because you have the wanted dataset already. You can just pass the datatable to the reporter and it will render correctly without needing to execute the procedure anymore.

    There are many tutorials that should make this easy for you, here is a simple example: Using a Local Reporting Services 2008 Report with an ADO.NET Data Set

  • If you really insist on using the older Reporting Services then it'll be slightly more complicated to setup but it's still possible. Here is detailed tutorial: Using an ADO.NET DataSet as a Reporting Services Data Source

Before rendering the report, you simply fill your dataset by executing the procedure. And check whether or not you wish to continue rendering the report.
Example: 

SqlDataAdapter daVendor = new SqlDataAdapter();
daVendor.SelectCommand = cmd;
DataSet dsVendors = new DataSet();
daVendor.Fill(dsVendors);

if(dsVendors.Any()){
//Render the report (passing the dataset to the reporter) and attach it to the mail.
}
else {
//Don't render the report and create corresponding message to send as email.
}

I strongly suggets using the Local Reporting Services. You don't want to add extra functionality anyway, as you're only sending it as attachment to an email.

I hope this helps you along, if you have any more trouble just leave a comment.

Oceans
  • 3,445
  • 2
  • 17
  • 38
  • Thanks, I am giving you the 200 points for having me think outside the box. I did not think about sending in the dataset to the report. This would defiantly solve my problem. As for the local report, I don't know if a local report could render and deliver the contents as a byte[]. – Ross Bush Feb 25 '16 at 14:52
  • You have several render options, like PDF and Excel for example. https://msdn.microsoft.com/en-us/library/ms251839(v=vs.90).aspx – Oceans Feb 25 '16 at 15:14