Email when no data in the Crystal Report 1

[cmmrfrds]

I have a Cyrstal Report that runs daily and compares 2 tables where the record counts should be equal. When there is missing data I want to email the report. Is there a way to detect that the report found data (I can do this) and then email only when data was found. How to email conditionally.

 

[synapsevampire]

Probably.

SInce Crystal Reports doesn't do automated emails, you'd need to supply the tool you're using to do the scheduled emailing, if in fact you're using one, and make sure that you supply your Crystal or whatever tools version.

For suggestions check out:

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

Or you might use Crystal Enterprise/Crystal Reports Server/BOE, depending upon your Crystal version.

Again, pelase remember to post your software version with any post about any software product.

-k

 

[cmmrfrds]

The report will be run on the BOXI Enterprise server version 11.5. The Crystal Report is BOXI version 11.5.
How would this be done on the Enterprise Server?

 

[MJRBIM]

You would still need one of the third-party tools.

There is not a native functionality to do this on BOXI Enterprise server version 11.5

 

[Harinmenon]

I am not sure if you could somehow define that condition as an event, which then could be used to event-trigger the report using scheduler.

Thanks,
Krish...

 

[deb13]

You should be able to use report alerts - you should be able to count the number of records returned which don't have a match in the other table and then set your report to alert you if the number of records found >0

You can then set the email alert notification in Enterprise to alert if the condition number of records found >0 is met

 

[Harinmenon]

How do you setup an email alert notification? Could you please elaborate....

Thanks,
Krish...

 

[deb13]

Set up your alert in the report, publish the report to Enterprise, then on the schedule tab, choose alert notification. Enable alert notification and then configure the email options to use when the alert is triggered. You will also need to set which viewer to use - I use the default %SI_VIEWER_URL%

Then schedule the report to run as often as you wish. If ever the alert is triggered, the email will be sent out to whoever you have set it to go to.

 

[cmmrfrds]

Thank you deb13.

I found how to setup the alert. What do you normally use in the condition for the alert event? I tried to check a field in the record but it seems to always trigger the alert.

 

[deb13]

I would do two reports to compare two tables:

1. Table A left outer join Table B to discover where a record exists in Table A but not in Table B
2. Table B left outer join Table A to discover where a record exists in Table B but not in Table A

(you may be able to do this in one report with a full outer join but I’m a bit weak on full outer joins, they never seem to do what they are supposed to do for me)

In each of the reports, put the fields tableA.comparefield and tableB.comparefield:

In report 1, you should get some nulls in tableB.compare field
In report 2, yu should get sum nulls in tableA.compare field

These show your records which do not match

I would put a boolean in for each record which has a null and do a summary adding these up which will give you the number of relevant records – e.g. in report 1 – {@relevantRecord}=if isnull(tableB.comparefield) then 1 else 0 then sum({@relevantRecord})

Set your report alert condition in report 1 to be where sum({@relevantRecord})>0 – this should ensure you only trigger the alert if it is returning nulls in the left outer joined table.

Give that a try and let me know how you go on.

Kind regards

Deb

 

[cmmrfrds]

Thank you.

What I had missed was going into the CMC to setup the Notification.

 

[deb13]

good - glad I could help