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

[sebkha.livejournal.com]

Crazy thought: try not(Funfield = Foo).

[terrycloth.livejournal.com]

Can you specifically include blanks somehow maybe?

I Are Not Reel Programr

[rowyn]

I shouldn't've used Foo for my example, because funfield isn't actually a string field -- it's numeric. With some (but not all!) string fields that are null in the database, using something like String ="" will work.


But with this numeric field, if I look at the null fields in the actual database, they display as blank, not zero. However, in Crystal's report they show as 0:

Name Funfield
Boo 10
Goo 2
Joo 5
Noo 0

Funfield = 2 would get me:

Name Funfield
Goo 2

But Funfield = 0 just gives:

Name Funfield

No results. >.

It might work!

[rowyn]

The actual construction for the formula I'm using is something like:

{funfield} not in [70,80,90]

I'm not sure offand if Crystal has another construction for "not" but I'll take a look when I'm at work tomorrow. Can't hurt.

Re: I Are Not Reel Programr

[terrycloth.livejournal.com]

Right -- a blank cell is different than a cell with value 0 or value '0 length string'. The underlying database engine probably has special blank-handling formulas, maybe Crystal Reports does too?

Re: I Are Not Reel Programr

[rowyn]

Crystal has "isnull" but isnull({funfield}) doesn't get any results either.

Crystal also has a toggle for "force NULL values to 0", I think. I can try playing with that and isnull and =0 in various configurations, and see if I get anything.

[the-vulture.livejournal.com]

'Master Reset' - That's what I'd do if it was a cellphone. Seriously, it looks like a bug.

[postrodent.livejournal.com]

Whooa, time snap! Would you believe that one of my very first jobs was writing and formatting Crystal Reports, in 1995 or so? My mom got me the job, since I'd never seen it before then -- rather naked nepotism. Anyway, I can't help you, but I do sympathize.

[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.

IEEE single precision programmer

[tetsujinnooni.livejournal.com]

(real programmers use patch panels to hotpatch running binaries, don'tcha know....)

for the case of Oracle behind Crystal:

nvl(Foo,0) will substitute 0 for Foo if Foo is null

MS SQL Server uses isnull(Foo,0) in the same fashion.

Oracle will treat a zero length string as null.

Hopefully those three items will help! Feel free to direct mail further questions, I'm looking at it from a DB engine perspective rather than the reporting tool obviously...

[bburnell.livejournal.com]

I am certified in Crystal Reports 8.5, but alas I have been banished to SQL Reporting Services by my boss. Wish I could help. *sigh*

Try the CR forums at tekteps.com!

Hope that helps!

Re: I Are Not Reel Programr

[bburnell.livejournal.com]

If I remember correctly, you have to check a bunch of things in Crystal, like isnull and len(trim(x)) = 0, etc...