It's a problem that's been plaguing me ever since I took over the Crystal reports. I work in the loan department at Toddler Bank, and from time to time, people want reports that show certain loans along with the collateral (car, house, CD, whatever) that secures those loans.
And this is where Crystal and the bank database clash in a horrible, fiery inferno. Because the field that show that collateral isn't the same on every loan. If I tell Crystal "look here for it" and there's nothing there, Crystal choked and would omit the loan entirely.
At first, I thought the problem was that the field was blank, and therefore the solution garyamort pointed me to -- set the option to have all NULLs converted to default value -- would work. But no.
The trouble is that on the loans that don't use the regular collateral field, the whole database that the field would normally be in doesn't exist. The field isn't just blank; it's not even there.
However, we've got reports done by better Crystal users than I (people with actual training and stuff! Imagine that!) and in two of these, they managed to get the reports to work. Loans with the description in one place would show it correctly, and with the description field in another, it would show that. It was like magic!
And we have many, many, many other reports, also done by better Crystal users than I, where the report just drops any loan that doesn't have the field where it's supposed to be.
Anyway, I took it into my head that I Would Fix one of the more important of the broken reports. I would make it work the way it was supposed to! I would steal the necessary code from one of the working reports, and that would solve it!
So I stole the formula from one of the working reports, put it into my broken report, and ... it still didn't work.
All of these reports are extremely complicated, with several subreports and many formulas involved in each one. All right,let's make this simple. I'll write a new report that just shows me the loan number and collateral for one customer that I know has some problem loans. My new report, as expected, doesn't work.
I went back to the working report, and began methodically tearing it apart. My reasoning: I'll keep stripping it down until it looks just like my new report. When I get to the point where it's broken and not showing the problem loans, either, I'll know that whatever I just removed was the necessary bit.
Except that the working report refused to break.
Maybe it's a setting somewhere.
I looked at all the setting. ALL OF THEM. I made them all the same on both reports. Both reports were identical in the databases they used, the links they showed in Crystal, the setting they had filled in, the selection criteria they used.
Except that one report worked and the other didn't.
All right. telnar and garyamort had both mentioned that Crystal reports generate SQL code, which is what does the actual query. I know nothing about SQL. I don't even know how to find the SQL code that Crystal makes. But I will hunt it down, and I will compare the two SQL queries, and I Will Find The Difference.
And I did.
My new report joined all my databases with "INNER JOIN" lines. This is, so far as I can tell, the only way that Crystal will join databases. (The "Visual Linking Expert" displays the two reports' links in identical manners). HOWEVER, the working report used "LEFT OUTER JOIN".
I can only guess at what "INNER" and "LEFT OUTER" means to SQL, but what it means to me is -- if I use the latter, my report will actually work.
While I was at it, I cleaned up the formula that picked which collateral field to display for which type of loan so that it'd work better, too. Bonus points! All right, it took me several hours but I got it done! All by myself! I was so proud of myself. I am so smart!
And then five minutes later, a customer called and asked me if her loan was paid off. I took one look at the screen, said "sure" and hung up. And then screamed at myself because it's not and this is the sort of bone-headed mistake that only someone who knows nothing about our system and/or loans would make, and I so know better. Not only that, two days ago I had personally sent a fax to this same woman to explain that her loan wasn't paid off, and why.
For every moment of brilliance, there is an equal and opposite moment of unfathomable stupidity.