How to Avoid blank report to be sent via CE scheduling 5

[Lidiana]

Hi,

I am using CE 11 (now should call BO Enterprise 11) to schedule many reports that runs on daily base to list the exception records. Since these are exception records, so sometimes, it will return 0 record and send user a blank report. It is kind of annoying to have to open the email and then open the attachment only to find nothing there.

Does anyone know if CE 11 having the feature to have the central setting where allow you to stop sending the email if the report does not contain any record in it. Event might be able to get what I need. But we have so many reports, it is not convenient to set one by one.

Thanks in advance!

Diana

 

[Madawc]

I can offer a work-round, so the report sent is not blank. Have a section saying 'no details found' or similar. Right-click and give it a suppression formula, something linke

not isnull({your.acc})

, a field that will never be null if there are record there. Then the user at least knows what's happening.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[MJRBIM]

Do you send your reports as e-mail attachments (eg. PDFs) or just as URL links to your BOE-XI site...?

If you use URL, you could set an ALERT in the report for any record count greater than or equal to 1 (one) record. Then in the CMC for BOE-XI, you can enable the "Alert Notification" to send the e-mail with a URL link to the report anytime the Report Object's ALERT is triggered.

That would ONLY send the Alert Notification E-mail with the Report Instance URL if there was content in the results of the exception report.

Unfortunately, this won't work with e-mail attachments (eg. PDFs).

I have sent "Conditional E-mail Attachments" into the BOBJ product team as one of our WISHLIST items - but I haven't seen anything or had any feedback from them to date.

 

[Lidiana]

Thank you MADAWC and MJRBIM for your reply. Yes, I am sending reports as email attachment, that's why it is bit annoying to open the email and attachment, find no records in the report. I did not send URL, sending report to user will be easier for them.

MJRBIM, if you have heard anything from BO's product team, please let me know. I think they should add this feature in their future release as this is very value added solution to users.

 

[IdoMillet]

If you need this functionality immediately, there are 3rd-party Crystal Report Managers (see list at:

http://www.kenhamady.com/bookmarks.html)

that can do this.

In the case of my Visual CUT software, you simply use a -e instead of -E flag in the scheduled command line to indicate that exporting/emailing/printing should be aborted if zero records were selected.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[mocgp]

I haven't tried this but do any of you know if the File/Report Options/Suppress Printing If No Records option would have any affect on the (blank) scheduled report?

 

[MJRBIM]

The "Suppress Printing If No Records" option attaches a totally BLANK page PDF (no Header/Footer, etc)...which is probably more confusing than sending a report with no data.

 

[sbiowa]

I just found a way that works and is easy. I put a report header on the report and surpressed it if one of the data items on the report was null ( a field that was never null if the report returned any data). I set the New Page After option on the report header.

Then in Crystal Enterprise Manager on the Format tab, I set the report to send pages 2/100. Now the report fails if there is only one page. That means that it will not send a blank report.

 

[bessebo]

I do this using events within Crystal Enterprise. I set up a file-based event within Crystal Management Console. Then I have a SQL stored procedure that I wrote that looks out in the database and determines if there will be records that will satisfy that report. If I find records I use the cmdshell routine to actually set the event or just perform a rename of the file to whatever I set the file-based event file to. In other words, I typically have a file out there called "file.old". To set the event I just rename it to "file.txt".
I then set up the report as a recurring report at a specific time at night and have it set to kick off before "Set Events" SQL job runs and I schedule it With Events. When the report kicks off say at 9:00 it will not run because the file is still file.old. At 9:30 when the "Set events" job runs if it finds data it will rename the file to file.txt. Since that report was waiting for the event to be set it will now kick off and run. If the "Set Events" job did not find any data then the report will not run and will run on the next night.
The last thing that you have to remember to do is that after the report runs since the file is still called file.txt you have to have another job set up that will rename the file back to .old before the "Set Events" job runs the following night. So what I do is this:

8:00 each night- Run a job that re-sets the file to .old
9:00 each night - The recurring report is set to run based on an event.
9:30 each night - Run a job that sets the event (renames the file to .txt if there is data that satisfies the report)

It is working like a champ and you can then build on this framework by adding more events that are tied to other reports. I just implemented this last week. If you have any more questions please let me know. I can send you sample code if you like...

Regards,
Bessebo

 

[Lidiana]

Thank you everybody for share your experience with me. I will have my teammate tried them out when they get chance. And I will post the result here and let you know which one works for us.

Once again, thank you all for the contribution.

 

[tubbers]

Bessebo,

I would be interested in seeing your code. I have been challenged with doing something similar and I haven't really had enough experience to get it started on my own.

 

[bessebo]

Tubbers,
I am travelling in San Fran at the Business Objects conference this week. I'd like to post my work email address but it is not recommended. When I am back at work I'll try to post the basic commands that I use within the stored procedure that renames the txt files that trigger the events.

Regards,
Bessebo

 

[tubbers]

I would appreciate that!

 

[rhinok]

bessebo, I too would be very interested in seeing your code!

On a side note, I think you attended my session on SQL Expressions judging by the names on my feedback!

I also saw Ido and MJRBIM there, as I have for the past few years!

~Kurt

http://www.acuityreporting.com

 

[bessebo]

Yes Kurt, I was in your SQL Expressions talk and I came up and spoke with you on the podium afterwards. It was a very worthwhile talk. I'll try to get you that code and will probably just send to your email address.

Regards,
Bob