Crystal (not so) Clear
Dec. 23rd, 2004 01:00 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
rowynI 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?
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?
![[identity profile]](https://www.dreamwidth.org/img/silk/identity/openid.png){kind=small}
no subject
Date: 2004-12-23 11:37 am (UTC)HAS_FLR=FALSE
if NOT field_blank then // this eliminates all the blank fields
{
if field>0 then
{
HAS_FLR=TRUE
}
}
no subject
Date: 2004-12-23 01:05 pm (UTC)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.
no subject
Date: 2004-12-23 02:20 pm (UTC)Two reasons:
1) Per my boss, no one in the bank has authority/ability to make mass changes to the database. When we need them done, we either pay our provider or do it manually. >.<
2) I'd have to run the update on the database manually every time I did the report, to fix all new loans that had been put on the system. I'd hate to make this One More Thing people have to remember when booking loans, as this is the only instance that I know of where it's any kind of issue, and even so it's not MUCH of an issue. It just annoys me!
Thank you for the "show SQL option" idea! I haven't done anything with SQL so far, but I suspect looking at that will prove useful in the future, even if I can't make anything of it on this particular report. :)
no subject
Date: 2004-12-23 07:18 pm (UTC)To tell the truth, I would tend towards this solution for the simple reason that it could be causing drops from other reports and just not be noticed.
I try very hard to code databases so as not to have nulls in vital fields for just this reason. Ok, actually, it's because when someone discovers something weird, first I spend a couple days figuring out why the records they think they should be seeing are not showing up, than I spend a day working out a query that will show them what they want, and than I end up spending a week or so 'verifying' that this isn't causing a problem with other reports(since without checking the reports, I can't see if their having the same problem)
At the end of it all, I am highly frustrated, and just want to implement a no nulls rule. So I avoid nulls in my own databases when possible.
Huh, learn something new everyday
Date: 2004-12-24 02:44 am (UTC)http://groups-beta.google.com/group/comp.lang.basic.visual.database/browse_thread/thread/f555606d7d0e1217/a8e52b6dbc3fb166?q=crystal+reports+null&_done=%2Fgroups%3Fq%3Dcrystal+reports+null%26hl%3Den%26btnG%3DGoogle+Search%26&_doneTitle=Back+to+Search&&d#a8e52b6dbc3fb166
"In your reports setup, under the File menu of crystal reports, you have
to specify that you want to include null values...a pain in the ass, i
know, but that's the way it is..."
Unfortunately, everything that pops up on my search seems to address SQL or using Visual Basic to program Crystal Reports. Nothing at the user level.
Hmm, searching the web indicates one avenue:
File> Options> Reporting Tab
(http://www.crystaltrainingpartners.com/crystal-forum-message.asp?topicid=16)
There should be various options on that tab for "converting" nulls, for this report if you choose convert nulls to zero and than run the report, CR should treat the nulls as zeros.
Assuming I'm intuiting this correctly.
Might be easier than trying to work out how to figure out how to run SQL directly.
Though if you can figure out how to run SQL through Crystal Reports, than you can get all sorts of help since SQL is a programming language. As opposed to the bastardized user friendly crud.
My hero! <3
Date: 2004-12-30 03:03 pm (UTC)Woohoo! I am SO happy! I wonder if this will help address some of the problems we've had on other reports, where NULLs have been dropped from the report.
no subject
Date: 2004-12-23 01:42 pm (UTC)Sounds like the stupid dispatching program my company payed tens of thousands of dollars for. >.<
no subject
Date: 2004-12-23 01:51 pm (UTC)no subject
Date: 2004-12-23 02:15 pm (UTC)It's not a matter of Crystal interpreting FLOOR_FIELD nulls as bad data. It's not coming back with "This loan has a null value in FLOOR-FIELD and I'm saying it's got a floor". It's coming back with "FLOOR_FIELD is a null value so I'm going to say the whole loan doesn't exist for purposes of your query."
Show SQL again
Date: 2004-12-23 08:39 pm (UTC)Re: Show SQL again
Date: 2004-12-30 03:05 pm (UTC)no subject
Date: 2004-12-23 04:42 pm (UTC)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