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.
So ... um ... any of you real techie-types know what the proper solution is?