I Are Dumb

[rowyn]

Or maybe it's Crystal Reports, not me.

I run into this problem now and again, where I need to do a report on a database field that might or might not have actual data in it.

As an example, starting with a report that shows the value of Funfield in all records:

Name Funfield
Boo Foo
Goo Ooo
Joo Due
Noo

Now I modify this report to show all records where Funfield = Foo.

Name Funfield
Boo Foo

Next, show all records where Funfield != Foo.

Name Funfield
Goo Ooo
Joo Due

Why isn't Noo on this report?

If I try isnull({funfield}), I get no results:

Name Funfield

Now, say I do a formula named "NoFun" and define it as "if {funfield} = Foo then true, else false" and display it on my original all-results report, I get:

Name Funfield Nofun
Boo Foo True
Goo Ooo False
Joo Due False
Noo False

But if I try to get sneaky and ask for all results where Nofun is False:

Name Funfield Nofun
Goo Ooo False
Joo Due False

... Noo's still missing.

Anyone out there smarter than me that knows (a) why this happens and/or (b) how I can get around this? I'm using Crystal v.11.

... and preferably short of redoing the report in Excel, because it's not this simple a report. ;_;

Comments

[telnar.livejournal.com]

If you can use raw SQL to generate the report (perhaps by editing the SQL generated by Cyrstal before using it), you could probably fix the incorrect handling of the NULL value. Is sounds like what you want (in ANSI SQL) is:

Select ... From ...
Where (Funfield is NULL) or (FunField not in (70, 80, 90))

and Crystal is mangling the interpretation of that expression.

I've never used Crystal Reports, but if there's a way to see what SQL it is generating to retrieve the data, you can better understand the nature of the bug.