CODEWITHSUNDEEP
crystal-reports
Nate C-K
Nate C-K

Reputation: 5932

Crystal Reports row counter gives no result for empty result set

I'm editing a Crystal Report that someone else developed. I'm not very well-versed with Crystal, so I'm hoping someone can spot where I'm going wrong here.

We are using Crystal Reports 2008.

This report has a query in a subreport that gets a set of rows matching certain criteria, then counts the number of returned rows that match a certain formula. A matching row will have g_bIsMatching set to true.

The row count is incremented by a formula that looks like this:

// Counter Increment

WhilePrintingRecords;

shared NumberVar s_lMatchCount;
global BooleanVar g_bIsMatching;

if g_bIsMatching
then
    s_lMatchCount := s_lMatchCount + 1;

"The count of matched rows is now " & totext(s_lMatchCount, "#") & "."

There is another formula that returns the shared counter value after the records are printed. This is used to pass the matched rows count from the subreport back to the parent report. Here is its original, very simple definition (before my own changes):

// Total Count

shared NumberVar s_lMatchCount;

s_lMatchCount

However, this report has to go back an entire year, and the current DB system hasn't been installed that long. As a result, I had to incorporate some data from the legacy system. Rather than try to incorporate the old data in the database somehow, I created some parameters that allow the user to supply the legacy system data when the report is run. I add the value of the appropriate parameter to the total. This changes the final formula to look like this:

// Total Count

shared NumberVar s_lMatchCount;

// Add legacy data, which is supplied in parameters, a different one for each month.
if {EPISODE.END_DATE} >= #1/1/2012# and {EPISODE.END_DATE} < #2/1/2012# then
s_lMatchCount := s_lMatchCount + {LegacyCount_2012_01};
if {EPISODE.END_DATE} >= #2/1/2012# and {EPISODE.END_DATE} < #3/1/2012# then
s_lMatchCount := s_lMatchCount + {LegacyCount_2012_02};

// and so on for a bunch of other months...

s_lMatchCount

This is working for me when there are results coming back from the main database. However, when the main database returns no rows at all, then the box that's supposed to display s_lMatchCount is blank.

The data ranges for my values should be triggering the if clauses here, but they don't; instead I get no results at all.

I've tried a number of variations on the "Total Count" formula to get it to display at least some kind of default value, but regardless of what I do I get no result; this makes me think that it's not running at all.

How can I get a value for s_lMatchCount even when the result set has zero rows?

Where am I going wrong? Is there some small thing I can change here, or am I running up against a fundamental limitation of Crystal Reports that clashes with the approach that I'm using to get this row count?

Upvotes: 0

Views: 1599

Answers (2)

Nate C-K
Nate C-K

Reputation: 5932

I eventually figured out what was going wrong in the report that I describe above.

The Total Count formula to compute s_lMatchCount uses a bunch of if statements that look like this:

if {EPISODE.END_DATE} >= #1/1/2012# and {EPISODE.END_DATE} < #2/1/2012# then

These statements reference a column in the database, {EPISODE.END_DATE}. If there's no data coming back from the database then obviously this is useless. My understand of what's going on is that this reference is forcing the formula to be evaluated as the rows are processed, which means that since there are no rows, it doesn't get processed at all.

I was able to fix the problem by replacing the date from the database with a parameter reference.

Upvotes: 0

Siva
Siva

Reputation: 9101

To the extent I understand the problem try below solution.

At the end of all If statements write below linw.

Else s_lMatchCount=0

OR

Else If ISNULL({LegacyCount_2012_02})
Then s_lMatchCount=0

Upvotes: 0

Related Questions