rowyn: (studious)
[personal profile] 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?

Date: 2004-12-23 11:37 am (UTC)
From: [identity profile] 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
}
}

Date: 2004-12-23 01:05 pm (UTC)
From: [identity profile] 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.

Date: 2004-12-23 07:18 pm (UTC)
From: [identity profile] garyamort.livejournal.com
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!

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)
From: [identity profile] garyamort.livejournal.com
Hey, it might actually BE crystal reports:
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.

Date: 2004-12-23 01:42 pm (UTC)
From: [identity profile] sheepdog.livejournal.com
*HUGS*
Sounds like the stupid dispatching program my company payed tens of thousands of dollars for. >.<

Date: 2004-12-23 01:51 pm (UTC)
From: [identity profile] tuftears.livejournal.com
Any chance you can do something like 'if HAS_FLOOR is not NULL and HAS_FLOOR > 0'?

Show SQL again

Date: 2004-12-23 08:39 pm (UTC)
From: [identity profile] telnar.livejournal.com
It's likely that once you see the SQL for the query, that you will find an error in the join expression or where clause which is causing those records to be dropped. E.g. a where expression like "Where Floor_Field > 0" would automatically discard records with a null value in the floor_field.

Date: 2004-12-23 04:42 pm (UTC)
From: [identity profile] 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

June 2025

S M T W T F S
12345 67
891011121314
15161718192021
22232425262728
2930     

Most Popular Tags

Active Entries

Style Credit

Expand Cut Tags

No cut tags
Page generated Jun. 12th, 2025 05:04 pm
Powered by Dreamwidth Studios