Email Someone when data not available 2

[SimonPetherick]

Hi,

I'm not sure if anyone is going to be able to help me but here goes:

Our database is constantly being updated with data every 30 minutes. We also publish our reports using Crystal Enterprise.

I have written a simple report that checks whether data has registered against a certain site within the past 1.5 hours. To check whether the data is in, I have included a "Total Count" on the data so if there is no data, the count is 0.

I have set this report as a scheduled report (every 30 minutes) in Crystal Enterprise. When there is no data against a site (ie the count is zero) I need to email someone warning them that there may be data issues. Obviously I'd like to do this automatically.

Does anyone have any ideas as to how I can overcome this problem.

Thanks,

SP

 

[Naith]

It is possible to write to email in Crystal Enterprise by simply setting your SMTP options to reflect your email server in the Crystal Management Console.

The trouble with your count proposal is that if there is no information to count, the count will be null, as opposed to zero.

It sounds like the sole purpose of this report is to provide an alert functionality. Like, no one will read it even if there has been data inserted into the database, right?

If that's the case, the way you can get it to email only when relevant, as opposed to all the time, is by forcing the report to failover if there's anything to count.

Use a formula to force something like a division by zero error like this:

If IsNull(Count({CoreField}))
Then 0
Else 1/0

You may need to be a bit more subtle in your division by zero part. I don't have Crystal Reports here to test, but the formula may highlight a blatant 1/0 when you try to save out. Something like X/(X-X) may suffice.

You can base another formula to display an error message based on the above formula being zero.

When you promote to Enterprise, whenever there's data, the report should generate a failed instance, which won't get emailed out. But if the count is null: email.

All the best,

Naith

 

[crystalreporting]

Yikes - or, I presume your report is looking for activity with a date or time range, and that this is probably being done in the selection formula i.e. I'm thinking your report looks for any additions or changes within a certain time frame, and in some cases there will be none, so the fact that Crystal finds no records gives you your null.

If you removed your criteria, it's likely that there would be some records there from a prior time period, and then you would not have a problem with the null - with me so far?

OK, so we remove the selection criteria, that fixes our null problem, but now we're finding all activity, when we only want the last 30 minutes or 60 minutes or whatever. So, create a formula in your report using an if clause that only increments when it finds records in the time frame you're looking for, and add that up.

Phew - I hope that's what you were talking about - if not I completely missed the boat!

 

[IdoMillet]

One option is to use a UNION query as a data source.

The UNION would combine your SELECT with a SELECT of a single dummy record. Then, create a selection criterion of Count being exactly 1.

This is an interesting question so if the above is not clear enough or if you want to discuss other options (I can see two other ways of achieving this), feel free to call.

Cheers,
- Ido

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

http://www.MilletSoftware.com