This is my first time using CR, so I apologize if this is obvious...

I'm getting information from a SQL Server 2000 database using a fairly simple query (just a few inner joins). There's one field in a table (I'll call it AnnoyingField) that has possible values of NULL, 0, or 1, and that row should only show up in the report if the value is NULL or 0. I put IsNull({AnnoyingField}) OR {AnnoyingField}=0 into the selection formula and it seemed to do the trick. The problem is that sometimes there are two rows in the underlying data, one with the NULL value for AnnoyingField and one with a 0, and otherwise identical. This is intentional (I didn't design this DB of course), but in this particular report, it needs to show up as one row. All the other values are the same, and AnnoyingField isn't even displayed in the report, so it just looks like a duplicate entry.

Normally, I'd accomplish this with a SELECT DISTINCT, but that doesn't work here, because Crystal Reports adds AnnoyingField to the list of fields being SELECTed from the database, even though that's unnecessary. The current query is

SELECT DISTINCT firstname, lastname, etc, AnnoyingField FROM tables WHERE AnnoyingField IS NULL OR AnnoyingField = 0

and it should be

SELECT DISTINCT firstname, lastname, etc FROM tables WHERE AnnoyingField IS NULL OR AnnoyingField = 0

Is there any way to do this? Thanks for any help.

-Eric

Or, you could set up your grouping so that it's at a level above the AnnoyingField and put your data in the group header instead of in the details.  That way you would get your distinct data.

Another way is to set up a suppress formula on the line where your data resides that looks somewhat like this:

{table_with_AnnoyingField.key field} = previous({table_with_AnnoyingField.key field})

I've used them frequently!