Why, Crystal? Whyyyyyy?
One of the most annoying things about working with Crystal is having it give you the same results multiple times when you only want them once. Say, for example, I want a report that gives me all the payments made over $1000, plus the total balances on those loans.
Crystal's default response here is to give me each payment and each loan balance over $1000 ... even if some of those payments are on the same loan. So:
Loan # Balance Payment Amt
123 10,000 1,100
234 20,000 1,200
123 10,000 2,000
Total # loans: 3 Total Balances: 40,000 4,300
My total payments makes sense, because that's all the individual payments over $1000. But the total number of loans and total balances doesn't, because it's counting loan 123 twice.
But! Crystal has a solution for this! DistinctCount, which will look at field in a record and only count that record once per time it appears in the report. Hence:
Loan # Balance Payment Amt
123 10,000 1,100
234 20,000 1,200
123 10,000 2,000
Total # loans: 2 Total Balances: 40,000 4,300
Now my count of loans is right! Except ... the balance total is still adding 123 twice.
Because DistinctCount only does counts. It will total the number of times unique values in a field occur. That's all it will do. It won't let you sum only unique values. It won't let you average them, or find the median value, or, well, anything else you might conceivably want to do. You can count.
WHY CRYSTAL WHY? Why could this not be a boolean value of "Distinct", at least? WHY?
So I put in this horrible little bit of code into one report to try to retroactively turn the count into a boolean so that I could use it to make a unique sum, which is a pain in the neck because Crystal DOES NOT WANT to look at anything that's happened in previous records on a report when it's reading a current record. Crystal is zen, with no past and no future: all there is is now. Except I know you can tell because how else could DistinctCount exist you little ratfink of a reporting tool?
And it seems to work, on that report.
Now I need it on a different report, and I'm trying to work myself up to adapting it to that report. And hoping it works again.
...
I really need to learn SQL. And convince the bank they should let me use that instead.
...
One of the frustrating things about Crystal is that it can do so much that's hidden away in some submenu of a submenu and you never even think to look there until two years later after you gave up on ever getting this right and you're complaining to someone about it and she says "Oh, can't you do that with a running total that changes by field?"*
When I first learned how to use Crystal, I used to tell my boss "You can't do that with Crystal". Now I tell her "You can do that but it's REALLY ANNOYING." Or "I don't know how to do that, which is not necessarily the same as 'it's impossible', but it might be."
Maybe there is a boolean value of Distinct in there, somewhere, and I'm just not finding it. Whee. Well, writing this out didn't give me any brilliant inspirations for doing this some other way, so I guess I'll go do it that hard way now.
* This is one workaround for the DistinctCount issue, actually, but it only works if you've got the records sorted by the field you want to use as the distinct one. It doesn't work for my payment example above because I want to sort by the payment amount.