"Do not send emails for empty reports" - how to make it work when sub Dataset is empty

Use case description

When scheduling report jobs or selecting to run report in background you specified email addresses to which output files should be sent. You have also checked the "Do not send emails for empty reports" option to prevent any emails when report output returns no useful data. However recipients still receive emails even when report is empty.

Cause

This commonly happens when a sub Dataset is used in a report. Sub Datasets are common with Charts and Table components. By default, JasperReports library has no mechanism to identify whether or not sub Dataset query returns any data and only checks main data set.

Solution for Ad Hoc reports

In latest version of TIBCO JasperReports^® Server (6.4.* and newer) this issue should no longer exist. If you are getting empty reports when scheuduling reports based on Ad Hoc Views, there are two possibilities:

• you are using an older version of the application

• report data is filtered out with interactive component (such as column-level filters in a Table) and not using execution time filters. The application has no mechanism to indicate to Scheduler whether or not data has been filtered out for interactive component. If there is any data returned at the execution time, the Scheduler treats the report as non-empty.

Solution other reports (created in tools like TIBCO Jaspersoft^® Studio)

The solution involves using a custom property in the report to signal that from and end user perspective, the report should be considered empty. From TIBCO JasperReports^® Library perspective, the report is not empty because it actually contains a page with elements (such as a title). It displays something.

But they are empty from an end user perspective, because they do not contain any useful information.

Note that from the program perspective, we cannot say if a report is empty, if it actually contains some elements on a page.

We have to be told it is empty, from a logical perspective, because we cannot asses this programatically.

The custom property mentioned above is this:

The important part is that since TIBCO JasperReports 6.3.1 you can set an evaluation time for property expressions. So to prevent reports from being emailed when Table data source is empty, we have to follow these steps:

1. Lets say you have a report with one Table that uses sub data set. At the main report level create a variable which we will use to store the number of rows returned by sub data set query. Lets call this variable "dataset_record_count".

2. At the Table level, open Dataset properties and add a Return Value. Pass the value of the built-in REPORT_COUNT variable from sub data set to dataset_record_count variable you defined in step 1.

3. At the main report level add custom property com.jaspersoft.jrs.export.report.empty. The value of this property should be an expression which returns TRUE when dataset_record_count variable equals NULL, and FALSE if it has any values. The following should work:

   $V{dataset_record_count}==null?"true":"false"

4. IMPORTANT: set the evaluation time of the property expression from step 3 to "Report".

5. Publish the report to TIBCO JasperReports Server, schedule and set parameters in a way that sub data set returns no values. Specify email addresses to send the output to, and check the 'Do not send emails for empty reports' option.

Attached is an example report built on sample Sugarcrm data source. The report demonstrates the use case described above. Steps to run:

1. Import into TIBCO JasperReports Server. It will end up under the /Public folder

2. Create a scheduled job or select 'Run in Background'

3. To get report output with values, specify a valid value for Industry parameter (e.g. "Transportation"). To get an empty output specify any random string for the parameter.

4. Add a list of email recipients, check the 'Do not send emails for empty reports' option and run the job.