Crystal (not so) Clear

[rowyn]

I am not a computer geek. I have, for decades now, fallen into the category of "talented amateur". I am just good enough with computers that people who are befuddled by them think I am an expert, when in fact, I don't really know what I'm doing either.

So I'm at work, and I'm trying to use Crystal Reports v. 8.5 to do a report on our database of all the variable-rate loans that have a floor (a minimum interest rate that the loan cannot drop below) and all the ones that don't.

And you'd think this would be simple, but since it involves Crystal and the insanely complicated database structure that our banking software employs, it's not.

I set up a formula. It's a very simple formula. "If FLOOR_FIELD is greater than 0, then the loan has a floor. Otherwise, it doesn't."

Crystal cannot parse this formula correctly, however. In Crystal's world, there are two possible types of data that FLOOR_FIELD can have: it can hold a number, or it can be blank. This is fine with me: if the field is blank, it's the same as zero to me.

But it's not fine with Crystal. In Crystal's mind, if the field is blank, it cannot look at it in a formula. So when Crystal looks at my formula, it sees "If FLOOR_FIELD" -- and it doesn't get any farther. It's already thrown out every loan where FLOOR_FIELD isn't completed.

And I have banged my head against this all morning, because it seems like there just HAS to be a way that I can tell Crystal "BLANK = ZERO". But I can't figure it out. If this were a real programming language, I would try to trick it with something like:

HAS_FLOOR = NO
IF FLOOR_FIELD > 0, then HAS_FLOOR = YES

In which case, Crystal would have set my HAS_FLOOR variable correctly before it got to the verboten FLOOR_FIELD of doom and averted its eyes ("Aieee! The blank field, it hurts us! Make it go a-waaaay!")

But Crystal is not a real programming language, and I've never even been trained how to create reports in it. I've been fumbling my way around in it for a year and a half and have eventually worked out assorted features. But my "HAS_FLOOR" is a formula and not a variable, and I have to get that formula, at some point, to look at FLOOR_FIELD, and once I do, Crystal runs off screaming into the night with a third of my loan portfolio.

I can get it to show me all the loans that have blank FLOOR_FIELDs by telling it "Just print all loans, but sort them out by HAS_FLOOR". In which case it sorts "Blank" into one block, "0.00" into another block, and "> 0.00" into the last block. But I cannot, for the life of me, figure out how to tell it "Just put the blanks and the zeroes together, you stupid program!"

I wound up exporting the thing to Excel and then adding them manually. But I am annoyed by this step. The reason that so many of my board reports are such a pain to do is that they're all ancient kludgy things that require this kind of silly manual massaging. Crystal Reports is a powerful tool. It's much more powerful than the tools I'm using in Excel to do this process. So why can't I get Crystal to do everything for me? Then the next time I want this report, I can just push a button and, bing! There it is.

But no, I am stuck with Yet Another Not-Quite-Right report.

Piffle.

So ... um ... any of you real techie-types know what the proper solution is?

Comments

[shockwave77598.livejournal.com]

I'm not familiar with the specific language, so it'll be pseudo-code.

HAS_FLR=FALSE
if NOT field_blank then // this eliminates all the blank fields
{
if field>0 then
{
HAS_FLR=TRUE
}
}

[garyamort.livejournal.com]

Why not just do a mass update of the database and set floor field to 0 if it is blank.

Than you can do your query.

Otherwise, I always look for the "show SQL option" and then see if I can modify it.

Which reminds me, SQL basically has that exact problem with nulls. If it tries to treat them as numbers, the database either always returns a positive result or negative result for Nulls, you have to do some tricky query building in order to get it to work right(and it varies from database vendor to vendor). So I wouldn't blame Crystal Reports, it's SQL that causes the problem, Crystal Reports is just reporting it.

[sheepdog.livejournal.com]

*HUGS*
Sounds like the stupid dispatching program my company payed tens of thousands of dollars for. >.<

[tuftears.livejournal.com]

Any chance you can do something like 'if HAS_FLOOR is not NULL and HAS_FLOOR > 0'?

[zaimoni.livejournal.com]

Do you have the authority to construct temporary tables?

Say:
* copy Master Data to Dopplegänger Data Temporary Table
* Smash NULL to 0.00 in Dopplegänger Data
* Do the report on Dopplegänger Data
* Delete Dopplegänger Data