Report by Email only if Row count > 1 1

[markcobb]

I am trying to create a report that will only mail to personnel when the report returns >= 1 row of data. By this I mean that the report may not have an instance for a number of day and then 5 in one day, on that one day the report shgould be sent out. I have tried creating files using sql by obviously this will also generate a blank file etc. Can anyone tell me a better way

 

[Naith]

I'm assuming that if the report brings back 0 rows, then you're not interested in having the report's output at all.

If that's true, then what you could do is set up an If condition to count a mandatory field, and if the count = 0 then suicide the report by making it fail over.

I'm thinking of something like:

If count({CompulsoryField}) < 1
Then
1/0

which will force a Division by Zero error, so the report will fail, so no email. The danger with this solution is that you may be encouraged to be complacent when you get Failure messages, and may miss genuine failure reports.

Just remember to check out all failures.

All the best,

Naith

 

[markcobb]

Many thanks for that, I will give this a go now and see what happens.

 

[markcobb]

Naith,
I cant seem to get this to run as it always falls over with the divide by zero error regardless if the count is > 1 or < 1. I have tried putting it into a formula and into the record selection.

 

[Naith]

Mark,

Place the code in a formula, and place it in your report header. Make sure the formula is suppressed after you've finished testing.

Amend the code to include an Else clause. (I also changed the count to distinctcount, but either should be as good as the other):

If DistinctCount({CompulsoryField}) < 1
Then
1/0
Else 0

Let me know how you get on,

Naith

 

[Naith]

So much for advising without testing.

Crystal picks up on obvious Division by Zero errors before you quit the formula. I though that they were picked up at runtime - (perhaps I'm thinking of pre v8)

Anyway, if you're having this problem, then you want to use this formula instead:

WhilePrintingRecords;
NumberVar Zero;

If DistinctCount({algScenarioTemplate.OrgID}) < 1
Then 1/Zero
Else 0;

Sorry for the confusion,

Naith

 

[markcobb]

OK,
I have copied this into the report header

If DistinctCount ({Contract_PaymentDate.adcurrency}) < 1
Then 1/0
Else 0

Whereby the {Contract_PaymentDate.adcurrency}) is required in the Database.

The report runs off of the proviso that the Payment_Date = Current Date and this has been added to the Report Selection. Therefore the report should only bring back data whereby the Payment_Date = Current Date. Trouble is even with the code you supplied tucked into the Report Header I am still getting a returned file once scheduled, even though the file is blank, what have I done wrong.....this is one of those things that may well tip me over the edge.

 

[Naith]

Do you get the same situation using the WhilePrintingRecords formula in my last post?

Can you unsuppress the count so that you can see that the value of the field is 0?

Can you confirm which version of Crystal you're using?

Naith

 

[markcobb]

Naith,
OK this now does not error out everytime I try and run the report. I have placed the formula in the report header and when data is returned it show a 0.00 i.e 1 row returned. However when I run this where the row count is = 0 the report does not fall over it just returns a blank report but with my text columns etc.

I am running Crystal 8.5.2.663

So this is a step closer, all the report now needs to do is actually fall over when no rows are returned.

 

[Naith]

Insert another formula which is just Count({YourField}) and tell me what the value is. I'm trying to ascertain the status of the count when the report doesn't fail over. Also, I need to know if you're testing this with/without the email functionality turned on - as when I test this on my machine in CR, the WhilePrintingRecords formula fails over on 1/Zero.

If the new count I've asked you to create also returns zero, then post up your email address so I can have a look at the report.

Naith

 

[markcobb]

OK, I have added another
count({Contract_PaymentDate.adcurrency}) and it returns the value of 1, which is correct and in accordance with the report. I then change the data in the db so that the Report criteria was not met (i.e. I changed the date) and the report now does not bring back anything at all apart from the text fields.

I am scheduling these reports using Enterprise. I just cant understand why this will not fall over.

 

[Naith]

Have you not tested this in Crystal Reports yet - without taking it to ePortfolio?

 

[markcobb]

Yes I have tested in CR first. Refreshing the report etc each time I have changed the DB etc, and each time I have then refreshed the report and tried in EPortfolio. So to recap. I have added this to the report header


WhilePrintingRecords;
NumberVar Zero;

If DistinctCount({Contract_PaymentDate.sona}) < 1
Then 1/Zero
Else 0;

I have then added a simple count
Count({Contract_PaymentDate.sona}) to the Report Header.

The report Criteria is as follows
{Contract_PaymentDate.Payment_Date} = currentdate

So the report will be scheduled every day and only when the row count is >1 should it actually fire off and email (via Portfolio) to the end user, a sort of exception report if you like.

However what appears to be happening is the report works fine when there is a record to return e.g. the Date = Currentdate (and the whileprintrecords formula returns a 0.00), but when there is no data the report does not fall over instead it just returns the text objects I have placed in the report with no other data and the While Printing Records formula is simply blank.

 

[Naith]

Change the first two lines of the formula to:

WhilePrintingRecords;
NumberVar Zero := 0;

 

[Naith]

Honestly - I swear I must be half asleep today. The next time I give you some advice, ask me if I've tested it first.

This is the correct formula:

WhilePrintingRecords;
NumberVar Zero := 0;

If IsNull(DistinctCount({Contract_PaymentDate.sona})) or DistinctCount({Contract_PaymentDate.sona}) < 1
Then 1/Zero
Else 0;

The report wasn't failing before because the count result was null, not zero. Nulls can't be compared to integers with relational operators.

Sorry about all the faffing around.

Naith

 

[markcobb]

Thats the one Naith,
Many many thanks for all your help.