Simple WHERE clause filter not working

[kj27]

I'm actually developing a simple report using Version 8.5. This report goes against a SQL Server back-end, and has a grouping on Account ID's. The selection criteria for the entire dataset essentially goes against a table with 6 status fields. So in my group selection formula, I have simply added:

(<table_name>.status_1 = "Y") or (<table_name>.status_2 = "Y") or (<table_name>.status_3 = "Y")...

This generates the correct WHERE clause when I look at the SQL statement. And if I run the SQL statement directly, it generates 35 results. But in Crystal reports, only 26 results are brought back.

It seems that if I put the status_1 condition, then all records where status_1 = "Y" (regardless of whether other stauses are "Y") are brought back.

But if I physically put status_2 clause first (then the others) in the filter formula, only the records matching record_2 are selected.

I can't figure out what's missing here. Any help in this regard would be greatly appreciated.

 

[Turkbear]

Hi,

When you look at the SQL in CR, are all the WHERE clauses being sent to the database or is the filtering done by CR after the records are returned?

Are there NULLS in any of those fields in some records? If so, test for them first since CR stops evaluating formulas as soon as it finds a NULL..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kj27]

Hi Turkbear,

Thanks a lot for the info. You're right - one or more flags per record can be NULL. So I've done this, and it seems to be working:

Crystal Reports 8.5:

Go to: File > Report Options
Check on: Convert NULL Field Value to Default

Thanks!